Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

MySql 5.6 slow Creating Sort Index

Posted on 2014-12-09
6
Medium Priority
?
5,730 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 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?
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 143

Accepted Solution

by:
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
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

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL several years ago, it seemed like now was a good time to update it for object-oriented PHP.  This article does that, replacing as much as possible the pr…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

618 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