dolythgoe
asked on
MySQL 5.56 slower queries than 5.1.56
Hello,
I dumped my database and installed it on the newer version 5.56 from 5.1.56.
The queries are now some 500% slower.
Is this a case of re-inexing the content? Does anyone know the differences I should be mindful of? They are MyISAM engine and select statements. The queries have been optimised and the indexes applied so want to focus on just the differences between 5.56 and 5.1.56.
Thanks for anyhelp.
David
I dumped my database and installed it on the newer version 5.56 from 5.1.56.
The queries are now some 500% slower.
Is this a case of re-inexing the content? Does anyone know the differences I should be mindful of? They are MyISAM engine and select statements. The queries have been optimised and the indexes applied so want to focus on just the differences between 5.56 and 5.1.56.
Thanks for anyhelp.
David
ASKER
Oddly enough - the same in every way but 5.56 actually looks up on less rows - not sure if this is because of an efficiency improvement.
I think I've realised the error of my ways in the my.cnf. The queries were fast up until a point where more frequent words were used and then it dropped off a cliff - the tmp_table_size and max_heap_table_size were set at 16M and it went over this so I've set about to find the my.cnf on ubuntu server.
I have a db master and slave and found my.cnf in etc/mysql/my.cnf (on master) except it's readonly..
Coming from CentOs it's all slightly different - do you know where the writable one is?
I have a load balanced web1 web2 and db master and db slave all running ubuntu.
Cheers
David
I think I've realised the error of my ways in the my.cnf. The queries were fast up until a point where more frequent words were used and then it dropped off a cliff - the tmp_table_size and max_heap_table_size were set at 16M and it went over this so I've set about to find the my.cnf on ubuntu server.
I have a db master and slave and found my.cnf in etc/mysql/my.cnf (on master) except it's readonly..
Coming from CentOs it's all slightly different - do you know where the writable one is?
I have a load balanced web1 web2 and db master and db slave all running ubuntu.
Cheers
David
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
>Coming from CentOs it's all slightly different - do you know where the writable one is?
no, my world is microsoft in that regards ... (shame on me ... :)
but I think with my.cnf you are on the right track (would have been my suggestion to compare those parameters)
is the file readonly because of permissions on plain file-level?
no, my world is microsoft in that regards ... (shame on me ... :)
but I think with my.cnf you are on the right track (would have been my suggestion to compare those parameters)
is the file readonly because of permissions on plain file-level?
ASKER
good point...just tried to chmod it but got a permissions denied - so checking with the host about that.
SQL Yog showed this for the query with frequent words:
state duration (summed) in sec percentage
converting HEAP to MyISAM 3.09217 60.35410
Copying to tmp table 0.98924 19.30835
Copying to tmp table on disk 0.95379 18.61642
That confirms it I think - absolutely trashed the speed when it gets above the mem allocation!!
Starting to learn a lot more about analysing queries with your help which is so useful!
SQL Yog showed this for the query with frequent words:
state duration (summed) in sec percentage
converting HEAP to MyISAM 3.09217 60.35410
Copying to tmp table 0.98924 19.30835
Copying to tmp table on disk 0.95379 18.61642
That confirms it I think - absolutely trashed the speed when it gets above the mem allocation!!
Starting to learn a lot more about analysing queries with your help which is so useful!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Seeking more confirmation around optimisation methods - looks like I'm ont he right track :)
hi,
it's a bit of compromise, based on the application.
the settings in question are per server, not per user, so you play safe there.
with 1GB on your hands, you could assign 256MB for the key_buffer_size, and 640MB for the table_cache, for example.
CHeers.
it's a bit of compromise, based on the application.
the settings in question are per server, not per user, so you play safe there.
with 1GB on your hands, you could assign 256MB for the key_buffer_size, and 640MB for the table_cache, for example.
CHeers.
you may post them here ...