[plug] MySQL performance tuning

[Bill Kenworthy]

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