Query regarding my.cnf optimization

Tsuna

Member
Jun 22, 2019
11
1
1
India
cPanel Access Level
Root Administrator
I own a small rig with around 40 diff cpanel accounts, each holding 1 website.

Mostly wordpress.
We use php 7.3 with litespeed.

Recently facing lots of cpu overload with mysql using almost all of it.

This is the cnf file



[mysqld]
log-error=/var/lib/mysql/hostname.example.com
performance-schema=0
default-storage-engine=MyISAM
innodb_file_per_table=1
max_allowed_packet=268435456
open_files_limit=10000
local-infile=0



mysql tuner says

Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found.
See dev.mysql.com/doc/internals/en/join-buffer-size.html
(specially the conclusions at the bottom of the page).
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
Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: [removed url]
Variables to adjust:
query_cache_size (=0)
query_cache_type (=0)
query_cache_limit (> 1M, or use smaller result sets)
join_buffer_size (> 256.0K, or always use indexes with JOINs)
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
table_definition_cache(400) > 2461 or -1 (autosizing if supported)
performance_schema = ON enable PFS
innodb_buffer_pool_size (>= 565.8M) if possible.
innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.



We have mariadb on this.

Can someone who is more familiar on this recommend us what to update to make effective use of this?
 
Last edited by a moderator:

GOT

Get Proactive!
PartnerNOC
Apr 8, 2003
1,768
322
363
Chesapeake, VA
cPanel Access Level
DataCenter Provider
These are the recommendations that are most important:

join_buffer_size (> 256.0K, or always use indexes with JOINs)
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
table_definition_cache(400) > 2461 or -1 (autosizing if supported)
performance_schema = ON enable PFS
innodb_buffer_pool_size (>= 565.8M) if possible.
innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.

So add/modify these values to your my.cnf. Go up slowly and re-run tuner after about 4 hours. Keep an eye on max mysql memory vs. overall system memory so you do not set them so hig that you cause your server to run out of ram potentially.​
 

Tsuna

Member
Jun 22, 2019
11
1
1
India
cPanel Access Level
Root Administrator
These are the recommendations that are most important:

join_buffer_size (> 256.0K, or always use indexes with JOINs)
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
table_definition_cache(400) > 2461 or -1 (autosizing if supported)
performance_schema = ON enable PFS
innodb_buffer_pool_size (>= 565.8M) if possible.
innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.

So add/modify these values to your my.cnf. Go up slowly and re-run tuner after about 4 hours. Keep an eye on max mysql memory vs. overall system memory so you do not set them so hig that you cause your server to run out of ram potentially.​

I asked my managed support for advice and they added this

[mysqld]
log-error=/var/lib/mysql/site.site.com.err
performance-schema=0
default-storage-engine=MyISAM
innodb_file_per_table=1
max_allowed_packet=536870912
open_files_limit=10000
local-infile=0
innodb_buffer_pool_size=2G
max_heap_table_size = 512M
tmp_table_size =5126M
max_connections=500
table_open_cache=32502
table_definition_cache=-1
thread_cache_size=640
key_buffer_size=512M
sort_buffer_size = 2M
read_buffer_size = 4M
read_rnd_buffer_size = 2M
join_buffer_size = 512M
innodb_log_file_size=16M
Note: A day ago i changed the sql engine on my wp installs to innodb, after which i noticed if im running a db query then the website would say resource unavailable to my visitors.

So switched back to myisam.

Moving on to issue,

Thing were okay but i yet again have load spikes.

nodequery says

mysqld1100%4.57 GBmysql
lsphp425.1%198.09 MBsitse1
litespeed23.3%56.71 MBnobody
lmtp10.4%32.47 MBsite2
lsphp20.2%59.73 MBsite3
lsphp10.2%16.29 MBsite4
lsphp30.1%39.96 MBsite5
lfd - sleeping10.1%30 MBroot
lsphp10.1%16.29 MBsite6
litespeed10.1%16.27 MBroot


PidOwnerPriorityCPU %Memory %Command
24376mysql0
119.59

29.51
/usr/sbin/mysqld

