Indexing dynamics in MySQL Community Edition 5.1.34

--=_alternative 001E3DA3852575E0_=
Content-Type: text/plain; charset="US-ASCII"

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

--=_alternative 001E3DA3852575E0_=--

Re: Indexing dynamics in MySQL Community Edition 5.1.34

--=_alternative 001EF149852575E0_=
Content-Type: text/plain; charset="US-ASCII"

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@lists.mysql.com
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


--=_alternative 001EF149852575E0_=--

Re: Indexing dynamics in MySQL Community Edition 5.1.34

At 12:37 AM 6/25/2009, you wrote:
>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?
>
>
>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.

Mike,
You mean "key_buffer_size" don't you and not "key_buffer"? If you are
using MySQL 5.0 then the upper limit is 4gb but 5.1 allows for more than 4gb.
Also try increasing "sort_buffer_size".

Posting your "Show Status" will help people see where the bottle neck is.

Mike


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Indexing dynamics in MySQL Community Edition 5.1.34

--=_mixed 00275E53852575E0_=
Content-Type: multipart/alternative; boundary="=_alternative 00275E53852575E0_="


--=_alternative 00275E53852575E0_=
Content-Type: text/plain; charset="US-ASCII"

Like I said in the subject line, I am using 5.1.34. I started with
my-huge.cnf, which says "key_buffer" rather than "key_buffer_size"; SHOW
GLOBAL VARIABLES confirms, however, that my key_buffer_size is 8GB.

That indexing operation finally finished after about 1.5 hours; that was
about 0.5 hours ago. Now I am on to other things. I have attached a ZIP
archive of the status you suggested, taken at this point in time.



Thanks,
Mike Spreitzer




mos
06/25/09 02:32 AM

To
mysql@lists.mysql.com
cc

Subject
Re: Indexing dynamics in MySQL Community Edition 5.1.34






At 12:37 AM 6/25/2009, you wrote:
>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?
>
>
>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.

Mike,
You mean "key_buffer_size" don't you and not "key_buffer"? If you are

using MySQL 5.0 then the upper limit is 4gb but 5.1 allows for more than
4gb.
Also try increasing "sort_buffer_size".

Posting your "Show Status" will help people see where the bottle neck is.

Mike


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=mspreitz@us.ibm.com



--=_alternative 00275E53852575E0_=
Content-Type: text/html; charset="US-ASCII"



Like I said in the subject line, I am
using 5.1.34.  I started with my-huge.cnf, which says "key_buffer"
rather than "key_buffer_size"; SHOW GLOBAL VARIABLES confirms,
however, that my key_buffer_size is 8GB.



That indexing operation finally finished
after about 1.5 hours; that was about 0.5 hours ago.  Now I am on
to other things.  I have attached a ZIP archive of the status you
suggested, taken at this point in time.







Thanks,

Mike Spreitzer

RE: Indexing dynamics in MySQL Community Edition 5.1.34

>-----Original Message-----
>From: Mike Spreitzer [mailto:mspreitz@us.ibm.com]
>Sent: Thursday, June 25, 2009 1:38 AM
>To: mysql@lists.mysql.com
>Subject: Re: Indexing dynamics in MySQL Community Edition 5.1.34
>
>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?
>
[JS] Yes, it is very reasonable if you have enough data cached. One of the
things it has to do is sort, and because memory access is much faster than
disk access you want to avoid the latter if at all possible.

If you'd ever seen a sort using magnetic tape (pre-disk), you'd have a vivid
mental image of this.

As for using a single thread, that is not compulsory. You could partition
the data and do a multi-threaded sort-merge operation. I no longer remember
the results of the "sort wars" of the early days, and I have no idea what
MySQL does.

>Thanks,
>Mike Spreitzer
>
>
>
>
>Mike Spreitzer/Watson/IBM@IBMUS
>06/25/09 01:30 AM
>
>To
>mysql@lists.mysql.com
>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





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Indexing dynamics in MySQL Community Edition 5.1.34

Mike,
I re-posted your Show Status to the group to see if anyone can offer
a way to speed up the indexing for you.

BTW, you are adding ALL of the indexes to the table using ONE sql statement
right? And not a separate SQL statement to build each index?

Mike

At 02:01 AM 6/25/2009, you wrote:

>Like I said in the subject line, I am using 5.1.34. I started with
>my-huge.cnf, which says "key_buffer" rather than "key_buffer_size"; SHOW
>GLOBAL VARIABLES confirms, however, that my key_buffer_size is 8GB.
>
>That indexing operation finally finished after about 1.5 hours; that was
>about 0.5 hours ago. Now I am on to other things. Here is the status you
>suggested:
>
>+-----------------------------------+-----------+
>| Variable_name | Value |
>+-----------------------------------+-----------+
>| Aborted_clients | 0 |
>| Aborted_connects | 0 |
>| Binlog_cache_disk_use | 0 |
>| Binlog_cache_use | 0 |
>| Bytes_received | 135 |
>| Bytes_sent | 1405 |
>| Com_admin_commands | 0 |
>| Com_assign_to_keycache | 0 |
>| Com_alter_db | 0 |
>| Com_alter_db_upgrade | 0 |
>| Com_alter_event | 0 |
>| Com_alter_function | 0 |
>| Com_alter_procedure | 0 |
>| Com_alter_server | 0 |
>| Com_alter_table | 0 |
>| Com_alter_tablespace | 0 |
>| Com_analyze | 0 |
>| Com_backup_table | 0 |
>| Com_begin | 0 |
>| Com_binlog | 0 |
>| Com_call_procedure | 0 |
>| Com_change_db | 0 |
>| Com_change_master | 0 |
>| Com_check | 0 |
>| Com_checksum | 0 |
>| Com_commit | 0 |
>| Com_create_db | 0 |
>| Com_create_event | 0 |
>| Com_create_function | 0 |
>| Com_create_index | 0 |
>| Com_create_procedure | 0 |
>| Com_create_server | 0 |
>| Com_create_table | 0 |
>| Com_create_trigger | 0 |
>| Com_create_udf | 0 |
>| Com_create_user | 0 |
>| Com_create_view | 0 |
>| Com_dealloc_sql | 0 |
>| Com_delete | 0 |
>| Com_delete_multi | 0 |
>| Com_do | 0 |
>| Com_drop_db | 0 |
>| Com_drop_event | 0 |
>| Com_drop_function | 0 |
>| Com_drop_index | 0 |
>| Com_drop_procedure | 0 |
>| Com_drop_server | 0 |
>| Com_drop_table | 0 |
>| Com_drop_trigger | 0 |
>| Com_drop_user | 0 |
>| Com_drop_view | 0 |
>| Com_empty_query | 0 |
>| Com_execute_sql | 0 |
>| Com_flush | 0 |
>| Com_grant | 0 |
>| Com_ha_close | 0 |
>| Com_ha_open | 0 |
>| Com_ha_read | 0 |
>| Com_help | 0 |
>| Com_insert | 0 |
>| Com_insert_select | 0 |
>| Com_install_plugin | 0 |
>| Com_kill | 0 |
>| Com_load | 0 |
>| Com_load_master_data | 0 |
>| Com_load_master_table | 0 |
>| Com_lock_tables | 0 |
>| Com_optimize | 0 |
>| Com_preload_keys | 0 |
>| Com_prepare_sql | 0 |
>| Com_purge | 0 |
>| Com_purge_before_date | 0 |
>| Com_release_savepoint | 0 |
>| Com_rename_table | 0 |
>| Com_rename_user | 0 |
>| Com_repair | 0 |
>| Com_replace | 0 |
>| Com_replace_select | 0 |
>| Com_reset | 0 |
>| Com_restore_table | 0 |
>| Com_revoke | 0 |
>| Com_revoke_all | 0 |
>| Com_rollback | 0 |
>| Com_rollback_to_savepoint | 0 |
>| Com_savepoint | 0 |
>| Com_select | 1 |
>| Com_set_option | 0 |
>| Com_show_authors | 0 |
>| Com_show_binlog_events | 0 |
>| Com_show_binlogs | 0 |
>| Com_show_charsets | 0 |
>| Com_show_collations | 0 |
>| Com_show_column_types | 0 |
>| Com_show_contributors | 0 |
>| Com_show_create_db | 0 |
>| Com_show_create_event | 0 |
>| Com_show_create_func | 0 |
>| Com_show_create_proc | 0 |
>| Com_show_create_table | 0 |
>| Com_show_create_trigger | 0 |
>| Com_show_databases | 0 |
>| Com_show_engine_logs | 0 |
>| Com_show_engine_mutex | 0 |
>| Com_show_engine_status | 0 |
>| Com_show_events | 0 |
>| Com_show_errors | 0 |
>| Com_show_fields | 0 |
>| Com_show_function_status | 0 |
>| Com_show_grants | 0 |
>| Com_show_keys | 0 |
>| Com_show_master_status | 0 |
>| Com_show_new_master | 0 |
>| Com_show_open_tables | 0 |
>| Com_show_plugins | 0 |
>| Com_show_privileges | 0 |
>| Com_show_procedure_status | 0 |
>| Com_show_processlist | 0 |
>| Com_show_profile | 0 |
>| Com_show_profiles | 0 |
>| Com_show_slave_hosts | 0 |
>| Com_show_slave_status | 0 |
>| Com_show_status | 1 |
>| Com_show_storage_engines | 0 |
>| Com_show_table_status | 0 |
>| Com_show_tables | 0 |
>| Com_show_triggers | 0 |
>| Com_show_variables | 1 |
>| Com_show_warnings | 0 |
>| Com_slave_start | 0 |
>| Com_slave_stop | 0 |
>| Com_stmt_close | 0 |
>| Com_stmt_execute | 0 |
>| Com_stmt_fetch | 0 |
>| Com_stmt_prepare | 0 |
>| Com_stmt_reprepare | 0 |
>| Com_stmt_reset | 0 |
>| Com_stmt_send_long_data | 0 |
>| Com_truncate | 0 |
>| Com_uninstall_plugin | 0 |
>| Com_unlock_tables | 0 |
>| Com_update | 0 |
>| Com_update_multi | 0 |
>| Com_xa_commit | 0 |
>| Com_xa_end | 0 |
>| Com_xa_prepare | 0 |
>| Com_xa_recover | 0 |
>| Com_xa_rollback | 0 |
>| Com_xa_start | 0 |
>| Compression | OFF |
>| Connections | 9 |
>| Created_tmp_disk_tables | 0 |
>| Created_tmp_files | 13 |
>| Created_tmp_tables | 1 |
>| Delayed_errors | 0 |
>| Delayed_insert_threads | 0 |
>| Delayed_writes | 0 |
>| Flush_commands | 1 |
>| Handler_commit | 0 |
>| Handler_delete | 0 |
>| Handler_discover | 0 |
>| Handler_prepare | 0 |
>| Handler_read_first | 0 |
>| Handler_read_key | 0 |
>| Handler_read_next | 0 |
>| Handler_read_prev | 0 |
>| Handler_read_rnd | 0 |
>| Handler_read_rnd_next | 34 |
>| Handler_rollback | 0 |
>| Handler_savepoint | 0 |
>| Handler_savepoint_rollback | 0 |
>| Handler_update | 0 |
>| Handler_write | 33 |
>| Innodb_buffer_pool_pages_data | 19 |
>| Innodb_buffer_pool_pages_dirty | 0 |
>| Innodb_buffer_pool_pages_flushed | 0 |
>| Innodb_buffer_pool_pages_free | 493 |
>| Innodb_buffer_pool_pages_misc | 0 |
>| Innodb_buffer_pool_pages_total | 512 |
>| Innodb_buffer_pool_read_ahead_rnd | 1 |
>| Innodb_buffer_pool_read_ahead_seq | 0 |
>| Innodb_buffer_pool_read_requests | 77 |
>| Innodb_buffer_pool_reads | 12 |
>| Innodb_buffer_pool_wait_free | 0 |
>| Innodb_buffer_pool_write_requests | 0 |
>| Innodb_data_fsyncs | 3 |
>| Innodb_data_pending_fsyncs | 0 |
>| Innodb_data_pending_reads | 0 |
>| Innodb_data_pending_writes | 0 |
>| Innodb_data_read | 2494464 |
>| Innodb_data_reads | 25 |
>| Innodb_data_writes | 3 |
>| Innodb_data_written | 1536 |
>| Innodb_dblwr_pages_written | 0 |
>| Innodb_dblwr_writes | 0 |
>| Innodb_log_waits | 0 |
>| Innodb_log_write_requests | 0 |
>| Innodb_log_writes | 1 |
>| Innodb_os_log_fsyncs | 3 |
>| Innodb_os_log_pending_fsyncs | 0 |
>| Innodb_os_log_pending_writes | 0 |
>| Innodb_os_log_written | 512 |
>| Innodb_page_size | 16384 |
>| Innodb_pages_created | 0 |
>| Innodb_pages_read | 19 |
>| Innodb_pages_written | 0 |
>| Innodb_row_lock_current_waits | 0 |
>| Innodb_row_lock_time | 0 |
>| Innodb_row_lock_time_avg | 0 |
>| Innodb_row_lock_time_max | 0 |
>| Innodb_row_lock_waits | 0 |
>| Innodb_rows_deleted | 0 |
>| Innodb_rows_inserted | 0 |
>| Innodb_rows_read | 0 |
>| Innodb_rows_updated | 0 |
>| Key_blocks_not_flushed | 26 |
>| Key_blocks_unused | 6844469 |
>| Key_blocks_used | 676158 |
>| Key_read_requests | 635624989 |
>| Key_reads | 12664 |
>| Key_write_requests | 112359740 |
>| Key_writes | 689890 |
>| Last_query_cost | 10.499000 |
>| Max_used_connections | 2 |
>| Not_flushed_delayed_rows | 0 |
>| Open_files | 70 |
>| Open_streams | 0 |
>| Open_table_definitions | 38 |
>| Open_tables | 37 |
>| Opened_files | 312 |
>| Opened_table_definitions | 0 |
>| Opened_tables | 0 |
>| Prepared_stmt_count | 0 |
>| Qcache_free_blocks | 1 |
>| Qcache_free_memory | 33536880 |
>| Qcache_hits | 0 |
>| Qcache_inserts | 0 |
>| Qcache_lowmem_prunes | 0 |
>| Qcache_not_cached | 12 |
>| Qcache_queries_in_cache | 0 |
>| Qcache_total_blocks | 1 |
>| Queries | 98 |
>| Questions | 3 |
>| Rpl_status | NULL |
>| Select_full_join | 0 |
>| Select_full_range_join | 0 |
>| Select_range | 0 |
>| Select_range_check | 0 |
>| Select_scan | 1 |
>| Slave_open_temp_tables | 0 |
>| Slave_retried_transactions | 0 |
>| Slave_running | OFF |
>| Slow_launch_threads | 0 |
>| Slow_queries | 0 |
>| Sort_merge_passes | 0 |
>| Sort_range | 0 |
>| Sort_rows | 0 |
>| Sort_scan | 0 |
>| Ssl_accept_renegotiates | 0 |
>| Ssl_accepts | 0 |
>| Ssl_callback_cache_hits | 0 |
>| Ssl_cipher | |
>| Ssl_cipher_list | |
>| Ssl_client_connects | 0 |
>| Ssl_connect_renegotiates | 0 |
>| Ssl_ctx_verify_depth | 0 |
>| Ssl_ctx_verify_mode | 0 |
>| Ssl_default_timeout | 0 |
>| Ssl_finished_accepts | 0 |
>| Ssl_finished_connects | 0 |
>| Ssl_session_cache_hits | 0 |
>| Ssl_session_cache_misses | 0 |
>| Ssl_session_cache_mode | NONE |
>| Ssl_session_cache_overflows | 0 |
>| Ssl_session_cache_size | 0 |
>| Ssl_session_cache_timeouts | 0 |
>| Ssl_sessions_reused | 0 |
>| Ssl_used_session_cache_entries | 0 |
>| Ssl_verify_depth | 0 |
>| Ssl_verify_mode | 0 |
>| Ssl_version | |
>| Table_locks_immediate | 101 |
>| Table_locks_waited | 0 |
>| Tc_log_max_pages_used | 0 |
>| Tc_log_page_size | 0 |
>| Tc_log_page_waits | 0 |
>| Threads_cached | 0 |
>| Threads_connected | 2 |
>| Threads_created | 2 |
>| Threads_running | 2 |
>| Uptime | 6952 |
>| Uptime_since_flush_status | 6952 |
>+-----------------------------------+-----------+
>
>Thanks,
>Mike Spreitzer
>
>
>
>mos
>
>06/25/09 02:32 AM
>To
>mysql@lists.mysql.com
>cc
>Subject
>Re: Indexing dynamics in MySQL Community Edition 5.1.34
>
>
>
>
>At 12:37 AM 6/25/2009, you wrote:
> >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?
> >
> >
> >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.
>
>Mike,
> You mean "key_buffer_size" don't you and not "key_buffer"? If you are
>using MySQL 5.0 then the upper limit is 4gb but 5.1 allows for more than 4gb.
>Also try increasing "sort_buffer_size".
>
>Posting your "Show Status" will help people see where the bottle neck is.
>
>Mike
>
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe: http://lists.mysql.com/mysql?unsub=mspreitz@us.ibm.com
>


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Indexing dynamics in MySQL Community Edition 5.1.34

