Solved

MySql 5.6 slow Creating Sort Index

Posted on 2014-12-09
6
4,783 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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

Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

Question has a verified solution.

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

Foreword This article was written many years ago, in the days when PHP supported the MySQL extension (http://php.net/manual/en/function.mysql-connect.php).  Today (http://php.net/manual/en/migration70.removed-exts-sapis.php) you would not use MySQL…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

695 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