mysqld CPU 100%+ on query

Questions about hosting a Website, using MySQL, PhP or MariaDB may be posted here.
Forum rules
1) This is a user forum for Synology users to share experience/help out each other: if you need direct assistance from the Synology technical support team, please use the following form:
https://myds.synology.com/support/suppo ... p?lang=enu
2) To avoid putting users' DiskStation at risk, please don't paste links to any patches provided by our Support team as we will systematically remove them. Our Support team will provide the correct patch for your DiskStation model.
NielsE
I'm New!
I'm New!
Posts: 2
Joined: Sat Oct 21, 2017 9:27 am

mysqld CPU 100%+ on query

Postby NielsE » Tue Oct 24, 2017 8:13 pm

Hello,

On our Synology we have running a custom ordering program which using mysql. Most program functions works fine for years, but some takes a long time to load (up to 130 s !).

In the slow-query-log I see 2 slow queries (112 s and 16 s), which takes almost the complete load time of the program feature.

When I run this query (see below) directly in phpMyAdmin it also takes this long time to load te result. The message on the result page: "Showing records 0 - 24 (67 total, Query takes 2.6838 seconds.)".
What does the server the remaining 110 seconds? With top or htop I see that process mysqld takes 100% (sometimes up to 113%) of the CPU.

The program developer have tested our dataset on their server and the same program feature takes only a few seconds. I asked their mysql configuration settings and changed our my.cnf file, but didn't change loading time.

I searched the web and tested a lot of things but did not come to a solution. I don't know what to do next. Hope someone here could help me out with this problem.

Kind regards,
Niels

Setup
Synology DS713+
DSM 6.1.3-15152 Update 7
MariaDB5

The slowest query from the slow-query-log

Code: Select all

select distinct DEB.DEB_NR,DEB.SORT_NAAM,DEB.DEB_NAAM,DEB.DEB_ADRES,DEB.DEB_PC,DEB.DEB_WOONPL,DEB.DEB_LAND,DEB.DEB_TELNR,DEB.DEB_VRZKOS,DEB.DEB_FRANCO,DEB.LEVERING,DEB.DEB_ACTIVE
from TOTVRD as TVD
 join ARTCRED as ACR on (TVD.ART_CODE = ACR.ART_CODE)
 join DEBITEUR as DEB on (ACR.CRED_NR = DEB.DEB_NR)
where (TVD.VOORRAAD + TVD.BACKORDER - TVD.RESERVED < TVD.MINSTOCK)
  and (ACR.CRED_NR is not null)
  and (ACR.CRED_NR <> '')
  and (ACR.ISNIETLEV is null or ACR.ISNIETLEV = 0);


my.cnf

Code: Select all

[client]
port = 3306
socket = /run/mysqld/mysqld.sock

[mysqld]
bind-address = 0.0.0.0
port = 3306
socket = /run/mysqld/mysqld.sock
#skip-name-resolve
net_buffer_length = 2K
thread_stack = 240K
innodb_data_home_dir = /var/packages/MariaDB/target/mysql
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /var/packages/MariaDB/target/mysql
innodb_additional_mem_pool_size = 2M
lower_case_table_names = 1
key_buffer_size = 64M
read_buffer_size = 64K
read_rnd_buffer_size = 256K
table_open_cache = 2000
tmp_table_size = 522M
thread_cache_size = 10
myisam_max_sort_file_size = 100G
myisam_sort_buffer_size = 2G
innodb_buffer_pool_size = 4G
innodb_thread_concurrency = 9
innodb_autoextend_increment = 64
innodb_buffer_pool_instances = 8
innodb_concurrency_tickets = 5000
innodb_old_blocks_time = 1000
innodb_stats_on_metadata = 0
innodb_file_per_table = 1
back_log = 80
join_buffer_size = 256K
max_allowed_packet = 500M
max_connect_errors = 100
open_files_limit = 4161
query_cache_type = 0
sort_buffer_size = 256K
table_definition_cache = 1400
binlog_row_event_max_size = 8K
sync_master_info = 10000
sync_relay_log = 10000
sync_relay_log_info = 10000
#general_log = 1
#slow-query-log = 1

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M

[mysqlhotcopy]
interactive-timeout


server.err from restart mysqld process till end of query

Code: Select all

Version: '5.5.54-MariaDB'  socket: '/run/mysqld/mysqld.sock'  port: 3306  Source distribution
171020 20:38:01 [Note] /usr/bin/mysqld: Normal shutdown

171020 20:38:01 [Note] Event Scheduler: Purging the queue. 0 events
171020 20:38:01  InnoDB: Starting shutdown...
171020 20:38:07  InnoDB: Shutdown completed; log sequence number 8045362150
171020 20:38:07 [Note] /usr/bin/mysqld: Shutdown complete

171020 20:38:08 mysqld_safe mysqld from pid file /run/mysqld/mysqld.pid ended
171020 20:38:11 mysqld_safe Starting mysqld daemon with databases from /var/packages/MariaDB/target/mysql
171020 20:38:12 [Note] /usr/bin/mysqld (mysqld 5.5.54-MariaDB) starting as process 10811 ...
171020 20:38:12 InnoDB: The InnoDB memory heap is disabled
171020 20:38:12 InnoDB: Mutexes and rw_locks use InnoDB's own implementation
171020 20:38:12 InnoDB: Compressed tables use zlib 1.2.8
171020 20:38:12 InnoDB: Using Linux native AIO
171020 20:38:12 InnoDB: Initializing buffer pool, size = 4.0G
171020 20:38:13 InnoDB: Completed initialization of buffer pool
171020 20:38:13 InnoDB: highest supported file format is Barracuda.
171020 20:38:15  InnoDB: Waiting for the background threads to start
171020 20:38:16 Percona XtraDB (http://www.percona.com) 5.5.52-MariaDB-38.3 started; log sequence number 8045362150
171020 20:38:16 [Note] Plugin 'FEEDBACK' is disabled.
171020 20:38:16 [Note] Server socket created on IP: '0.0.0.0'.
171020 20:38:17 [Note] Event Scheduler: Loaded 0 events
171020 20:38:17 [Note] /usr/bin/mysqld: ready for connections.
Version: '5.5.54-MariaDB'  socket: '/run/mysqld/mysqld.sock'  port: 3306  Source distribution
171020 20:40:53 [Warning] IP address '192.168.1.75' could not be resolved: Name or service not known
NielsE
I'm New!
I'm New!
Posts: 2
Joined: Sat Oct 21, 2017 9:27 am

Re: mysqld CPU 100%+ on query

Postby NielsE » Thu Dec 07, 2017 6:27 pm

Nobody in here with a similar problem or have some thoughs on how to handle this?

Thanks.

Return to “Web Server + PHP / MySQL + MariaDB”

Who is online

Users browsing this forum: No registered users and 4 guests