[thelist] mysql optimising large table

Richard Bennett richard.bennett at skynet.be
Thu Oct 21 06:04:55 CDT 2004


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?


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