I have problem with MariaDB , when connect analytics ~ 200
mysql started to appear 132% load and pushed the main CPU to about 40%. Making the website extremely slow
I'm using centos 7 ( 8GB ram , 2 core , NVME SSD + Nginx + php-fpm )
[client]
#password = your_password
port = 3306
socket = /var/lib/mysql/mysql.sock
[mysqld]
wait_timeout=300
interactive_timeout = 300
binlog_cache_size = 128K
thread_stack = 256K
join_buffer_size = 2048K
query_cache_type = 1
max_heap_table_size = 512M
port = 3306
socket = /var/lib/mysql/mysql.sock
datadir = /var/lib/mysql
default_storage_engine = InnoDB
performance_schema_max_table_instances = 400
table_definition_cache = 400
skip-external-locking
key_buffer_size = 384M
max_allowed_packet = 100G
table_open_cache = 384
sort_buffer_size = 1024K
net_buffer_length = 4K
read_buffer_size = 1024K
read_rnd_buffer_size = 768K
myisam_sort_buffer_size = 16M
thread_cache_size = 128
query_cache_size = 0
query_cache_type = 0
query_cache_limit = 8M
tmp_table_size = 512M
#sql-mode=NO_ENGINE_SUBSTITUTION
#skip-name-resolve
max_connections = 500
max_connect_errors = 100
open_files_limit = 65535
log-bin=mysql-bin
binlog_format=mixed
server-id = 1
expire_logs_days = 10
slow_query_log=1
slow-query-log-file=/home/abc.com.com/logs/mysql-slow.log
long_query_time=3
#log_queries_not_using_indexes=on
innodb_data_home_dir = /var/lib/mysql
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /var/lib/mysql
innodb_buffer_pool_size = 1G
innodb_log_file_size = 128M
innodb_log_buffer_size = 32M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
innodb_max_dirty_pages_pct = 90
innodb_read_io_threads = 2
innodb_write_io_threads = 2
innodb_buffer_pool_instances = 1
[mysqldump]
quick
max_allowed_packet = 500M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 64M
sort_buffer_size = 1M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
And tunning via mysqltuner
-------- Storage Engine Statistics ---------------------------------------------
[--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in InnoDB tables: 48.8M (Tables: 21)
[OK] Total fragmented tables: 0
-------- Analysis Performance Metrics ---------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.
-------- Security Recommendations ---------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] There is no basic password file list!
-------- CVE Security Recommendations ---------------------------------------------
[--] Skipped due to --cvefile option undefined
-------- Performance Metrics ---------------------------------------------
[--] Up for: 8m 0s (85K q [178.621 qps], 6K conn, TX: 41M, RX: 7M)
[--] Reads / Writes: 98% / 2%
[--] Binary logging is enabled (GTID MODE: ON)
[--] Physical Memory : 7.6G
[--] Max MySQL memory : 504.5G
[--] Other process memory: 0B
[--] Total buffers: 2.0G global + 1.0G per thread (500 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[!!] Maximum reached memory usage: 95.5G (1250.25% of installed RAM)
[!!] Maximum possible memory usage: 504.5G (6604.94% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (0/85K)
[OK] Highest usage of available connections: 18% (93/500)
[OK] Aborted connections: 0.04% (3/6727)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 700 sorts)
[OK] No joins without indexes
[!!] Temporary tables created on disk: 88% (5K on disk / 6K total)
[OK] Thread cache hit rate: 98% (93 created / 6K connections)
[OK] Table cache hit rate: 215% (114 open / 53 opened)
[OK] table_definition_cache(400) is upper than number of tables(261)
[OK] Open file limit used: 0% (29/65K)
[OK] Table locks acquired immediately: 100% (65K immediate / 65K locks)
[OK] Binlog cache memory access: 100.00% (1144 Memory / 1144 Total)
-------- Performance schema ---------------------------------------------
[--] Performance schema is disabled.
[--] Memory used by P_S: 0B
-------- ThreadPool Metrics ---------------------------------------------
[--] ThreadPool stat is enabled.
[--] Thread Pool Size: 2 thread(s).
[--] Using default value is good enough for your version (10.0.38-MariaDB)
-------- MyISAM Metrics ---------------------------------------------
[!!] Key buffer used: 18.7% (75M used / 402M cache)
[OK] Key buffer size / total MyISAM indexes: 384.0M/123.0K
[OK] Read Key buffer hit rate: 99.7% (355 cached / 1 reads)
[OK] Write Key buffer hit rate: 100.0% (96 cached / 96 writes)
-------- InnoDB Metrics ---------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 1.0G/48.8M
[OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 128.0M * 2/1.0G should be equal to 25%
[OK] InnoDB buffer pool instances: 1
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[OK] InnoDB Read buffer efficiency: 100.00% (422760044 hits/ 422761190 total)
[!!] InnoDB Write Log efficiency: 46.23% (484 hits/ 1047 total)
[OK] InnoDB log waits: 0.00% (0 waits / 1531 writes)
-------- Aria Metrics ---------------------------------------------
[--] Aria Storage Engine is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/0B
[OK] Aria pagecache hit rate: 98.6% (146 cached / 2 reads)
-------- TokuDB Metrics ---------------------------------------------
[--] TokuDB is disabled.
-------- XtraDB Metrics ---------------------------------------------
[--] XtraDB is disabled.
-------- Galera Metrics ---------------------------------------------
[--] Galera is disabled.
-------- Replication Metrics ---------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] Binlog format: MIXED
[--] XA support enabled: ON
[--] Semi synchronous replication Master: Not Activated
[--] Semi synchronous replication Slave: Not Activated
[--] This is a standalone server
-------- Recommendations ---------------------------------------------
General recommendations:
Control warning line(s) into /var/lib/mysql/dichvu.err file
Control error line(s) into /var/lib/mysql/dichvu.err file
MySQL was started within the last 24 hours - recommendations may be inaccurate
Reduce your overall MySQL memory footprint for system stability
Dedicate this server to your database for highest performance.
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
Temporary table size is already large - reduce result set size
Reduce your SELECT DISTINCT queries without LIMIT clauses
Performance schema should be activated for better diagnostics
Performance schema shouldn't be activated for MariaDB 10.0 for performance issue
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
performance_schema = ON enable PFS
performance_schema = OFF disable PFS
Please give me advice in this case
Free -m when cpu load high
total used free shared buff/cache available
Mem: 7821 1421 4755 431 1644 5715
Swap: 1023 0 1023
