improve mysql performance wordpress my.cnf file configuration

mysql bufferpool, mysql memory, mysql open file limit, mysqltuner

monitoring mysql with newrelic, mysql max connection limit, php fpm config for 1000 concurrent users,

mysql logs

mysql open file limit

mysql error 24: “Too many open files.”

open_files_limit=8000
(less than os limit)
ulimit -n
open-files-limit=infinity
to view existing

mysql> select @@open_files_limit;

add to permanently in my.cnf
open_files_limit=infinity
systemctl edit mysql.service
[Service]
LimitNOFILE=8192

also change in system /etc/sysctl.conf

mysql query cache disabled 5.5 8 newer versions

query_cache_size (=0)

query_cache_type (=0)

query_cache_limit (> 0B, or use smaller result sets)

join_buffer_size (> 256.0K, or always use indexes with joins)

tmp_table_size (> 64M)

max_heap_table_size (> 64M)

innodb_buffer_pool_size (>= 1G) if possible.

innodb_log_file_size * innodb_log_files_in_group should be equal to 1/4 of buffer pool size (=512M) if possible.

innodb_buffer_pool_instances (=1)

default value is 5MB.

innodb_log_file_size

134/5*100=3.73 4%

innodb_log_file_size = 256M # 25% of buffer pool size

larger the value, the less checkpoint flush activity is required in the buffer pool, saving disk I/O. Larger log files also make crash recovery slower,

join_buffer_size default 128

Resource & performance

Threads & Connections

connection related errors

SHOW GLOBAL STATUS LIKE ‘max_connections’;

SHOW GLOBAL STATUS LIKE ‘Connection_errors_max_connections’;

Connection_errors_max_connections

SHOW GLOBAL STATUS LIKE ‘max_connections’;

SHOW GLOBAL STATUS LIKE ‘Connection_errors_max_connections’;

Connection_errors_max_connections

Threads_connected

Threads_running

errors

Connection_errors_ internal

Aborted_connects

Connection_errors_ max_connections

SHOW GLOBAL STATUS LIKE ‘Max_used_connections’;

peak number of connections since mysqld started.

php fpm pm.max_children=5 equal mysql concurent connections

how to check mysql concurrent connections

mysql> show processlist;

SHOW GLOBAL STATUS LIKE ‘Threads_connected’;

php & nginx process

pstree | grep php

ps aux | grep nginx

ps aux | grep mysql

Threads_running: connections currently running some sql

SHOW GLOBAL STATUS LIKE ‘Threads_running’;

+—————–+——-+

| Variable_name | Value |

+—————–+——-+

| Threads_running | 2 |

+—————–+——-+

mysql> SHOW GLOBAL STATUS LIKE ‘Threads_connected’;

+——————-+——-+

| Variable_name | Value |

+——————-+——-+

| Threads_connected | 3 |

+——————-+——-+

1 row in set (0.00 sec)

mysql> SHOW GLOBAL STATUS LIKE ‘Threads_running’

-> ^C

mysql> SHOW GLOBAL STATUS LIKE ‘Threads_running’;

+—————–+——-+

| Variable_name | Value |

+—————–+——-+

| Threads_running | 2 |

+—————–+——-+

1 row in set (0.00 sec)

mysql> show processlist;

+——-+—————–+———–+——+———+——–+————————+——————+

+——-+—————–+———–+——+———+——–+————————+——————+

+——-+—————–+———–+——+———+——–+————————+——————+

4 rows in set (0.00 sec)

mysql> ps aux | grep mysql

-> ^DBye

root@instance-2:~# ps aux | grep mysql

root 7849 0.0 0.2 39420 8216 pts/0 T 06:56 0:00 mysql -u root -p

root 8506 0.0 0.2 39420 8372 pts/0 T 07:35 0:00 mysql -u root -p

root 10645 0.0 0.0 14856 980 pts/0 S+ 09:17 0:00 grep –color=auto mysql

