MariaDB сумасшедший на 500% процессорного времени
ГЛОБАЛЬНЫЙ СТАТУС
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)]>
мой.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
Характеристики сервера:
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
ОБНОВЛЯТЬ
MYSQL SLOW QUERY DUMP: (настоящие имена баз данных изменены)
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
Пожалуйста, сообщите, почему у меня иногда возникают очень большие скачки до 500%, и что нужно сделать, чтобы решить проблему, поскольку я получаю много ошибок 50x. Пожалуйста, простите и мой плохой английский. Заранее спасибо.