Using MyISAM on a table loaded from 8GB of CSV, I am now adding some
indices. In a separate shell I monitor the progress, alternately with
`vmstat` and "show full processlist". At first vmstat shows rapid
progress; an example is
# vmstat 5
procs -----------memory---------- ---swap-- -----io---- --system--
-----cpu------
r b swpd free buff cache si so bi bo in cs us
sy id wa st
1 6 0 8542108 256860 53940400 0 0 0 152783 1314 298 3 4
60 32 0
1 6 0 8541000 256868 53940392 0 0 0 147868 1301 287 3 4
68 25 0
1 5 0 8541380 256876 53940400 0 0 0 150633 1310 277 3 4
72 21 0
1 6 0 8541108 256884 53940392 0 0 0 152066 1307 271 3 4
71 21 0
1 7 0 8541116 256892 53940400 0 0 0 151452 1312 311 3 4
64 29 0
1 6 0 8541992 256900 53940392 0 0 0 192175 1402 295 3 4
66 26 0
1 6 0 8535684 256908 53940400 0 0 0 108783 1227 276 3 4
69 24 0
1 8 0 8539116 256916 53940392 0 0 0 155958 1318 262 3 4
82 11 0
1 6 0 8540860 256924 53940392 0 0 0 166599 1340 328 3 4
66 27 0
1 9 0 8538512 256932 53940392 0 0 0 165386 1336 319 3 4
62 31 0
1 6 0 8536776 256940 53940392 0 0 0 175106 1358 303 3 5
66 27 0
2 0 0 8538884 256944 53940396 0 0 0 187839 1402 305 3 5
70 22 0
1 1 0 8517060 256952 53940400 0 0 0 188694 1379 307 3 4
66 27 0
1 10 0 8511604 256960 53940400 0 0 0 175821 1335 294 2 5
69 24 0
1 10 0 8513340 256968 53940400 0 0 0 164252 1335 300 3 4
65 28 0
2 0 0 8523012 256976 53940392 0 0 0 151527 1318 305 3 5
60 33 0
1 10 0 8490152 256976 53940400 0 0 0 178613 1352 301 2 5
67 26 0
2 0 0 8499576 256976 53940400 0 0 0 142186 1319 302 2 5
69 23 0
1 10 0 8474280 256984 53940400 0 0 0 185598 1348 301 2 5
57 36 0
2 9 0 8440676 256984 53940400 0 0 0 166807 1334 306 2 5
53 39 0
1 9 0 8465228 256988 53940396 0 0 0 114594 1268 306 1 6
56 36 0
1 9 0 16819736 256992 45542944 0 0 0 185034 1342 301 1
6 56 36 0
1 9 0 20314428 257028 42134724 0 0 0 186163 1371 282 1
6 56 37 0
1 9 0 20276856 257068 42171252 0 0 0 166406 1342 281 2
5 59 34 0
1 9 0 20237672 257108 42209344 0 0 0 166810 1333 252 2
5 56 37 0
At this point, and not for the first time, I stop vmstat and "show full
processlist". It says
+----+------+-----------+----------+---------+------+-------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State |
Info |
+----+------+-----------+----------+---------+------+-------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | root | localhost | cel_4x52 | Query | 542 | copy to tmp table |
ALTER TABLE ntfelt ADD PRIMARY KEY (p, epoch, ssi, q, kind, ev, c),
ADD UNIQUE INDEX pesqekvc(p, epoch, ssi, q, eqoch, kind, version,
c),
ADD INDEX tc(t, c),
ORDER BY p, epoch, ssi, q, kind, ev, c |
| 3 | root | localhost | NULL | Query | 0 | NULL |
show full processlist |
+----+------+-----------+----------+---------+------+-------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
OK, so it is still indexing. Then I start up `vmstat` again, and it shows
very different dynamics:
# vmstat 5
procs -----------memory---------- ---swap-- -----io---- --system--
-----cpu------
r b swpd free buff cache si so bi bo in cs us
sy id wa st
1 0 0 32429508 257248 30439256 0 0 0 30 7 6 0 0
100 0 0
1 1 0 32416124 257272 30451668 0 0 0 2471 1020 111 6 0
94 0 0
1 0 0 32405096 257292 30461780 0 0 0 2467 1017 109 6 0
94 0 0
1 0 0 32391828 257312 30474436 0 0 0 2056 1019 107 6 0
94 0 0
1 0 0 32378684 257332 30486356 0 0 0 2563 1040 109 6 0
94 0 0
1 0 0 32358224 257352 30500824 0 0 0 3756 1038 109 6 0
93 0 0
1 0 0 32342600 257380 30519492 0 0 0 3356 1035 112 6 0
93 0 0
1 0 0 32322140 257404 30537688 0 0 0 3696 1023 108 6 0
94 0 0
I check "show full processlist" again, and it is still indexing. I check
`vmstat` again, and it is still crawling. Low disk I/O rate AND low CPU
usage. What have I done wrong?
The MySQL server is running on a 64-bit RHEL 5 machine with 16 Intel cores
at 2.4 GHz, and 64 GB RAM. The db storage is on fiber channel. I created
my.cnf based on my-huge.cnf, expanding key_buffer to 8G,
myisam_sort_buffer_size to 256M, and putting tmpdir on the fiber channel
disk.
Thanks,
Mike Spreitzer
