Solved

MySql 5.6 slow Creating Sort Index

Posted on 2014-12-09
6
3,828 Views
Last Modified: 2014-12-10
Dear Professionals,

So my years of Linux has started and now we are stumbling onto below issue.
This machine is a HP DL360G8 with 24 cores and 128GB reading from a HP raid5-set operating 15k SAS disks. In Hyper-v we have created a virtual machine dedicated for mysql bases on Ubuntu 14.04. The machine has access to 8 cores and 40gb ram.

Below database has 450.000 rows and measures 650MB, can anyone explain why creating the sort index takes almost a full second?

Query and details below:

SELECT rates_id FROM datastore WHERE lcase(basepartcode)='ABC900' and rate > 0 AND price > 0 AND lcase(provider)='' and lcase(color) = 'black' status > 0 order by price,action_price limit 1

+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000060 |
| Waiting for query cache lock   | 0.000018 |
| init                           | 0.000016 |
| checking query cache for query | 0.000149 |
| checking permissions           | 0.000023 |
| Opening tables                 | 0.000050 |
| init                           | 0.000094 |
| System lock                    | 0.000037 |
| Waiting for query cache lock   | 0.000016 |
| System lock                    | 0.000068 |
| optimizing                     | 0.000044 |
| statistics                     | 0.033595 |
| preparing                      | 0.000054 |
| Sorting result                 | 0.000020 |
| executing                      | 0.000017 |
| Sending data                   | 0.000025 |
| Creating sort index            | 0.809063 |
| Waiting for query cache lock   | 0.000053 |
| Creating sort index            | 0.000017 |
| storing result in query cache  | 0.333763 |
| end                            | 0.000059 |
| query end                      | 0.000032 |
| closing tables                 | 0.000040 |
| freeing items                  | 0.000548 |
| cleaning up                    | 0.000055 |
+--------------------------------+----------+

my.cnf
# * Fine Tuning
#
innodb_buffer_pool_size = 8000M
join_buffer_size        = 8M
key_buffer              = 150M
max_allowed_packet      = 150M
thread_stack            = 128K
thread_cache_size       = 128

table_open_cache        = 8192
table_definition_cache  = 6144


query_cache_type        = 1
query_cache_limit       = 8M
query_cache_size        = 512M
max_heap_table_size     = 512M
tmp_table_size          = 512M

skip-name-resolve

max_connections = 2000
open_files_limit = 100000
max_connect_errors=5000
wait_timeout=600


innodb-log-file-size = 250M
innodb-flush-log-at-trx-commit = 2
0
Comment
Question by:Patricksr1972
  • 3
  • 2
6 Comments
 
LVL 50

Expert Comment

by:Steve Bink
ID: 40490593
Your query, as it is, will not run.  Please post the exact query from  your code.  Also, post the EXPLAIN for the query.

Why do you use lcase() when you're comparing against a capitalized string?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40490608
the query will run, but surely return 0 records due to this:
 WHERE lcase(basepartcode)='ABC900'

anyhow, I do agree with the question: why are you using lcase() on the fields?
the issue is that indeed you need to change the db/application logic to avoid this function at that place
the typical solution would be to store the values basepartcode  in lowercase, and use this query:
 WHERE basepartcode =lcase('ABC900')
0
 
LVL 19

Author Comment

by:Patricksr1972
ID: 40491046
Hi Guys,

My bad, i'm sorry, i had to anonimize this query because it is recognizable for my customer who is a big retailer in western europe.
In the original query 'ABC900' was indeed lower case so this does not seem the issue.

According to my limited MySQL knowledge it seems that sorting is done on harddisk in stead of in memory, does this makes sence? If so, how can i change this behaviour?
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 40491055
>it seems that sorting is done on harddisk
this can happen indeed if the memory assigned to MySQL (on startup) is too small
http://dev.mysql.com/doc/refman/5.0/en/server-parameters.html

nevertheless, the lcase() function on the column is very likely to be the "killer" here, as it makes it impossible to use a index on the field(s)
0
 
LVL 19

Author Comment

by:Patricksr1972
ID: 40491165
Dear Guy,

You are spot on, lcase IS the killer, we rebuilded the query to WHERE basepartcode =('abc900')  and the query finished in 0,1 seconds in stead of 1.2 seconds.

Guess we forced MySQL to rebuild the table allready excisting in memory to be recreated as lowercase (on harddisk)... thank you for opening up my eyes.Much appreciated.
0
 
LVL 19

Author Closing Comment

by:Patricksr1972
ID: 40491167
It was in fact the KILLER.
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Mysqli Query 5 50
How do I do MySQLi table maintenance for things like email addresses and passwords? 5 75
join tables 4 54
mysql date time 14 27
Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
Creating and Managing Databases with phpMyAdmin in cPanel.
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.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …

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