--0016e64769b697e4a4046d3be61b
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

Who can please tell me what is mean of "The db storage is on fiber
channel."?

On Fri, Jun 26, 2009 at 1:05 AM, mos wrote:

> Mike,
> I re-posted your Show Status to the group to see if anyone can offer a
> way to speed up the indexing for you.
>
> BTW, you are adding ALL of the indexes to the table using ONE sql statement
> right? And not a separate SQL statement to build each index?
>
> Mike
>
> At 02:01 AM 6/25/2009, you wrote:
>
> Like I said in the subject line, I am using 5.1.34. I started with
>> my-huge.cnf, which says "key_buffer" rather than "key_buffer_size"; SHOW
>> GLOBAL VARIABLES confirms, however, that my key_buffer_size is 8GB.
>>
>> That indexing operation finally finished after about 1.5 hours; that was
>> about 0.5 hours ago. Now I am on to other things. Here is the status you
>> suggested:
>>
>> +-----------------------------------+-----------+
>> | Variable_name | Value |
>> +-----------------------------------+-----------+
>> | Aborted_clients | 0 |
>> | Aborted_connects | 0 |
>> | Binlog_cache_disk_use | 0 |
>> | Binlog_cache_use | 0 |
>> | Bytes_received | 135 |
>> | Bytes_sent | 1405 |
>> | Com_admin_commands | 0 |
>> | Com_assign_to_keycache | 0 |
>> | Com_alter_db | 0 |
>> | Com_alter_db_upgrade | 0 |
>> | Com_alter_event | 0 |
>> | Com_alter_function | 0 |
>> | Com_alter_procedure | 0 |
>> | Com_alter_server | 0 |
>> | Com_alter_table | 0 |
>> | Com_alter_tablespace | 0 |
>> | Com_analyze | 0 |
>> | Com_backup_table | 0 |
>> | Com_begin | 0 |
>> | Com_binlog | 0 |
>> | Com_call_procedure | 0 |
>> | Com_change_db | 0 |
>> | Com_change_master | 0 |
>> | Com_check | 0 |
>> | Com_checksum | 0 |
>> | Com_commit | 0 |
>> | Com_create_db | 0 |
>> | Com_create_event | 0 |
>> | Com_create_function | 0 |
>> | Com_create_index | 0 |
>> | Com_create_procedure | 0 |
>> | Com_create_server | 0 |
>> | Com_create_table | 0 |
>> | Com_create_trigger | 0 |
>> | Com_create_udf | 0 |
>> | Com_create_user | 0 |
>> | Com_create_view | 0 |
>> | Com_dealloc_sql | 0 |
>> | Com_delete | 0 |
>> | Com_delete_multi | 0 |
>> | Com_do | 0 |
>> | Com_drop_db | 0 |
>> | Com_drop_event | 0 |
>> | Com_drop_function | 0 |
>> | Com_drop_index | 0 |
>> | Com_drop_procedure | 0 |
>> | Com_drop_server | 0 |
>> | Com_drop_table | 0 |
>> | Com_drop_trigger | 0 |
>> | Com_drop_user | 0 |
>> | Com_drop_view | 0 |
>> | Com_empty_query | 0 |
>> | Com_execute_sql | 0 |
>> | Com_flush | 0 |
>> | Com_grant | 0 |
>> | Com_ha_close | 0 |
>> | Com_ha_open | 0 |
>> | Com_ha_read | 0 |
>> | Com_help | 0 |
>> | Com_insert | 0 |
>> | Com_insert_select | 0 |
>> | Com_install_plugin | 0 |
>> | Com_kill | 0 |
>> | Com_load | 0 |
>> | Com_load_master_data | 0 |
>> | Com_load_master_table | 0 |
>> | Com_lock_tables | 0 |
>> | Com_optimize | 0 |
>> | Com_preload_keys | 0 |
>> | Com_prepare_sql | 0 |
>> | Com_purge | 0 |
>> | Com_purge_before_date | 0 |
>> | Com_release_savepoint | 0 |
>> | Com_rename_table | 0 |
>> | Com_rename_user | 0 |
>> | Com_repair | 0 |
>> | Com_replace | 0 |
>> | Com_replace_select | 0 |
>> | Com_reset | 0 |
>> | Com_restore_table | 0 |
>> | Com_revoke | 0 |
>> | Com_revoke_all | 0 |
>> | Com_rollback | 0 |
>> | Com_rollback_to_savepoint | 0 |
>> | Com_savepoint | 0 |
>> | Com_select | 1 |
>> | Com_set_option | 0 |
>> | Com_show_authors | 0 |
>> | Com_show_binlog_events | 0 |
>> | Com_show_binlogs | 0 |
>> | Com_show_charsets | 0 |
>> | Com_show_collations | 0 |
>> | Com_show_column_types | 0 |
>> | Com_show_contributors | 0 |
>> | Com_show_create_db | 0 |
>> | Com_show_create_event | 0 |
>> | Com_show_create_func | 0 |
>> | Com_show_create_proc | 0 |
>> | Com_show_create_table | 0 |
>> | Com_show_create_trigger | 0 |
>> | Com_show_databases | 0 |
>> | Com_show_engine_logs | 0 |
>> | Com_show_engine_mutex | 0 |
>> | Com_show_engine_status | 0 |
>> | Com_show_events | 0 |
>> | Com_show_errors | 0 |
>> | Com_show_fields | 0 |
>> | Com_show_function_status | 0 |
>> | Com_show_grants | 0 |
>> | Com_show_keys | 0 |
>> | Com_show_master_status | 0 |
>> | Com_show_new_master | 0 |
>> | Com_show_open_tables | 0 |
>> | Com_show_plugins | 0 |
>> | Com_show_privileges | 0 |
>> | Com_show_procedure_status | 0 |
>> | Com_show_processlist | 0 |
>> | Com_show_profile | 0 |
>> | Com_show_profiles | 0 |
>> | Com_show_slave_hosts | 0 |
>> | Com_show_slave_status | 0 |
>> | Com_show_status | 1 |
>> | Com_show_storage_engines | 0 |
>> | Com_show_table_status | 0 |
>> | Com_show_tables | 0 |
>> | Com_show_triggers | 0 |
>> | Com_show_variables | 1 |
>> | Com_show_warnings | 0 |
>> | Com_slave_start | 0 |
>> | Com_slave_stop | 0 |
>> | Com_stmt_close | 0 |
>> | Com_stmt_execute | 0 |
>> | Com_stmt_fetch | 0 |
>> | Com_stmt_prepare | 0 |
>> | Com_stmt_reprepare | 0 |
>> | Com_stmt_reset | 0 |
>> | Com_stmt_send_long_data | 0 |
>> | Com_truncate | 0 |
>> | Com_uninstall_plugin | 0 |
>> | Com_unlock_tables | 0 |
>> | Com_update | 0 |
>> | Com_update_multi | 0 |
>> | Com_xa_commit | 0 |
>> | Com_xa_end | 0 |
>> | Com_xa_prepare | 0 |
>> | Com_xa_recover | 0 |
>> | Com_xa_rollback | 0 |
>> | Com_xa_start | 0 |
>> | Compression | OFF |
>> | Connections | 9 |
>> | Created_tmp_disk_tables | 0 |
>> | Created_tmp_files | 13 |
>> | Created_tmp_tables | 1 |
>> | Delayed_errors | 0 |
>> | Delayed_insert_threads | 0 |
>> | Delayed_writes | 0 |
>> | Flush_commands | 1 |
>> | Handler_commit | 0 |
>> | Handler_delete | 0 |
>> | Handler_discover | 0 |
>> | Handler_prepare | 0 |
>> | Handler_read_first | 0 |
>> | Handler_read_key | 0 |
>> | Handler_read_next | 0 |
>> | Handler_read_prev | 0 |
>> | Handler_read_rnd | 0 |
>> | Handler_read_rnd_next | 34 |
>> | Handler_rollback | 0 |
>> | Handler_savepoint | 0 |
>> | Handler_savepoint_rollback | 0 |
>> | Handler_update | 0 |
>> | Handler_write | 33 |
>> | Innodb_buffer_pool_pages_data | 19 |
>> | Innodb_buffer_pool_pages_dirty | 0 |
>> | Innodb_buffer_pool_pages_flushed | 0 |
>> | Innodb_buffer_pool_pages_free | 493 |
>> | Innodb_buffer_pool_pages_misc | 0 |
>> | Innodb_buffer_pool_pages_total | 512 |
>> | Innodb_buffer_pool_read_ahead_rnd | 1 |
>> | Innodb_buffer_pool_read_ahead_seq | 0 |
>> | Innodb_buffer_pool_read_requests | 77 |
>> | Innodb_buffer_pool_reads | 12 |
>> | Innodb_buffer_pool_wait_free | 0 |
>> | Innodb_buffer_pool_write_requests | 0 |
>> | Innodb_data_fsyncs | 3 |
>> | Innodb_data_pending_fsyncs | 0 |
>> | Innodb_data_pending_reads | 0 |
>> | Innodb_data_pending_writes | 0 |
>> | Innodb_data_read | 2494464 |
>> | Innodb_data_reads | 25 |
>> | Innodb_data_writes | 3 |
>> | Innodb_data_written | 1536 |
>> | Innodb_dblwr_pages_written | 0 |
>> | Innodb_dblwr_writes | 0 |
>> | Innodb_log_waits | 0 |
>> | Innodb_log_write_requests | 0 |
>> | Innodb_log_writes | 1 |
>> | Innodb_os_log_fsyncs | 3 |
>> | Innodb_os_log_pending_fsyncs | 0 |
>> | Innodb_os_log_pending_writes | 0 |
>> | Innodb_os_log_written | 512 |
>> | Innodb_page_size | 16384 |
>> | Innodb_pages_created | 0 |
>> | Innodb_pages_read | 19 |
>> | Innodb_pages_written | 0 |
>> | Innodb_row_lock_current_waits | 0 |
>> | Innodb_row_lock_time | 0 |
>> | Innodb_row_lock_time_avg | 0 |
>> | Innodb_row_lock_time_max | 0 |
>> | Innodb_row_lock_waits | 0 |
>> | Innodb_rows_deleted | 0 |
>> | Innodb_rows_inserted | 0 |
>> | Innodb_rows_read | 0 |
>> | Innodb_rows_updated | 0 |
>> | Key_blocks_not_flushed | 26 |
>> | Key_blocks_unused | 6844469 |
>> | Key_blocks_used | 676158 |
>> | Key_read_requests | 635624989 |
>> | Key_reads | 12664 |
>> | Key_write_requests | 112359740 |
>> | Key_writes | 689890 |
>> | Last_query_cost | 10.499000 |
>> | Max_used_connections | 2 |
>> | Not_flushed_delayed_rows | 0 |
>> | Open_files | 70 |
>> | Open_streams | 0 |
>> | Open_table_definitions | 38 |
>> | Open_tables | 37 |
>> | Opened_files | 312 |
>> | Opened_table_definitions | 0 |
>> | Opened_tables | 0 |
>> | Prepared_stmt_count | 0 |
>> | Qcache_free_blocks | 1 |
>> | Qcache_free_memory | 33536880 |
>> | Qcache_hits | 0 |
>> | Qcache_inserts | 0 |
>> | Qcache_lowmem_prunes | 0 |
>> | Qcache_not_cached | 12 |
>> | Qcache_queries_in_cache | 0 |
>> | Qcache_total_blocks | 1 |
>> | Queries | 98 |
>> | Questions | 3 |
>> | Rpl_status | NULL |
>> | Select_full_join | 0 |
>> | Select_full_range_join | 0 |
>> | Select_range | 0 |
>> | Select_range_check | 0 |
>> | Select_scan | 1 |
>> | Slave_open_temp_tables | 0 |
>> | Slave_retried_transactions | 0 |
>> | Slave_running | OFF |
>> | Slow_launch_threads | 0 |
>> | Slow_queries | 0 |
>> | Sort_merge_passes | 0 |
>> | Sort_range | 0 |
>> | Sort_rows | 0 |
>> | Sort_scan | 0 |
>> | Ssl_accept_renegotiates | 0 |
>> | Ssl_accepts | 0 |
>> | Ssl_callback_cache_hits | 0 |
>> | Ssl_cipher | |
>> | Ssl_cipher_list | |
>> | Ssl_client_connects | 0 |
>> | Ssl_connect_renegotiates | 0 |
>> | Ssl_ctx_verify_depth | 0 |
>> | Ssl_ctx_verify_mode | 0 |
>> | Ssl_default_timeout | 0 |
>> | Ssl_finished_accepts | 0 |
>> | Ssl_finished_connects | 0 |
>> | Ssl_session_cache_hits | 0 |
>> | Ssl_session_cache_misses | 0 |
>> | Ssl_session_cache_mode | NONE |
>> | Ssl_session_cache_overflows | 0 |
>> | Ssl_session_cache_size | 0 |
>> | Ssl_session_cache_timeouts | 0 |
>> | Ssl_sessions_reused | 0 |
>> | Ssl_used_session_cache_entries | 0 |
>> | Ssl_verify_depth | 0 |
>> | Ssl_verify_mode | 0 |
>> | Ssl_version | |
>> | Table_locks_immediate | 101 |
>> | Table_locks_waited | 0 |
>> | Tc_log_max_pages_used | 0 |
>> | Tc_log_page_size | 0 |
>> | Tc_log_page_waits | 0 |
>> | Threads_cached | 0 |
>> | Threads_connected | 2 |
>> | Threads_created | 2 |
>> | Threads_running | 2 |
>> | Uptime | 6952 |
>> | Uptime_since_flush_status | 6952 |
>> +-----------------------------------+-----------+
>>
>> Thanks,
>> Mike Spreitzer
>>
>>
>>
>> mos
>>
>> 06/25/09 02:32 AM
>> To
>> mysql@lists.mysql.com
>> cc
>> Subject
>> Re: Indexing dynamics in MySQL Community Edition 5.1.34
>>
>>
>>
>>
>> At 12:37 AM 6/25/2009, you wrote:
>> >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?
>> >
>> >
>> >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.
>>
>> Mike,
>> You mean "key_buffer_size" don't you and not "key_buffer"? If you are
>> using MySQL 5.0 then the upper limit is 4gb but 5.1 allows for more than
>> 4gb.
>> Also try increasing "sort_buffer_size".
>>
>> Posting your "Show Status" will help people see where the bottle neck is.
>>
>> Mike
>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: http://lists.mysql.com/mysql?unsub=mspreitz@us.ibm.com
>>
>>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=yueliangdao0608@gmail.com
>
>


