MySql 5.6 slow Creating Sort Index

Posted on 2014-12-09
Medium Priority
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 |

# * 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


max_connections = 2000
open_files_limit = 100000

innodb-log-file-size = 250M
innodb-flush-log-at-trx-commit = 2
Question by:Patrick Bogers
  • 3
  • 2
LVL 51

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?
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')
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?
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 2000 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

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)
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.
LVL 23

Author Closing Comment

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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

624 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