[plug] MySQL performance tuning

[Jon Miller]

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