Mysql 5 performance tuning

Morning, AKLUG.
Wait.. "afternoon".

I recently made a few changes at work with one of the production mysql
servers. We moved from Mysql 4.0.x on Red Hat 7.3 to Mysql 4.1.15 on
Centos 4.1 . We are not using the RPMS distributed by Centos (or Red
Hat), but are using the Glibc 2.3 tarball version from the mysql.com
web site.

We 'swapped' mysql servers when moving from RH and mysql 4.0.x to
Centos and 4.1.x - moving from a dual 2.8GHz Xeon (with RAID5) to a
dual 2.66 Xeon (with a RAID1). One thing I noticed was that Mysql no
longer starts a great number of threads, but rather, seems to start
one (or two - a launcher, and the daemons). The load on the 2.66 was
almost always between 1 and 3, and it seemed that what the older
server handled without the slightest bit of sweat, the new server
could barely manage. It WAS running slowly, and it seemed like a lot
of things were getting backed up on the server, and you could *feel*
the difference on mysql-heavy web pages.

I tweaked the my.cnf (see below) a bit to see if that would help, and
it did - but only to a point. I'll discuss this a bit below.

Because the CMS we use (webgui... ugh) is going to require Mysql 5
soon, I've been taking the first server (the dual 2.8GHz and RAID5
box) and have loaded Centos and Mysql 5 on it. It has the same basic
behavior; it has two 'threads' running (if you check ps -efw) and
seems to not like large queries (and especially, database loads).

I'm worried; there's a few variables here that I just can't account
for. The only thing that I could fathom is causing this huge
difference is that the Red Hat mysql server was running the
Intel-compiled binaries version of the mysql daemon
(mysql-blahblahblah-icc). There is no 32bit version of 4.1.15, and
5.0.16 (which is the version of 5 I'm running) so I can't test that
against the 'normal' version. When 4.0.x-icc was installed, a fellow
admin ran a test and claimed somewhere around 20% performance gain.
However, the performance difference from that version, to the 4.1.x
and 5.0.x version is NOT 20%. It's more like 80% slower, on the lean side.

I've been testing mysql5 so we can roll it out soon (other projects
are waiting on it). I've been checking out tweaks and such, and I've
edited my my.cnf to look like this:

[mysqld]
skip-innodb
#datadir = /usr/local/databases/mysql.icc
#log-slow-queries = /usr/local/databases/mysql.icc/slow-queries.log
datadir = /usr/local/databases/mysql
log-slow-queries = /usr/local/databases/mysql/slow-queries.log
set-variable = max_connections=5000

# Added by ADB, Dec 3 2005. Added to improve performance. These
# taken from the webgui WRE my.cnf file. I increased some of the numbres
# since we've got a beefier server here.
set-variable = key_buffer=768M
set-variable = key_buffer_size=128M
set-variable = max_allowed_packet=64M
set-variable = myisam_sort_buffer_size=256M
set-variable = sort_buffer=16M
set-variable = sort_buffer_size=16M
set-variable = record_buffer=8M
set-variable = thread_stack=1M
set-variable = thread_cache=32
set-variable = thread_concurrency=16
set-variable = table_cache=1024
set-variable = query_cache_size=128M
set-variable = max_connect_errors=900
set-variable = wait_timeout=60
set-variable = connect_timeout=15
set-variable = interactive_timeout=120
set-variable = join_buffer_size=64M

Adding some of these variables on the other server (which is in
production) helped, as I stated, but I haven't included all these on
the production server. The current production server has 2G of ram,
where the dual 2.8 (the new mysql5 server) has 4G of ram - which is
why I went so nuts on all my numbers above.

I'm running the sql-bench utilities, and I'll report my findings once
the second (plain) one runs. However, if you have ideas for how to
make things run faster, or have warnings for me on my huge numbers
above, I'd love to hear them.

Adam

---------
To unsubscribe, send email to <aklug-request@aklug.org>
with 'unsubscribe' in the message body.
Received on Mon Dec 5 12:29:54 2005