Solved

Mysql performance

Posted on 2016-08-03
8
76 Views
Last Modified: 2016-08-29
Mysql slow log files is filled with slow queries. I used percona toolkit(pt-query-digest) to analyze them, and found the most used query that take very long is just a simple select with where clause using the primary key

the table has 2 500 000 rows and the primary key is a varchar(255). when I run the query within the mysql command line, it's very fast.

any idea where I can look to determine why the query is so slow( at 95% takes up to 9s to run).

Thanks
0
Comment
Question by:CCVOIP
  • 4
  • 3
8 Comments
 
LVL 4

Expert Comment

by:vvk
ID: 41741175
where the query being called from when it is slow? that may affect default session parameters etc. what DB engine type you're using? can you use FORCE INDEX in the query?
0
 

Author Comment

by:CCVOIP
ID: 41741182
it's an innodb table that has 4 columns with longblob and 1 column longtext in addition to a mix of varchar/datetime/tinyint

it's slowing when it gets called from the application server hosts which are under the same domain as the db host

when I do explain extended it shows 1 under rows column

+----+-------------+----------------+-------+----------------------+---------------+---------+-------+------+----------+-------+
| id | select_type | table       | type  | possible_keys | key           | key_len | ref   | rows | filtered | Extra |
+----+--------------+--------------+----------+-------------------+---------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | tabl1_       | const | pk_ID              | pk_ID          | 768     | const |    1 |   100.00 | NULL  |
+----+---------------+--------------+--------+--------------------+---------------+---------+-------+------+----------+-------+
0
 
LVL 4

Expert Comment

by:vvk
ID: 41741200
can you run it with show warnings to get additional output about execution plan and also post query itself if possible. value of 100 in filtered is sometimes confusing without additional data
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.

 

Author Comment

by:CCVOIP
ID: 41741223
show warnings doesn't return much because of the longblob columns:
mysql> show warnings \G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message:*▒H▒▒elect#1 */ select '830wdLLXs6k4vtNVDOqXIZ70It_FAtMT3F-xLxO0JsI=' AS `DEVICE_ID`,'NONE' AS `BLACKLISTED_TYPE`,'infotel' AS `CARRIER`,'r4g' AS `PRODUCT`,'5.0' AS `VER`,'0▒▒0▒ 5▒V@▒50
1 row in set (0.00 sec)
0
 
LVL 4

Expert Comment

by:vvk
ID: 41741593
it'll be really helpful to see query itself. just to dismiss that it may be just network, is application run on the same server with database? does it retrieve blob? what is approximate size of that? do you use STATS_PERSISTENT and how often do you run ANALYZE TABLE
0
 

Author Comment

by:CCVOIP
ID: 41743160
here is the exact query:

select * from devices where device_id='54789wdLLXs6k4jhgjyuyUOIOXIZ70It_FAtMT3F-JHIUOPIm='  

device_id is a primary key
the query retrieves 4 longblob columns
we use mysql5.6.15 and STATS_PERSISTENT=1 by default
we don analyze tables that often(you can say at all)
the table has up to 3 000 000 row
0
 
LVL 4

Expert Comment

by:vvk
ID: 41748147
not running analyze table should not have effect in this situation. just to confirm, when intially looking at your question i assumed that with pt-query-digest you'd calculated ratio of rows_sent/rows_exemined and it equal 1 for those long running queries, am i correct?
0
 
LVL 25

Accepted Solution

by:
Tomas Helgi Johannsson earned 500 total points
ID: 41751820
Hi!

Having STATS_PERSISTENT=1 on your tables you should run ANALYZE TABLE periodically or set the STATS_AUTO_RECALC=1 on the tables for optimal performance for your  sql queries.
Having 3 million rows in your table and no statistics on it your queries will not run with optimal performance. Note that I may be that some queries will run fast and some don't. You wan't all queries to run as optimal as possible so I recommend doing as I mentioned above.

https://dev.mysql.com/doc/refman/5.6/en/innodb-persistent-stats.html

Regards,
      Tomas Helgi
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

Question has a verified solution.

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

I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
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…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

679 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