--
David Yeung,
MySQL Senior Support Engineer,
Sun Gold Partner.
My Blog:http://yueliangdao0608.cublog.cn

--0016e64769b697e4a4046d3be61b--

RE: Indexing dynamics in MySQL Community Edition 5.1.34

>-----Original Message-----
>From: Moon's Father [mailto:yueliangdao0608@gmail.com]
>Sent: Friday, June 26, 2009 4:12 AM
>To: mos
>Cc: mysql@lists.mysql.com
>Subject: Re: Indexing dynamics in MySQL Community Edition 5.1.34
>
>Who can please tell me what is mean of "The db storage is on fiber
>channel."?
>
[JS] Fiber Channel (and that is the official way it is spelled, even in the
USA) is an ultra-high-speed network for disk arrays, computers, and other
devices. Although the physical medium is usual fiber optic, there is a lot
more to it than that. It is more like an intranet in some ways, in that
devices have addresses and there is intelligence in the network itself.

>On Fri, Jun 26, 2009 at 1:05 AM, mos wrote:
>
>> Mike,
>> I re-posted your Show Status to the group to see if anyone can
>offer a
>> way to speed up the indexing for you.
>>
>> BTW, you are adding ALL of the indexes to the table using ONE sql
>statement
>> right? And not a separate SQL statement to build each index?
>>
>> Mike
>>
>> At 02:01 AM 6/25/2009, you wrote:
>>
>> Like I said in the subject line, I am using 5.1.34. I started with
>>> my-huge.cnf, which says "key_buffer" rather than "key_buffer_size";
>SHOW
>>> GLOBAL VARIABLES confirms, however, that my key_buffer_size is 8GB.
>>>
>>> That indexing operation finally finished after about 1.5 hours; that
>was
>>> about 0.5 hours ago. Now I am on to other things. Here is the
>status you
>>> suggested:
>>>
>>> +-----------------------------------+-----------+
>>> | Variable_name | Value |
>>> +-----------------------------------+-----------+
>>> | Aborted_clients | 0 |
>>> | Aborted_connects | 0 |
>>> | Binlog_cache_disk_use | 0 |
>>> | Binlog_cache_use | 0 |
>>> | Bytes_received | 135 |
>>> | Bytes_sent | 1405 |
>>> | Com_admin_commands | 0 |
>>> | Com_assign_to_keycache | 0 |
>>> | Com_alter_db | 0 |
>>> | Com_alter_db_upgrade | 0 |
>>> | Com_alter_event | 0 |
>>> | Com_alter_function | 0 |
>>> | Com_alter_procedure | 0 |
>>> | Com_alter_server | 0 |
>>> | Com_alter_table | 0 |
>>> | Com_alter_tablespace | 0 |
>>> | Com_analyze | 0 |
>>> | Com_backup_table | 0 |
>>> | Com_begin | 0 |
>>> | Com_binlog | 0 |
>>> | Com_call_procedure | 0 |
>>> | Com_change_db | 0 |
>>> | Com_change_master | 0 |
>>> | Com_check | 0 |
>>> | Com_checksum | 0 |
>>> | Com_commit | 0 |
>>> | Com_create_db | 0 |
>>> | Com_create_event | 0 |
>>> | Com_create_function | 0 |
>>> | Com_create_index | 0 |
>>> | Com_create_procedure | 0 |
>>> | Com_create_server | 0 |
>>> | Com_create_table | 0 |
>>> | Com_create_trigger | 0 |
>>> | Com_create_udf | 0 |
>>> | Com_create_user | 0 |
>>> | Com_create_view | 0 |
>>> | Com_dealloc_sql | 0 |
>>> | Com_delete | 0 |
>>> | Com_delete_multi | 0 |
>>> | Com_do | 0 |
>>> | Com_drop_db | 0 |
>>> | Com_drop_event | 0 |
>>> | Com_drop_function | 0 |
>>> | Com_drop_index | 0 |
>>> | Com_drop_procedure | 0 |
>>> | Com_drop_server | 0 |
>>> | Com_drop_table | 0 |
>>> | Com_drop_trigger | 0 |
>>> | Com_drop_user | 0 |
>>> | Com_drop_view | 0 |
>>> | Com_empty_query | 0 |
>>> | Com_execute_sql | 0 |
>>> | Com_flush | 0 |
>>> | Com_grant | 0 |
>>> | Com_ha_close | 0 |
>>> | Com_ha_open | 0 |
>>> | Com_ha_read | 0 |
>>> | Com_help | 0 |
>>> | Com_insert | 0 |
>>> | Com_insert_select | 0 |
>>> | Com_install_plugin | 0 |
>>> | Com_kill | 0 |
>>> | Com_load | 0 |
>>> | Com_load_master_data | 0 |
>>> | Com_load_master_table | 0 |
>>> | Com_lock_tables | 0 |
>>> | Com_optimize | 0 |
>>> | Com_preload_keys | 0 |
>>> | Com_prepare_sql | 0 |
>>> | Com_purge | 0 |
>>> | Com_purge_before_date | 0 |
>>> | Com_release_savepoint | 0 |
>>> | Com_rename_table | 0 |
>>> | Com_rename_user | 0 |
>>> | Com_repair | 0 |
>>> | Com_replace | 0 |
>>> | Com_replace_select | 0 |
>>> | Com_reset | 0 |
>>> | Com_restore_table | 0 |
>>> | Com_revoke | 0 |
>>> | Com_revoke_all | 0 |
>>> | Com_rollback | 0 |
>>> | Com_rollback_to_savepoint | 0 |
>>> | Com_savepoint | 0 |
>>> | Com_select | 1 |
>>> | Com_set_option | 0 |
>>> | Com_show_authors | 0 |
>>> | Com_show_binlog_events | 0 |
>>> | Com_show_binlogs | 0 |
>>> | Com_show_charsets | 0 |
>>> | Com_show_collations | 0 |
>>> | Com_show_column_types | 0 |
>>> | Com_show_contributors | 0 |
>>> | Com_show_create_db | 0 |
>>> | Com_show_create_event | 0 |
>>> | Com_show_create_func | 0 |
>>> | Com_show_create_proc | 0 |
>>> | Com_show_create_table | 0 |
>>> | Com_show_create_trigger | 0 |
>>> | Com_show_databases | 0 |
>>> | Com_show_engine_logs | 0 |
>>> | Com_show_engine_mutex | 0 |
>>> | Com_show_engine_status | 0 |
>>> | Com_show_events | 0 |
>>> | Com_show_errors | 0 |
>>> | Com_show_fields | 0 |
>>> | Com_show_function_status | 0 |
>>> | Com_show_grants | 0 |
>>> | Com_show_keys | 0 |
>>> | Com_show_master_status | 0 |
>>> | Com_show_new_master | 0 |
>>> | Com_show_open_tables | 0 |
>>> | Com_show_plugins | 0 |
>>> | Com_show_privileges | 0 |
>>> | Com_show_procedure_status | 0 |
>>> | Com_show_processlist | 0 |
>>> | Com_show_profile | 0 |
>>> | Com_show_profiles | 0 |
>>> | Com_show_slave_hosts | 0 |
>>> | Com_show_slave_status | 0 |
>>> | Com_show_status | 1 |
>>> | Com_show_storage_engines | 0 |
>>> | Com_show_table_status | 0 |
>>> | Com_show_tables | 0 |
>>> | Com_show_triggers | 0 |
>>> | Com_show_variables | 1 |
>>> | Com_show_warnings | 0 |
>>> | Com_slave_start | 0 |
>>> | Com_slave_stop | 0 |
>>> | Com_stmt_close | 0 |
>>> | Com_stmt_execute | 0 |
>>> | Com_stmt_fetch | 0 |
>>> | Com_stmt_prepare | 0 |
>>> | Com_stmt_reprepare | 0 |
>>> | Com_stmt_reset | 0 |
>>> | Com_stmt_send_long_data | 0 |
>>> | Com_truncate | 0 |
>>> | Com_uninstall_plugin | 0 |
>>> | Com_unlock_tables | 0 |
>>> | Com_update | 0 |
>>> | Com_update_multi | 0 |
>>> | Com_xa_commit | 0 |
>>> | Com_xa_end | 0 |
>>> | Com_xa_prepare | 0 |
>>> | Com_xa_recover | 0 |
>>> | Com_xa_rollback | 0 |
>>> | Com_xa_start | 0 |
>>> | Compression | OFF |
>>> | Connections | 9 |
>>> | Created_tmp_disk_tables | 0 |
>>> | Created_tmp_files | 13 |
>>> | Created_tmp_tables | 1 |
>>> | Delayed_errors | 0 |
>>> | Delayed_insert_threads | 0 |
>>> | Delayed_writes | 0 |
>>> | Flush_commands | 1 |
>>> | Handler_commit | 0 |
>>> | Handler_delete | 0 |
>>> | Handler_discover | 0 |
>>> | Handler_prepare | 0 |
>>> | Handler_read_first | 0 |
>>> | Handler_read_key | 0 |
>>> | Handler_read_next | 0 |
>>> | Handler_read_prev | 0 |
>>> | Handler_read_rnd | 0 |
>>> | Handler_read_rnd_next | 34 |
>>> | Handler_rollback | 0 |
>>> | Handler_savepoint | 0 |
>>> | Handler_savepoint_rollback | 0 |
>>> | Handler_update | 0 |
>>> | Handler_write | 33 |
>>> | Innodb_buffer_pool_pages_data | 19 |
>>> | Innodb_buffer_pool_pages_dirty | 0 |
>>> | Innodb_buffer_pool_pages_flushed | 0 |
>>> | Innodb_buffer_pool_pages_free | 493 |
>>> | Innodb_buffer_pool_pages_misc | 0 |
>>> | Innodb_buffer_pool_pages_total | 512 |
>>> | Innodb_buffer_pool_read_ahead_rnd | 1 |
>>> | Innodb_buffer_pool_read_ahead_seq | 0 |
>>> | Innodb_buffer_pool_read_requests | 77 |
>>> | Innodb_buffer_pool_reads | 12 |
>>> | Innodb_buffer_pool_wait_free | 0 |
>>> | Innodb_buffer_pool_write_requests | 0 |
>>> | Innodb_data_fsyncs | 3 |
>>> | Innodb_data_pending_fsyncs | 0 |
>>> | Innodb_data_pending_reads | 0 |
>>> | Innodb_data_pending_writes | 0 |
>>> | Innodb_data_read | 2494464 |
>>> | Innodb_data_reads | 25 |
>>> | Innodb_data_writes | 3 |
>>> | Innodb_data_written | 1536 |
>>> | Innodb_dblwr_pages_written | 0 |
>>> | Innodb_dblwr_writes | 0 |
>>> | Innodb_log_waits | 0 |
>>> | Innodb_log_write_requests | 0 |
>>> | Innodb_log_writes | 1 |
>>> | Innodb_os_log_fsyncs | 3 |
>>> | Innodb_os_log_pending_fsyncs | 0 |
>>> | Innodb_os_log_pending_writes | 0 |
>>> | Innodb_os_log_written | 512 |
>>> | Innodb_page_size | 16384 |
>>> | Innodb_pages_created | 0 |
>>> | Innodb_pages_read | 19 |
>>> | Innodb_pages_written | 0 |
>>> | Innodb_row_lock_current_waits | 0 |
>>> | Innodb_row_lock_time | 0 |
>>> | Innodb_row_lock_time_avg | 0 |
>>> | Innodb_row_lock_time_max | 0 |
>>> | Innodb_row_lock_waits | 0 |
>>> | Innodb_rows_deleted | 0 |
>>> | Innodb_rows_inserted | 0 |
>>> | Innodb_rows_read | 0 |
>>> | Innodb_rows_updated | 0 |
>>> | Key_blocks_not_flushed | 26 |
>>> | Key_blocks_unused | 6844469 |
>>> | Key_blocks_used | 676158 |
>>> | Key_read_requests | 635624989 |
>>> | Key_reads | 12664 |
>>> | Key_write_requests | 112359740 |
>>> | Key_writes | 689890 |
>>> | Last_query_cost | 10.499000 |
>>> | Max_used_connections | 2 |
>>> | Not_flushed_delayed_rows | 0 |
>>> | Open_files | 70 |
>>> | Open_streams | 0 |
>>> | Open_table_definitions | 38 |
>>> | Open_tables | 37 |
>>> | Opened_files | 312 |
>>> | Opened_table_definitions | 0 |
>>> | Opened_tables | 0 |
>>> | Prepared_stmt_count | 0 |
>>> | Qcache_free_blocks | 1 |
>>> | Qcache_free_memory | 33536880 |
>>> | Qcache_hits | 0 |
>>> | Qcache_inserts | 0 |
>>> | Qcache_lowmem_prunes | 0 |
>>> | Qcache_not_cached | 12 |
>>> | Qcache_queries_in_cache | 0 |
>>> | Qcache_total_blocks | 1 |
>>> | Queries | 98 |
>>> | Questions | 3 |
>>> | Rpl_status | NULL |
>>> | Select_full_join | 0 |
>>> | Select_full_range_join | 0 |
>>> | Select_range | 0 |
>>> | Select_range_check | 0 |
>>> | Select_scan | 1 |
>>> | Slave_open_temp_tables | 0 |
>>> | Slave_retried_transactions | 0 |
>>> | Slave_running | OFF |
>>> | Slow_launch_threads | 0 |
>>> | Slow_queries | 0 |
>>> | Sort_merge_passes | 0 |
>>> | Sort_range | 0 |
>>> | Sort_rows | 0 |
>>> | Sort_scan | 0 |
>>> | Ssl_accept_renegotiates | 0 |
>>> | Ssl_accepts | 0 |
>>> | Ssl_callback_cache_hits | 0 |
>>> | Ssl_cipher | |
>>> | Ssl_cipher_list | |
>>> | Ssl_client_connects | 0 |
>>> | Ssl_connect_renegotiates | 0 |
>>> | Ssl_ctx_verify_depth | 0 |
>>> | Ssl_ctx_verify_mode | 0 |
>>> | Ssl_default_timeout | 0 |
>>> | Ssl_finished_accepts | 0 |
>>> | Ssl_finished_connects | 0 |
>>> | Ssl_session_cache_hits | 0 |
>>> | Ssl_session_cache_misses | 0 |
>>> | Ssl_session_cache_mode | NONE |
>>> | Ssl_session_cache_overflows | 0 |
>>> | Ssl_session_cache_size | 0 |
>>> | Ssl_session_cache_timeouts | 0 |
>>> | Ssl_sessions_reused | 0 |
>>> | Ssl_used_session_cache_entries | 0 |
>>> | Ssl_verify_depth | 0 |
>>> | Ssl_verify_mode | 0 |
>>> | Ssl_version | |
>>> | Table_locks_immediate | 101 |
>>> | Table_locks_waited | 0 |
>>> | Tc_log_max_pages_used | 0 |
>>> | Tc_log_page_size | 0 |
>>> | Tc_log_page_waits | 0 |
>>> | Threads_cached | 0 |
>>> | Threads_connected | 2 |
>>> | Threads_created | 2 |
>>> | Threads_running | 2 |
>>> | Uptime | 6952 |
>>> | Uptime_since_flush_status | 6952 |
>>> +-----------------------------------+-----------+
>>>
>>> Thanks,
>>> Mike Spreitzer
>>>
>>>
>>>
>>> mos
>>>
>>> 06/25/09 02:32 AM
>>> To
>>> mysql@lists.mysql.com
>>> cc
>>> Subject
>>> Re: Indexing dynamics in MySQL Community Edition 5.1.34
>>>
>>>
>>>
>>>
>>> At 12:37 AM 6/25/2009, you wrote:
>>> >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?
>>> >
>>> >
>>> >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.
>>>
>>> Mike,
>>> You mean "key_buffer_size" don't you and not "key_buffer"? If you
>are
>>> using MySQL 5.0 then the upper limit is 4gb but 5.1 allows for more
>than
>>> 4gb.
>>> Also try increasing "sort_buffer_size".
>>>
>>> Posting your "Show Status" will help people see where the bottle neck
>is.
>>>
>>> Mike
>>>
>>>
>>> --
>>> MySQL General Mailing List
>>> For list archives: http://lists.mysql.com/mysql
>>> To unsubscribe:
>http://lists.mysql.com/mysql?unsub=mspreitz@us.ibm.com
>>>
>>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:
>> http://lists.mysql.com/mysql?unsub=yueliangdao0608@gmail.com
>>
>>
>
>
>--
>David Yeung,
>MySQL Senior Support Engineer,
>Sun Gold Partner.
>My Blog:http://yueliangdao0608.cublog.cn




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: Indexing dynamics in MySQL Community Edition 5.1.34

