Jon Gardiner wrote:
> Analyzing a table requires looking at every
> record in the table. If the
> table you are dealing with is a large one then
> there is a good chance that
> after analyzing the table your disk cache will
> not contain the records that
> you are trying to grab. Once you run the query
> it will almost certainly
> contain those records. You could test this
> theory by rebooting your machine
> (if that is an option) and seeing if it takes
> the longer amount of time for
> the first query.
*sigh* I think you are right. I cannot reboot
this machine durring the day. But I wrote a script
to rewrite the MyISAM files and it seems to have
the same effect as myisamchk -a was having.
b.
#!/bin/bash
#
DISKS="3p0 3p1 4p0 4p1 5p0 5p1"
for disk in $DISKS; do
PATH=/var/flashdisk/$disk/mysql/pcm_test
for file in $PATH/*.MYI $PATH/*.MYD; do
TMP=$file.tmp
echo
echo $file
/bin/mv -v $file $TMP
if [ $? != 0 ]; then
echo "Could not move";
exit;
fi
/bin/cat $TMP > $file
if [ $? != 0 ]; then
echo "Could not cat the file?"
exit
fi
/bin/rm -fv $TMP
if [ $? != 0 ]; then
echo "Could not remove temp file?"
exit
fi
done
done
>
>
> Or I could be completely off base. It wouldn't
> be the first time. :-)
>
> Jon Gardiner.
>
> > -----Original Message-----
> > From: Bill Adams [mailto:[EMAIL PROTECTED]]
> > Sent: Tuesday, November 13, 2001 2:47 PM
> > To: Mysql List
> > Subject: myisamchk -a + indexes + hidden...?
> >
> >
> > 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
> >
>
> -
> -------------------------------------------------------------------
>
> 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
--
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
