It seems like myisamchk -a is hosing some
statistic in a MyISAM table that gets re-created
and stored permanently as once a query is run that
uses that index, it always runs well until
myisamchk -a is run again even between restarts of
mysqld. It also seems that key_buffer_size has no
effect on the results.
Can someone explain this to me?
There are a a bunch of tables merged into three
main tables. The query does a two column join
between the tables, e.g.: a.1=b.1 AND a.2=b.2 AND
b.1=c.1 AND b.3=c.3
--Bill
[root@host /usr/local/mysql-4.0/var]#
../bin/mysqladmin -uroot -p version
../bin/mysqladmin Ver 8.22 Distrib 4.0.0-alpha,
for pc-linux-gnu on i686
Copyright (C) 2000 MySQL AB & MySQL Finland AB &
TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY.
This is free software,
and you are welcome to modify and redistribute it
under the GPL license
Server version 4.0.0-alpha-log
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /tmp/mysql-4.0.sock
Uptime: 7 min 2 sec
Threads: 3 Questions: 103 Slow queries: 0
Opens: 12 Flush tables: 46 Open tables: 3
Queries per second avg: 0.244
[root@host /usr/local/mysql-4.0/var]# uname -a
Linux host.tqs.com 2.2.19 #6 SMP Wed Jul 11
10:55:03 PDT 2001 i686 unknown
[root@host /usr/local/mysql-4.0/var]# vmstat
procs memory
swap io system cpu
r b w swpd free buff cache si so
bi bo in cs us sy id
2 1 0 4 2612 40752 1450688 0 0
4 3 3 6 10 2 6
[root@host /usr/local/mysql-4.0/var]# ldd
../libexec/mysqld
librt.so.1 => /lib/librt.so.1 (0x2aac8000)
libdl.so.2 => /lib/libdl.so.2 (0x2aacc000)
libpthread.so.0 => /lib/libpthread.so.0
(0x2aad0000)
libz.so.1 => /usr/lib/libz.so.1
(0x2aae3000)
libcrypt.so.1 => /lib/libcrypt.so.1
(0x2aaf3000)
libnsl.so.1 => /lib/libnsl.so.1
(0x2ab20000)
libstdc++-libc6.1-1.so.2 =>
/usr/lib/libstdc++-libc6.1-1.so.2 (0x2ab36000)
libm.so.6 => /lib/libm.so.6 (0x2ab78000)
libc.so.6 => /lib/libc.so.6 (0x2ab95000)
/lib/ld-linux.so.2 => /lib/ld-linux.so.2
(0x2aaab000)
[root@host /usr/local/mysql-4.0/var]#
myisamchk -a (on all tables)
set-variable = key_buffer=32M
Time to start getting results: 127 seconds.
Total Time: 146 seconds (18251 rows, 125 rows/sec
overall)
Run the query again:
Time to start getting results: 11 seconds.
Total Time: 30 seconds (18251 rows, 608 rows/sec)
shutdown mysql
set-variable = key_buffer=1M
start mysql
myisamchk -a (on all tables)
Time to start getting results: 121 seconds.
Total Time: 141 seconds (18251 rows, 129 rows/sec)
Second Run:
Run the query again:
Time to start getting results: 10 seconds.
Total Time: 29 seconds (18251 rows, 629 rows/sec)
Shutdown and restart MySQL.
Note: key_buffer still at 1M
Time to start getting results: 12 seconds.
Total Time: 31 seconds (18251 rows, 588 rows/sec)
Shutdown MySQL
Set key buffer size to 0
start mysql
Time to start getting results: 10 seconds.
Total Time: 29 seconds (18251 rows, 629 rows/sec)
myisamchk -a
Time to start getting results: 145 seconds.
Total Time: 164 seconds (18251 rows, 111 rows/sec)
--
Bill Adams
TriQuint Semiconductor
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
