• Home
  • WordPress
  • web Hosting
    • Free hosting
    • Cheap Hosting
    • comparison cloud , vps, shared, wordpress
    • managed wordpress hosting
    • managed cloud hosting
  • page Speed
  • Deals
  • Services
  • About

RAaz Kumar .com

wordpress tutorials seo hosting etc


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’;
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.
  1. 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);
  1. 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”
  1. Right MySQL Filesystem
  2. 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.
  1. Innodb_buffer_pool_pages_total Total number of pages in the buffer pool
  2. Buffer pool utilization >> Ratio of used to total pages in the buffer pool
  3. innodb_buffer_pool_read_requests >>>Requests made to the buffer pool
  4. 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)
SHOW GLOBAL VARIABLES LIKE “innodb_buffer_pool_instances”;
+——————————+——-+
| 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
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.

Related topics:

  1. mysql configuration file location linux , windows , mac
  2. mysql max connections limit check increase how to decide thread cache size
  3. mysql open_files_limit increase to raise open file cache table definition cache
  4. MySQL innodb memory allocation & usage calculation
  5. How measure & adjust mysql buffer pool size by pages, read requests etc

tutorials

  • Vivek Bindra Videos Transcription (business strategy)
  • git commands
  • new relic php agent install in 3 steps
  • new relic mysql install integration - 2 ways fix problems
  • new relic installation linux (infrastructure agent , php, mysql , nginx)
  • xampp tutorials 2021 installation errors fix wordpress phpmyadmin mysql apache
  • Redis performance metrics & tuning for nginx apache ubuntu & debian
  • Devops course Syllabus topics PDF AWS, Azure, cisco, redhat
  • CCNA Syllabus pdf (CCNA / CCNP vs devops vs mcsa /MCSE)
  • how to create a website free of cost on google
  • what is vpn vs proxy vs tor, http vs https, http2, tcp vs udp, kali linux sql source code injection
  • nginx server tutorials (installation, configuration, performance tuning, security)
  • MySQL Tutorial (create,connect database, update tables, export etc) Documentation & TIPS
    • mysql 8 installation on ubuntu 20.4
    • mysql auto backup ubuntu 22.04 using automysqlbackup
    • mysqltuner installation suggestions
    • mysql memory limit setting increase or decrease allocation
    • mysql open_files_limit increase to raise open file cache table definition cache
    • mysql restart ubuntu status start stop in linux windows reload
    • monitoring mysql with new relic
    • mysql access denied for user 'root'@'localhost' (using password yes) no Error 1045
    • mysql slow query log enable disable set query time ideal mysql long query time
    • mysql user creation, password , grant permissions user management guide
    • mysql root password change reset update set A-Z info
    • mysql configuration file location linux , windows , mac
    • mysqldump import /export mysql database command line, phpmyadmin, Cpanel, mysql workbench, xamp
    • MySQL query cache vs redis vs memcached buffer pool database cache
    • MySQL innodb memory allocation & usage calculation
    • mysql max connections limit check increase how to decide thread cache size
    • Innodb vs myisam (table engines row lock vs table lock)
    • mysql errors
    • mysql workbench tutorials (Sql Development , modelling, server admin export & import)
    • How measure & adjust mysql buffer pool size by pages, read requests etc
    • improve mysql performance wordpress my.cnf file configuration
    • phpmyadmin install / configure on nginx ubuntu 20.04 apache2 debian 10
    • mysql commands
  • AUdio Editing Background Noise removal (Audacity, Adobe Premiere Addition, Camtasia Filmora Windows Obs)
  • Android Studio tutorials syllabus Topics Course details #AndroidApplicationDevelopment
  • [INTRO] Ethical hacking / cyber Security / Penetration testing Tutorial -{updates frequently}
  • redis install ubuntu 20.04 with wordpress php redis mysql configuration
  • ubuntu tutorials installation download issues etc
  • Php tutorials
  • HTML & CSS Tutorials
  • Core Java Tutorial Free online
  • Linux sysadmin tutorials linux system administrator
  • apache server tutorial (install,config, performance tuning, security, error handling)

 

 

wordpress Pagespeed optimization

Digitalocean free $100 Credit

Cloudways Review pricing promo codes

Siteground cpu limits Pricing

Shared Hosting

Managed wordpress Hosting

Managed Hosting Cloud server

VPS Hosting

Cloud Hosting – Unmanaged

Google cloud Pricing

Aws Pricing

Azure pricing

nginx server tutorials

apache server tutorials

linux sysadmin tutorials

mysql Commands list pdf

LEMP Install Ubuntu

Mysql Performance Tuning

Nginx Performance tuning

Linux Performance tuning

Php -fpm performance tuning

Redis Performance tuning

linux server security

nginx security best practices

wordpress security plugins

 

 

wordpress

 

Top 5  WP Google Analytics Plugins

WP Backup Plugins

Wp Comment Plugins

Top wordpress Security Plugins

WP Seo Plugins

WP Caching Plugins

Best Adsense Plugins for WordPress

Wp social Sharing Plugins

autoshare social media plugins

WP speed Optimization Plugins

Speedup WordPress google Score

More Wp tuts

Server Admin Cloud

 

Installing Nginx LEMP On ubuntu

Installing apache Lamp ubuntu

nginx fastcgi cache enable

php – fpm install  & Configuration

Opcache install & Configure

php -fpm pool manager explained

Mysql Install & Configuration

Redis Object cache install & configure

 

Nginx as Reverse Proxy and Load balancer

Load Balance / auto scaling in google cloud

Linux Commands PDF

Mysql Commands Pdf

Letsencrypt tutorial

mysqldump export & import 

Pagespeed Module install & configure

nginx.conf best file

mysql.conf best file

upgrade ubuntu

© 2023 - All Rights Reserved Disclaimer & Privacy Policy