Query regarding my.cnf optimization

Tsuna

October 12, 2019 07:14

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?

Comments

17 comments

GOT

October 14, 2019 13:38

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.

cPanelLauren

October 15, 2019 21:20
I would also not enable skip-name-resolve - this is known to cause issues and the benefits are inconsequential
0

Tsuna

October 17, 2019 13:45

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 [QUOTE][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

mysqld	1	100%	4.57 GB	mysql
lsphp	4	25.1%	198.09 MB	sitse1
litespeed	2	3.3%	56.71 MB	nobody
lmtp	1	0.4%	32.47 MB	site2
lsphp	2	0.2%	59.73 MB	site3
lsphp	1	0.2%	16.29 MB	site4
lsphp	3	0.1%	39.96 MB	site5
lfd - sleeping	1	0.1%	30 MB	root
lsphp	1	0.1%	16.29 MB	site6
litespeed	1	0.1%	16.27 MB	root

Pid	Owner	Priority	CPU %	Memory %	Command
24376	mysql	0	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

Id	User	Host	db	Command	Time	State	Info
1	system user		NULL	Daemon	NULL	InnoDB purge coordinator	NULL
3	system user		NULL	Daemon	NULL	InnoDB purge worker	NULL
2	system user		NULL	Daemon	NULL	InnoDB purge worker	NULL
4	system user		NULL	Daemon	NULL	InnoDB purge worker	NULL
5	system user		NULL	Daemon	NULL	InnoDB shutdown handler	NULL
1070989	site1_db1	localhost	site1_db1	Sleep	10		NULL
1071100	site1_db1	localhost	site1_db1	Query	0	Creating sort index	SELECT wp_posts.ID FROM wp_posts WHERE 1=1 AND (((wp_posts.post_title LIKE '%bungou%') OR (hu
1071109	site2_db2	localhost	site2_db2	Sleep	0		NULL
1071110	site3_db3	localhost	site3_db3	Query	0	Sending data	SELECT wp_posts.ID FROM wp_posts LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relati
1071111	root	localhost	NULL	Query	0	Init	SHOW PROCESSLIST

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

GOT

October 17, 2019 15:32
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.
0
Tsuna

October 18, 2019 02:18
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
0
GOT

October 18, 2019 14:00
We are not really allowed to self promote, but there are a number of management companies who can help, ours included. List is here:
0
Tsuna

October 21, 2019 02:34
We are not really allowed to self promote, but there are a number of management companies who can help, ours included. List is here:
0
GOT

October 21, 2019 14:02
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!
0
cPanelLauren

October 21, 2019 15:52
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.
0
Tsuna

March 11, 2020 12:49
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 [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 [--] 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.
0
GOT

March 11, 2020 12:57
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.
0
Tsuna

March 11, 2020 13:07
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 [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)
0
GOT

March 11, 2020 13:10
That is a bit trickier to track down. It means that you're hitting a limit imposed elsewhere. This article could be of assistance.
0
Tsuna

March 11, 2020 13:43
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.
0
GOT

March 11, 2020 13:55
Is that a question?
0
Tsuna

March 11, 2020 14:34
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? Could not increase number of max_open_files to more than 10000 (request: 78973
So LimitNOFILE=80000 LimitMEMLOCK=80000 on each of these?
0
nlaruelle

June 23, 2022 12:31
[quote][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 [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
0

Please sign in to leave a comment.

Comments

Didn't find what you were looking for?