:(

I'm confused.

We arent even running huge db queries or such.



Then i went to

Show MySQL Processes
IdUserHostdbCommandTimeStateInfoProgress
1system userNULLDaemonNULLInnoDB purge coordinatorNULL0.000
3system userNULLDaemonNULLInnoDB purge workerNULL0.000
2system userNULLDaemonNULLInnoDB purge workerNULL0.000
4system userNULLDaemonNULLInnoDB purge workerNULL0.000
5system userNULLDaemonNULLInnoDB shutdown handlerNULL0.000
1070989site1_db1localhostsite1_db1Sleep10NULL0.000
1071100site1_db1localhostsite1_db1Query0Creating sort indexSELECT wp_posts.ID FROM wp_posts WHERE 1=1 AND (((wp_posts.post_title LIKE '%bungou%') OR (hu0.000
1071109site2_db2localhostsite2_db2Sleep0NULL0.000
1071110site3_db3localhostsite3_db3Query0Sending dataSELECT wp_posts.ID FROM wp_posts LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relati0.000
1071111rootlocalhostNULLQuery0InitSHOW PROCESSLIST0.000


I'm not big on databases so im confused what im doing wrong for mysql to eat this much resources.
 

GOT

Get Proactive!
PartnerNOC
Apr 8, 2003
1,768
322
363
Chesapeake, VA
cPanel Access Level
DataCenter Provider
This is something that would require live diagnosis. Since you have a management provider, I would have them consult with you on this so they can sdo testing and see where the issue lies.
 

Tsuna

Member
Jun 22, 2019
11
1
1
India
cPanel Access Level
Root Administrator
Unfortunately, my managed support does not cover this deep into the database diagnosis.
And while typing this im facing a usage so bad that you can consider the server to be down.

Thats it, imma hire a db admin.

If there is anyone here would you like to, for a fee. im up for it
 

GOT

Get Proactive!
PartnerNOC
Apr 8, 2003
1,768
322
363
Chesapeake, VA
cPanel Access Level
DataCenter Provider
We are not really allowed to self promote, but there are a number of management companies who can help, ours included. List is here:

 
  • Like
Reactions: cPanelLauren

Tsuna

Member
Jun 22, 2019
11
1
1
India
cPanel Access Level
Root Administrator
We are not really allowed to self promote, but there are a number of management companies who can help, ours included. List is here:

I looked into this but as my problem seems like a database issue and not a server management issue, i dont feel that administration can help.
 

GOT

Get Proactive!
PartnerNOC
Apr 8, 2003
1,768
322
363
Chesapeake, VA
cPanel Access Level
DataCenter Provider
That is possible, though a good server manager will be able to assist you with long running queries to help your devs find what the issue is.

I wish you luck!
 
  • Like
Reactions: cPanelLauren

cPanelLauren

Product Owner II
Staff member
Nov 14, 2017
13,274
1,291
313
Houston
I will second what @GOT is saying, anyone that is well versed in system administration should have a firm handle on database administration. That's not meant to sway you one way or the other, just to clarify.
 

Tsuna

Member
Jun 22, 2019
11
1
1
India
cPanel Access Level
Root Administrator
Bumping my old post
We shifted to a new rig, things have changed a lot

Note: Im posting this so i can get an opinion on this, at the moment we dont have a lot of load on the rig so things are kinda stable.
Its a ryzen hexa core with 64gb ram and purely ssd.


This is what we are using currently

Code:
[mysqld]
log-error=/var/lib/mysql/myhostname.err
performance-schema=ON

#innodb stuff
innodb_buffer_pool_size=2G
innodb_log_file_size=256M
innodb_file_per_table=1
innodb_buffer_pool_instances=2

#max
max_allowed_packet=268435456
max_heap_table_size = 256M
max_connections=500

#table
table_open_cache=39390
table_definition_cache=12000

#read
read_buffer_size = 4M
read_rnd_buffer_size = 1M

#query
query_cache_limit=3M
query_cache_size=0
query_cache_type=0

open_files_limit=10000
tmp_table_size = 256M
thread_cache_size=640
key_buffer_size=128M
sort_buffer_size = 2M
join_buffer_size=16M
local-infile=0

#Skip reverse dns lookup of clients
skip-name-resolve

This is what mysqltuner had to say about this


Code:
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 10.3.22-MariaDB
[OK] Operating on 64-bit architecture

-------- Log file Recommendations ------------------------------------------------------------------
[OK] Log file /var/lib/mysql/myhostname.err exists
[--] Log file: /var/lib/mysql/myhostname.err(650K)
[OK] Log file /var/lib/mysql/myhostname.err is readable.
[OK] Log file /var/lib/mysql/myhostname.err is not empty
[OK] Log file /var/lib/mysql/myhostname.err is smaller than 32 Mb
[!!] /var/lib/mysql/myhostname.err contains 4984 warning(s).
[!!] /var/lib/mysql/myhostname.err contains 16 error(s).
[--] 11 start(s) detected in /var/lib/mysql/myhostname.err
[--] 1) 2020-03-09  7:46:25 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 2) 2020-03-09  7:46:04 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 3) 2020-03-09  7:41:46 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 4) 2020-03-09  7:41:00 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 5) 2020-03-09  7:18:08 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 6) 2020-03-09  7:14:32 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 7) 2020-03-09  6:41:21 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 8) 2020-03-09  6:41:18 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 9) 2020-03-09  6:41:13 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 10) 2020-03-09  6:40:35 140591177423040 [Note] /usr/sbin/mysqld: ready for connections.
[--] 10 shutdown(s) detected in /var/lib/mysql/myhostname.err
[--] 1) 2020-03-09  7:46:24 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 2) 2020-03-09  7:46:04 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 3) 2020-03-09  7:41:45 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 4) 2020-03-09  7:40:59 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 5) 2020-03-09  7:16:40 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 6) 2020-03-09  7:14:31 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 7) 2020-03-09  6:41:20 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 8) 2020-03-09  6:41:17 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 9) 2020-03-09  6:40:50 140590704916224 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 10) 2020-03-09  6:40:34 139725103073024 [Note] /usr/sbin/mysqld: Shutdown complete

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE
[--] Data in MyISAM tables: 2.2G (Tables: 1514)
[--] Data in InnoDB tables: 2.0G (Tables: 1156)
[--] Data in MEMORY tables: 12.7M (Tables: 3)
[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: 2d 5h 55m 7s (64M q [334.860 qps], 866K conn, TX: 3705G, RX: 22G)
[--] Reads / Writes: 96% / 4%
[--] Binary logging is disabled
[--] Physical Memory     : 62.8G
[--] Max MySQL memory    : 139.4G
[--] Other process memory: 0B
[--] Total buffers: 2.5G global + 279.3M per thread (500 max threads)
[--] P_S Max memory usage: 500M
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 9.3G (14.78% of installed RAM)
[!!] Maximum possible memory usage: 139.4G (222.11% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (0/64M)
[OK] Highest usage of available connections: 4% (23/500)
[OK] Aborted connections: 0.00%  (31/866208)
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (4K temp sorts / 9M sorts)
[!!] Joins performed without indexes: 8965
[!!] Temporary tables created on disk: 70% (1M on disk / 2M total)
[OK] Thread cache hit rate: 99% (23 created / 866K connections)
[OK] Table cache hit rate: 95% (3K open / 3K opened)
[OK] table_definition_cache(12000) is upper than number of tables(2938)
[OK] Open file limit used: 32% (3K/10K)
[OK] Table locks acquired immediately: 99% (15M immediate / 15M locks)

-------- Performance schema ------------------------------------------------------------------------
[--] Memory used by P_S: 500.2M
[--] Sys schema is installed.

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

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 56.1% (75M used / 134M cache)
[OK] Key buffer size / total MyISAM indexes: 128.0M/487.4M
[OK] Read Key buffer hit rate: 100.0% (715M cached / 47K reads)
[!!] Write Key buffer hit rate: 81.6% (906K cached / 739K writes)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[!!] InnoDB buffer pool / data size: 2.0G/2.0G
[OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 256.0M * 2/2.0G should be equal to 25%
[OK] InnoDB buffer pool instances: 2
[--] Number of InnoDB Buffer Pool Chunk : 16 for 2 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: 100.00% (36428798343 hits/ 36428863230 total)
[OK] InnoDB Write log efficiency: 95.74% (34321685 hits/ 35850702 total)
[OK] InnoDB log waits: 0.00% (0 waits / 1529017 writes)

-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/1B
[OK] Aria pagecache hit rate: 99.7% (518M cached / 1M 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:
    Control warning line(s) into /var/lib/mysql/myhostname.err file
    Control error line(s) into /var/lib/mysql/myhostname.err file
        Reduce your overall MySQL memory footprint for system stability
    Dedicate this server to your database for highest performance.
    We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found.
             See https://dev.mysql.com/doc/internals/en/join-buffer-size.html
             (specially the conclusions at the bottom of the page).
    Temporary table size is already large - reduce result set size
    Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    join_buffer_size (> 16.0M, or always use indexes with JOINs)
    innodb_buffer_pool_size (>= 2.0G) if possible.
 
  • Like
Reactions: nlaruelle

GOT

Get Proactive!
PartnerNOC
Apr 8, 2003
1,768
322
363
Chesapeake, VA
cPanel Access Level
DataCenter Provider
I would start with reduced by your max_connectiins down to 150 and increase your innodb buffer size to 3 gb. Then rerun mysqltuner after 24 hours.
 

Tsuna

Member
Jun 22, 2019
11
1
1
India
cPanel Access Level
Root Administrator
I would start with reduced by your max_connectiins down to 150 and increase your innodb buffer size to 3 gb. Then rerun mysqltuner after 24 hours.
Okay, done that, restarted mysql.

Side query
When i do restart mysql i get
Code:
[Warning] Could not increase number of max_open_files to more than 10000 (request: 78973)
[Warning] Changed limits: max_open_files: 10000 max_connections: 150 (was 150) table_cache: 4910 (was 39390)
 

GOT

Get Proactive!
PartnerNOC
Apr 8, 2003
1,768
322
363
Chesapeake, VA
cPanel Access Level
DataCenter Provider
That is a bit trickier to track down. It means that you're hitting a limit imposed elsewhere. This article could be of assistance.


I have found though that these file locations are not always correct so you may have to do some hunting around.
 

Tsuna

Member
Jun 22, 2019
11
1
1
India
cPanel Access Level
Root Administrator
hmmmm
That is a bit trickier to track down. It means that you're hitting a limit imposed elsewhere. This article could be of assistance.


I have found though that these file locations are not always correct so you may have to do some hunting around.

Code:
/etc/systemd/system/mariadb.service.d/migrated-from-my.cnf-settings.conf
# converted using /usr/bin/mariadb-service-convert
[Service]
LimitNOFILE=10000

/etc/systemd/system/mysqld.service.d/limits.conf
Empty

/usr/lib/systemd/system/mariadb.service
LimitNOFILE=16364
/usr/lib/systemd/system/mariadb.service
LimitNOFILE=16364

/etc/systemd/system/mysql.service
LimitNOFILE=16364

/etc/systemd/system/mysqld.service
LimitNOFILE=16364
Current values

Didnt find any
LimitMEMLOCK=
 

Tsuna

Member
Jun 22, 2019
11
1
1
India
cPanel Access Level
Root Administrator
Is that a question?
My bad

I meant to ask that none of the files have both the values

LimitNOFILE=
LimitMEMLOCK=


Should I create these and then input the values?

Code:
Could not increase number of max_open_files to more than 10000 (request: 78973
So


LimitNOFILE=80000
LimitMEMLOCK=80000

on each of these?
 

nlaruelle

Active Member
Sep 4, 2017
37
16
58
Belgium
cPanel Access Level
Website Owner
Code:
[mysqld]
log-error=/var/lib/mysql/myhostname.err
performance-schema=ON

#innodb stuff
innodb_buffer_pool_size=2G
innodb_log_file_size=256M
innodb_file_per_table=1
innodb_buffer_pool_instances=2

#max
max_allowed_packet=268435456
max_heap_table_size = 256M
max_connections=500

#table
table_open_cache=39390
table_definition_cache=12000

#read
read_buffer_size = 4M
read_rnd_buffer_size = 1M

#query
query_cache_limit=3M
query_cache_size=0
query_cache_type=0

open_files_limit=10000
tmp_table_size = 256M
thread_cache_size=640
key_buffer_size=128M
sort_buffer_size = 2M
join_buffer_size=16M
local-infile=0

#Skip reverse dns lookup of clients
skip-name-resolve
Sorry for the bump. I would like to say thank you to @Tsuna for the full my.cfg shared above, as it help me to understand my own MySQLTuner report and find the right settings to fix massive MySQL IO consumption for one of my server

mine is

Code:
[mysqld]
log-error = /var/lib/mysql/papaya.easyhoster.com.err
performance-schema = 0
innodb_file_per_table = 1
unix_socket = OFF
innodb_buffer_pool_size = 4G
innodb_log_file_size=256M
max_allowed_packet = 268435456
max_connections = 300
connect_timeout = 10
wait_timeout = 300
interactive_timeout = 300
join_buffer_size = 8M
table_definition_cache = 12000
open_files_limit=48000