mysql 27136 2.8 34.4 2456784 1300976 ? Ssl Mar12 237:43 /usr/sbin/mysqld

connection can run a maximum of one query at once, and does it in a single thread. The server opens one thread per query.

single connections are run one-after-another

* Query throughput

SHOW GLOBAL STATUS LIKE “Questions”;

+—————+———+

| Variable_name | Value |

+—————+———+

| Questions | 3970148 |

+—————+———+

Questions Count of executed statements (sent by client)

Com_select SELECT statements

Writes Inserts, updates, or deletes

Questions or Com_select:

Read queries are generally captured by the Com_select

SHOW GLOBAL STATUS LIKE “Queries”;

| Variable_name | Value |

+—————+———+

| Queries | 3990353 |

+—————+———+

SHOW GLOBAL STATUS LIKE “Com_update”;

Writes = Com_insert + Com_update + Com_delete

SHOW GLOBAL STATUS LIKE “Com_insert”;

SHOW GLOBAL STATUS LIKE “Com_delete”;

mysql> SHOW GLOBAL STATUS LIKE “Com_update”;

+—————+——-+

| Variable_name | Value |

+—————+——-+

| Com_update | 45522 |

+—————+——-+

1 row in set (0.00 sec)

mysql> SHOW GLOBAL STATUS LIKE “Com_insert”;

+—————+——–+

| Variable_name | Value |

+—————+——–+

| Com_insert | 163378 |

+—————+——–+

1 row in set (0.00 sec)

mysql> SHOW GLOBAL STATUS LIKE “Com_delete”;

+—————+——-+

| Variable_name | Value |

+—————+——-+

| Com_delete | 38804 |

+—————+——-+

1 row in set (0.00 sec)

mysql> SHOW GLOBAL STATUS LIKE “Questions”;

+—————+———+

| Variable_name | Value |

+—————+———+

| Questions | 3987286 |

+—————+———+

1 row in set (0.00 sec)

Quires & Threads running relation

to check queries running concurrently

SHOW GLOBAL STATUS LIKE “Threads_running”;

+—————–+——-+

| Variable_name | Value |

+—————–+——-+

| Threads_running | 2 |

+—————–+——-+

1 row in set (0.00 sec)

mysql> SHOW GLOBAL STATUS LIKE “Queries”;

+—————+———+

| Variable_name | Value |

+—————+———+

| Queries | 3992147 |

+—————+———+

Query run time

Query errors Available by Performance schema query

Slow_queries

performance schema’s events_statements_summary_by_digest table,

per-schema average run time in microseconds by database,

SELECT schema_name

, SUM(count_star) count

, ROUND( (SUM(sum_timer_wait) / SUM(count_star))

/ 1000000) AS avg_microsec

FROM performance_schema.events_statements_summary_by_digest

WHERE schema_name IS NOT NULL

GROUP BY schema_name;

22k

to count the total number of statements per schema that generated errors:

mycase no errors.

hoc queries and investigation, however, it is usually easier to use MySQL’s sys schema.

to find the slowest statements (those in the 95th percentile by runtime):

SELECT * FROM sys.statements_with_runtimes_in_95th_percentile;

mycase 174 rows

normalized statements have generated errors:

SELECT * FROM sys.statements_with_errors_or_warnings;

mycase 22 rows caused by redirection plugin wp

Slow queries

10 seconds by default:

99% of requests returned within 0.1s.” latency

SHOW VARIABLES LIKE ‘long_query_time’;

SET GLOBAL long_query_time = 5;

Innodb_row_lock_waits metric, which counts how often the InnoDB storage engine had to wait to acquire a lock on a particular row

SHOW VARIABLES LIKE ‘long_query_time’;

SHOW GLOBAL STATUS LIKE ‘Slow_queries’;

Slow_queries

+—————+——-+

| Variable_name | Value |

+—————+——-+

| Slow_queries | 0 |

+—————+——-+

query errors top 10

SELECT * FROM sys.statements_with_errors_or_warnings

ORDER BY errors DESC

LIMIT 10;

