[plug] MySQL performance tuning[Scanned]

[Ranz]

Hi Jon,

Before we go delving into your problems with MYSQL, and this may have
already been done (but you didn't mention it) .... Have you tried
restarting either the MYSQL service or the entire server and see if that
makes any difference?

I'll wait for your reply regards to this before we explore further.

Many thanks,

Randal Adamson


-----Original Message-----
From: plug-bounces at plug.linux.org.au
[mailto:plug-bounces at plug.linux.org.au] On Behalf Of Jon Miller
Sent: Saturday, 4 September 2004 9:54 AM
To: plug at plug.linux.org.au
Subject: [plug] MySQL performance tuning[Scanned]

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