MariaDB crazy at 500% cpu time
GLOBAL STATUS
MariaDB [(none)]> SHOW GLOBAL STATUS;
+--------------------------------------------------------------+--------------------------------------------------+
| Variable_name | Value |
+--------------------------------------------------------------+--------------------------------------------------+
| Aborted_clients | 10 |
| Aborted_connects | 17 |
| Access_denied_errors | 2808 |
| Acl_column_grants | 0 |
| Acl_database_grants | 255 |
| Acl_function_grants | 0 |
| Acl_procedure_grants | 0 |
| Acl_proxy_users | 1 |
| Acl_role_grants | 0 |
| Acl_roles | 0 |
| Acl_table_grants | 2 |
| Acl_users | 253 |
| Aria_pagecache_blocks_not_flushed | 0 |
| Aria_pagecache_blocks_unused | 15706 |
| Aria_pagecache_blocks_used | 40 |
| Aria_pagecache_read_requests | 287044 |
| Aria_pagecache_reads | 20253 |
| Aria_pagecache_write_requests | 36614 |
| Aria_pagecache_writes | 36593 |
| Aria_transaction_log_syncs | 1 |
| Binlog_commits | 0 |
| Binlog_group_commits | 0 |
| Binlog_group_commit_trigger_count | 0 |
| Binlog_group_commit_trigger_lock_wait | 0 |
| Binlog_group_commit_trigger_timeout | 0 |
| Binlog_snapshot_file | |
| Binlog_snapshot_position | 0 |
| Binlog_bytes_written | 0 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Binlog_stmt_cache_disk_use | 0 |
| Binlog_stmt_cache_use | 0 |
| Busy_time | 0.000000 |
| Bytes_received | 243371501 |
| Bytes_sent | 2355355672 |
| Com_admin_commands | 2293 |
| Com_alter_db | 0 |
| Com_alter_db_upgrade | 0 |
| Com_alter_event | 0 |
| Com_alter_function | 0 |
| Com_alter_procedure | 0 |
| Com_alter_server | 0 |
| Com_alter_table | 0 |
| Com_alter_tablespace | 0 |
| Com_alter_user | 0 |
| Com_analyze | 0 |
| Com_assign_to_keycache | 0 |
| Com_begin | 0 |
| Com_binlog | 0 |
| Com_call_procedure | 0 |
| Com_change_db | 92 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 0 |
| Com_compound_sql | 0 |
| Com_create_db | 0 |
| Com_create_event | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_procedure | 0 |
| Com_create_role | 0 |
| Com_create_server | 0 |
| Com_create_table | 0 |
| Com_create_temporary_table | 0 |
| Com_create_trigger | 0 |
| Com_create_udf | 0 |
| Com_create_user | 0 |
| Com_create_view | 0 |
| Com_dealloc_sql | 0 |
| Com_delete | 8354 |
| Com_delete_multi | 0 |
| Com_do | 0 |
| Com_drop_db | 0 |
| Com_drop_event | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_procedure | 0 |
| Com_drop_role | 0 |
| Com_drop_server | 0 |
| Com_drop_table | 0 |
| Com_drop_temporary_table | 0 |
| Com_drop_trigger | 0 |
| Com_drop_user | 0 |
| Com_drop_view | 0 |
| Com_empty_query | 0 |
| Com_execute_immediate | 0 |
| Com_execute_sql | 0 |
| Com_flush | 0 |
| Com_get_diagnostics | 0 |
| Com_grant | 0 |
488 rows in set (0.00 sec)
MariaDB [(none)]>
my.cnf
[root@host ~]# cat /etc/my.cnf
#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
[mysqld]
log-error=/var/lib/mysql/s102.halabtech.net.err
performance-schema=0
innodb_file_per_table=1
default-storage-engine=MyISAM
max_allowed_packet=268435456
open_files_limit=40000
skip-name-resolve
sql_mode=''
local-infile=0
connect_timeout=25
wait_timeout=30
interactive_timeout=30
slow-query-log=1
long_query_time=5
slow_query_log_file="/var/log/mysql-slow.log"
key_buffer_size = 128M
thread_stack = 128K
thread_cache_size = 8
max_heap_table_size = 256M
query_cache_limit = 4M
query_cache_size = 512M
innodb_buffer_pool_size = 2G
Server specs:
Intel(R) Core(TM) i7-8700 CPU @ 3.20GHz
Memory: 4107488k/68927488k available (7792k kernel code, 1900528k absent, 1353716k reserved, 5950k data, 1984k init)
Filesystem Size Used Avail Use% Mounted on
devtmpfs 32G 0 32G 0% /dev
tmpfs 32G 0 32G 0% /dev/shm
tmpfs 32G 28M 32G 1% /run
tmpfs 32G 0 32G 0% /sys/fs/cgroup
/dev/md2 437G 273G 142G 66% /
/dev/md1 488M 401M 62M 87% /boot
tmpfs 6.3G 0 6.3G 0% /run/user/0
tmpfs 6.3G 0 6.3G 0% /run/user/987
tmpfs 6.3G 0 6.3G 0% /run/user/1034
UPDATE
MYSQL SLOW QUERY DUMP: (Database real names changed)
Reading mysql slow query log from /var/log/mysql-slow.log
Count: 2 Time=20.58s (41s) Lock=0.00s (0s) Rows_sent=4089261.5 (8178523), Rows_examined=4089261.5 (8178523), Rows_affected=0.0 (0), root[root]@localhost
SELECT /*!40001 SQL_NO_CACHE */ * FROM `hb_udownloads`
Count: 3 Time=15.38s (46s) Lock=0.00s (0s) Rows_sent=81.0 (243), Rows_examined=10026558.0 (30079674), Rows_affected=0.0 (0), server***_fusion[server***_fusion]@localhost
SELECT *,(select count(id) from tbl_swkey
where group_id=tbl_keygroup.id and used=0) as selled,(select count(id) from tbl_swkey
where group_id=tbl_keygroup.id and used=1) as used FROM tbl_keygroup
order by `displayorder` asc
Count: 1 Time=8.68s (8s) Lock=0.00s (0s) Rows_sent=0.0 (0), Rows_examined=4090482.0 (4090482), Rows_affected=0.0 (0), support***_res[support***_res]@localhost
UPDATE hb_udownloads SET `upackage_id` = 0 WHERE upackage_id = 403775
Count: 1 Time=5.64s (5s) Lock=0.00s (0s) Rows_sent=0.0 (0), Rows_examined=4088258.0 (4088258), Rows_affected=0.0 (0), support***_res[support***_res]@localhost
SELECT udownload_id AS retval FROM hb_udownloads WHERE user_id = 415064 AND file_id = 78499 LIMIT 1
Count: 1 Time=5.58s (5s) Lock=0.00s (0s) Rows_sent=0.0 (0), Rows_examined=4088256.0 (4088256), Rows_affected=0.0 (0), support***_res[support***_res]@localhost
SELECT udownload_id AS retval FROM hb_udownloads WHERE user_id = 208286 AND file_id = 202629 LIMIT 1
Count: 1 Time=5.35s (5s) Lock=0.00s (0s) Rows_sent=0.0 (0), Rows_examined=4088255.0 (4088255), Rows_affected=0.0 (0), support***_res[support***_res]@localhost
SELECT udownload_id AS retval FROM hb_udownloads WHERE user_id = 235082 AND file_id = 473624 LIMIT 1
Count: 1 Time=5.21s (5s) Lock=0.00s (0s) Rows_sent=0.0 (0), Rows_examined=4088254.0 (4088254), Rows_affected=0.0 (0), support***_res[supporth***_res]@localhost
SELECT udownload_id AS retval FROM hb_udownloads WHERE user_id = 61350 AND file_id = 493488 LIMIT 1
Count: 1 Time=5.17s (5s) Lock=0.00s (0s) Rows_sent=0.0 (0), Rows_examined=4088259.0 (4088259), Rows_affected=0.0 (0), support***_res[support***_res]@localhost
SELECT udownload_id AS retval FROM hb_udownloads WHERE user_id = 338554 AND file_id = 439150 LIMIT 1
Kindly please advise why I am having really big spikes sometimes to 500% and what should be done to solve the problem since I am getting lots of 50x errors. Please forgive my bad English too. Thank you in advance.
High CPU usage usually means lots of in-memory table-scanning.
Looking at one of your slow queries:
Time=5.64s (5s) Rows_sent=0.0 (0) Rows_examined=4088258.0 (4088258)
SELECT udownload_id AS retval
FROM hb_udownloads
WHERE user_id = 415064
AND file_id = 78499
LIMIT 1
The query is reading the entire 4M rows table but returning at most one of them (and, in this case, none at all).
You need indexes on this table to support this query. A composite one on user_id and file_id would be a good start.
Another performance killer here:
Time=20.58s (41s) Rows_sent=4089261.5 (8178523) Rows_examined=4089261.5 (8178523)
SELECT /*!40001 SQL_NO_CACHE */ *
FROM `hb_udownloads`
Never use "select *" in Production code.
Always specify the columns you want explicitly. I would guess that this table has been "growing" (gaining columns) lately.
OK, without a where clause, this is going to table-scan anyway, but that begs the question - what is the poor client [application] going to do with the 4M rows that this query "throws" at it?
Some of these queries look like they might be in need of optimization.
For example:
Rows_examined=10026558.0 (30079674), Rows_affected=0.0 (0), server***_fusion[server***_fusion]@localhost
SELECT *,(select count(id) from tbl_swkey
where group_id=tbl_keygroup.id and used=0) as selled,(select count(id) from tbl_swkey
where group_id=tbl_keygroup.id and used=1) as used FROM tbl_keygroup
order by `displayorder` asc
This query is scanning 30000000 rows.
Its possible you could change this one, for example to two queries:
SELECT * FROM tbl_keygroup ORDER by `displayorder` asc;
SELECT count(id) FROM tbl_swkey WHERE used IN (0,1) GROUP BY used;
Since the query itself involves a useless join and two full table scans that could be halved.
Much of what you've shown points to issues with query optimization and I'd be looking at that slow query log to determine what the best way to retrieve that data would be with the least amount of table scans.
You'll also probably want to index a lot of that data too for the same purpose.
** This should make the first one a lot faster:
SELECT k.*, s.sold, s.used
FROM ( SELECT group_id,
SUM(used = 0) AS sold,
SUM(used = 1) AS used
FROM tbl_swkey
) AS s
JOIN tbl_keygroup AS k ON s.group_id = k.id
ORDER BY displayorder
And have
tbl_swkey: INDEX(group_id, used)
tbl_keygroup: INDEX(displayorder)
If you need to discuss this query further, please provide SHOW CREATE TABLE for both tables, the table sizes, and EXPLAIN SELECT ....
** This
SELECT udownload_id AS retval
FROM hb_udownloads
WHERE user_id = 415064
AND file_id = 78499
LIMIT 1
needs a 3-column composite index to be a lot faster:
INDEX(user_id, file_id, udownload_id)
You don't care which matching row you get? Or should you add an ORDER BY? If you add an ORDER BY, my index advice may need to change.
** Did this come from mysqldump?
SELECT /*!40001 SQL_NO_CACHE */ * FROM `hb_udownloads`
If you are not happy with how invasive dumps are, there are several discussions on dba.stackexchange.com about that topic.