* Sstem that is 50% saturated has two requests waiting service on average

* A system that is 90% saturated has 10 queued requests on average

* A system that is 99% saturated has 100 queued requests on average

errors

SET SESSION `sql_mode` = ? count 70000

SET `sql_mode` = ? 2000

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

SET GLOBAL sql_mode = ‘NO_ENGINE_SUBSTITUTION’;

SET SESSION sql_mode = ‘NO_ENGINE_SUBSTITUTION’;

permancent add to cnf

sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

sudo nano /etc/mysql/my.cnf

max connection

You can see that in /etc/my.cnf file. The directive looks like this:

innodb_file_per_table=1

uses disk space.

store mysql server on different disk/ server

3.innodb_buffer_pool_size (50-70% of memory from server ram)

SHOW VARIABLES LIKE ‘%innodb_buffer_pool_size%’;

Variable_name | Value |

+————————-+———–+

| innodb_buffer_pool_size | 134217728 | //131 MB

SET GLOBAL innodb_buffer_pool_size = (value in bytes); //without restarting the server.

2342177288 //2.34 GB

2147483648

SET GLOBAL innodb_buffer_pool_size =4026531840;

ERROR 1238 (HY000): Variable ‘innodb_buffer_pool_size’ is a read only variable

4 disable swappiness

sysctl vm.swappiness

vm.swappiness = 60

sysctl -w vm.swappiness=0

5.MySQL Max Connections

100-200 connections while larger may require 500-800

show variables like “max_connections”; //151

mysql -u root -pRajuginne23@#

mysql> set global max_connections := 800;

max.connection=(available RAM-global buffers)/thread buffers

check max used connections at previous time.

read more at mysql max connections limit

6.thread_cache_size

this setting has little affect on performance, unless you are receiving hundreds of connections per minute, at which time this value should be increased so the majority of connections can be made on cached threads.

mysql> show status like ‘Threads_created’;

mysql> show status like ‘Connections’;

100 – ((Threads_created / Connections) * 100) lower means new connections higher ok.

317355/108

100-((317355/108)*100)

ex: 100-(173/2850567)*100=99.993931

-293747.222222

show variables like “max_connections”; //151

show variables like “thread_cache_size”;

thread_cache_size | 8

show status where `variable_name` = ‘Threads_connected’; 5 //all the open connections.

mysql> show variables like “thread_cache_size”;

+——————-+——-+

| Variable_name | Value |

+——————-+——-+

| thread_cache_size | 8 |

+——————-+——-+

1 row in set (0.00 sec)

set global thread_cache_size = 16;

mysql> SHOW GLOBAL STATUS LIKE ‘Connections’; // connections created

+—————+———+

| Variable_name | Value |

+—————+———+

| Connections | 2853193 | 950

+—————+———+

1 row in set (0.01 sec)

mysql> SHOW GLOBAL STATUS LIKE ‘Threads_created’;

+—————–+——-+

| Variable_name | Value |

+—————–+——-+

| Threads_created | 173 | 6 after restart

+—————–+——-+

1 row in set (0.00 sec)

mysql> SHOW GLOBAL STATUS LIKE ‘Max_used_connections’; //maximum amount of connections you have had in the past

+———————-+——-+

| Variable_name | Value |

+———————-+——-+

| Max_used_connections | 20 |

+———————-+——-+

1 row in set (0.00 sec)

Threads_created / Connections : If this is over 0.01, then increase thread_cache_size. At the very least, thread_cache_size should be greater than Max_used_connections.

You should not set thread_cache_size higher than Max_used_connections

173/2853193=

set global thread_cache_size = 16;

max_connections

sets the maximum amount of concurrent connections. It is best to consider the maximum amount of connections you have had in the past before setting this number,

SHOW STATUS WHERE variable_name = ‘Max_used_connections’;

show status like ‘%onn%’;

mysql> set global thread_cache_size = 16;

dynamically changed without having to restart the MySQL service.

