Improving Read Performance on Large Databases

[Michael42]

Hello,

I am using MySQL 4.0 on a Sun Solaris 8 system. What settings would be optimal to change to improve MySQL performance with repect to doing Reads (SELECTs) on a databases 10-30 gig in size for reporting?

Will InnoDB improve read performance? If so how would you recommend configuring it?

At the moment I am using the "medium-ish" properties in my my.cnf file shown below.

# The MySQL server
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
set-variable = key_buffer=16M
set-variable = max_allowed_packet=1M
set-variable = table_cache=64
set-variable = sort_buffer=512K
set-variable = net_buffer_length=8K
set-variable = myisam_sort_buffer_size=8M
log-bin
server-id = 1

[mysqldump]
quick
set-variable = max_allowed_packet=16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates




Thanks,

Michael42

 

[sleipnir214]

InnoDB tables don't give you improved read speed. They just give you transactions.

The probable best way of improving SELECTs is to make sure you're using indeces on the right columns.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[Michael42]

sleipnir214,

Thanks - that info is useful on InnoDB. Unfortunately I cannot make any SQL level or table\index changes as there is an abstraction layer (application) that governs this.

What I can change is the my.cnf. What changes to the my.cnf would be advisable to improve read performance?

Thanks,

Michael42