Hello,
There is a table (TEST) with ~100 million records, 70 columns (mostly
integers, some doubles, and a few short fixed char()), and has a ~100Gb
size.
The table has a single (not unique) index on one integer column: MMi.
If I invoke a simple select based on MMi, then the selection is VERY slow:
nohup time mysql CAT -u catadmin -p$MPWD -e "create table test2
select * from TEST where MMi < 9000;"
( this selects only ~0.5 % of the table, by the way, so test2 is a
small table, and the time is not spent with writing it on disk)
Time used: 47 minutes:
0.00user 0.00system 47:17.37elapsed 0%CPU (0avgtext+0avgdata
0maxresident)k 0inputs+0outputs (0major+416minor)pagefaults 0swaps
If I do the same but ignore the index, the select time drops to 1/5th !!!
nohup time mysql CAT -u catadmin -p$MPWD -e "create table test3 \
select * from TEST ignore index (MMi) where \
MMi < 9000;"
Time used: 11 minutes:
0.00user 0.00system 11:08.23elapsed 0%CPU (0avgtext+0avgdata
0maxresident)k 0inputs+0outputs (0major+415minor)pagefaults 0swaps
Without the index, MySQL does a simple thing; it reads in sequentially
the 100Gb database, and while reading, it parses the lines, and
determines if the MMi is < 9000. This is done with about 16Mb/s speed.
With the index, it performs a large number of random seeks. The data
(.MYD) is probably not organized on the disk according to sorted MMi.
Questions;
1. Is there a way to decrease random seeks? E.g. mysqld config
parameters, increase some buffer/cache sizes?
2. Optimize table: is there a way to rearrange data so that random
seeks are minimized?
3. If we have to live with large number of random seeks does anyone
know how the underlying FS can be optimized?
Cheers,
Gaspar
( All this TEST DB is kept on FC3 Redhat + MySQL 5.0.22, XFS
filesystem on a 1.5Tb RAID-5 array of 4 SATA disks (served by a
3ware RAID controller). The computer is running on two opteron
2.0GHZ CPUs and 4Gb RAM.
)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
