Please Help Me Optimize MariaDB, Database is Really Slow

[701Hosting]

My server has really slowed down recently. I think it's due to MySQL. Please find below the results from mysqltuner.pl.

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE
[--] Data in MyISAM tables: 318.4M (Tables: 1673)
[--] Data in InnoDB tables: 127.8M (Tables: 393)
[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 are 620 basic passwords in the list.

-------- CVE Security Recommendations --------------------------------------------------------------
[OK] NO SECURITY CVE FOUND FOR YOUR VERSION

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 5d 6h 0m 18s (9M q [21.904 qps], 92K conn, TX: 55G, RX: 5G)
[--] Reads / Writes: 81% / 19%
[--] Binary logging is disabled
[--] Physical Memory : 3.7G
[--] Max MySQL memory : 38.6G
[--] Other process memory: 0B
[--] Total buffers: 417.0M global + 258.9M per thread (151 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[!!] Maximum reached memory usage: 3.9G (106.66% of installed RAM)
[!!] Maximum possible memory usage: 38.6G (1042.76% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (41/9M)
[OK] Highest usage of available connections: 9% (14/151)
[OK] Aborted connections: 0.07% (62/92622)
[!!] 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% (3 temp sorts / 1M sorts)
[OK] No joins without indexes
[!!] Temporary tables created on disk: 57% (361K on disk / 627K total)
[OK] Thread cache hit rate: 99% (15 created / 92K connections)
[OK] Table cache hit rate: 71% (10M hits / 14M requests)
[!!] table_definition_cache(400) is lower than number of tables(2230)
[OK] Open file limit used: 7% (3K/40K)
[OK] Table locks acquired immediately: 99% (8M immediate / 8M locks)

-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.
[--] Memory used by P_S: 0B
[--] Sys schema isn't installed.

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is enabled.
[--] Thread Pool Size: 2 thread(s).
[--] Using default value is good enough for your version (10.3.32-MariaDB)

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 26.3% (33.7M used / 128.0M cache)
[OK] Key buffer size / total MyISAM indexes: 128.0M/44.1M
[OK] Read Key buffer hit rate: 99.9% (74M cached / 48K reads)
[!!] Write Key buffer hit rate: 59.7% (2M cached / 1M writes)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 128.0M/127.8M
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (75 %): 48.0M * 2/128.0M should be equal to 25%
[OK] InnoDB buffer pool instances: 1
[--] Number of InnoDB Buffer Pool Chunk : 1 for 1 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 99.96% (107376041 hits/ 107416842 total)
[OK] InnoDB Write log efficiency: 90.07% (1665464 hits/ 1849171 total)
[OK] InnoDB log waits: 0.00% (0 waits / 183707 writes)

-------- Aria Metrics ------------------------------------------------------------------------------
[--] Aria Storage Engine is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/0B
[!!] Aria pagecache hit rate: 92.9% (4M cached / 340K 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: OFF
[--] Semi synchronous replication Slave: OFF
[--] This is a standalone server

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
Check warning line(s) in /var/lib/mysql/host2.xxx.com.err file
Check error line(s) in /var/lib/mysql/host2.xxx.com.err file
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
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries which have no LIMIT clause
Performance schema should be activated for better diagnostics
Consider installing Sys schema from GitHub - mysql/mysql-sys: The MySQL sys schema for MySQL
Consider installing Sys schema from GitHub - FromDual/mariadb-sys: The MariaDB sys schema for MariaDB
Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: MySQL :: MySQL 8.0 Reference Manual :: 15.6.5 Redo Log
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
table_definition_cache(400) > 2230 or -1 (autosizing if supported)
performance_schema = ON enable PFS
innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.

I'm having a hard time deciphering what to change. Any help would be greatly appreciated.

 

[701Hosting]

This is what my.cnf contains

[mysqld]
log-error=/var/lib/mysql/xxx.xxx.com.err
default-storage-engine=MyISAM
innodb_file_per_table=1
performance-schema=0
max_allowed_packet=268435456
open_files_limit=40000

 

[cPanelAnthony]

Hello! If you require MySQL optimization, it may be worth reaching out to a systems administrator. A list of services can be found here.

 

[701Hosting]

Thank you for the suggestion. I was hoping that someone might be able to check the output of mysqltuner and offer some suggestions. I'm guessing this might be the problem.

*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***

But I'm unsure on how to correct it. Honestly I think that cPanel should include better tools to improve MySQL functionality. Possibly allowing users to run mysqltuner from the GUI instead of SSH and offering suggestions. Then allowing those suggestions to be applied from WHM. That would have been awesome! Any help is appreciated.

 

[cPanelAnthony]

Thank you for the suggestion. I was hoping that someone might be able to check the output of mysqltuner and offer some suggestions. I'm guessing this might be the problem.

*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***

But I'm unsure on how to correct it. Honestly I think that cPanel should include better tools to improve MySQL functionality. Possibly allowing users to run mysqltuner from the GUI instead of SSH and offering suggestions. Then allowing those suggestions to be applied from WHM. That would have been awesome! Any help is appreciated.

These are great suggestions! Would you be able to submit a feature request using the link in my signature?

 

[701Hosting]

I'll gladly submit a feature request, but would like some help tuning my.cnf... Atleast an idea on where to start would be great.

 

[cPanelAnthony]

Thanks for the confirmation! I'm sure some users here will offer input. The following thread may contain useful information for you as well.

MySQL optimization

 

[HostNoc]

Hi

Following parameters that need to be optimized to get the maximum out of your MariaDB as we did in our server and DB persormance is excellent

innodb_file_per_table=1

innodb_buffer_pool_size = 1G

sysctl -w vm.swappiness=0

Max connections = 300

Thread Cache Size : 100 –((Threads_Created / Connections) *100)

query_cache_size = 64M

tmp_table_size= 64M

slow-query-log = 1
slow-query-log-file = /var/lib/mysql/mysql-slow.log
long_query_time = 1

wait_timeout=60

Note: values should be set after proper calculation of traffic forecast and system specifications of database server.

Regards
HostNoc

 

[marcuszan]

Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
table_definition_cache(400) > 2230 or -1 (autosizing if supported)
performance_schema = ON enable PFS
innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.

I'm having a hard time deciphering what to change. Any help would be greatly appreciated.

I have been tuning myself and started from the suggestions I got from mysql tuner. So in your case, I would try to start with >

locate your my.cnf file , make a backup before doing anything with this file and next add:

set-variable = tmp_table_size=32M
set-variable = max_heap_table_size=32M
set-variable = table_definition_cache=2480
set-variable = performance-schema=ON
set-variable = innodb_log_file_size=16M
set-variable = innodb_buffer_pool_size=64M

please note it suggests to do > innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
so tube these values accordingly
In my case I have this but that depends on server specs and use.

innodb_buffer_pool_size=1024M
innodb_log_file_size=256M

save, restart your DB server and run mysqltuner again

Give it a try and let us know if it works for you