Re: Mysql 5 performance tuning

From: Christopher E. Brown <cbrown@woods.net>
Date: Mon Dec 05 2005 - 14:40:45 AKST

On Mon, 5 Dec 2005, Adam bultman wrote:

>
> 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.

One thing to note. Modern pstools/proctools are thread aware, and display
differently than older (1995 - 2003/4) tools. Older tools would display
*each* thread as if it were a full process, modern (by default) list all
assoc threads as one process (even if there are 500 threads, only proc
listed). You can feed command lime params to PS to get it to display raw
threads.

> 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:

I am running local copies of 4.0.x and 5.0.x, bother on a glibc 2.3
Linuxthreads system and on a glibc 2.3 NPTL system, and have not noted and
issues. However, both MySQL installs are build local, against the systems
they run in, and the compilers are GCC 3.3 and 3.4.

If I were hunting this issue, my first line would be to build a copy of
MySQL on the system and test. I would suspect the BUILD glibc/threads/etc
vs the system glibc/threads/etc as a possable issue.

My main box is a dual Opteron w/ 8GB RAM and hardware RAID, average table
is 5 million rows + and I have not noted any issues (load or performance).

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

This archive was generated by hypermail 2.1.8 : Mon Dec 05 2005 - 14:40:54 AKST