Solved

MySQL 5.56 slower queries than 5.1.56

Posted on 2011-09-23
8
392 Views
Last Modified: 2012-08-14
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
0
Comment
Question by:dolythgoe
  • 4
  • 4
8 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 36586764
please check the EXPLAIN plans for the same queries on the 2 servers, and compare.
you may post them here ...
0
 

Author Comment

by:dolythgoe
ID: 36587125
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
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 36587146
>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?
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:dolythgoe
ID: 36587263
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!

0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 36587483
you surely know about this reference page?
http://dev.mysql.com/doc/refman/5.0/en/server-parameters.html
0
 

Assisted Solution

by:dolythgoe
dolythgoe earned 0 total points
ID: 36589883
I do - I've read it a few times but just like to run something by you. Bolded excerpts below.

So if you have 1GB memory on the DB server and expect very many simultaneous connects, what would you derive from these statements. On one hand I want lots of memory allocated to the heavy sorting operations but also want to make sure I don't bloat or 'ringfence' too much memory per user...

Is my thinking wrong and it will adapt to suit?

   If you have at least 256MB of memory and many tables and want maximum performance with a moderate number of clients, you should use something like this:

    shell> mysqld_safe --key_buffer_size=64M --table_cache=256 \
               --sort_buffer_size=4M --read_buffer_size=1M &


    If you have only 128MB of memory and only a few tables, but you still do a lot of sorting, you can use something like this:

    shell> mysqld_safe --key_buffer_size=16M --sort_buffer_size=1M

    If there are very many simultaneous connections, swapping problems may occur unless mysqld has been configured to use very little memory for each connection. mysqld performs better if you have enough memory for all connections.

    With little memory and lots of connections, use something like this:

    shell> mysqld_safe --key_buffer_size=512K --sort_buffer_size=100K \
               --read_buffer_size=100K &

    Or even this:

    shell> mysqld_safe --key_buffer_size=512K --sort_buffer_size=16K \
               --table_cache=32 --read_buffer_size=8K \
               --net_buffer_length=1K &


0
 

Author Closing Comment

by:dolythgoe
ID: 36895812
Seeking more confirmation around optimisation methods - looks like I'm ont he right track :)
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 36598762
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.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

816 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now