[plug] MySQL performance tuning

[Marc Wiriadisastra]

If you want to use HT.

Use taskset to allocate it to the "processor" cpu affinity I think the 
program is called. Basically it makes HT more effective in certain 
applications. You can also recompile the kernel to improve scheduling in 
the kernel.  If you run a 2.6 kernel the scheduling has improved however 
you can make it less cpu intesive by lowering the HZ down.  This will 
lower teh cpu usage.  The only reason I know this is because I run a 
colo server that is cpu intensive but had to be tweaked with.

http://ck.kolivas.org/webpage/

If its a 2.4 kernel there are kernel patches there to improve scheduling.

HTH

Marc

Bill Kenworthy wrote:

>Try with HT off, there have been issues with HT blocking in the short
>pipelines that can cause a significant drop in database performance when
>under load.  Wasnt mysql, but is worth checking.
>
>BillK
>
>On Sat, 2004-09-04 at 09:53, Jon Miller wrote:
>  
>
>>We are having a problem with our database in that when several users are accessing it and a report is being run it slows the network down immensely.  When running top to check I can see that the mysqld-max uses up to 99.9% CPU and about 1-2% memory.  The queries are accessing database tables in the range of 3-300MB sizes.  The server itself is a dual Xeon 2.8GHz Hyperthreading  with 2GB memory and about 200GB of free space on the partition of the database.  We have about 25 users accessing the database and plan to have about twice that in the neat future.
>>This is our config file, are there settings that can be tweaked to use more memory and less CPU?
>>
>>The entire system runs on a 2GB backbone with w/ full duplex 100Mbps switch to each workstation.  Unfortunately the user workstations are WXPPSP1 and W2KSP4.  They access the database via a VB front-end client-server design.
>>
>>Thanks
>>
>>
>>[mysqld]
>>datadir=/var/lib/mysql
>>socket=/var/lib/mysql/mysql.sock
>>port=3306
>>
>>[mysql.server]
>>user=root
>>basedir=/var/lib
>>
>>[safe_mysqld]
>>err-log                 = /var/log/mysqld.log
>>pid-file                = /var/run/mysqld/mysqld.pid
>>log_slow_queries        = /var/log/slow-queries.log
>>long_query_time         = 1
>>max_allowed_packet      = 16M
>>table cache             = 512
>>sort_buffer_size        = 2M
>>read_buffer_size        = 2M
>>myisam_sort_buffer_size = 64M
>>thread_cache            = 16
>>query_cache             = 32M
>>thread_concurrency      = 32
>>innodb_buffer_pool_size = 384M
>>innodb_additional_mem_pool_size = 20M
>>key_buffer_size         = 384M
>>
>>
>>Jon L. Miller,  ASE, CNS, CLS, MCNE
>>Director/Sr Systems Consultant
>>MMT Networks Pty Ltd
>>http://www.mmtnetworks.com.au
>>
>>"I don't know the key to success, but the key to failure
>> is trying to please everybody." -Bill Cosby
>>
>>
>>
>>
>>_______________________________________________
>>PLUG discussion list: plug at plug.linux.org.au
>>http://mail.plug.linux.org.au/cgi-bin/mailman/listinfo/plug
>>Committee e-mail: committee at plug.linux.org.au
>>    
>>
>
>_______________________________________________
>PLUG discussion list: plug at plug.linux.org.au
>http://mail.plug.linux.org.au/cgi-bin/mailman/listinfo/plug
>Committee e-mail: committee at plug.linux.org.au
>
>  
>