• 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


How measure & adjust mysql buffer pool size by pages, read requests etc

SET GLOBAL innodb_buffer_pool_size=bytes;//8G 512M 256M
innodb_buffer_pool_size adjusted to multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances

innodb_buffer_pool_reads/innodb_buffer_pool_read_requests*100= 0.001

Innodb_buffer_pool_read_requests are the number of requests to read a row from the buffer pool and
Innodb_buffer_pool_reads is the number of times Innodb has to perform read data from disk >> should be low

read efficiency:
write efficiency:

Adjusting buffer pool size according usage

 

after you set you can measure how much buffer pool utilization by this possible  way its one of other things.

buffer pool pages /free vs

(Innodb_buffer_pool_pages_total – Innodb_buffer_pool_pages_free)
—————————————————————-
Innodb_buffer_pool_pages_total

Previously  ISET Buffer pool size to 1GB

i am calculating it

buffer pool Pages

SHOW GLOBAL STATUS LIKE ‘Innodb_buffer_pool_pages%’;
(Innodb_buffer_pool_pages_total – Innodb_buffer_pool_pages_free)
—————————————————————-
                   Innodb_buffer_pool_pages_total
Innodb_buffer_pool_pages_total   | 8192   |
+———————————-+——
| Innodb_buffer_pool_pages_free    | 1020
8192-1020/8192= 0.875488281 *100= 87.5%
SHOW GLOBAL STATUS LIKE ‘Innodb_buffer_pool_pages%’;
+———————————-+——-+
| Variable_name                    | Value |
+———————————-+——-+
| Innodb_buffer_pool_pages_data    | 31484 |
| Innodb_buffer_pool_pages_dirty   | 0     |
| Innodb_buffer_pool_pages_flushed | 20608 |
| Innodb_buffer_pool_pages_free    | 33128 |
| Innodb_buffer_pool_pages_misc    | 924   |
| Innodb_buffer_pool_pages_total   | 65536 |
(65536-33128)/65536*100=49.4%  (1GB i set maybe i can decrease to 512MB)
2nd time calculation
free -m
total used free shared buff/cache available
Mem: 3693 1726 208 134 1758 1560
after reducing bp size
free -m
total used free shared buff/cache available
Mem: 3693 1200 584 135 1908 2091
SHOW GLOBAL STATUS LIKE ‘Innodb_buffer_pool_pages%’;
+———————————-+——–+
| Variable_name | Value |
+———————————-+——–+
| Innodb_buffer_pool_pages_data | 36099 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 702960 |
| Innodb_buffer_pool_pages_free | 28445 |
| Innodb_buffer_pool_pages_misc | 992 |
| Innodb_buffer_pool_pages_total | 65536 |
+———————————-+——–+
Total – free /toal
(65536-28445)/65536*100=56.6 %
anyway i decreased to 512 MB
SHOW GLOBAL STATUS LIKE ‘Innodb_buffer_pool_pages%’;
+———————————-+——-+
| Variable_name | Value |
+———————————-+——-+
| Innodb_buffer_pool_pages_data | 30080 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 180 |
| Innodb_buffer_pool_pages_free | 1834 |
| Innodb_buffer_pool_pages_misc | 854 |
| Innodb_buffer_pool_pages_total | 32768 |
+———————————-+——-+
and restarted
service mysql  restart
determine the actual size of your buffer pool. For instance, this simple formula gives us the total size of buffer pool in bytes:
Innodb_buffer_pool_pages_total * innodb_page_size
The innodb_page_size can be retrieved using a SHOW VARIABLES query:
SHOW VARIABLES LIKE “innodb_page_size”
SHOW VARIABLES LIKE “innodb_page_size”;
innodb_page_size | 16384 |
16384
Innodb_buffer_pool_pages_total * innodb_page_size
65536*16384= (1073741824 bytes equal to 1GB)
innodb_log_file_size = 1G
innodb_log_buffer_size = 256M
innodb_io_capacity = 1000

 

 

