My.cnf.huge is not good enough for your system specs. Calculate the Key efficiency from the show status command. I bet the key efficiency is less then 90% or so. In this case increase the key_buffer_size try 512M. A good stat for a proper key_buffer_size in the sum of all index files block size. This would be optimal since the index remains in memory. Increase your tmp_table_size to 64 MB your prob going to tmp_table and mysql uses this buffer for some internal optimizations. Also try increasing range alloc block size a little bit, you might see a 5% perf boost.
DVP ---- Dathan Vance Pattishall http://www.friendster.com > -----Original Message----- > From: Richard Bennett [mailto:[EMAIL PROTECTED] > Sent: Thursday, October 21, 2004 5:48 AM > To: [EMAIL PROTECTED] > Subject: mysql optimising large table > > Hi, > > I have a table containing logfiles in mysql v4.0, myISAM. > The table has about 8.5 million records. > I'm using the my_huge.cnf file on mandrake10 Linux with 1 gig ram and > 250gig > HD space. > Some Info: > Space usage : > Type Usage > Data 3,063 MB > Index 660,855 KB > Total 3,708 MB > > Row Statistic : > Statements Value > Format dynamic > Rows 8,781,134 > Row length ø 365 > Row size ø 443 Bytes > Next Autoindex 8,781,135 > Creation Oct 14, 2004 at 09:23 PM > Last update Oct 20, 2004 at 11:57 AM > Last check Oct 14, 2004 at 09:34 PM > > Indexes : > Keyname Type Cardinality Field > PRIMARY PRIMARY 8781134 id > originalID UNIQUE 8781134 originalID > databaseName INDEX 9 databaseName > origID INDEX 8781134 origID > destinationcode INDEX 8625 destinationcode > finaldestination INDEX 2195283 finaldestination > datetime INDEX 8781134 datetime > > > Normally i'd like to be able to get statistics from the database in 1month > chunks (about 1 million records) but if I do a: > SELECT count( * ) > FROM `table` > WHERE datetime > BETWEEN '2004-09-01 00:00:00' AND '2004-10-01 00:00:00' > > It will return the count: 1372668, but it takes 2 or 3 minutes to do this. > If > I add any other (indexed) criteria it becomes even slower. > > I have noticed if I just request 1 or 2 days' records, the result comes > fast, but once the count gets over 100000 or so, everything slows down. > > My own solution at the moment is to make temporary tables for each month, > as > things seem to stay fast with less than 2mil. records in a table. > > Does anyone have any advice on how to optimise this setup? > > Thanks, > Richard. > > PS, some extra mysql info: (sorry for the long post) > > Server variables and settings > Variable Global value > back log 50 > basedir / > binlog cache size 32768 > bulk insert buffer size 8388608 > character set latin1 > character sets latin1 big5 czech euc_kr gb2312 gbk latin1_de sjis tis620 > ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew > win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 > latin5 concurrent insert ON > connect timeout 5 > convert character set > datadir /var/lib/mysql/ > default week format 0 > delay key write ON > delayed insert limit 100 > delayed insert timeout 300 > delayed queue size 1000 > flush OFF > flush time 0 > ft boolean syntax + -><()~*:""&| > ft min word len 4 > ft max word len 254 > ft max word len for sort 20 > ft stopword file (built-in) > have bdb NO > have crypt YES > have innodb YES > have isam YES > have raid NO > have symlink YES > have openssl NO > have query cache YES > init file > innodb additional mem pool size 1048576 > innodb buffer pool size 8388608 > innodb data file path ibdata1:10M:autoextend > innodb data home dir > innodb file io threads 4 > innodb force recovery 0 > innodb thread concurrency 8 > innodb flush log at trx commit 1 > innodb fast shutdown ON > innodb flush method > innodb lock wait timeout 50 > innodb log arch dir ./ > innodb log archive OFF > innodb log buffer size 1048576 > innodb log file size 5242880 > innodb log files in group 2 > innodb log group home dir ./ > innodb mirrored log groups 1 > innodb max dirty pages pct 90 > interactive timeout 28800 > join buffer size 131072 > key buffer size 402653184 > language /usr/share/mysql/english/ > large files support ON > local infile ON > locked in memory OFF > log OFF > log update OFF > log bin ON > log slave updates OFF > log slow queries OFF > log warnings OFF > long query time 10 > low priority updates OFF > lower case table names 0 > max allowed packet 1047552 > max binlog cache size 4294967295 > max binlog size 1073741824 > max connections 100 > max connect errors 10 > max delayed threads 20 > max heap table size 16777216 > max join size 4294967295 > max relay log size 0 > max seeks for key 4294967295 > max sort length 1024 > max user connections 0 > max tmp tables 32 > max write lock count 4294967295 > myisam max extra sort file size 268435456 > myisam max sort file size 2147483647 > myisam repair threads 1 > myisam recover options OFF > myisam sort buffer size 67108864 > net buffer length 16384 > net read timeout 30 > net retry count 10 > net write timeout 60 > new OFF > open files limit 1024 > pid file /var/lib/mysql/server.pid > log error > port 3306 > protocol version 10 > query alloc block size 8192 > query cache limit 1048576 > query cache size 33554432 > query cache type ON > query prealloc size 8192 > range alloc block size 2048 > read buffer size 2093056 > read only OFF > read rnd buffer size 262144 > rpl recovery rank 0 > server id 1 > slave net timeout 3600 > skip external locking ON > skip networking OFF > skip show database OFF > slow launch time 2 > socket /var/lib/mysql/mysql.sock > sort buffer size 2097144 > sql mode 0 > table cache 457 > table type MYISAM > thread cache size 8 > thread stack 196608 > tx isolation REPEATABLE-READ > timezone CEST > tmp table size 33554432 > tmpdir /home/rich/tmp/ > transaction alloc block size 8192 > transaction prealloc size 4096 > version 4.0.18-log > version comment Source distribution > wait timeout 28800 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