SHOW STATUS WHERE variable_name = ‘Max_used_connections’;

7.Disable MySQL Reverse DNS Lookups

dding the following in your configuration file:

[mysqld]

# Skip reverse DNS lookup of clients

skip-name-resolve

8.MySQL query_cache_size

If you have many repetitive queries and your data does not change often – use query cache. Usually value of 200-300 MB should be more than enough

ou will have to add the following settings in the MySQL configuration file:

query_cache_type = 1

query_cache_limit = 256K

#query_cache_min_res_unit = 2k

query_cache_size = 80M

SET GLOBAL query_cache_type = 1;

SHOW VARIABLES LIKE ‘%query_cache_size%’;

83886080 83.88608 MB

9. tmp_table_size and max_heap_table_size

tmp_table_size= 64M

max_heap_table_size= 64M

10.MySQL Slow query Logs

SHOW VARIABLES LIKE ‘%slow_query_log%’;

it off

SET GLOBAL slow_query_log = ‘ON’;

SET GLOBAL slow_query_log = ‘OFF’;

SET GLOBAL long_query_time = 1;

SET GLOBAL slow_query_log_file = ‘/var/log/mysql/mysql-slow.log’;

chown mysql:mysql /var/log/mysql.error.log

slow-query-log = 1

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

long_query_time = 1

X with a value that is greater than the long_query_time setting:

SELECT SLEEP(2);

MySQL idle Connections

SHOW VARIABLES LIKE ‘%wait_timeout%’;

+————————–+———-+

| Variable_name | Value |

+————————–+———-+

| innodb_lock_wait_timeout | 50 |

| lock_wait_timeout | 31536000 |

| wait_timeout | 28800 |

+————————–+———-

SET GLOBAL wait_timeout=60;

wait_timeout=60 //28800

mysqladmin processlist -u root -p | grep “Sleep”

Right MySQL Filesystem
MySQL max_allowed_packet

MySQL splits data into packets. Usually a single packet is considered a row that is sent to a client. The max_allowed_packet directive defines the maximum size of packet that can be sent.

15.MySQL Performance Tuning wuth mysqltuner

The tool is called mysqltuner.

To download and run it, use the following set of commands:

# wget https://github.com/major/MySQLTuner-perl/tarball/master

# tar xf master

# cd major-MySQLTuner-perl-993bc18/

# ./mysqltuner.pl

Optimize and Repair MySQL Databases

# mysqlcheck -u root -ppass –auto-repair –check –optimize –all-databases

# mysqlcheck -u root -p –auto-repair –check –optimize databasename

Changed limits: max_open_files: 1024 (requested 5000)

2018-01-29T05:41:05.683065Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000)

show variables like ‘%file%’;

SET GLOBAL open_files_limit=65535;

ERROR 1238 (HY000): Variable ‘open_files_limit’ is a read only variable

append these two lines to /etc/security/limits.conf

cat >> /etc/security/limits.conf

mysql hard nofile 65535

mysql soft nofile 65535

append this line to /usr/lib/systemd/system/mysqld.service (in the [service] section)

LimitNOFILE=65535

table_cache / table_open_cache

table_cache setting is now table_open_cache MySQL 5.6

This value should be kept higher than your open_tables value. To determine this value use:

SHOW STATUS LIKE ‘open%’;

mysql> SHOW STATUS LIKE ‘open%’;

+————————–+——–+

| Variable_name | Value |

+————————–+——–+

| Open_files | 82 |

| Open_streams | 0 |

| Open_table_definitions | 615 |

| Open_tables | 416 |

| Opened_files | 750599 |

| Opened_table_definitions | 0 |

| Opened_tables | 0 |

+————————–+——–+

show variables like ‘table_cache’;

table_open_cache

table_open_cache is related to max_connections. For example, for 200 concurrent running connections, specify a table cache size of at least 200 * N, where N is the maximum number of tables per join in any of the queries which you execute

MySQL manual recommends setting the table_cache value using the formula (table_cache = max_connections x N), where N is the number of tables in a typical join.

