mysql 5 performance
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 infodone (12858 unique products, 1 secs). creating product tablesdone (7 sec). getting all presold itemsdone (1 sec). getting order historydone (13 sec). *mysql 5:* getting all product infodone (12858 unique products, 0 secs). creating product tablesdone (273 sec). getting all presold itemsdone (1 sec). getting order historydone (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
Re: mysql 5 performance
On Fri, Jan 30, 2009 at 9:32 AM, lance raymond lance.raym...@gmail.com wrote: basically I have an old fc5 machine running mysql 4.1 and a newer server running centos5 / mysql 5.0.45. So, different hardware, different OS, different database server... could be anything. I suggest you run EXPLAIN plans for the slow queries on both servers and compare them. The most likely reasons for a difference that large are missing indexes or vastly different filesystem performance. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysql 5 performance
creating product tablesdone (273 sec). Can you tell us what queries this code actually executes? This doesn't tell us too much ;) Walter OlinData: Professional services for MySQL Support * Consulting * Administration http://www.olindata.com On Fri, Jan 30, 2009 at 3:32 PM, lance raymond lance.raym...@gmail.com 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 infodone (12858 unique products, 1 secs). creating product tablesdone (7 sec). getting all presold itemsdone (1 sec). getting order historydone (13 sec). *mysql 5:* getting all product infodone (12858 unique products, 0 secs). creating product tablesdone (273 sec). getting all presold itemsdone (1 sec). getting order historydone (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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysql 5 performance
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.raym...@gmail.com 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 infodone (12858 unique products, 1 secs). creating product tablesdone (7 sec). getting all presold itemsdone (1 sec). getting order historydone (13 sec). *mysql 5:* getting all product infodone (12858 unique products, 0 secs). creating product tablesdone (273 sec). getting all presold itemsdone (1 sec). getting order historydone (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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
