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 | |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
