• 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 memory allocation usage buffers and cache find out problem

subject: To identify which mysql process / setting using high memory usage.

mysql high memory usage things to check

Every connection = Apporx 100MB memory, >> check active connections(hint CPU usage also raise)

Query cache: disbaled from mysql 8 no worries.

Innodb Memory: it doesn’t go above limit specified

key_buffer_size: if you are not using MYISAM as storage engineer ignore it.

mysql open limit files: deafult 1000 but my case 30000 similar to linux inode limit

mysql open table cache: how many tables in memory 

mysql temporary tables memory limit: max_heap_table_size 

mysql is multi threaded so htop command shows same percentage for all all child procesess or threads.

Identify: problems with mysqltuner

mysql table cache >> look at this 1st:

Parameter MySQL Default SIZE MB
  key_buffer_size (myisam)
+ query_cache_size
+ tmp_table_size
+ innodb_buffer_pool_size
+ innodb_additional_mem_pool_size
+ innodb_log_buffer_size
+ max_connections
×
  sort_buffer_size
+ read_buffer_size
+ read_rnd_buffer_size
+ join_buffer_size
+ thread_stack
+ binlog_cache_size
  • Global Buffer:

    key_buffer_size
    innodb_buffer_pool_size
    innodb_log_buffer_size
    innodb_additional_mem_pool_size
    net_buffer_size
    
    
  • Thread Buffer:

    sort_buffer_size
    myisam_sort_buffer_size
    read_buffer_size 
    join_buffer_size
    read_rnd_buffer_size
    thread_stack
    

 

Share
mysql> SHOW VARIABLES LIKE '%buffer%';
+-------------------------------------+----------------+
| Variable_name                       | Value          |
+-------------------------------------+----------------+
| bulk_insert_buffer_size             | 8388608        |
| innodb_buffer_pool_chunk_size       | 55574528       |
| innodb_buffer_pool_dump_at_shutdown | ON             |
| innodb_buffer_pool_dump_now         | OFF            |
| innodb_buffer_pool_dump_pct         | 25             |
| innodb_buffer_pool_filename         | ib_buffer_pool |
| innodb_buffer_pool_in_core_file     | ON             |
| innodb_buffer_pool_instances        | 1              |
| innodb_buffer_pool_load_abort       | OFF            |
| innodb_buffer_pool_load_at_startup  | ON             |
| innodb_buffer_pool_load_now         | OFF            |
| innodb_buffer_pool_size             | 55574528       |
| innodb_change_buffer_max_size       | 25             |
| innodb_change_buffering             | all            |
| innodb_log_buffer_size              | 16777216       |
| innodb_sort_buffer_size             | 1048576        |
| join_buffer_size                    | 262144         |
| key_buffer_size                     | 8388608        |
| myisam_sort_buffer_size             | 8388608        |
| net_buffer_length                   | 16384          |
| preload_buffer_size                 | 32768          |
| read_buffer_size                    | 131072         |
| read_rnd_buffer_size                | 262144         |
| sort_buffer_size                    | 262144         |
| sql_buffer_result                   | OFF            |
+-------------------------------------+----------------+
25 rows in set (0.02 sec)

I

innodb buffer pool

mysql global buffers + thread buffers(connection level)

mysql max connections formula

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

key_buffer myisam

not more than 25% ram when using myisam engine.

thread_cache_size

new connection being made needs a new thread created for it. When the connections disengage the thread is destroyed.

majority of connections can be made on cached threads.

default value:

 

mysql max_connections memory usage

128 concurrent connections

table_cache

You might start with a value 2x of  max_connections or 2x the total number of tables and then tune from there.

tables opened from disk cached in memory

mysql file descriptor limit should greater than tables limit.

 

 

Monitoring MySQL Memory Usage

mysql> SELECT * FROM performance_schema.setup_instruments WHERE NAME LIKE ‘%memory%’;

retuns 400+ rows

SELECT * FROM performance_schema.setup_instruments
WHERE NAME LIKE ‘%memory/innodb%’;

reruned 200+ rows

*** MySQL’s maximum memory usage is dangerously high ***

innodb_buffer_pool_size (>= 624.2M) if possible.
innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.

check exsting innodb log file size and adjust accordigly.

SHOW VARIABLES LIKE ‘innodb_buffer_pool_size’;
+————————-+———–+
| Variable_name | Value |
+————————-+———–+
| innodb_buffer_pool_size | 134217728 |  128MB
+————————-+———–+
1 row in set (0.01 sec)

Mysql memory usage in linux by htop command

mysql memory usage by htop command linux

 

3278M = 3.3GB Memory consuming now as per Htop.

watch mysql processlist

HOW FULL PROCESSLIST;

show mysql process list command line

 

