Portal Home > Knowledgebase > Articles Database > How to repair and optimize a massive database?
How to repair and optimize a massive database?
| Posted by awaisuk, 12-02-2012, 01:34 PM |
| Hey,
How to repair and optimize a massive database?
Database is very big and it the database needs repairing and optimizing. Running the normal mysql -r command takes very long and the process never finishes.
What else can I do to repair the database faster?
Thank you
|
| Posted by SolidJoe, 12-02-2012, 01:39 PM |
| What do you define as massive? Give us some specs.
|
| Posted by Dr_Michael, 12-02-2012, 01:58 PM |
| How many tables? How big is the biggest table?
|
| Posted by nessic, 12-02-2012, 02:09 PM |
| Try using myisamck with some buffer settings you think are in accordance with your systems memory.
I could help you with a little more info on the db.
|
| Posted by awaisuk, 12-02-2012, 02:23 PM |
| I think the total size is around 30gb.
Repairing the database using normal -r takes a long time, even though the ssh session was left on for hours, the repairing process never finished.
What is a more efficient way of repairing and optimizing the table?
|
| Posted by nessic, 12-02-2012, 02:29 PM |
| Is the whole database corrupt or some tables?
|
| Posted by Server Management, 12-02-2012, 02:32 PM |
| What exactly is wrong with the database?
|
| Posted by awaisuk, 12-02-2012, 03:21 PM |
| How can I check which tables in the database is corrupted? I do not have a control panel to check..and only have access to ssh.
Some of that tables are corrupted and needs to be repaired.
I am new at this, so any help regarding what commands to use will be appreciated.
Last edited by awaisuk; 12-02-2012 at 03:29 PM.
|
| Posted by nessic, 12-02-2012, 03:42 PM |
| myisamchk /var/lib/mysql/database name here/* >> ./dbcheck.log
Post the output of that file here or tell us the broken tables it displays in ssh
|
| Posted by awaisuk, 12-02-2012, 04:39 PM |
| myisamchk: error: Record-count is not ok; is 6607161 Should be: 0
myisamchk: warning: Found 6623942 key parts. Should be: 0
MyISAM-table '/var/lib/mysql/database/posts.MYI' is corrupted
Fix it using switch "-r" or "-o"
And other similar tables have that issue as well.
There are two posts file. Posts.myi and Posts.myd both are around 12gb in size.
Thanks
Last edited by awaisuk; 12-02-2012 at 04:43 PM.
|
| Posted by nessic, 12-02-2012, 04:43 PM |
| Find / -name 'dB check.log'
|
| Posted by awaisuk, 12-02-2012, 04:44 PM |
| I have found file, but it does not state which tables are corrupt...
Also I edited my post above.
|
| Posted by nessic, 12-02-2012, 04:50 PM |
| Make sure you back everything up first.
myisamchk --silent --force --update-state \
--key_buffer_size=512M --sort_buffer_size=512M \
--read_buffer_size=8M --write_buffer_size=8M \
/var/lib/mysql/database/posts.MYI
OR to repair all tables
myisamchk --silent --force --update-state \
--key_buffer_size=512M --sort_buffer_size=512M \
--read_buffer_size=8M --write_buffer_size=8M \
/var/lib/mysql/database/*.MYI
For a db that big it will still take some time so leave it be for a few hours.
Last edited by nessic; 12-02-2012 at 04:56 PM.
|
| Posted by awaisuk, 12-02-2012, 11:10 PM |
| There are alot of these errors:
myisamchk: warning: 1 client is using or hasn't closed the table properly
myisamchk: MyISAM file /var/lib/mysql/database/members.MYI
myisamchk: warning: 1 client is using or hasn't closed the table properly
Does that mean its being fixed?
|
| Posted by bashibuzuk, 12-03-2012, 03:18 AM |
| To optimize small dbs, I usually use mysqlcheck --optimize --repair option.
For databases of a big quantity, I prefer using myisamchk command. the details can be given via the link
|
Add to Favourites 
Print this Article
Also Read