show variables like ‘%table_open_cache%’;

mysql> show variables like ‘%table_open_cache%’;

+—————————-+——-+

| Variable_name | Value |

+—————————-+——-+

| table_open_cache | 431 |

| table_open_cache_instances | 16 |

+—————————-+——-+

SET GLOBAL table_open_cache=2000;

Query OK, 0 rows affected (0.00 sec)

query cache

In total server about 2 million page views per month):

query_cache_type = 1

query_cache_limit = 256K

query_cache_min_res_unit = 2k

query_cache_size = 80M

show variables like ‘%query_cache%’;

HOW VARIABLES LIKE ‘have_query_cache’;

have_query_cache | YES

show variables like ‘%query_cache%’;

+——————————+———-+

| Variable_name | Value |

+——————————+———-+

| have_query_cache | YES |

| query_cache_limit | 262144 |

| query_cache_min_res_unit | 4096 |

| query_cache_size | 83886080 | 83MB

| query_cache_type | ON |

| query_cache_wlock_invalidate | OFF

SHOW STATUS LIKE ‘Qcache%’;

mysql> SHOW STATUS LIKE ‘Qcache%’;

+————————-+———–+

| Variable_name | Value |

+————————-+———–+

| Qcache_free_blocks | 6181 |

| Qcache_free_memory | 19662008 |

| Qcache_hits | 181624842 |

| Qcache_inserts | 14681695 |

| Qcache_lowmem_prunes | 4690665 |

| Qcache_not_cached | 4723291 |

| Qcache_queries_in_cache | 19730 |

| Qcache_total_blocks | 47603 |

+————————-+———–+

Qcache_lowmem_prunes is a large number. This means that the query cache had to remove queries to make room for other queries. Based on this example, you would want to increase query-cache-size slightly to keep your queries inside cache. If your Qcache_lowmem_prunes is “0”, then you may want to decrease query-cache-size to free up memory for other processes.

key buffer myisam only

show variables like ‘%buffer%’;

show variables like ‘key_buffer_size’;

key_buffer_size | 16777216 // 16 MB

According to mysql 256MB ram or more it should be 64M

servers with less than 128Mb should be 16 MB

SHOW PROCESSLIST

SHOW FULL PROCESSLIST\G

mysql> SHOW FULL PROCESSLIST\G

*************************** 1. row ***************************

Id: 2850482

User: root

Host: localhost

db: NULL

Command: Sleep

Time: 2300

State:

Info: NULL

*************************** 2. row ***************************

Id: 2850990

User: root

Host: localhost

db: NULL

Command: Sleep

Time: 1740

State:

Info: NULL

If you have at least 1-2GB of memory and many tables and want maximum performance with a moderate number of clients, use something like this:

shell> mysqld_safe –key_buffer_size=384M –table_open_cache=4000 \ –sort_buffer_size=4M –read_buffer_size=1M &

SET GLOBAL table_open_cache=4000;

SET GLOBAL sort_buffer_size=4000000; 4M; 4

SET GLOBAL key_buffer_size=384M;

Set global key_buffer_size = 380000000; // worked

SET GLOBAL read_buffer_size=1000000; //1M;

* speed up queries that use the ORDER BY or GROUP BY clause to sort the result set by increasing the value of MySQL’s sort buffer, controlled via the sort_buffer variable. Also consider increasing the read_rnd_buffer_size variable to speed up reading of the sorted rows.

* You can speed up SELECT queries that scan the table sequentially by increasing the size of MySQL’s read buffer via the read_buffer_size variable.

Use innodb_flush_method=O_DIRECT to avoid a double buffer when writing.

26. Avoid O_DIRECT and EXT3 filesystem – you will serialize all your writes.

27. Allocate enough innodb_buffer_pool_size to load your entire InnoDB file into memory – less reads from disk.

28. Do not make innodb_log_file_size too big, with faster and more disks – flushing more often is good and lowers the recovery time during crashes.