Oops, my spell checker got the better of me: it should be "Fibre" Channel.

Regards,
 
Jerry Schwartz
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
 
860.674.8796 / FAX: 860.674.8341
 
www.the-infoshop.com




>-----Original Message-----
>From: Jerry Schwartz [mailto:jschwartz@the-infoshop.com]
>Sent: Friday, June 26, 2009 10:22 AM
>To: 'Moon's Father'; 'mos'
>Cc: mysql@lists.mysql.com
>Subject: RE: Indexing dynamics in MySQL Community Edition 5.1.34
>
>
>
>>-----Original Message-----
>>From: Moon's Father [mailto:yueliangdao0608@gmail.com]
>>Sent: Friday, June 26, 2009 4:12 AM
>>To: mos
>>Cc: mysql@lists.mysql.com
>>Subject: Re: Indexing dynamics in MySQL Community Edition 5.1.34
>>
>>Who can please tell me what is mean of "The db storage is on fiber
>>channel."?
>>
>[JS] Fiber Channel (and that is the official way it is spelled, even in
>the
>USA) is an ultra-high-speed network for disk arrays, computers, and
>other
>devices. Although the physical medium is usual fiber optic, there is a
>lot
>more to it than that. It is more like an intranet in some ways, in that
>devices have addresses and there is intelligence in the network itself.
>
>>On Fri, Jun 26, 2009 at 1:05 AM, mos wrote:
>>
>>> Mike,
>>> I re-posted your Show Status to the group to see if anyone can
>>offer a
>>> way to speed up the indexing for you.
>>>
>>> BTW, you are adding ALL of the indexes to the table using ONE sql
>>statement
>>> right? And not a separate SQL statement to build each index?
>>>
>>> Mike
>>>
>>> At 02:01 AM 6/25/2009, you wrote:
>>>
>>> Like I said in the subject line, I am using 5.1.34. I started with
>>>> my-huge.cnf, which says "key_buffer" rather than "key_buffer_size";
>>SHOW
>>>> GLOBAL VARIABLES confirms, however, that my key_buffer_size is 8GB.
>>>>
>>>> That indexing operation finally finished after about 1.5 hours; that
>>was
>>>> about 0.5 hours ago. Now I am on to other things. Here is the
>>status you
>>>> suggested:
>>>>
>>>> +-----------------------------------+-----------+
>>>> | Variable_name | Value |
>>>> +-----------------------------------+-----------+
>>>> | Aborted_clients | 0 |
>>>> | Aborted_connects | 0 |
>>>> | Binlog_cache_disk_use | 0 |
>>>> | Binlog_cache_use | 0 |
>>>> | Bytes_received | 135 |
>>>> | Bytes_sent | 1405 |
>>>> | Com_admin_commands | 0 |
>>>> | Com_assign_to_keycache | 0 |
>>>> | Com_alter_db | 0 |
>>>> | Com_alter_db_upgrade | 0 |
>>>> | Com_alter_event | 0 |
>>>> | Com_alter_function | 0 |
>>>> | Com_alter_procedure | 0 |
>>>> | Com_alter_server | 0 |
>>>> | Com_alter_table | 0 |
>>>> | Com_alter_tablespace | 0 |
>>>> | Com_analyze | 0 |
>>>> | Com_backup_table | 0 |
>>>> | Com_begin | 0 |
>>>> | Com_binlog | 0 |
>>>> | Com_call_procedure | 0 |
>>>> | Com_change_db | 0 |
>>>> | Com_change_master | 0 |
>>>> | Com_check | 0 |
>>>> | Com_checksum | 0 |
>>>> | Com_commit | 0 |
>>>> | Com_create_db | 0 |
>>>> | Com_create_event | 0 |
>>>> | Com_create_function | 0 |
>>>> | Com_create_index | 0 |
>>>> | Com_create_procedure | 0 |
>>>> | Com_create_server | 0 |
>>>> | Com_create_table | 0 |
>>>> | Com_create_trigger | 0 |
>>>> | Com_create_udf | 0 |
>>>> | Com_create_user | 0 |
>>>> | Com_create_view | 0 |
>>>> | Com_dealloc_sql | 0 |
>>>> | Com_delete | 0 |
>>>> | Com_delete_multi | 0 |
>>>> | Com_do | 0 |
>>>> | Com_drop_db | 0 |
>>>> | Com_drop_event | 0 |
>>>> | Com_drop_function | 0 |
>>>> | Com_drop_index | 0 |
>>>> | Com_drop_procedure | 0 |
>>>> | Com_drop_server | 0 |
>>>> | Com_drop_table | 0 |
>>>> | Com_drop_trigger | 0 |
>>>> | Com_drop_user | 0 |
>>>> | Com_drop_view | 0 |
>>>> | Com_empty_query | 0 |
>>>> | Com_execute_sql | 0 |
>>>> | Com_flush | 0 |
>>>> | Com_grant | 0 |
>>>> | Com_ha_close | 0 |
>>>> | Com_ha_open | 0 |
>>>> | Com_ha_read | 0 |
>>>> | Com_help | 0 |
>>>> | Com_insert | 0 |
>>>> | Com_insert_select | 0 |
>>>> | Com_install_plugin | 0 |
>>>> | Com_kill | 0 |
>>>> | Com_load | 0 |
>>>> | Com_load_master_data | 0 |
>>>> | Com_load_master_table | 0 |
>>>> | Com_lock_tables | 0 |
>>>> | Com_optimize | 0 |
>>>> | Com_preload_keys | 0 |
>>>> | Com_prepare_sql | 0 |
>>>> | Com_purge | 0 |
>>>> | Com_purge_before_date | 0 |
>>>> | Com_release_savepoint | 0 |
>>>> | Com_rename_table | 0 |
>>>> | Com_rename_user | 0 |
>>>> | Com_repair | 0 |
>>>> | Com_replace | 0 |
>>>> | Com_replace_select | 0 |
>>>> | Com_reset | 0 |
>>>> | Com_restore_table | 0 |
>>>> | Com_revoke | 0 |
>>>> | Com_revoke_all | 0 |
>>>> | Com_rollback | 0 |
>>>> | Com_rollback_to_savepoint | 0 |
>>>> | Com_savepoint | 0 |
>>>> | Com_select | 1 |
>>>> | Com_set_option | 0 |
>>>> | Com_show_authors | 0 |
>>>> | Com_show_binlog_events | 0 |
>>>> | Com_show_binlogs | 0 |
>>>> | Com_show_charsets | 0 |
>>>> | Com_show_collations | 0 |
>>>> | Com_show_column_types | 0 |
>>>> | Com_show_contributors | 0 |
>>>> | Com_show_create_db | 0 |
>>>> | Com_show_create_event | 0 |
>>>> | Com_show_create_func | 0 |
>>>> | Com_show_create_proc | 0 |
>>>> | Com_show_create_table | 0 |
>>>> | Com_show_create_trigger | 0 |
>>>> | Com_show_databases | 0 |
>>>> | Com_show_engine_logs | 0 |
>>>> | Com_show_engine_mutex | 0 |
>>>> | Com_show_engine_status | 0 |
>>>> | Com_show_events | 0 |
>>>> | Com_show_errors | 0 |
>>>> | Com_show_fields | 0 |
>>>> | Com_show_function_status | 0 |
>>>> | Com_show_grants | 0 |
>>>> | Com_show_keys | 0 |
>>>> | Com_show_master_status | 0 |
>>>> | Com_show_new_master | 0 |
>>>> | Com_show_open_tables | 0 |
>>>> | Com_show_plugins | 0 |
>>>> | Com_show_privileges | 0 |
>>>> | Com_show_procedure_status | 0 |
>>>> | Com_show_processlist | 0 |
>>>> | Com_show_profile | 0 |
>>>> | Com_show_profiles | 0 |
>>>> | Com_show_slave_hosts | 0 |
>>>> | Com_show_slave_status | 0 |
>>>> | Com_show_status | 1 |
>>>> | Com_show_storage_engines | 0 |
>>>> | Com_show_table_status | 0 |
>>>> | Com_show_tables | 0 |
>>>> | Com_show_triggers | 0 |
>>>> | Com_show_variables | 1 |
>>>> | Com_show_warnings | 0 |
>>>> | Com_slave_start | 0 |
>>>> | Com_slave_stop | 0 |
>>>> | Com_stmt_close | 0 |
>>>> | Com_stmt_execute | 0 |
>>>> | Com_stmt_fetch | 0 |
>>>> | Com_stmt_prepare | 0 |
>>>> | Com_stmt_reprepare | 0 |
>>>> | Com_stmt_reset | 0 |
>>>> | Com_stmt_send_long_data | 0 |
>>>> | Com_truncate | 0 |
>>>> | Com_uninstall_plugin | 0 |
>>>> | Com_unlock_tables | 0 |
>>>> | Com_update | 0 |
>>>> | Com_update_multi | 0 |
>>>> | Com_xa_commit | 0 |
>>>> | Com_xa_end | 0 |
>>>> | Com_xa_prepare | 0 |
>>>> | Com_xa_recover | 0 |
>>>> | Com_xa_rollback | 0 |
>>>> | Com_xa_start | 0 |
>>>> | Compression | OFF |
>>>> | Connections | 9 |
>>>> | Created_tmp_disk_tables | 0 |
>>>> | Created_tmp_files | 13 |
>>>> | Created_tmp_tables | 1 |
>>>> | Delayed_errors | 0 |
>>>> | Delayed_insert_threads | 0 |
>>>> | Delayed_writes | 0 |
>>>> | Flush_commands | 1 |
>>>> | Handler_commit | 0 |
>>>> | Handler_delete | 0 |
>>>> | Handler_discover | 0 |
>>>> | Handler_prepare | 0 |
>>>> | Handler_read_first | 0 |
>>>> | Handler_read_key | 0 |
>>>> | Handler_read_next | 0 |
>>>> | Handler_read_prev | 0 |
>>>> | Handler_read_rnd | 0 |
>>>> | Handler_read_rnd_next | 34 |
>>>> | Handler_rollback | 0 |
>>>> | Handler_savepoint | 0 |
>>>> | Handler_savepoint_rollback | 0 |
>>>> | Handler_update | 0 |
>>>> | Handler_write | 33 |
>>>> | Innodb_buffer_pool_pages_data | 19 |
>>>> | Innodb_buffer_pool_pages_dirty | 0 |
>>>> | Innodb_buffer_pool_pages_flushed | 0 |
>>>> | Innodb_buffer_pool_pages_free | 493 |
>>>> | Innodb_buffer_pool_pages_misc | 0 |
>>>> | Innodb_buffer_pool_pages_total | 512 |
>>>> | Innodb_buffer_pool_read_ahead_rnd | 1 |
>>>> | Innodb_buffer_pool_read_ahead_seq | 0 |
>>>> | Innodb_buffer_pool_read_requests | 77 |
>>>> | Innodb_buffer_pool_reads | 12 |
>>>> | Innodb_buffer_pool_wait_free | 0 |
>>>> | Innodb_buffer_pool_write_requests | 0 |
>>>> | Innodb_data_fsyncs | 3 |
>>>> | Innodb_data_pending_fsyncs | 0 |
>>>> | Innodb_data_pending_reads | 0 |
>>>> | Innodb_data_pending_writes | 0 |
>>>> | Innodb_data_read | 2494464 |
>>>> | Innodb_data_reads | 25 |
>>>> | Innodb_data_writes | 3 |
>>>> | Innodb_data_written | 1536 |
>>>> | Innodb_dblwr_pages_written | 0 |
>>>> | Innodb_dblwr_writes | 0 |
>>>> | Innodb_log_waits | 0 |
>>>> | Innodb_log_write_requests | 0 |
>>>> | Innodb_log_writes | 1 |
>>>> | Innodb_os_log_fsyncs | 3 |
>>>> | Innodb_os_log_pending_fsyncs | 0 |
>>>> | Innodb_os_log_pending_writes | 0 |
>>>> | Innodb_os_log_written | 512 |
>>>> | Innodb_page_size | 16384 |
>>>> | Innodb_pages_created | 0 |
>>>> | Innodb_pages_read | 19 |
>>>> | Innodb_pages_written | 0 |
>>>> | Innodb_row_lock_current_waits | 0 |
>>>> | Innodb_row_lock_time | 0 |
>>>> | Innodb_row_lock_time_avg | 0 |
>>>> | Innodb_row_lock_time_max | 0 |
>>>> | Innodb_row_lock_waits | 0 |
>>>> | Innodb_rows_deleted | 0 |
>>>> | Innodb_rows_inserted | 0 |
>>>> | Innodb_rows_read | 0 |
>>>> | Innodb_rows_updated | 0 |
>>>> | Key_blocks_not_flushed | 26 |
>>>> | Key_blocks_unused | 6844469 |
>>>> | Key_blocks_used | 676158 |
>>>> | Key_read_requests | 635624989 |
>>>> | Key_reads | 12664 |
>>>> | Key_write_requests | 112359740 |
>>>> | Key_writes | 689890 |
>>>> | Last_query_cost | 10.499000 |
>>>> | Max_used_connections | 2 |
>>>> | Not_flushed_delayed_rows | 0 |
>>>> | Open_files | 70 |
>>>> | Open_streams | 0 |
>>>> | Open_table_definitions | 38 |
>>>> | Open_tables | 37 |
>>>> | Opened_files | 312 |
>>>> | Opened_table_definitions | 0 |
>>>> | Opened_tables | 0 |
>>>> | Prepared_stmt_count | 0 |
>>>> | Qcache_free_blocks | 1 |
>>>> | Qcache_free_memory | 33536880 |
>>>> | Qcache_hits | 0 |
>>>> | Qcache_inserts | 0 |
>>>> | Qcache_lowmem_prunes | 0 |
>>>> | Qcache_not_cached | 12 |
>>>> | Qcache_queries_in_cache | 0 |
>>>> | Qcache_total_blocks | 1 |
>>>> | Queries | 98 |
>>>> | Questions | 3 |
>>>> | Rpl_status | NULL |
>>>> | Select_full_join | 0 |
>>>> | Select_full_range_join | 0 |
>>>> | Select_range | 0 |
>>>> | Select_range_check | 0 |
>>>> | Select_scan | 1 |
>>>> | Slave_open_temp_tables | 0 |
>>>> | Slave_retried_transactions | 0 |
>>>> | Slave_running | OFF |
>>>> | Slow_launch_threads | 0 |
>>>> | Slow_queries | 0 |
>>>> | Sort_merge_passes | 0 |
>>>> | Sort_range | 0 |
>>>> | Sort_rows | 0 |
>>>> | Sort_scan | 0 |
>>>> | Ssl_accept_renegotiates | 0 |
>>>> | Ssl_accepts | 0 |
>>>> | Ssl_callback_cache_hits | 0 |
>>>> | Ssl_cipher | |
>>>> | Ssl_cipher_list | |
>>>> | Ssl_client_connects | 0 |
>>>> | Ssl_connect_renegotiates | 0 |
>>>> | Ssl_ctx_verify_depth | 0 |
>>>> | Ssl_ctx_verify_mode | 0 |
>>>> | Ssl_default_timeout | 0 |
>>>> | Ssl_finished_accepts | 0 |
>>>> | Ssl_finished_connects | 0 |
>>>> | Ssl_session_cache_hits | 0 |
>>>> | Ssl_session_cache_misses | 0 |
>>>> | Ssl_session_cache_mode | NONE |
>>>> | Ssl_session_cache_overflows | 0 |
>>>> | Ssl_session_cache_size | 0 |
>>>> | Ssl_session_cache_timeouts | 0 |
>>>> | Ssl_sessions_reused | 0 |
>>>> | Ssl_used_session_cache_entries | 0 |
>>>> | Ssl_verify_depth | 0 |
>>>> | Ssl_verify_mode | 0 |
>>>> | Ssl_version | |
>>>> | Table_locks_immediate | 101 |
>>>> | Table_locks_waited | 0 |
>>>> | Tc_log_max_pages_used | 0 |
>>>> | Tc_log_page_size | 0 |
>>>> | Tc_log_page_waits | 0 |
>>>> | Threads_cached | 0 |
>>>> | Threads_connected | 2 |
>>>> | Threads_created | 2 |
>>>> | Threads_running | 2 |
>>>> | Uptime | 6952 |
>>>> | Uptime_since_flush_status | 6952 |
>>>> +-----------------------------------+-----------+
>>>>
>>>> Thanks,
>>>> Mike Spreitzer
>>>>
>>>>
>>>>
>>>> mos
>>>>
>>>> 06/25/09 02:32 AM
>>>> To
>>>> mysql@lists.mysql.com
>>>> cc
>>>> Subject
>>>> Re: Indexing dynamics in MySQL Community Edition 5.1.34
>>>>
>>>>
>>>>
>>>>
>>>> At 12:37 AM 6/25/2009, you wrote:
>>>> >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?
>>>> >
>>>> >
>>>> >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.
>>>>
>>>> Mike,
>>>> You mean "key_buffer_size" don't you and not "key_buffer"? If you
>>are
>>>> using MySQL 5.0 then the upper limit is 4gb but 5.1 allows for more
>>than
>>>> 4gb.
>>>> Also try increasing "sort_buffer_size".
>>>>
>>>> Posting your "Show Status" will help people see where the bottle
>neck
>>is.
>>>>
>>>> Mike
>>>>
>>>>
>>>> --
>>>> MySQL General Mailing List
>>>> For list archives: http://lists.mysql.com/mysql
>>>> To unsubscribe:
>>http://lists.mysql.com/mysql?unsub=mspreitz@us.ibm.com
>>>>
>>>>
>>>
>>> --
>>> MySQL General Mailing List
>>> For list archives: http://lists.mysql.com/mysql
>>> To unsubscribe:
>>> http://lists.mysql.com/mysql?unsub=yueliangdao0608@gmail.com
>>>
>>>
>>
>>
>>--
>>David Yeung,
>>MySQL Senior Support Engineer,
>>Sun Gold Partner.
>>My Blog:http://yueliangdao0608.cublog.cn
>
>
>
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe: http://lists.mysql.com/mysql?unsub=jschwartz@the-
>infoshop.com





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Indexing dynamics in MySQL Community Edition 5.1.34