Mysql and php fpm connection timeout

mysql connection: 3600 seconds or 1 hour.

max_execution_time  300s or 5 mins

Only 1 process running.

To check Active mysql active connections

run mysql connection commands to know current connections

show global status like ‘%conn%’;

Variable_name | Value |
+——————————————————-+———————+
| Aborted_connects | 1 |
| Connection_errors_accept | 0 |
| Connection_errors_internal | 0 |
| Connection_errors_max_connections | 0 |
| Connection_errors_peer_address | 0 |
| Connection_errors_select | 0 |
| Connection_errors_tcpwrap | 0 |
| Connections | 158913 |
| Locked_connects | 0 |
| Max_used_connections | 43 |
| Max_used_connections_time | 2021-08-06 15:31:22 |
| Mysqlx_connection_accept_errors | 0 |
| Mysqlx_connection_errors | 5 |
| Mysqlx_connections_accepted | 13 |
| Mysqlx_connections_closed | 13 |
| Mysqlx_connections_rejected | 0 |
| Performance_schema_session_connect_attrs_longest_seen | 116 |
| Performance_schema_session_connect_attrs_lost | 0 |
| Ssl_client_connects | 0 |
| Ssl_connect_renegotiates | 0 |
| Ssl_finished_connects | 0 |
| Threads_connected | 1 |
+——————————————————-+———————+
22 rows in set (0.03 sec)

innodb_buffer_pool_size

512MB

mysql> SHOW VARIABLES LIKE ‘innodb_buffer_pool_size’;
+————————-+———–+
| Variable_name | Value |
+————————-+———–+
| innodb_buffer_pool_size | 536870912 |
+————————-+———–+

 

SHOW VARIABLES LIKE ‘key_buffer_size’;

mysql> SHOW VARIABLES LIKE ‘key_buffer_size’;
+—————–+———+
| Variable_name | Value |
+—————–+———+
| key_buffer_size | 8388608 | 8MB
+—————–+———+
1 row in set (0.00 sec)

SHOW VARIABLES LIKE ‘max_allowed_packet’;

+——————–+———-+
| Variable_name | Value |
+——————–+———-+
| max_allowed_packet | 67108864 | 64mb
+——————–+———-+

tmp_table_size

SHOW VARIABLES LIKE ‘tmp_table_size’;

mysql> SHOW VARIABLES LIKE ‘tmp_table_size’;
+—————-+———–+
| Variable_name | Value |
+—————-+———–+
| tmp_table_size | 134217728 | 134MB
+—————-+———–+
1 row in set (0.00 sec)

 

Problem identified:  open table cache

Find current value of open_tables and opened_tables
mysql> show global status like ‘open%’;

Find out Table cache hit rate
Table cache hit rate = table_open_cache*100/Opened_tables

Calculate the tune value of table_open_cache and set it
Table_open_cache = total_tables*Threads_connected

Table_open_cache = total_tables*Threads_connected*.50

Along with table_open_cache you should also tune open_files_limit system variable.
In general it is 2x of table_open_cache.

open_files_limit= Table_open_cache*2

Mysql table cache 1101 total tables in mysql.  and allocated table cache and file descriptor limits to 30000

has to check out mysql open file limit file descripot increase 

suggestion by mysql tuner.

open table cache: 2x of  max_connections or 2x the total number of tables

mysql> SHOW VARIABLES LIKE ‘max_connections’;
+—————–+——-+
| Variable_name | Value |
+—————–+——-+
| max_connections | 151 |  //default  (100mb memory for each connection general maintained mysql)

table_open_cache | 10000

151*2=302

+—————–+——-+
1 row in set (0.00 sec)

mysql> show global variables like ‘%open%’;
+—————————-+——–+
| Variable_name | Value |
+—————————-+——–+
| have_openssl | YES |
| innodb_open_files | 58000 |
| mysqlx_port_open_timeout | 0 |
| open_files_limit | 300000 |
| table_open_cache | 58000 |
| table_open_cache_instances | 16 |
+—————————-+——–+
6 rows in set (0.01 sec)

mysql> show global status like ‘opened_tables’;
+—————+——-+
| Variable_name | Value |
+—————+——-+
| Opened_tables | 7095 |
+—————+——-+
1 row in set (0.01 sec)

mysql> show global status like ‘open_tables’;
+—————+——-+
| Variable_name | Value |
+—————+——-+
| Open_tables | 6693 |
+—————+——-+
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE ‘table_open_cache’;
+——————+——-+
| Variable_name | Value |
+——————+——-+
| table_open_cache | 10000 |
+——————+——-+
1 row in set (0.00 sec)

mysql open tables vs opened_tables  table_open_cache

number of opened tables is increasing rapidly, you should look at increasing the table_open_cache value

