Home » RDBMS Server » Performance Tuning » DB Optimization
DB Optimization [message #65677] Tue, 30 November 2004 19:06 Go to next message
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 Go to previous message
Frank Naude
Messages: 4566
Registered: April 1998
Senior Member

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.

Previous Topic: URGENT:-ORA-01555: snapshot too old: rollback segment number 17 with name "_SYSS
Next Topic: For second Name oracle is not using function Index
Goto Forum:

Current Time: Sun Jan 17 12:52:32 CST 2021