MySQL 5.1 queries 1000x slower than 5.0

Yesterday I decided to take the plunge and upgrade from MySQL 5.01 to
5.1.30 because after all, it was Saturday and what else is there to do on a
Saturday?

I'm running it on XP Pro with 3gb ram and used the my.ini for very large
system. I decided not to tweak any of these settings except for the datadir
and I commented out skip-networking since my old my.ini file didn't have it
either. I'm using only MyISAM tables so skip-innodb is enabled.

Well to make a sad story short, I ran my application last night and
returned this morning only to find it still running. Select queries that
would run on 1 table to return 1 row should take under a second, now takes
over an hour. An Explain shows that it is using the index.

The query goes something like this:
select purch_date from items where prod_code='ABC' and ((store_id='A' and
purch_date>'2007-01-01') or (store_id='B' and purch_date>'2007-01-05') or
(store_id='C' and purch_date>'2007-01-09')) and (col1 is null or col2 is
null or col3 is null or col4 is null or col5 is null or col6 is null) order
by purch_date limit 1;

There are 2 compound keys: prod_code,purch_date,store_id and
prod_code,purch_date,store_id
The table items has approx 30 million rows in it and there are approx 5,000
rows for 'ABC'.

I copied these tables over from the MySQ 5.0 data directory to the MySQL
5.1 data directory.

My question is this. Has the table or index structure changed in 5.1? Do I
have to optimize all of the tables before using 5.1? Did I overlook
something when installing 5.1 that would account for this 1000x speed
slowdown? I thought I'd bounce this question off the group before I start
tearing things apart here to find out what is going on.

I'm running a check now on the tables to see if they are damaged but I'd be
grateful if someone could chime in and let me know if I overlooked
something simple so I could have a "d'uh moment" and put this behind me.

TIA
Mike


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: MySQL 5.1 queries 1000x slower than 5.0

On Sun, Jan 11, 2009 at 2:24 PM, mos wrote:
> Yesterday I decided to take the plunge and upgrade from MySQL 5.01 to 5.1.30
> because after all, it was Saturday and what else is there to do on a
> Saturday?
>
> I'm running it on XP Pro with 3gb ram and used the my.ini for very large
> system. I decided not to tweak any of these settings except for the datadir
> and I commented out skip-networking since my old my.ini file didn't have it
> either. I'm using only MyISAM tables so skip-innodb is enabled.

Why didn't you re-use your my.ini from your 5.0 system?

> Well to make a sad story short, I ran my application last night and returned
> this morning only to find it still running. Select queries that would run on
> 1 table to return 1 row should take under a second, now takes over an hour.
> An Explain shows that it is using the index.
>
> The query goes something like this:
> select purch_date from items where prod_code='ABC' and ((store_id='A' and
> purch_date>'2007-01-01') or (store_id='B' and purch_date>'2007-01-05') or
> (store_id='C' and purch_date>'2007-01-09')) and (col1 is null or col2 is
> null or col3 is null or col4 is null or col5 is null or col6 is null) order
> by purch_date limit 1;
>
> There are 2 compound keys: prod_code,purch_date,store_id and
> prod_code,purch_date,store_id
> The table items has approx 30 million rows in it and there are approx 5,000
> rows for 'ABC'.

We have a client in a similar situation but I'm still waiting for
access to the server to investigate the problem.

I suspect that EXPLAIN is lying, in the client's case, but I can't
know until I get on the box and see. In your case I can't comment.
It sounds like the same thing but it might not be.

--
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=gcdmg-mysql-2@m.gmane.org