0

I have a query triggered towards a MySQL server. Every time I run such a query, the cpu goes up until the query ends. After that, cpu usage goes down, but not to normal value: a little cpu is still being consumed by MySQL. Only after restarting the server, cpu goes back to normal consumption.

In the next plot the pink area is cpu during the SQL query time. The dark-turquoise is a Pandas operation on the SQL table from before. Once done, cpu goes down but a little "pink" remains. After restart of the MySQL server, "pink" is hardly seen.

enter image description here

My query has some many joins. I'm using this query towards a MySQL 5.7.26-0ubuntu0.16.04.1. This DB is the one that comes out of the box when installing RackTables.

SELECT  
ob.id,
ob.name,
ob.label,
INET_NTOA(ip.ip), 
a.name,
d.dict_value,
av.string_value,
min(oh.ctime),
max(oh.ctime),
max(oh.user_name),
min(ol.date),
ol.user,
ol.content,
FROM Object AS ob    
JOIN AttributeValue AS av ON (av.object_id  = ob.id) LEFT 
JOIN Attribute      AS a  ON (av.attr_id    = a.id) LEFT 
JOIN Dictionary     AS d  ON (av.uint_value = d.dict_key) LEFT 
JOIN ObjectLog      AS ol ON (ob.id         = ol.object_id ) LEFT 
JOIN ObjectHistory  AS oh ON (ob.id         = oh.id) LEFT 
JOIN TagStorage     AS ts ON (ob.id         = ts.entity_id) LEFT 
JOIN IPv4Allocation AS ip ON (ob.id         = ip.object_id AND ip.name = 'int0') 
WHERE (a.name LIKE 'TEST%' OR a.name LIKE 'TRIP%') 
GROUP BY ob.id, ob.name, ob.label, a.name, d.dict_value, av.uint_value, av.string_value, ol.user, ol.content, ip.ip  

While running the query, I've issued a show processlist and obtained the following:

mysql> show processlist;
+-----+---------+-----------------+------------+---------+------+--------------+---------------------------------------------------------+
| Id  | User    | Host            | db         | Command | Time | State        | Info                                                    |
+-----+---------+-----------------+------------+---------+------+--------------+---------------------------------------------------------+
|   3 | netdata | localhost       | NULL       | Sleep   |    1 |              | NULL                                                    |
| 372 | root    | localhost       | NULL       | Query   |    0 | starting     | show processlist                                        |
| 377 | myUser  | localhost:42308 | racktables | Query   |   33 | Sending data | SELECT  ob.id,  ob.name,   ob.label,  INET_NTOA(ip.ip)  |
+-----+---------+-----------------+------------+---------+------+--------------+---------------------------------------------------------+

The Id 377 goes away, once the query has ended.

This is the content of my.cnf

$ grep -v '#' /etc/mysql/my.cnf
[client]
port        = 3306
socket      = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
bind-address = 0.0.0.0
key_buffer_size     = 16M
max_allowed_packet  = 16M
thread_stack        = 192K
thread_cache_size       = 8
myisam_recover_options  = BACKUP
query_cache_limit   = 1M
query_cache_size        = 16M
log_error = /var/log/mysql/error.log
expire_logs_days    = 10
max_binlog_size         = 100M
innodb_buffer_pool_size = 1G

[mysqldump]
quick
quote-names
max_allowed_packet  = 16M

[mysql]

[isamchk]
key_buffer_size     = 16M

Any hint? May be some tunning is needed on the server ...

Thanks!

3
  • I'd install the mysql debug symbols package and perf (package; linux-tools-common). Record what mysql is doing after the query; perf record -o mysqld-after-query.perf -g -p $(pidof mysqld) -- sleep 10; then include perf report -g --no-children -i mysqld-after-query.perf --stdio in the question (the top page or so of it anyway).
    – danblack
    Jan 9 '20 at 2:41
  • Please provide EXPLAIN SELECT ... and the approximate sizes of the tables.
    – Rick James
    Jan 9 '20 at 5:33
  • Additional information request. RAM size, # cores, any SSD devices on MySQL Host server? Post on pastebin.com and share the links. From SSH login root, Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; E) complete MySQLTuner report AND Optional very helpful information, if available includes - htop OR top for most active apps, ulimit -a for a Linux/Unix list of limits, iostat -xm 5 3 for IOPS by device and core/cpu count, for server workload tuning analysis to provide suggestions. Jan 10 '20 at 15:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.