Re: optimizing mySQL

[Praj]

 >Also, when a query takes too long I kill it by shutting down the 
server, is this safe or is there a better alternative (MyISAM tables)?

Use show processlist to see the query that taking more time and use " 
kill <processid>; " for terminating the query .

--
Praj

Surendra Singhi wrote:

>Hello Chris,
>
>Chris <dmagick@stripped> writes:
>
>
>>Surendra Singhi wrote:
>>
>>>  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.
>>
>>
>Thanks a lot, this link was indeed helpful, and I think I am able to optimize
>the database much better now.
>
>I have an update operation where I am able to update 10 million records in approx
>2.5 mins. 
>
>But when I tried to do the same update on say 40-50 million records, mysql
>takes forever to finish. Its the same table, and same update operation, i am
>just changing the range of ids using where clause.
>
>Why might this be happening, and how to solve this?  
>
>Also, when a query takes too long I kill it by shutting down the server, is
>this safe or is there a better alternative (MyISAM tables)?
>
>Thanks.
>