29. Do not mix innodb_thread_concurrency and thread_concurrency variables – these two values are not compatible.

30. Allocate a minimal amount for max_connections – too many connections can use up your RAM and lock up your MySQL server.

31. Keep thread_cache at a relatively high number, about 16 – to prevent slowness when opening connections.

32. Use skip-name-resolve – to remove dns lookups.

33. Use query cache if your queries are repetitive and your data does not change often – however using query cache on data that changes often will give you a performance hit.

34. Increase temp_table_size – to prevent disk writes.

35. Increase max_heap_table_size – to prevent disk writes.

36. Do not set your sort_buffer_size too high – this is per connection and can use up memory fast.

37. Monitor key_read_requests and key_reads to determine your key_buffer size – the key read requests should be higher than your key_reads, otherwise you are not efficiently using your key_buffer.

38. Set innodb_flush_log_at_trx_commit = 0 will improve performance, but leaving it to default (1), you will ensure data integrity, you will also ensure replication is not lagging

39. Have a test environment where you can test your configs and restart often, without affecting production.

fix mysql high memory usage

Created_tmp_disk_tables | 1318 |

| Created_tmp_files | 53 |

| Created_tmp_tables | 1574

FOr WordPress

wp-optimize

Transient Cleaner plugin (version 4.9 of WordPress, transient housekeeping now takes place automatically )

query monitor plugin (helps you to identify long running queries with some suggestions, you can also implement MySQL slow queries log in mysql.cnf)

Delete Post Revisions

Limit auto revision to 2 or turn off.

wp-config.php file

define( ‘WP_POST_REVISIONS’, 2 );

OPTIMIZE TABLE ‘wp_posts’

identifying performance bottleneck is the key to faster improvement.

NOTE: your Server RAM should be 2x of Mysql Database Size.

MySql configuration files location

/etc/my.cnf
/etc/mysql/my.cnf
/var/lib/mysql/my.cnf
/etc/mysql/my.cnf

etc/my.cnf
innodb_buffer_pool_size = 384M
key_buffer = 256M
query_cache_size = 1M
query_cache_limit = 128M
thread_cache_size = 8
max_connections = 400
innodb_lock_wait_timeout = 100

for a server

Dell Server
CPU cores: Two
Processor(s): 1x Dual Xeon
Clock Speed: >= 2.33GHz
RAM: 2 GBytes
Disks: 1×250 GB SATA

check mysql version command line ubuntu

mysqladmin -V
mysqladmin Ver 8.0.12 for Linux on x86_64 (MySQL Community Server – GPL)

MySQL latest version

Monitoring

1. Work metrics:
* Database/transaction/query throughput (query latency)
* Query execution performance
2. Resource metrics:
* Connections

(Connection_errors_ internal, Aborted_connects, Connection_errors_ max_connections)

connection limit
* Buffer pool usage

InnoDB uses area of memory called he buffer pool cache data for tables and indexes

Buffer pool size mysql suggests upto

80% of physical memory incase its a dedicated mysql server.

but we allocate memory for php, nginx, fastcgi, opache, Memcached, if we are in a single machine.

buffer pool is more important as it caches your indexes and reduces disk IO.

checking Innodb bufferpool size on mysql 8

InnoDB Buffer Pool Metrics

SHOW GLOBAL VARIABLES LIKE “innodb_buffer_pool_size”;

+————————-+———–+
| Variable_name | Value |
+————————-+———–+
| innodb_buffer_pool_size | 134217728 |
+————————-+———–+

134.2MB

Ideal buffer pool size 50-70% (but for a shared application server)

mycase 3.7 gb physical ram

free -m

free -m
used free shared buff/cache available
Mem: 3693

1150

131

195

2411

2091
Swap: 0 0

there maybe 1 gb free ram always free.

actually 2091 mb avilable there,

but i decided increase up to 512 MB. or 1 GB later.

based on below calculation i need to adjust.