mysql> show global status like ‘%open%’;
+—————————-+———-+
| Variable_name | Value |
+—————————-+———-+
| Com_ha_open | 0 |
| Com_show_open_tables | 0 |
| Innodb_num_open_files | 456 |
| Mysqlx_cursor_open | 0 |
| Mysqlx_expect_open | 0 |
| Open_files | 370 |
| Open_streams | 0 |
| Open_table_definitions | 639 |
| Open_tables | 6909 |
| Opened_files | 370 |
| Opened_table_definitions | 709 |
| Opened_tables | 7077 |
| Slave_open_temp_tables | 0 |
| Table_open_cache_hits | 13056111 |
| Table_open_cache_misses | 7077 |
| Table_open_cache_overflows | 0 |
+—————————-+———-+
16 rows in set (0.00 sec)

The table_open_cache and max_connections system variables affect the maximum number of files the server keeps open

table cache hit rate: 7% (274 open / 3K opened) since the server start,

opned_tables = from disk

open tables = from memory.

a restart required to effect he stats.

Open_tables | 6694 |
| Opened_files | 243

 

max_allowed_packet
Total (per session)
innodb_buffer_pool_size
key_buffer_size
Total Memory Needed (for X connection)

 

To know the current running threads

mysql> SHOW PROCESSLIST;
+——-+—————–+———–+——+———+——–+————— ———+——————+
| Id | User | Host | db | Command | Time | State | Info |
+——-+—————–+———–+——+———+——–+————— ———+——————+
| 5 | event_scheduler | localhost | NULL | Daemon | 145300 | Waiting on emp ty queue | NULL |
| 73165 | root | localhost | NULL | Query | 0 | init | SHOW PROCESSLIST |
+——-+—————–+———–+——+———+——–+————— ———+——————+
2 rows in set (0.00 sec)

show global status like ‘%thread%’;

mysql> show global status like ‘%thread%’;
+——————————————+——-+
| Variable_name | Value |
+——————————————+——-+
| Delayed_insert_threads | 0 |
| Mysqlx_worker_threads | 2 |
| Mysqlx_worker_threads_active | 0 |
| Performance_schema_thread_classes_lost | 0 |
| Performance_schema_thread_instances_lost | 0 |
| Slow_launch_threads | 0 |
| Threads_cached | 49 |  //mysql thread cache
| Threads_connected | 1 |
| Threads_created | 84 |
| Threads_running | 2 |
+——————————————+——-+
10 rows in set (0.03 sec)

 

mysql> SHOW STATUS \G;

displays the name and values of variables that gives you information about the server status

Identify: problems with mysqltuner

  1.  ./mysqltuner.pl
    

*** MySQL’s maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
key_buffer_size=0
innodb_buffer_pool_size (>= 803.1M) if possible. (512mb)
innodb_log_file_size should be (=64M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.

 

key_buffer_size=0  (not using myisam as storage engine.)

16M is probably sufficient for any situation.

Buffer Key MyISAM set to 0, no MyISAM table detected

mysql> SHOW VARIABLES LIKE ‘innodb_buffer_pool_size’;
+————————-+———–+
| Variable_name | Value |
+————————-+———–+
| innodb_buffer_pool_size | 536870912 |  (512mb)

 

mysql> SHOW VARIABLES LIKE ‘innodb_log_file_size’;
+———————-+———–+
| Variable_name | Value |
+———————-+———–+
| innodb_log_file_size | 134217728 |  128mb
+———————-+———–+
1 row in set (0.00 sec)

more at mysql bufferpool size

table_definition_cache(900) > 1100 or -1 (autosizing if supported)

 

table_definition_cache

 

Default Value -1 (signifies autosizing; do not assign this literal value)
Minimum Value 400
Maximum Value 524288

SHOW VARIABLES LIKE ‘table_definition_cache’;

mysql> SHOW VARIABLES LIKE ‘table_definition_cache’;
+————————+——-+
| Variable_name | Value |
+————————+——-+
| table_definition_cache | 900 |
+————————+——-+
1 row in set (0.00 sec)

table_definition_cache setting acts as a soft limit for the number of table instances in the InnoDB data dictionary cache and the number file-per-table tablespaces that can be open at one time.

Related topics:

  1. MySQL innodb memory allocation & usage calculation
  2. mysql max connections limit check increase how to decide thread cache size
  3. mysql open_files_limit increase to raise open file cache
  4. improve mysql performance wordpress my.cnf file configuration
  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
    • mysqltuner installation suggestions
    • mysql memory allocation usage buffers and cache find out problem
    • mysql open_files_limit increase to raise open file 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

© 2022 - All Rights Reserved Disclaimer & Privacy Policy