List:General Discussion« Previous MessageNext Message »
From:Mike Spreitzer Date:June 25 2009 5:37am
Subject:Re: Indexing dynamics in MySQL Community Edition 5.1.34
View as plain text  
Actually, my characterization of the current state is wrong.  It appears 
that one core is completely busy, I suppose MySQL does this indexing work 
in a single thread.  Is it reasonable for indexing to be CPU bound?

Thanks,
Mike Spreitzer




Mike Spreitzer/Watson/IBM@IBMUS 
06/25/09 01:30 AM

To
mysql@stripped
cc

Subject
Indexing dynamics in MySQL Community Edition 5.1.34






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


Thread
Indexing dynamics in MySQL Community Edition 5.1.34Mike Spreitzer25 Jun
  • Re: Indexing dynamics in MySQL Community Edition 5.1.34Mike Spreitzer25 Jun
    • Re: Indexing dynamics in MySQL Community Edition 5.1.34mos25 Jun
      • Re: Indexing dynamics in MySQL Community Edition 5.1.34Mike Spreitzer25 Jun
      • Re: Indexing dynamics in MySQL Community Edition 5.1.34Simon J Mudd27 Jun
    • RE: Indexing dynamics in MySQL Community Edition 5.1.34Jerry Schwartz25 Jun
Re: Indexing dynamics in MySQL Community Edition 5.1.34mos25 Jun
  • Re: Indexing dynamics in MySQL Community Edition 5.1.34Moon's Father26 Jun
    • RE: Indexing dynamics in MySQL Community Edition 5.1.34Jerry Schwartz26 Jun
      • RE: Indexing dynamics in MySQL Community Edition 5.1.34Jerry Schwartz26 Jun
  • Re: Indexing dynamics in MySQL Community Edition 5.1.34Mike Spreitzer27 Jun
    • Re: Indexing dynamics in MySQL Community Edition 5.1.34Mike Spreitzer27 Jun
      • Re: Indexing dynamics in MySQL Community Edition 5.1.34Todd Lyons27 Jun