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.
DSM 6.1.3-15152 Update 7
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);
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
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