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’;
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;
+——-+—————–+———–+——+———+——–+————————+——————+
| Id | User | Host | db | Command | Time | State | Info |
+——-+—————–+———–+——+———+——–+————————+——————+
| 4 | event_scheduler | localhost | NULL | Daemon | 508904 | Waiting on empty queue | NULL |
| 99327 | root | localhost | NULL | Sleep | 6386 | | NULL |
| 99777 | root | localhost | NULL | Sleep | 5515 | | NULL |
| 99842 | root | localhost | NULL | Query | 0 | starting | 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
to
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’;
18
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)
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 |
+————————-+———–+
| 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
used free shared buff/cache available
Mem: 3693
1150
131
195
2411
2091
Swap: 0 0
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
SHOW GLOBAL VARIABLES LIKE “innodb_buffer_pool_chunk_size”;
+——————————-+———–+
| Variable_name | Value |
+——————————-+———–+
| innodb_buffer_pool_chunk_size | 134217728 |
+——————————-+———–+
1 row in set (0.01 sec)
+——————————-+———–+
| Variable_name | Value |
+——————————-+———–+
| innodb_buffer_pool_chunk_size | 134217728 |
+——————————-+———–+
1 row in set (0.01 sec)
SHOW GLOBAL VARIABLES LIKE “innodb_buffer_pool_instances”;
+——————————+——-+
| Variable_name | Value |
+——————————+——-+
| innodb_buffer_pool_instances | 1 |
+——————————+——-+
1 row in set (0.00 sec)
+——————————+——-+
| Variable_name | Value |
+——————————+——-+
| innodb_buffer_pool_instances | 1 |
+——————————+——-+
1 row in set (0.00 sec)
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
query_cache_size = 0
ON OFF ON_DEMAND
Query Optimization
Slow query log
login
mysql -u root -p
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.
