MySQL Forums
Forum List  »  MyISAM

How to get high performance on repair huge MYISAM tables?
Posted by: Jerry Tian
Date: December 24, 2008 02:43AM

Hardware: AMD X2 5000+/8G DDRII 800/RAID0(2X120G normal SATA disks)
Software: ubuntu 8.0.4 x86_64 server edition/MySQL GA 5.1.30 binary install

my.cnf:
////////////////////////////////////
key_buffer = 4096M
max_allowed_packet = 16M
table_cache = 1024
sort_buffer_size = 256M
read_buffer_size = 64M
read_rnd_buffer_size = 1M
myisam_sort_buffer_size = 256M
thread_cache_size = 8
thread_stack = 128K
#thread_concurrency = 4
tmp_table_size = 2048M

#
# * Query Cache Configuration
#
query_cache_limit = 1048576
query_cache_size = 16777216
query_cache_type = 1

////////////////////////////////////

This server is acting as a slave to the remote production master via ADSL link(not that very stable), mainly for BI report generation. The biggest MyISAM table is about the size of 100G, having about a billion rows of records in it, it is also partitioned. The second largest one is about the size of 50G.

Recently, I have to repair this tables for some times, or the server can not continue its replication. But the process is really miserable, it almost takes a day to finish. The command I used is:

mysqlcheck --auto-repair --fast --all-databases

I am not using "--quick" option since according to the help manual, it will only check index file of tables, which is somewhat too risky for me.

I wonder if I could speed this process by tuning some server settings or use other tools? I have used "iostat" and "top" to monitor the server status during the process, the CPU have little chance to reach over 90% usage; and the RAID0 combination's read and write performance just struggle below 5MB/s. Very pool, isn't it?

Any suggestion is appreciated. ;-)

Options: ReplyQuote


Subject
Views
Written By
Posted
How to get high performance on repair huge MYISAM tables?
4734
December 24, 2008 02:43AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.