mos99@fastmail.fm (mos) writes:

> At 12:37 AM 6/25/2009, you wrote:

....

> >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.
>
> You mean "key_buffer_size" don't you and not "key_buffer"? If you
> are using MySQL 5.0 then the upper limit is 4gb but 5.1 allows for
> more than 4gb.

That's not entirely true. Later versions of 5.0 (above 5.0.56?) also allow
key_buffer_size to be greater than 4GB and we are using that on
several machines. Earlier versions of 5.0 did indeed have this problem.

Simon


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Indexing dynamics in MySQL Community Edition 5.1.34

Yes, all the indices are added in one "ALTER TABLE" statement. Thursday's
incarnation took about 1.5 hours, on a table created from about 8 GB of
CSV. Today's has already taken over 8 hours, on a table created from
about 22 GB of data. The logarithm of 22 GB is about 24/23 of the
logarithm of 8 GB. I seem to have fallen off an additional cliff.

As a reminder, here is the situation. I load a table from CSV with zero
indices defined. Then I add some indices, and that takes a long time. On
Thursday it kept one core busy, but the disk was not very busy for much of
that time (about 600 blocks out per sec). Now I am seeing about 2.5
blocks out per second --- still nowhere near capacity --- and one core
busy.

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[_size] to 8G,
myisam_sort_buffer_size to 256M, and putting tmpdir on the fiber channel
disk.

