> Let's look at the slowlog to see which statements are the biggies
What is the result of this??
> and even 4 HDDs in RAID0, nothing different
I find that hard to believe. You should have seen a substantial increase in disk I/O resulting in faster reads and writes. You moved the datadir and tmpdir locations to the raid when you installed it?
1.
+--------------------------------+--------+---------+------------+----------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+--------------------+
2.
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options |
3.
+--------------------------------+--------+---------+------------+----------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+--------------------+
4.
| xxxxxxxxxxxx | InnoDB | 10 | Compact | 66813 | 935 | 62504960 | 0 | 0 | 5242880 | NULL | 2010-11-17 00:45:56 | NULL | NULL | utf8_general_ci | NULL | |
5.
| xxxxxx | InnoDB | 10 | Compact | 26 | 630 | 16384 | 0 | 0 | 0 | NULL | 2010-11-17 00:45:57 | NULL | NULL | utf8_general_ci | NULL | row_format=DYNAMIC |
6.
| xxxxxxxxxx | InnoDB | 10 | Compact | 4095 | 644 | 2637824 | 0 | 0 | 4194304 | NULL | 2010-11-17 00:46:25 | NULL | NULL | utf8_general_ci | NULL | |
7.
| xxxxxxxxxxxxxxxxx | InnoDB | 10 | Compact | 8104 | 62 | 507904 | 0 | 0 | 0 | NULL | 2010-11-17 00:46:01 | NULL | NULL | utf8_general_ci | NULL | |
8.
| xxxxxxxxxxxxxxxx | InnoDB | 10 | Compact | 4027 | 73 | 294912 | 0 | 0 | 0 | NULL | 2010-11-17 00:45:39 | NULL | NULL | utf8_general_ci | NULL | |
9.
| xxxxxxxxxxxxxxxxxxxxxx | InnoDB | 10 | Compact | 118635 | 7918 | 939360256 | 0 | 0 | 9437184 | NULL | 2010-11-22 04:22:16 | NULL | NULL | utf8_general_ci | NULL | |
10.
| xxxxxxxxxxxx | InnoDB | 10 | Compact | 12621 | 167 | 2113536 | 0 | 245760 | 4194304 | NULL | 2010-11-17 00:45:56 | NULL | NULL | utf8_general_ci | NULL | |
11.
| xxxxxxxxx | InnoDB | 10 | Compact | 7 | 2340 | 16384 | 0 | 0 | 0 | 133 | 2010-11-17 00:45:42 | NULL | NULL | utf8_general_ci | NULL | row_format=DYNAMIC |
12.
| xxxxxxxx | InnoDB | 10 | Compact | 3082 | 95 | 294912 | 0 | 0 | 0 | NULL | 2010-11-17 00:45:39 | NULL | NULL | utf8_general_ci | NULL | row_format=DYNAMIC |
13.
| xxxxxxxxxxxxxxxxxxxxxx | InnoDB | 10 | Compact | 135485 | 3419 | 463323136 | 0 | 0 | 6291456 | NULL | 2010-11-17 00:46:05 | NULL | NULL | utf8_general_ci | NULL | |
14.
| xxxxxxxxxxxxxxxxxxxxx | InnoDB | 10 | Compact | 2669783 | 61 | 165363712 | 0 | 0 | 6291456 | NULL | 2010-11-17 00:45:43 | NULL | NULL | utf8_general_ci | NULL | |
15.
| xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | InnoDB | 10 | Compact | 27265779 | 59 | 1624195072 | 0 | 0 | 7340032 | NULL | 2010-11-17 00:45:43 | NULL | NULL | utf8_general_ci | NULL | |
16.
| xxxxxxxxxxxxxxxx | InnoDB | 10 | Compact | 3046900 | 52 | 160038912 | 0 | 0 | 7340032 | NULL | 2010-11-17 00:45:56 | NULL | NULL | utf8_general_ci | NULL | row_format=DYNAMIC |
17.
| xxxxxxxxxxxxxxxxxxxxx | MyISAM | 10 | Fixed | 214743 | 15 | 3221145 | 4222124650659839 | 3230720 | 0 | NULL | 2010-09-14 09:48:02 | 2010-11-24 16:49:50 | 2010-11-06 04:15:17 | utf8_general_ci | NULL | |
18.
| xxxxxxxxxxxxxx | InnoDB | 10 | Compact | 152099 | 145 | 22102016 | 0 | 6488064 | 4194304 | NULL | 2010-11-17 00:45:48 | NULL | NULL | utf8_general_ci | NULL | row_format=DYNAMIC |
19.
| xxxxxxxxxxxxxxxxxxxxxxxxxxx | InnoDB | 10 | Compact | 369 | 133 | 49152 | 0 | 0 | 0 | NULL | 2010-11-17 00:46:25 | NULL | NULL | utf8_general_ci | NULL | row_format=DYNAMIC |
20.
| xxxxxxxxxxxxxxxxxxxxxxxxxxxxx | InnoDB | 10 | Compact | 1135 | 43 | 49152 | 0 | 0 | 0 | NULL | 2010-11-17 00:45:46 | NULL | NULL | utf8_general_ci | NULL | |
21.
| xxxxxxxxxxxxxxxxxxxxxx | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | NULL | 2010-11-17 00:46:25 | NULL | NULL | utf8_general_ci | NULL | row_format=DYNAMIC |
22.
| xxxxxxxxxxxxxxxxxxxxxxx | InnoDB | 10 | Compact | 12732 | 207 | 2637824 | 0 | 688128 | 4194304 | 26626 | 2010-11-17 00:45:39 | NULL | NULL | utf8_general_ci | NULL | |
23.
| xxxxxxxxxxxxxxxxxx | InnoDB | 10 | Compact | 255263 | 47 | 12075008 | 0 | 9977856 | 4194304 | NULL | 2010-11-22 04:15:20 | NULL | NULL | utf8_general_ci | NULL | |
24.
| xxxxxxxxxxxxxxxxxxx | InnoDB | 10 | Compact | 37685 | 69 | 2637824 | 0 | 0 | 4194304 | NULL | 2010-11-22 04:15:23 | NULL | NULL | utf8_general_ci | NULL | |
25.
| xxxxxxxxxxxxxxx | InnoDB | 10 | Compact | 94 | 174 | 16384 | 0 | 16384 | 0 | NULL | 2010-11-17 00:45:45 | NULL | NULL | utf8_general_ci | NULL | |
26.
| xxxxxxxxxxxxxxxx | InnoDB | 10 | Compact | 93669 | 61 | 5783552 | 0 | 0 | 4194304 | NULL | 2010-11-17 00:45:45 | NULL | NULL | utf8_general_ci | NULL | |
27.
| xxxxxxxxxxxxxxxxxx | InnoDB | 10 | Compact | 71321 | 66 | 4734976 | 0 | 0 | 4194304 | NULL | 2010-11-17 00:45:43 | NULL | NULL | utf8_general_ci | NULL | row_format=DYNAMIC |
28.
| xxxxxxxxxxxxxxxxxx | InnoDB | 10 | Compact | 21358 | 55 | 1179648 | 0 | 360448 | 5242880 | NULL | 2010-11-17 00:45:45 | NULL | NULL | utf8_general_ci | NULL | |
29.
| xxxxxxxxxxxxxxxxxxx | InnoDB | 10 | Compact | 3670734 | 52 | 191627264 | 0 | 89669632 | 5242880 | NULL | 2010-11-17 00:45:56 | NULL | NULL | utf8_general_ci | NULL | row_format=DYNAMIC |
30.
| xxxxxxxxxxxxx | InnoDB | 10 | Compact | 21979 | 215 | 4734976 | 0 | 524288 | 4194304 | NULL | 2010-11-17 00:45:56 | NULL | NULL | utf8_general_ci | NULL | row_format=DYNAMIC |
31.
| xxxxxxxxxxxxxxxxxxxxxxxxxx | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 16384 | 0 | NULL | 2010-11-17 00:45:39 | NULL | NULL | utf8_general_ci | NULL | row_format=DYNAMIC |
32.
| xxxxxxxxxxxxxxxxxxxxx | InnoDB | 10 | Compact | 9598225 | 111 | 1070579712 | 0 | 0 | 6291456 | NULL | 2010-11-17 00:45:46 | NULL | NULL | utf8_general_ci | NULL | row_format=DYNAMIC |
33.
| xxxxxxxxxxxxxxxxxxxxxxxxxxx | InnoDB | 10 | Compact | 2265 | 72 | 163840 | 0 | 49152 | 0 | NULL | 2010-11-17 00:45:42 | NULL | NULL | utf8_general_ci | NULL | row_format=DYNAMIC |
34.
| xxxxxxxxxxxxxxxxxxxxxxxxxxxx | InnoDB | 10 | Compact | 8724 | 50 | 442368 | 0 | 163840 | 0 | NULL | 2010-11-17 00:45:48 | NULL | NULL | utf8_general_ci | NULL | row_format=DYNAMIC |
35.
| xxxxxxxxxxxxxxxxxxxx | InnoDB | 10 | Compact | 6659494 | 56 | 377454592 | 0 | 0 | 5242880 | NULL | 2010-11-17 00:45:46 | NULL | NULL | utf8_general_ci | NULL | row_format=DYNAMIC |
36.
| xxxxxxxxxxxxxxxx | InnoDB | 10 | Compact | 1120308 | 45 | 50954240 | 0 | 0 | 4194304 | NULL | 2010-11-17 00:45:56 | NULL | NULL | utf8_general_ci | NULL | row_format=DYNAMIC |
37.
| xxxxxxxxxxxxxxxx | InnoDB | 10 | Compact | 263111 | 56 | 14991360 | 0 | 29786112 | 5242880 | NULL | 2010-11-17 00:45:45 | NULL | NULL | utf8_general_ci | NULL | row_format=DYNAMIC |
38.
| xxxxxxxxxxxxxxx | InnoDB | 10 | Compact | 6339144 | 50 | 319700992 | 0 | 0 | 4194304 | NULL | 2010-11-17 00:45:43 | NULL | NULL | utf8_general_ci | NULL | row_format=DYNAMIC |
39.
| xxxxxxxxxxxxxxxxxxxxxxxx | InnoDB | 10 | Compact | 366135 | 117 | 43073536 | 0 | 0 | 7340032 | NULL | 2010-11-17 00:45:56 | NULL | NULL | utf8_general_ci | NULL | |
40.
| xxxxxxxxxxxxxxx | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | NULL | 2010-11-17 00:45:43 | NULL | NULL | utf8_general_ci | NULL | |
41.
| xxxxxxxxxxxxxxxx | InnoDB | 10 | Compact | 1713859 | 40 | 68698112 | 0 | 0 | 7340032 | NULL | 2010-11-17 00:45:39 | NULL | NULL | utf8_general_ci | NULL | |
42.
| xxxxxxxxxxxxxxxxxx | InnoDB | 10 | Compact | 36392 | 101 | 3686400 | 0 | 1589248 | 4194304 | 43768 | 2010-11-17 00:45:50 | NULL | NULL | utf8_general_ci | NULL | row_format=DYNAMIC |
43.
| xxxxxxxxxx | InnoDB | 10 | Compact | 69812 | 1300 | 90800128 | 0 | 6406144 | 6291456 | NULL | 2010-11-17 00:45:48 | NULL | NULL | utf8_general_ci | NULL | row_format=DYNAMIC |
44.
| xxxxxx | InnoDB | 10 | Compact | 427 | 383 | 163840 | 0 | 65536 | 0 | NULL | 2010-11-17 00:45:46 | NULL | NULL | utf8_general_ci | NULL | row_format=DYNAMIC |
45.
| xxxxxxxxxxxxxxxxxxxxxxxxx | InnoDB | 10 | Compact | 2 | 8192 | 16384 | 0 | 0 | 0 | NULL | 2010-11-17 00:45:43 | NULL | NULL | utf8_general_ci | NULL | |
46.
| xxxxxxxxxxxxxxx | InnoDB | 10 | Compact | 15 | 1092 | 16384 | 0 | 0 | 0 | NULL | 2010-11-17 00:46:25 | NULL | NULL | utf8_general_ci | NULL | |
47.
| xxxxxxxxxxxxx | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | 1 | 2010-11-17 00:45:39 | NULL | NULL | utf8_general_ci | NULL | row_format=DYNAMIC |
48.
| xxxxxxxxxx | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | 1 | 2010-11-17 00:45:56 | NULL | NULL | utf8_general_ci | NULL | row_format=DYNAMIC |
49.
| xxxxxxxxxx | InnoDB | 10 | Compact | 1760 | 902 | 1589248 | 0 | 0 | 4194304 | 6946 | 2010-11-17 00:45:43 | NULL | NULL | utf8_general_ci | NULL | row_format=DYNAMIC |
50.
| xxxxxxxxxxxxxx | InnoDB | 10 | Compact | 2222 | 44 | 98304 | 0 | 65536 | 0 | NULL | 2010-11-17 00:46:05 | NULL | NULL | utf8_general_ci | NULL | |
51.
| xxxxxxxxxxxx | InnoDB | 10 | Compact | 1270 | 51 | 65536 | 0 | 0 | 0 | NULL | 2010-11-17 00:45:39 | NULL | NULL | utf8_general_ci | NULL | row_format=FIXED |
52.
| xxxxxx | InnoDB | 10 | Compact | 469 | 139 | 65536 | 0 | 16384 | 0 | NULL | 2010-11-17 00:46:00 | NULL | NULL | utf8_general_ci | NULL | row_format=FIXED |
53.
| xxxxx | InnoDB | 10 | Compact | 902 | 326 | 294912 | 0 | 0 | 0 | NULL | 2010-11-17 00:46:05 | NULL | NULL | utf8_general_ci | NULL | row_format=DYNAMIC |
54.
| xxxxxxxxxxxxxxxxxxx | InnoDB | 10 | Compact | 71125 | 81 | 5783552 | 0 | 8634368 | 4194304 | NULL | 2010-11-17 00:45:39 | NULL | NULL | utf8_general_ci | NULL | |
55.
| xxxxxxxxxxxxxxx | InnoDB | 10 | Compact | 111633 | 60 | 6750208 | 0 | 5947392 | 4194304 | NULL | 2010-11-17 00:46:00 | NULL | NULL | utf8_general_ci | NULL | |
56.
| xxxxxxxxxxxxxxxx | InnoDB | 10 | Compact | 18053 | 87 | 1572864 | 0 | 425984 | 4194304 | NULL | 2010-11-17 00:45:46 | NULL | NULL | utf8_general_ci | NULL | |
57.
| xxxxxxxxxxxxxx | InnoDB | 10 | Compact | 3809 | 73 | 278528 | 0 | 65536 | 0 | NULL | 2010-11-17 00:45:39 | NULL | NULL | utf8_general_ci | NULL | |
58.
| xxxxxxxxxxxxxx | InnoDB | 10 | Compact | 69034 | 68 | 4734976 | 0 | 6422528 | 4194304 | NULL | 2010-11-17 00:46:05 | NULL | NULL | utf8_general_ci | NULL | |
59.
| xxxxxxxxxxxx | InnoDB | 10 | Compact | 17844 | 144 | 2572288 | 0 | 753664 | 4194304 | NULL | 2010-11-17 00:45:48 | NULL | NULL | utf8_general_ci | NULL | row_format=FIXED |
60.
| xxxxxxxxxx | InnoDB | 10 | Compact | 5504 | 53 | 294912 | 0 | 114688 | 0 | NULL | 2010-11-17 00:45:39 | NULL | NULL | utf8_general_ci | NULL | row_format=DYNAMIC |
61.
| xxxxxxxx | InnoDB | 10 | Compact | 1838 | 89 | 163840 | 0 | 163840 | 0 | NULL | 2010-11-17 00:45:50 | NULL | NULL | utf8_general_ci | NULL | |
62.
| xxxxxxxxxxxxxx | InnoDB | 10 | Compact | 73 | 224 | 16384 | 0 | 16384 | 0 | NULL | 2010-11-17 00:45:57 | NULL | NULL | utf8_general_ci | NULL | |
63.
| xxxxxxxxxxxxx | InnoDB | 10 | Compact | 4168610 | 206 | 861929472 | 0 | 99106816 | 4194304 | NULL | 2010-11-17 00:45:45 | NULL | NULL | utf8_general_ci | NULL | row_format=DYNAMIC |
64.
| xxxxxxxxxxxxxxxxxxxx | InnoDB | 10 | Compact | 6991 | 49 | 344064 | 0 | 0 | 0 | NULL | 2010-11-17 00:45:43 | NULL | NULL | utf8_general_ci | NULL | |
65.
| xxxxxxxxxxx | InnoDB | 10 | Compact | 182 | 90 | 16384 | 0 | 0 | 0 | 183 | 2010-11-17 00:45:43 | NULL | NULL | utf8_general_ci | NULL | row_format=DYNAMIC |
66.
| xxxxxxxxxx | InnoDB | 10 | Compact | 1027893 | 54 | 56180736 | 0 | 50429952 | 6291456 | 1028258 | 2010-11-22 04:24:25 | NULL | NULL | latin1_swedish_ci | NULL | |
67.
| xxxx | InnoDB | 10 | Compact | 34488 | 187 | 6471680 | 0 | 1130496 | 4194304 | NULL | 2010-11-17 00:45:43 | NULL | NULL | utf8_general_ci | NULL | row_format=DYNAMIC |
68.
| xxxxxxxxxxxxx | MyISAM | 10 | Dynamic | 2211 | 80 | 178416 | 281474976710655 | 33792 | 0 | 2217 | 2010-08-12 01:54:05 | 2010-11-24 00:44:33 | 2010-11-06 04:37:08 | latin1_general_ci | NULL | |
69.
| xxxxxxxxxxxxxxxxxxx | MyISAM | 10 | Fixed | 2160 | 17 | 36720 | 4785074604081151 | 32768 | 0 | NULL | 2010-08-12 01:54:05 | 2010-11-24 00:44:25 | 2010-11-06 04:37:08 | latin1_general_ci | NULL | |
70.
| xxxxxxxxxx | InnoDB | 10 | Compact | 113638 | 50 | 5783552 | 0 | 3407872 | 4194304 | NULL | 2010-11-17 00:45:56 | NULL | NULL | utf8_general_ci | NULL | row_format=DYNAMIC |
71.
| xxxxxxxx | InnoDB | 10 | Compact | 36109 | 119 | 4308992 | 0 | 0 | 4194304 | NULL | 2010-11-17 00:45:48 | NULL | NULL | utf8_general_ci | NULL | row_format=DYNAMIC |
72.
| xxxxxxxxx | InnoDB | 10 | Compact | 383773 | 45 | 17317888 | 0 | 0 | 4194304 | NULL | 2010-11-17 00:45:50 | NULL | NULL | utf8_general_ci | NULL | row_format=DYNAMIC |
73.
| xxxxxxxxxxxxxxxxxx | InnoDB | 10 | Compact | 11225 | 119 | 1343488 | 0 | 0 | 4194304 | NULL | 2010-11-17 00:46:05 | NULL | NULL | utf8_general_ci | NULL | |
74.
| xxxxxxxx | InnoDB | 10 | Compact | 850 | 77 | 65536 | 0 | 16384 | 0 | NULL | 2010-11-17 00:46:01 | NULL | NULL | utf8_general_ci | NULL | row_format=DYNAMIC |
75.
| xxxxxxxxxxxxx | InnoDB | 10 | Compact | 2494 | 52 | 131072 | 0 | 131072 | 0 | NULL | 2010-11-17 00:45:57 | NULL | NULL | utf8_general_ci | NULL | row_format=DYNAMIC |
76.
| xxxxxxxxxxxxxxx | MyISAM | 10 | Fixed | 1 | 9 | 9 | 2533274790395903 | 1024 | 0 | NULL | 2010-08-12 01:54:16 | 2010-08-12 01:54:16 | 2010-09-05 04:56:50 | utf8_general_ci | NULL | row_format=FIXED |
77.
| xxxxxxxxxxx | InnoDB | 10 | Compact | 5 | 3276 | 16384 | 0 | 0 | 0 | NULL | 2010-11-17 00:45:56 | NULL | NULL | utf8_general_ci | NULL | row_format=DYNAMIC |
78.
+--------------------------------+--------+---------+------------+----------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+--------------------+
79.
74 rows in set (0.59 sec)
It looks like these rows do not have indexes:
9.
| xxxxxxxxxxxxxxxxxxxxxx | InnoDB | 10 | Compact | 118635 | 7918 | 939360256 | 0 | 0 | 9437184 | NULL | 2010-11-22 04:22:16 | NULL | NULL | utf8_general_ci | NULL | |
10.
-- -- -- -- -- -- -- -- -- -- -- -- -- --
16.
| xxxxxxxxxxxxxxxx | InnoDB | 10 | Compact | 3046900 | 52 | 160038912 | 0 | 0 | 7340032 | NULL | 2010-11-17 00:45:56 | NULL | NULL | utf8_general_ci | NULL | row_format=DYNAMIC |
17.
-- -- -- -- -- -- -- -- -- -- -- -- -- --
There are a other tables that don't have indexes. Also looks like some of them might be over indexed but I won't know without looking at the create table statements.
I think the best approach at this point is to follow Rick's advice and look at the slow log, it will give you lots of improvements to make and they will make the most difference the most quickly.
Shawn