| DB Optimization [message #65677] |
Tue, 30 November 2004 19:06  |
Raghunandhan
Messages: 3
Registered: April 2004
|
Junior Member |
|
|
I have the below query that takes about 5 seconds to execute on MySQL 4.0.18 with Windows 2000 with 512 MB RAM.
select HIGH_PRIORITY INET_NTOA(Table1.IP_COLUMN1) as COLA, INET_NTOA(Table1.IP_COLUMN2) as COLB, Table2.COLUMN1 as COLC, Table2.COLUMN2 as COLD, Table2.COLUMN3 as COLE, sum(Table1.COLUMN3) as n, Table2.COLUMN4 as COLF from Table1, Table2 where Table1.COLUMN5 = Table2.COLUMN5 and Table1.COLUMN5 = '37' and TIME between 20041130101200 and 20041201101200 group by COLA, COLB, COLC, COLD, COLE order by n desc LIMIT 100
Table1 (a MyISAM table) has about 1 million records and Table2 (InnoDB) has about 1500 records. Table1.COLUMN5='37' is satisfied by about 10000 rows. Table1 gets about 30000 records once every 3 minutes and this is loaded from a file using a "load data infile....". I have been able to reduce this "load data infile..." to about 2 seconds but my select queries (which is what I want to be really fast) have actually become slower after I changed the start up parameters as below:
I have started MySQL with the below options:
--read_buffer_size=8000000 --read_rnd_buffer_size=12000000 --sort_buffer_size=8400000 --myisam_sort_buffer_size=64000000 --key_buffer_size=128000000 --bulk_insert_buffer_size=16000000 --table_cache=256 --low-priority-updates
My key_reads to key_read_requests ratio is a healthy 1:700
Do I have the read_buffer_size and read_rnd_buffer_size right??? Any other suggestions?????
|
|
|
|
| Re: DB Optimization [message #65687 is a reply to message #65677] |
Sat, 04 December 2004 13:37  |
Frank Naude
Messages: 4587
Registered: April 1998
|
Senior Member |
|
|
Hi,
You realize that this is an Oracle Message Forum? So,
forgive me for suggesting you migrate your MySQL DB
to Oracle. A decently sized Oracle machine should
execute this query in milliseconds.
Best regards.
Frank
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