Here is my current "SHOW STATUS":

+-----------------------------------+------------+
| Variable_name | Value |
+-----------------------------------+------------+
| Aborted_clients | 8 |
| Aborted_connects | 0 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 95 |
| Bytes_sent | 180 |
| Com_admin_commands | 0 |
| Com_assign_to_keycache | 0 |
| Com_alter_db | 0 |
| Com_alter_db_upgrade | 0 |
| Com_alter_event | 0 |
| Com_alter_function | 0 |
| Com_alter_procedure | 0 |
| Com_alter_server | 0 |
| Com_alter_table | 0 |
| Com_alter_tablespace | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_binlog | 0 |
| Com_call_procedure | 0 |
| Com_change_db | 0 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_event | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_procedure | 0 |
| Com_create_server | 0 |
| Com_create_table | 0 |
| Com_create_trigger | 0 |
| Com_create_udf | 0 |
| Com_create_user | 0 |
| Com_create_view | 0 |
| Com_dealloc_sql | 0 |
| Com_delete | 0 |
| Com_delete_multi | 0 |
| Com_do | 0 |
| Com_drop_db | 0 |
| Com_drop_event | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_procedure | 0 |
| Com_drop_server | 0 |
| Com_drop_table | 0 |
| Com_drop_trigger | 0 |
| Com_drop_user | 0 |
| Com_drop_view | 0 |
| Com_empty_query | 0 |
| Com_execute_sql | 0 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 0 |
| Com_insert | 0 |
| Com_insert_select | 0 |
| Com_install_plugin | 0 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 0 |
| Com_optimize | 0 |
| Com_preload_keys | 0 |
| Com_prepare_sql | 0 |
| Com_purge | 0 |
| Com_purge_before_date | 0 |
| Com_release_savepoint | 0 |
| Com_rename_table | 0 |
| Com_rename_user | 0 |
| Com_repair | 0 |
| Com_replace | 0 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_revoke_all | 0 |
| Com_rollback | 0 |
| Com_rollback_to_savepoint | 0 |
| Com_savepoint | 0 |
| Com_select | 1 |
| Com_set_option | 0 |
| Com_show_authors | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_charsets | 0 |
| Com_show_collations | 0 |
| Com_show_column_types | 0 |
| Com_show_contributors | 0 |
| Com_show_create_db | 0 |
| Com_show_create_event | 0 |
| Com_show_create_func | 0 |
| Com_show_create_proc | 0 |
| Com_show_create_table | 0 |
| Com_show_create_trigger | 0 |
| Com_show_databases | 0 |
| Com_show_engine_logs | 0 |
| Com_show_engine_mutex | 0 |
| Com_show_engine_status | 0 |
| Com_show_events | 0 |
| Com_show_errors | 0 |
| Com_show_fields | 0 |
| Com_show_function_status | 0 |
| Com_show_grants | 0 |
| Com_show_keys | 0 |
| Com_show_master_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_plugins | 0 |
| Com_show_privileges | 0 |
| Com_show_procedure_status | 0 |
| Com_show_processlist | 0 |
| Com_show_profile | 0 |
| Com_show_profiles | 0 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 1 |
| Com_show_storage_engines | 0 |
| Com_show_table_status | 0 |
| Com_show_tables | 0 |
| Com_show_triggers | 0 |
| Com_show_variables | 0 |
| Com_show_warnings | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_stmt_close | 0 |
| Com_stmt_execute | 0 |
| Com_stmt_fetch | 0 |
| Com_stmt_prepare | 0 |
| Com_stmt_reprepare | 0 |
| Com_stmt_reset | 0 |
| Com_stmt_send_long_data | 0 |
| Com_truncate | 0 |
| Com_uninstall_plugin | 0 |
| Com_unlock_tables | 0 |
| Com_update | 0 |
| Com_update_multi | 0 |
| Com_xa_commit | 0 |
| Com_xa_end | 0 |
| Com_xa_prepare | 0 |
| Com_xa_recover | 0 |
| Com_xa_rollback | 0 |
| Com_xa_start | 0 |
| Compression | OFF |
| Connections | 87 |
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 87 |
| Created_tmp_tables | 0 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 0 |
| Handler_read_key | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 0 |
| Innodb_buffer_pool_pages_data | 19 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 0 |
| Innodb_buffer_pool_pages_free | 493 |
| Innodb_buffer_pool_pages_misc | 0 |
| Innodb_buffer_pool_pages_total | 512 |
| Innodb_buffer_pool_read_ahead_rnd | 1 |
| Innodb_buffer_pool_read_ahead_seq | 0 |
| Innodb_buffer_pool_read_requests | 77 |
| Innodb_buffer_pool_reads | 12 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 0 |
| Innodb_data_fsyncs | 3 |
| Innodb_data_pending_fsyncs | 0 |
| Innodb_data_pending_reads | 0 |
| Innodb_data_pending_writes | 0 |
| Innodb_data_read | 2494464 |
| Innodb_data_reads | 25 |
| Innodb_data_writes | 3 |
| Innodb_data_written | 1536 |
| Innodb_dblwr_pages_written | 0 |
| Innodb_dblwr_writes | 0 |
| Innodb_log_waits | 0 |
| Innodb_log_write_requests | 0 |
| Innodb_log_writes | 1 |
| Innodb_os_log_fsyncs | 3 |
| Innodb_os_log_pending_fsyncs | 0 |
| Innodb_os_log_pending_writes | 0 |
| Innodb_os_log_written | 512 |
| Innodb_page_size | 16384 |
| Innodb_pages_created | 0 |
| Innodb_pages_read | 19 |
| Innodb_pages_written | 0 |
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 0 |
| Innodb_row_lock_time_avg | 0 |
| Innodb_row_lock_time_max | 0 |
| Innodb_row_lock_waits | 0 |
| Innodb_rows_deleted | 0 |
| Innodb_rows_inserted | 0 |
| Innodb_rows_read | 0 |
| Innodb_rows_updated | 0 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 4852117 |
| Key_blocks_used | 2006827 |
| Key_read_requests | 5758452015 |
| Key_reads | 447924 |
| Key_write_requests | 417359004 |
| Key_writes | 2623617 |
| Last_query_cost | 0.000000 |
| Max_used_connections | 4 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 112 |
| Open_streams | 0 |
| Open_table_definitions | 54 |
| Open_tables | 60 |
| Opened_files | 10060 |
| Opened_table_definitions | 0 |
| Opened_tables | 0 |
| Prepared_stmt_count | 0 |
| Qcache_free_blocks | 3 |
| Qcache_free_memory | 33502088 |
| Qcache_hits | 3 |
| Qcache_inserts | 24 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 236 |
| Qcache_queries_in_cache | 8 |
| Qcache_total_blocks | 23 |
| Queries | 86450 |
| Questions | 2 |
| Rpl_status | NULL |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 0 |
| Select_range_check | 0 |
| Select_scan | 0 |
| Slave_open_temp_tables | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 0 |
| Sort_merge_passes | 0 |
| Sort_range | 0 |
| Sort_rows | 0 |
| Sort_scan | 0 |
| Ssl_accept_renegotiates | 0 |
| Ssl_accepts | 0 |
| Ssl_callback_cache_hits | 0 |
| Ssl_cipher | |
| Ssl_cipher_list | |
| Ssl_client_connects | 0 |
| Ssl_connect_renegotiates | 0 |
| Ssl_ctx_verify_depth | 0 |
| Ssl_ctx_verify_mode | 0 |
| Ssl_default_timeout | 0 |
| Ssl_finished_accepts | 0 |
| Ssl_finished_connects | 0 |
| Ssl_session_cache_hits | 0 |
| Ssl_session_cache_misses | 0 |
| Ssl_session_cache_mode | NONE |
| Ssl_session_cache_overflows | 0 |
| Ssl_session_cache_size | 0 |
| Ssl_session_cache_timeouts | 0 |
| Ssl_sessions_reused | 0 |
| Ssl_used_session_cache_entries | 0 |
| Ssl_verify_depth | 0 |
| Ssl_verify_mode | 0 |
| Ssl_version | |
| Table_locks_immediate | 27591 |
| Table_locks_waited | 0 |
| Tc_log_max_pages_used | 0 |
| Tc_log_page_size | 0 |
| Tc_log_page_waits | 0 |
| Threads_cached | 0 |
| Threads_connected | 4 |
| Threads_created | 4 |
| Threads_running | 2 |
| Uptime | 202522 |
| Uptime_since_flush_status | 202522 |
+-----------------------------------+------------+

