[thelist] mysql optimising large table
Richard Bennett
richard.bennett at skynet.be
Thu Oct 21 06:04:55 CDT 2004
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 still 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
More information about the thelist
mailing list