buffer pool caches
innodb_read_io_threads=4
innodb_write_io_threads=8  #To stress the double write buffer
innodb_buffer_pool_size=20G
innodb_buffer_pool_load_at_startup=ON
innodb_log_file_size = 32M #Small log files, more page flush
innodb_log_files_in_group=2
innodb_file_per_table=1
innodb_log_buffer_size=8M
innodb_flush_method=O_DIRECT
innodb_flush_log_at_trx_commit=0
skip-innodb_doublewrite  #commented or not depending on test

Allocating mysql memory

MySQL buffers, temporary tables, connection pools, and replication related buffers ,log file size,+ buffer pool
% of total RAM’ for OS needs
80% for dedicated mysql server
30-50% in shared server by free /available memory  free -m
cat /proc/meminfo
SHOW GLOBAL VARIABLES LIKE “innodb_buffer_pool_size”;
innodb_buffer_pool_size | 134217728 // Bytes
134MB   // it should be
nnodb_buffer_pool_size = chunk size *instances (automatically)
pool size upto 80-0% for dedicated but for wordpress  below 50% will be better (moniotor later)
mycase 3.7gb ram always 1-2gb free /cached by linux.
SET GLOBAL innodb_buffer_pool_size=1073741824;
increased to 1gb
doc
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_buffer_pool_size
innodb_buffer_pool_size=8G
always be equal to or a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances
SHOW GLOBAL VARIABLES LIKE “innodb_buffer_pool_instances”;
instance 8
size 1gb
mycase 1  & chunksize 134MB
134*8= 1072MB (Bytes actually 1024 in decimal but took 1072 KBytes)
SHOW GLOBAL VARIABLES LIKE “innodb_buffer_pool_chunk_size”;
SIZE
:  it should be 2x for chunksize ( 134MB defaults mysql 8)* instances

check innodb  buffer pool usage efficiency

