Solved

MySql 5.6 slow Creating Sort Index

Posted on 2014-12-09
6
4,506 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:Patrick Bogers
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 143

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 23

Author Comment

by:Patrick Bogers
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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 143

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 23

Author Comment

by:Patrick Bogers
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 23

Author Closing Comment

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

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
paypal ipn to mysql 3 104
MySQL-Design Help 12 63
No option to replace table data in phpmyadmin using CSV file import 11 40
emailing registered email addresses in phpmyadmin 5 61
More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

738 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