Thanks,
Mike Spreitzer




mos
06/25/09 01:05 PM

To
mysql@lists.mysql.com
cc

Subject
Re: Indexing dynamics in MySQL Community Edition 5.1.34






Mike,
I re-posted your Show Status to the group to see if anyone can
offer
a way to speed up the indexing for you.

BTW, you are adding ALL of the indexes to the table using ONE sql
statement
right? And not a separate SQL statement to build each index?

Mike

At 02:01 AM 6/25/2009, you wrote:

>Like I said in the subject line, I am using 5.1.34. I started with
>my-huge.cnf, which says "key_buffer" rather than "key_buffer_size"; SHOW
>GLOBAL VARIABLES confirms, however, that my key_buffer_size is 8GB.
>
>That indexing operation finally finished after about 1.5 hours; that was
>about 0.5 hours ago. Now I am on to other things. Here is the status
you
>suggested:
>
> [SNIP]
>
>Thanks,
>Mike Spreitzer
>
>
>
>mos
>
>06/25/09 02:32 AM
>To
>mysql@lists.mysql.com
>cc
>Subject
>Re: Indexing dynamics in MySQL Community Edition 5.1.34
>
>
>
>
>At 12:37 AM 6/25/2009, you wrote:
> >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?
> >
> >
> >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.
>
>Mike,
> You mean "key_buffer_size" don't you and not "key_buffer"? If you
are
>using MySQL 5.0 then the upper limit is 4gb but 5.1 allows for more than
4gb.
>Also try increasing "sort_buffer_size".
>
>Posting your "Show Status" will help people see where the bottle neck is.
>
>Mike
>
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe: http://lists.mysql.com/mysql?unsub=mspreitz@us.ibm.com
>


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=mspreitz@us.ibm.com




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Indexing dynamics in MySQL Community Edition 5.1.34

Today's instance finished shortly after I sent the email below. BTW, here
are some specifics on the table (which uses MyISAM). Thursday's instance
has 11 GB of data and 0.78 GB of index. Today's instance has 26 GB of
data and 1.8 GB of index.

Thanks,
Mike Spreitzer




Mike Spreitzer/Watson/IBM@IBMUS
06/27/09 09:48 AM

To
mos
cc
mysql@lists.mysql.com
Subject
Re: Indexing dynamics in MySQL Community Edition 5.1.34






Yes, all the indices are added in one "ALTER TABLE" statement. Thursday's

incarnation took about 1.5 hours, on a table created from about 8 GB of
CSV. Today's has already taken over 8 hours, on a table created from
about 22 GB of data. The logarithm of 22 GB is about 24/23 of the
logarithm of 8 GB. I seem to have fallen off an additional cliff.

As a reminder, here is the situation. I load a table from CSV with zero
indices defined. Then I add some indices, and that takes a long time. On

Thursday it kept one core busy, but the disk was not very busy for much of

that time (about 600 blocks out per sec). Now I am seeing about 2.5
blocks out per second --- still nowhere near capacity --- and one core
busy.

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[_size] to 8G,
myisam_sort_buffer_size to 256M, and putting tmpdir on the fiber channel
disk.