mysql * default config  134 Mb & 1 instances.
innodb reads from disk vs memory
| Innodb_buffer_pool_read_requests | 4355260687 |
| Innodb_buffer_pool_reads | 35780
innodb_buffer_pool_reads/innodb_buffer_pool_read_requests*100= 0.001
35780/4355260687*100=0.000821
SHOW GLOBAL STATUS LIKE ‘Innodb_buffer_pool%’;
SHOW GLOBAL STATUS LIKE ‘innodb_buffer_pool_rea%’;
for (high performance) Total_Instances:= Total_IO_Threads=(innodb_read_io_threads + innodb_write_io_threads)
mycase 8
innodb_write_io_threads=4
innodb_read_io_threads =4  // default however we can increase based on ready or write heavy to know this
but 4+4=8 (should be equal to CPU cores )
than 64 × innodb_read_io_threads pending read requests in SHOW ENGINE INNODB STATUS
mysql> SHOW GLOBAL STATUS LIKE ‘Com_select’;
+—————+——-+
| Variable_name | Value |
+—————+——-+
| Com_select    | 36811 |
+—————+——-+
1 row in set (0.00 sec)
SHOW GLOBAL STATUS WHERE Variable_name IN (‘Com_insert’, ‘Com_update’, ‘Com_replace’, ‘Com_delete’);
+—————+——-+
| Variable_name | Value |
+—————+——-+
| Com_delete    | 453   |
| Com_insert    | 5242  |
| Com_replace   | 0     |
| Com_update    | 631   |
5242+453+631=6326  vs 36811
wordpress blog  read requests 6 times bigger than then
innodb_write_io_threads=4
innodb_read_io_threads =8
 (I may change threds to 4 to 8 4 to 2
SHOW VARIABLES LIKE ‘innodb_read_io_threads’;
SHOW Global VARIABLES LIKE ‘innodb_read_io_threads’;
SET GLOBAL innodb_read_io_threads=8;
If you have a high end I/O subsystem and you see more than 64 × innodb_read_io_threads pending read requests in SHOW ENGINE INNODB STATUS output, you might improve performance by increasing the value of innodb_read_io_threads.
SHOW GLOBAL STATUS LIKE ‘Innodb_buffer_pool%’;
ptimizing InnoDB Disk I/O
top command to disk uage in linux
innodb_log_file_size
innodb_io_capacity 200 defaults
based on SSD caluclated system io
SHOW GLOBAL STATUS
SHOW GLOBAL STATUS; |\
read request Disk vs  / memory

Innodb_buffer_pool_reads  vs Innodb_buffer_pool_read_requests

monitoring Buffer pool efficiency reads & pages
Innodb_buffer_pool_read_requests are the number of requests to read a row from the buffer pool and Innodb_buffer_pool_reads is the number of times Innodb has to perform read data from disk  >> should be low
innodb_buffer_pool_reads/innodb_buffer_pool_read_requests*100= 0.001
Innodb_buffer_pool_read_ahead         | 68743954                                         |
| Innodb_buffer_pool_read_ahead_evicted | 236110                                           |
| Innodb_buffer_pool_read_requests      | 5030235492                                       |
| Innodb_buffer_pool_reads              | 8375112
8375112/5030235492*100= 0.166495426
6916/28835148*100=   0.0239846177
innodb_buffer_pool_reads/innodb_buffer_pool_read_requests*100= 0.001
SHOW GLOBAL STATUS LIKE ‘innodb_buffer_pool_rea%’;
+—————————————+———-+
| Variable_name                         | Value    |
+—————————————+———-+
| Innodb_buffer_pool_read_ahead_rnd     | 0        |
| Innodb_buffer_pool_read_ahead         | 23083    |
| Innodb_buffer_pool_read_ahead_evicted | 0        |
| Innodb_buffer_pool_read_requests      | 47049712 |
| Innodb_buffer_pool_reads              | 7727     |
+—————————————+———-+
7727/47049712*100=0.016
                                  |
SHOW ENGINE INNODB STATUS\G
Total large memory allocated 1099431936
Dictionary memory allocated 3427245
—BUFFER POOL 7
Buffer pool size   65536
Free buffers       33763
Database pages     30856
Old database pages 11546
Buffer pool hit rate 1000 / 1000
young 5/8 vs old pages 3/8
https://dev.mysql.com/doc/refman/5.7/en/innodb-standard-monitor.html
set global thread_cache_size = 16;
SET GLOBAL innodb_buffer_pool_size =4026531840;

InnoDB Write buffer efficiency”?

* Innodb_pages_written / Innodb_buffer_pool_write_requests — Write requests that had to hit disk — <15% is ‘good’
* Innodb_buffer_pool_wait_free / Innodb_buffer_pool_write_requests — Should not have to wait to get into the buffer pool. — <1% is ‘good’
* Innodb_buffer_pool_pages_flushed / Uptime — Writes actually going to disk
* Innodb_buffer_pool_pages_flushed / max(Questions, Queries) — Buffer pool writes per query
SHOW GLOBAL STATUS LIKE ‘innodb_buffer_pool_wri%’;
+———————————–+——–+
| Variable_name                     | Value  |
+———————————–+——–+
| Innodb_buffer_pool_write_requests | 168685 |
SHOW GLOBAL STATUS LIKE ‘Innodb_pages_written%’;
Innodb_pages_written | 23182
23182/168685*100= 13  (<15%)
SHOW GLOBAL STATUS LIKE ‘Innodb_buffer_pool_wait_free’;
Innodb_buffer_pool_wait_free  0
SHOW GLOBAL STATUS LIKE ‘Innodb_buffer_pool_wait_free’;
0/168685= 0% (<1%)
show engine innodb status\G
 Buffer pool hit rate 900 / 1000

innodb_log_file_size

default 128MB

The increase means that log flushes are less frequent

 

Related topics:

  1. MySQL innodb memory allocation & usage calculation
  2. MySQL query cache vs redis vs memcached buffer pool database cache
  3. mysql configuration file location linux , windows , mac
  4. mysql max connections limit check increase how to decide thread cache size
  5. improve mysql performance wordpress my.cnf file configuration

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