Hello.
Check if the speed of recovering changes if you run myisamchk with -n option among other parameters to force using sort recovery. However, it requires a lot of disk space. See: http://dev.mysql.com/doc/mysql/en/myisamchk-repair-options.html I guess it might help because this variable related to REPAIR command on the server could force server to use much faster sort method instead of failing to old and slow key cache method: "myisam_max_sort_file_size The maximum size of the temporary file MySQL is allowed to use while re-creating a MyISAM index (during REPAIR TABLE, ALTER TABLE, or LOAD DATA INFILE). If the file size would be bigger than this value, the index is created using the key cache instead, which is slower." RV Tec <[EMAIL PROTECTED]> wrote: > Folks, > > We are going through a nasty problem, and I hope you guys can help me out. > > We are running a couple of MySQL 4.0.25 at 2 OpenBSD Opteron (246, 2GB > RAM, 36GB RAID-1 15K), and for backup purposes, 1 Linux CentOS 4.0 Pentium > 4 (3GHz, 1GB RAM, 80GB SATA 10K). They are all connected through > replication, and everything works perfectly. > > Our database is now close to 20GB, divided in 160 tables. There are only 2 > tables that are larger than 1GB, all others are below 300MB. > > These two large tables, they have about 30.000.000 rows and 11 keys of > indexing (each). Every now and then, I used to run myisamchk to fix and > optimize this table (myisamchk -r, -S, -a). All of a sudden, this process > has become slower to the point that I have given up. It used to take 3 > hours to repair/optimize each table. Now we are running myisamchk for the > last 12 hours and it has completed only 1/3 of one table! > > Back when things used to work, after the optimization, the size of the > index file (MYI) was about 30% smaller. And everything was much faster (at > least for one month). > > And what scares me the most, is that this table is only growing. There is > no chance of trimming it. > > Does anyone know what can I do to optimize this table? > > Maybe I am reaching MySQL limits? Or just incorrect hardware for this task? > > What happens if we get a nasty table error? No way of repairing it? > > I have tried the same process over the OpenBSD's and Linux boxes. They all > show the exact same behaviour. Also, I have tried to create a new index > file (as told on MySQL docs: truncate table, copy back the MYD, then running > myisamchk -rq) and no luck... it is even slower. > > This is part of my.cnf: > > [myisamchk] > key_buffer = 512M > sort_buffer_size = 512M > read_buffer = 4M > write_buffer = 4M > tmpdir = /var/mysql/temp > > Just to make things clear: this tmpdir is another unused HD, connected to > the RAID's 2 channel -- this way we are not losing performance of > reading/writing, at least I think. The MySQL daemon is dead while running > myisamchk, and there is no other service running at the time -- so we can > bite memory some more. > > Only at the very beggining we see a CPU hog (peaking at 95%), but after a > few seconds, the CPU use drops to 5%, maybe even less, and we see only HD > activity -- and not that much. Is this right?! > > Any ideas?! > > Thanks for your time. > > Best regards, > RV > > PS: Here comes some useful(?) info. If things break, please, ask me for a > text file. > > [EMAIL PROTECTED] db]# ls -la offline* > -rw-rw---- 1 _mysql _mysql 2552151732 Jul 13 09:46 offline.MYD > -rw-rw---- 1 _mysql _mysql 5300221952 Jul 13 09:46 offline.MYI > -rw-rw---- 1 _mysql _mysql 8940 Apr 19 01:19 offline.frm > > [EMAIL PROTECTED] db]# ls -la history* > -rw-rw---- 1 _mysql _mysql 2372474988 Jul 13 09:47 history.MYD > -rw-rw---- 1 _mysql _mysql 3943869440 Jul 13 09:47 history.MYI > -rw-rw---- 1 _mysql _mysql 8926 Apr 19 01:26 history.frm > > alpha mysql> select count(*) from offline; > +----------+ > | count(*) | > +----------+ > | 30992996 | > +----------+ > 1 row in set (0.02 sec) > > alpha mysql> select count(*) from history; > +----------+ > | count(*) | > +----------+ > | 29676533 | > +----------+ > 1 row in set (0.01 sec) > > alpha mysql> describe offline; > +--------------+-----------------------+------+-----+---------+----------------+ > | Field | Type | Null | Key | Default | Extra > | > +--------------+-----------------------+------+-----+---------+----------------+ > | code | bigint(20) | | PRI | NULL | > auto_increment | > | history1 | int(10) | YES | MUL | NULL | > | > | desc | varchar(24) | YES | MUL | NULL | > | > | sold | int(1) | YES | MUL | NULL | > | > | emptor | int(9) | YES | MUL | NULL | > | > | shipment | int(5) | YES | MUL | NULL | > | > | share | bigint(20) | YES | MUL | NULL | > | > | sequence | bigint(20) | YES | MUL | NULL | > | > | model | enum('P','R') | YES | MUL | NULL | > | > | a4 | int(5) | YES | MUL | NULL | > | > | state | char(2) | YES | MUL | NULL | > | > +--------------+-----------------------+------+-----+---------+----------------+ > > alpha mysql> describe history; > +--------------+--------------+------+-----+---------+----------------+ > | Field | Type | Null | Key | Default | Extra | > +--------------+--------------+------+-----+---------+----------------+ > | code | bigint(20) | | PRI | NULL | auto_increment | > | vendor | int(9) | YES | MUL | NULL | | > | prod | int(6) | YES | MUL | NULL | | > | desc | varchar(24) | YES | MUL | NULL | | > | price | decimal(6,2) | YES | | NULL | | > | shipment | int(5) | YES | | NULL | | > | share | bigint(20) | YES | | NULL | | > | sequence | bigint(20) | YES | MUL | NULL | | > | count | int(2) | YES | | NULL | | > | print | int(2) | YES | | NULL | | > | a4 | int(16) | YES | MUL | NULL | | > +--------------+--------------+------+-----+---------+----------------+ > > alpha mysql> show indexes from offline; > +---------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+ > | Table | Non_unique | Key_name | Seq_in_index | Column_name | > Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment > | > +---------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+ > | offline | 0 | PRIMARY | 1 | code | A > | 30993308 | NULL | NULL | | BTREE | | > | offline | 1 | off_sequence | 1 | sequence | A > | 30993308 | NULL | NULL | YES | BTREE | | > | offline | 1 | off_desc | 1 | desc | A > | 30993308 | NULL | NULL | YES | BTREE | | > | offline | 1 | off_hist | 1 | history1 | A > | 1475871 | NULL | NULL | YES | BTREE | | > | offline | 1 | off_emptor | 1 | emptor | A > | 1147900 | NULL | NULL | YES | BTREE | | > | offline | 1 | off_share | 1 | share | A > | 208008 | NULL | NULL | YES | BTREE | | > | offline | 1 | off_sold | 1 | sold | A > | 4 | NULL | NULL | YES | BTREE | | > | offline | 1 | off_a4 | 1 | a4 | A > | 465 | NULL | NULL | YES | BTREE | | > | offline | 1 | off_model | 1 | model | A > | 2 | NULL | NULL | YES | BTREE | | > | offline | 1 | off_state | 1 | state | A > | 19 | NULL | NULL | YES | BTREE | | > | offline | 1 | off_ship | 1 | shipment | A > | 4189 | NULL | NULL | YES | BTREE | | > +---------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+ > 11 rows in set (0.02 sec) > > alpha mysql> show indexes from history; > +---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ > | Table | Non_unique | Key_name | Seq_in_index | Column_name | > Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment > | > +---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ > | history | 0 | PRIMARY | 1 | code | A > | 29676901 | NULL | NULL | | BTREE | | > | history | 1 | h_sequence | 1 | sequence | A > | 29676901 | NULL | NULL | YES | BTREE | | > | history | 1 | h_desc | 1 | desc | A > | 29676901 | NULL | NULL | YES | BTREE | | > | history | 1 | h_vendor | 1 | vendor | A > | 4946150 | NULL | NULL | YES | BTREE | | > | history | 1 | h_prod | 1 | prod | A > | 104 | NULL | NULL | YES | BTREE | | > | history | 1 | h_a4 | 1 | a4 | A > | 28 | NULL | NULL | YES | BTREE | | > | history | 1 | h_pr_ve | 1 | prod | A > | 104 | NULL | NULL | YES | BTREE | | > | history | 1 | h_pr_ve | 2 | vendor | A > | 5935380 | NULL | NULL | YES | BTREE | | > +---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ > > -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
