• 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

MySQL innodb memory allocation & usage calculation

innodb buffer pool size:

mysql memory calculation
caches the data of tables & indexes in memory rather than disk so faster connection handling.
increased performance by reducing response time.
80% memory in dedicated,
30-40% in LEMP*(check mysql connections /se before do this other wise memory will be wasted. You can measure /view buffer pool cache usage).
larger than  database size (2x*)
1gb database ,
2core 8gb ram.
allocating 2gb good idea even 1gb instead of 128M default.
innodb_log_file_size = 256M # 25% of buffer pool size
innodb_buffer_pool_instances (=1)
innodb_buffer_pool_size = innodb_buffer_pool_chunk_size
                            * innodb_buffer_pool_instances
(It’s automatically calculated)
128M, set to 4G, then instances will increase.

Buffer pool utilization

Innodb_buffer_pool_pages_total Total number of pages in the buffer pool
(Innodb_buffer_pool_pages_total – Innodb_buffer_pool_pages_free)
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%
(only 30% ram used remaining wasted)
innodb_page_size equals to buffer pool size.
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
  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
mysql query cache retired try redis/memcached.
redis preferred.
Buffer pool reads / Read requests
Reds = from disk
Reads Request = from memory
innodb_buffer_pool_reads = 91661
innodb_buffer_pool_read_requests = 4029033624

Performance = 91661 / 4029033624 * 100

InnoDB Performance = 0.0022750120389663. 100% from innodb buffer pool.

SHOW GLOBAL STATUS LIKE ‘innodb_buffer_pool_reads’;

mysql> SHOW GLOBAL STATUS LIKE ‘innodb_buffer_pool_reads’;
+————————–+——-+
| Variable_name | Value |
+————————–+——-+
| Innodb_buffer_pool_reads | 9037 |
+————————–+——-+
1 row in set (0.01 sec)

SHOW GLOBAL STATUS LIKE ‘innodb_buffer_pool_read_requests’;

SHOW GLOBAL STATUS LIKE ‘innodb_buffer_pool_read_requests’;
+———————————-+———–+
| Variable_name | Value |
+———————————-+———–+
| Innodb_buffer_pool_read_requests | 267939981 |
+———————————-+———–

 

SHOW GLOBAL STATUS LIKE ‘Innodb_buffer_pool_pages%’;

mysql bufferpool utilization total vs free pages

SHOW GLOBAL STATUS LIKE ‘Innodb_buffer_pool_pages%’;
+———————————-+———+
| Variable_name | Value |
+———————————-+———+
| Innodb_buffer_pool_pages_data | 11172 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 1074057 |
| Innodb_buffer_pool_pages_free | 21363 |
| Innodb_buffer_pool_pages_misc | 233 |
| Innodb_buffer_pool_pages_total | 32768 |
+———————————-+———+
6 rows in set (0.01 sec)

512MB – only 50% utilized 256 MB.

How to check mysql bufferpool memory usage/ allocation?

Note: mysql buffer pool only applies to innodb engine, for MYISAM e key_buffer_size
you can check mysql.cnf in /etc/mysql/mysql.cinf or /etc/mysql/conf.d/mysql.cnf  (permanent settings)
command line settings automatically overdose if mysql server / service restarts.
first login to mysql server
mysql -u root -p
SHOW GLOBAL STATUS LIKE ‘Innodb_buffer_pool_pages%’;
SHOW VARIABLES LIKE ‘innodb_buffer_pool_size’;
+————————-+———–+
| Variable_name | Value |
+————————-+———–+
| innodb_buffer_pool_size | 536870912 | //512M
+————————-+———–+
1 row in set (0.12 sec)
temporary tables max_heap_table_size
MyISAM also allocates buffer for every concurrent threads
max_allowed_packet
mysql> SHOW ENGINE INNODB STATUS\G
SHOW ENGINE INNODB STATUS;
BUFFER POOL AND MEMORY
———————-
Total large memory allocated 549453824
Dictionary memory allocated 4098788
Buffer pool size 32768
Free buffers 21368
Database pages 11167
Old database pages 4102

mysql performance metrics

sort_buffer_size
read_buffer_size
read_rnd_buffer_size
join_buffer_size
max_heap_table_size
tmp_table_size
table_open_cache
table_open_cache_instances
table_definition_cache
max_allowed_packet
max_connections
thread_cache_size
query_cache_size //disabled
innodb_buffer_pool_size
innodb_buffer_pool_instances
innodb_log_file_size
innodb_log_buffer_size
innodb_flush_log_at_trx_commit
innodb_thread_concurrency 2x for cpu cores+disk
innodb_flush_method
innodb_file_per_table
innodb_stats_on_metadata
innodb_io_capacity
innodb_write_io_threads
innodb_adaptive_flushing
innodb_dedicated_server – automatic variables on dedidicated
myisam key_buffer_size
1% of RAM or 256 MiB because its now innodb.
slow_query_log
long_query_time
sync_binlog
Dump/Restore Buffer Pool

 

#
https://severalnines.com/database-blog/mysql-performance-cheat-sheet

 

Related topics:

  1. How measure & adjust mysql buffer pool size by pages, read requests etc
  2. MySQL query cache vs redis vs memcached buffer pool database cache
  3. improve mysql performance wordpress my.cnf file configuration
  4. mysql max connections how to decide
  5. Innodb vs myisam (table engines row lock vs table lock)

 

PUBG Mobile Tips & Tricks

free fire how to play (a-z guide) guns, tips & tricks etc

Windows 10 tips & tricks

Pagespeed optimization

Pc building tips & tricks

nginx server tutorials

apache server tutorials

linux sysadmin tutorials

mysql Commands list pdf

Android studio tutorials

Gaming/ streaming tips

Airtel dth channel list

 

tutorials

  • Android Studio tutorials syllabus Topics Course details #AndroidApplicationDevelopment
  • xampp tutorials 2021 installation errors fix wordpress phpmyadmin mysql apache
  • 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)
  • Vivek Bindra Videos Transcription (business strategy)
  • AUdio Editing Background Noise removal (Audacity, Adobe Premiere Addition, Camtasia Filmora Windows Obs)
  • MySQL Tutorial (create,connect database, update tables, export etc) Documentation & TIPS
    • mysqldump import /export mysql database command line, phpmyadmin, Cpanel, mysql workbench, xamp
    • mysql commands
    • phpmyadmin install / configure on nginx ubuntu 20.04 apache2 debian 10
    • improve mysql performance wordpress my.cnf file configuration
    • How measure & adjust mysql buffer pool size by pages, read requests etc
    • mysql workbench tutorials (Sql Development , modelling, server admin export & import)
    • mysql errors
    • Innodb vs myisam (table engines row lock vs table lock)
    • mysql max connections how to decide
    • MySQL innodb memory allocation & usage calculation
    • MySQL query cache vs redis vs memcached buffer pool database cache
    • mysql 8 installation on ubuntu 20.4
    • mysql configuration file location linux , windows , mac
  • [INTRO] Ethical hacking / cyber Security / Penetration testing Tutorial -{updates frequently}
  • 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

 

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

© 2021 - All Rights Reserved Disclaimer & Privacy Policy