List:General Discussion« Previous MessageNext Message »
From:lance raymond Date:January 30 2009 2:32pm
Subject:mysql 5 performance
View as plain text  
Sorry if this seems to be a double post, but not sure if the mysql list send
yourself a copy (as I didn't get the 1st one).  But a little has changed,
not much, but basically I have an old fc5 machine running mysql 4.1 and a
newer server running centos5 / mysql 5.0.45.  The whole site is almost
unusable now (the older server is having hard drive issues and being
replaced), and I think it's just a mysql 5 startup or config change but I am
not sure.  Our test page shows the queries with the query times, and here is
just one admin page;
*mysql4:*
getting all product info....done (12858 unique products, 1 secs).
creating product tables....done (7 sec).
getting all presold items....done (1 sec).
getting order history....done (13 sec).

*mysql 5:*
getting all product info....done (12858 unique products, 0 secs).
creating product tables....done (273 sec).
getting all presold items....done (1 sec).
getting order history....done (15 sec).

I don't know but guessing now, mysql 5 shows the following 2 items running
on a ps list;

 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql
--socket=/var/lib/mysql/mysql.sock --log-error=/var/log/mysqld.log
--pid-file=/var/run/mysqld/mysqld.pid

 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql
--pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking
--socket=/var/lib/mysql/mysql.sock

Questions;
1.Do I need external-locking skipped?
2.Are there tools like mtop for mysql 5 to monitor what's going on?  The box
(even on the 200+ second page loads)  is sitting at 95% idle with no real
load.
3. MySql 4 process list shows an actual tree;
19188 ?        S      0:00 /bin/sh /usr/bin/mysqld_safe
--datadir=/var/lib/mysql --pid-file=/var/lib/mysql/classic.pid
19219 ?        S      0:03  \_ [mysqld]
19220 ?        S      0:05      \_ [mysqld]
19221 ?        S      0:00          \_ [mysqld]
19222 ?        S      0:00          \_ [mysqld]
19223 ?        S      0:00          \_ [mysqld]
19224 ?        S      0:00          \_ [mysqld]
19225 ?        S      0:00          \_ [mysqld]
19226 ?        S      0:06          \_ [mysqld]
19227 ?        S      0:00          \_ [mysqld]
19228 ?        S      0:02          \_ [mysqld]

I am not sure, but does that mean it's multi-threading allowing multiple
things to be done at once?  If so, is that possible on mysql5.

I may be way off on one or all, but at least some sort of reply is
appreciated.  It's been 3 day's, I see some of the questions being thrown
out and answered so I know it's an active list, so am I assuming no-one has
upgraded from 4 to 5 and seen this?

Anything to help me start fixing this is apprecited.
Lr

Thread
mysql 5 performancelance raymond30 Jan
  • Re: mysql 5 performancePerrin Harkins30 Jan
  • Re: mysql 5 performanceWalter Heck30 Jan
  • Re: mysql 5 performanceBaron Schwartz30 Jan