Innodb_buffer_pool_pages_total Total number of pages in the buffer pool
Buffer pool utilization >> Ratio of used to total pages in the buffer pool
innodb_buffer_pool_read_requests >>>Requests made to the buffer pool
Innodb_buffer_pool_reads >> Requests the buffer pool could not fulfill

innodb_buffer_pool_size = N * innodb_buffer_pool_chunk_size

* innodb_buffer_pool_instances

SHOW GLOBAL VARIABLES LIKE “innodb_buffer_pool_chunk_size”;

SHOW GLOBAL VARIABLES LIKE “innodb_buffer_pool_instances”;

2147MB

314*8= 2512 adjusted

314*1 =314 MB (actually there)

Buffer pool efficiency

nnodb_buffer_pool_reads/innodb_buffer_pool_read_requests*100= 0.001

requests 14871553 |

| Innodb_buffer_pool_reads | 39678

39678/14871553*100=0.26680468408

Buffer pool utilization

(Innodb_buffer_pool_pages_total – Innodb_buffer_pool_pages_free)

—————————————————————-

Innodb_buffer_pool_pages_total

SHOW GLOBAL STATUS LIKE ‘Innodb_buffer_pool_pages%’;

+———————————-+——–+

| Variable_name | Value |

+———————————-+——–+

| Innodb_buffer_pool_pages_data | 40136 |

| Innodb_buffer_pool_pages_dirty | 41 |

| Innodb_buffer_pool_pages_flushed | 4218 |

| Innodb_buffer_pool_pages_free | 90032 |

| Innodb_buffer_pool_pages_misc | 888 |

| Innodb_buffer_pool_pages_total | 131056 |

+———————————-+——–+

(131056-90032)/131056=0.31302649249

0.31302649249*100=31.30%

SHOW VARIABLES LIKE “innodb_page_size”;

+——————+——-+

| Variable_name | Value |

+——————+——-+

| innodb_page_size | 16384 |

+——————+——-+

Innodb_buffer_pool_pages_total * innodb_page_size

131056*16384=2147221504

2147.MB

SHOW VARIABLES LIKE “Innodb_buffer_pool_size”;

innodb_buffer_pool_size | 2147MB

query_cache_size

max_connections

max_connections = (Available RAM – Global Buffers) / Thread Buffers

key_buffer_size

20% of RAM size

innodb_buffer_pool_size

The size of the buffer pool plays a key role in system performance and is assigned a value between 50-70 % of the RAM available.

max_connections = 170

if you got an error says ‘MySQL Error, Too many connections…’

Query cache or redis / Memcached

MySQL 8.0: Retiring Support for the Query Cache (i am using msql 8 so i don’t need to worry about query cache)

change to a table invalidates ALL entries for that table.

MySQL will reset the query cache for the entire table. when the data updated.

query_cache_limit = 0
query_cache_size = 0

ON OFF ON_DEMAND

Query Optimization

Slow query log

free

check

SHOW GLOBAL VARIABLES LIKE “innodb_buffer_pool_chunk_size”;

or
SHOW GLOBAL VARIABLES LIKE “innodb_buffer_pool_size”;

bufferpool size (20-50% or upto 80% in dedicated mysql)

SET GLOBAL innodb_buffer_pool_size=536870912;

512*1024*1024

536870912

no need to restart mysql (for permanent you need to add my.cnf ) once the server resarts it fall back to my.cnf values.

mysql open file limit

mysql query cache disabled 5.5 8 newer versions

innodb_log_file_size

Resource & performance

Threads & Connections

Threads_connected

Threads_running

errors

* Query throughput

Questions or Com_select:

Quires & Threads running relation

Query run time

Slow_queries

Slow queries

max connection

4 disable swappiness

5.MySQL Max Connections

6.thread_cache_size

7.Disable MySQL Reverse DNS Lookups

8.MySQL query_cache_size

MySQL idle Connections

15.MySQL Performance Tuning wuth mysqltuner

table_cache / table_open_cache

table_open_cache

key buffer myisam only

FOr WordPress

Server Admin Cloud

wordpress