Re: optimizing mySQL

[Chris]

Surendra Singhi wrote:
> Hi,
>   I am using mySQL 5.0 and I have 2 tables with few hundred millions of
> records. 
> 
> To optimize things, I am using MyISAM tables, using the smallest possible data
> type and have set indexes.
> 
> Now, the problem which I am facing is that mySql process is wasting lot of
> time in disk access the CPU and memory utilization is very low. 
> Most of the table access are simple queries, with very few write operations.
> 
> What can I do optimize things and make queries faster?
> 
> My CPU is 64bit Athlon 3000, with  950 MB of RAM running Ubuntu Edgy Eft.
> 
> I don't mind making mysql eat lot of CPU and RAM. Just want things to be much
> faster, and loaded into memory instead of slow disk access.
> 
> The my.cnf file contains:
> 
> key_buffer              = 16M
> max_allowed_packet      = 16M
> thread_stack            = 128K
> 
> query_cache_limit       = 1048576
> query_cache_size        = 16777216
> query_cache_type        = 1
> 
> [isamchk]
> key_buffer              = 16M
> 
> I am considering increasing these values, please advise on what should I set
> them. Also, any other tips will be extremely helpful.

Completely depends on the queries you are running and the context of 
which they are run. A query that runs once a day won't matter if it's 
slow, a query that runs every time you do something will matter a lot.

This page might give you some ideas:

http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-in-mysql-server-after-installation/


Enable the slow query log in mysql and triple check that you have the 
proper indexes in place.