Here is my current "SHOW STATUS":

+-----------------------------------+------------+
| Variable_name | Value |
+-----------------------------------+------------+
| Aborted_clients | 8 |
| Aborted_connects | 0 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 95 |
| Bytes_sent | 180 |
| Com_admin_commands | 0 |
| Com_assign_to_keycache | 0 |
| Com_alter_db | 0 |
| Com_alter_db_upgrade | 0 |
| Com_alter_event | 0 |
| Com_alter_function | 0 |
| Com_alter_procedure | 0 |
| Com_alter_server | 0 |
| Com_alter_table | 0 |
| Com_alter_tablespace | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_binlog | 0 |
| Com_call_procedure | 0 |
| Com_change_db | 0 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_event | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_procedure | 0 |
| Com_create_server | 0 |
| Com_create_table | 0 |
| Com_create_trigger | 0 |
| Com_create_udf | 0 |
| Com_create_user | 0 |
| Com_create_view | 0 |
| Com_dealloc_sql | 0 |
| Com_delete | 0 |
| Com_delete_multi | 0 |
| Com_do | 0 |
| Com_drop_db | 0 |
| Com_drop_event | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_procedure | 0 |
| Com_drop_server | 0 |
| Com_drop_table | 0 |
| Com_drop_trigger | 0 |
| Com_drop_user | 0 |
| Com_drop_view | 0 |
| Com_empty_query | 0 |
| Com_execute_sql | 0 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 0 |
| Com_insert | 0 |
| Com_insert_select | 0 |
| Com_install_plugin | 0 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 0 |
| Com_optimize | 0 |
| Com_preload_keys | 0 |
| Com_prepare_sql | 0 |
| Com_purge | 0 |
| Com_purge_before_date | 0 |
| Com_release_savepoint | 0 |
| Com_rename_table | 0 |
| Com_rename_user | 0 |
| Com_repair | 0 |
| Com_replace | 0 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_revoke_all | 0 |
| Com_rollback | 0 |
| Com_rollback_to_savepoint | 0 |
| Com_savepoint | 0 |
| Com_select | 1 |
| Com_set_option | 0 |
| Com_show_authors | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_charsets | 0 |
| Com_show_collations | 0 |
| Com_show_column_types | 0 |
| Com_show_contributors | 0 |
| Com_show_create_db | 0 |
| Com_show_create_event | 0 |
| Com_show_create_func | 0 |
| Com_show_create_proc | 0 |
| Com_show_create_table | 0 |
| Com_show_create_trigger | 0 |
| Com_show_databases | 0 |
| Com_show_engine_logs | 0 |
| Com_show_engine_mutex | 0 |
| Com_show_engine_status | 0 |
| Com_show_events | 0 |
| Com_show_errors | 0 |
| Com_show_fields | 0 |
| Com_show_function_status | 0 |
| Com_show_grants | 0 |
| Com_show_keys | 0 |
| Com_show_master_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_plugins | 0 |
| Com_show_privileges | 0 |
| Com_show_procedure_status | 0 |
| Com_show_processlist | 0 |
| Com_show_profile | 0 |
| Com_show_profiles | 0 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 1 |
| Com_show_storage_engines | 0 |
| Com_show_table_status | 0 |
| Com_show_tables | 0 |
| Com_show_triggers | 0 |
| Com_show_variables | 0 |
| Com_show_warnings | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_stmt_close | 0 |
| Com_stmt_execute | 0 |
| Com_stmt_fetch | 0 |
| Com_stmt_prepare | 0 |
| Com_stmt_reprepare | 0 |
| Com_stmt_reset | 0 |
| Com_stmt_send_long_data | 0 |
| Com_truncate | 0 |
| Com_uninstall_plugin | 0 |
| Com_unlock_tables | 0 |
| Com_update | 0 |
| Com_update_multi | 0 |
| Com_xa_commit | 0 |
| Com_xa_end | 0 |
| Com_xa_prepare | 0 |
| Com_xa_recover | 0 |
| Com_xa_rollback | 0 |
| Com_xa_start | 0 |
| Compression | OFF |
| Connections | 87 |
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 87 |
| Created_tmp_tables | 0 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 0 |
| Handler_read_key | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 0 |
| Innodb_buffer_pool_pages_data | 19 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 0 |
| Innodb_buffer_pool_pages_free | 493 |
| Innodb_buffer_pool_pages_misc | 0 |
| Innodb_buffer_pool_pages_total | 512 |
| Innodb_buffer_pool_read_ahead_rnd | 1 |
| Innodb_buffer_pool_read_ahead_seq | 0 |
| Innodb_buffer_pool_read_requests | 77 |
| Innodb_buffer_pool_reads | 12 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 0 |
| Innodb_data_fsyncs | 3 |
| Innodb_data_pending_fsyncs | 0 |
| Innodb_data_pending_reads | 0 |
| Innodb_data_pending_writes | 0 |
| Innodb_data_read | 2494464 |
| Innodb_data_reads | 25 |
| Innodb_data_writes | 3 |
| Innodb_data_written | 1536 |
| Innodb_dblwr_pages_written | 0 |
| Innodb_dblwr_writes | 0 |
| Innodb_log_waits | 0 |
| Innodb_log_write_requests | 0 |
| Innodb_log_writes | 1 |
| Innodb_os_log_fsyncs | 3 |
| Innodb_os_log_pending_fsyncs | 0 |
| Innodb_os_log_pending_writes | 0 |
| Innodb_os_log_written | 512 |
| Innodb_page_size | 16384 |
| Innodb_pages_created | 0 |
| Innodb_pages_read | 19 |
| Innodb_pages_written | 0 |
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 0 |
| Innodb_row_lock_time_avg | 0 |
| Innodb_row_lock_time_max | 0 |
| Innodb_row_lock_waits | 0 |
| Innodb_rows_deleted | 0 |
| Innodb_rows_inserted | 0 |
| Innodb_rows_read | 0 |
| Innodb_rows_updated | 0 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 4852117 |
| Key_blocks_used | 2006827 |
| Key_read_requests | 5758452015 |
| Key_reads | 447924 |
| Key_write_requests | 417359004 |
| Key_writes | 2623617 |
| Last_query_cost | 0.000000 |
| Max_used_connections | 4 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 112 |
| Open_streams | 0 |
| Open_table_definitions | 54 |
| Open_tables | 60 |
| Opened_files | 10060 |
| Opened_table_definitions | 0 |
| Opened_tables | 0 |
| Prepared_stmt_count | 0 |
| Qcache_free_blocks | 3 |
| Qcache_free_memory | 33502088 |
| Qcache_hits | 3 |
| Qcache_inserts | 24 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 236 |
| Qcache_queries_in_cache | 8 |
| Qcache_total_blocks | 23 |
| Queries | 86450 |
| Questions | 2 |
| Rpl_status | NULL |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 0 |
| Select_range_check | 0 |
| Select_scan | 0 |
| Slave_open_temp_tables | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 0 |
| Sort_merge_passes | 0 |
| Sort_range | 0 |
| Sort_rows | 0 |
| Sort_scan | 0 |
| Ssl_accept_renegotiates | 0 |
| Ssl_accepts | 0 |
| Ssl_callback_cache_hits | 0 |
| Ssl_cipher | |
| Ssl_cipher_list | |
| Ssl_client_connects | 0 |
| Ssl_connect_renegotiates | 0 |
| Ssl_ctx_verify_depth | 0 |
| Ssl_ctx_verify_mode | 0 |
| Ssl_default_timeout | 0 |
| Ssl_finished_accepts | 0 |
| Ssl_finished_connects | 0 |
| Ssl_session_cache_hits | 0 |
| Ssl_session_cache_misses | 0 |
| Ssl_session_cache_mode | NONE |
| Ssl_session_cache_overflows | 0 |
| Ssl_session_cache_size | 0 |
| Ssl_session_cache_timeouts | 0 |
| Ssl_sessions_reused | 0 |
| Ssl_used_session_cache_entries | 0 |
| Ssl_verify_depth | 0 |
| Ssl_verify_mode | 0 |
| Ssl_version | |
| Table_locks_immediate | 27591 |
| Table_locks_waited | 0 |
| Tc_log_max_pages_used | 0 |
| Tc_log_page_size | 0 |
| Tc_log_page_waits | 0 |
| Threads_cached | 0 |
| Threads_connected | 4 |
| Threads_created | 4 |
| Threads_running | 2 |
| Uptime | 202522 |
| Uptime_since_flush_status | 202522 |
+-----------------------------------+------------+

Thanks,
Mike Spreitzer




mos
06/25/09 01:05 PM

To
mysql@lists.mysql.com
cc

Subject
Re: Indexing dynamics in MySQL Community Edition 5.1.34






Mike,
I re-posted your Show Status to the group to see if anyone can
offer
a way to speed up the indexing for you.

BTW, you are adding ALL of the indexes to the table using ONE sql
statement
right? And not a separate SQL statement to build each index?

Mike

At 02:01 AM 6/25/2009, you wrote:

>Like I said in the subject line, I am using 5.1.34. I started with
>my-huge.cnf, which says "key_buffer" rather than "key_buffer_size"; SHOW
>GLOBAL VARIABLES confirms, however, that my key_buffer_size is 8GB.
>
>That indexing operation finally finished after about 1.5 hours; that was
>about 0.5 hours ago. Now I am on to other things. Here is the status
you
>suggested:
>
> [SNIP]
>
>Thanks,
>Mike Spreitzer
>
>
>
>mos
>
>06/25/09 02:32 AM
>To
>mysql@lists.mysql.com
>cc
>Subject
>Re: Indexing dynamics in MySQL Community Edition 5.1.34
>
>
>
>
>At 12:37 AM 6/25/2009, you wrote:
> >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?
> >
> >
> >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.
>
>Mike,
> You mean "key_buffer_size" don't you and not "key_buffer"? If you
are
>using MySQL 5.0 then the upper limit is 4gb but 5.1 allows for more than
4gb.
>Also try increasing "sort_buffer_size".
>
>Posting your "Show Status" will help people see where the bottle neck is.
>
>Mike
>
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe: http://lists.mysql.com/mysql?unsub=mspreitz@us.ibm.com
>


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=mspreitz@us.ibm.com




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=mspreitz@us.ibm.com




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Indexing dynamics in MySQL Community Edition 5.1.34

On Sat, Jun 27, 2009 at 7:03 AM, Mike Spreitzer wrote:
> Today's instance finished shortly after I sent the email below. =A0BTW, h=
ere
> are some specifics on the table (which uses MyISAM). =A0Thursday's instan=
ce
> has 11 GB of data and 0.78 GB of index. =A0Today's instance has 26 GB of
> data and 1.8 GB of index.

If you have the ability to test, I'd compare that to importing the csv
into the table with the indexes already defined. The way you did it
should be faster, but since you see that it's only using one core, I'd
try splitting the data up into 16 separate files and importing them
all at once. In theory (SWAG actually), multiple imports would each
use their own core to whatever thread count you have innodb defined to
use ... Oh, just saw that you were using myisam. Never mind.

At any rate, I'd be very surprised if importing into a table with
indexes already defined was the same speed or faster, but doing so
could give you some useful information, such as at what point the
import (and concurrent index creation) drops from expected level X to
much reduced level Y. You could then (hopefully) find a correlation
between some cache or buffer setting that will explain the sudden drop
in speed.
--=20
Regards... Todd

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg