List:General Discussion« Previous MessageNext Message »
From:Baron Schwartz Date:January 30 2009 9:47pm
Subject:Re: mysql 5 performance
View as plain text  
Hi Lance,

Please post specific queries and EXPLAIN output with the \G
terminator.  It's likely your queries that are the problem, and
looking at 'ps' doesn't help you much.

On Fri, Jan 30, 2009 at 9:32 AM, lance raymond <lance.raymond@stripped> wrote:
> 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
>



-- 
Baron Schwartz, Director of Consulting, Percona Inc.
Our Blog: http://www.mysqlperformanceblog.com/
Our Services: http://www.percona.com/services.html
Thread
mysql 5 performancelance raymond30 Jan
  • Re: mysql 5 performancePerrin Harkins30 Jan
  • Re: mysql 5 performanceWalter Heck30 Jan
  • Re: mysql 5 performanceBaron Schwartz30 Jan