• 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 open_files_limit increase to raise open file cache table definition cache

 

 

Problem: open table file cache hit ratio: 5%

4000/85K

 

Max opentables 4919

open fileslimit: 10000

 

at

cat /etc/security/limits.conf

nginx soft nofile 10000
nginx hard nofile 30000
www-data soft nofile 20000
www-data hard nofile 50000
mysql soft nofile 20000
mysql hard nofile 50000
root soft nproc 1000000
root hard nproc 100000
root soft nofile 100000
root hard nofile 100000

 

Open file limit : 10% of available memory. bytes

also change number of active process per mysql.

connections = 30000+ if

fs file max system ide limit

cat /proc/sys/fs/file-max
2097152  (2 lakhs

cat /etc/sysctl.conf

# Increase size of file handles and inode cache
fs.file-max = 2097152  // linux file descriptor limit

This file is used during the boot process

 

check existing files limit

 

ps aux | grep mysql
653859

 

 

Max open files 10000 10000

mysql> show global variables like ‘table_open_cache’;
+——————+——-+
| Variable_name    | Value |
+——————+——-+
| open_files_limit | 10000 |
+——————+——-+
1 row in set (0.00 sec)

after restart mysql

cat /proc/658658/limits
Max open files 30000 30000 files
check mycnf setting
nano /etc/mysql/my.cnf
table_open_cache  = 100000
open_files_limit = 50000
restart mysql
service mysql restart
ps aux | grep mysql
658893
cat /proc/659295/limits
Max open files 30000 30000 files

check mysql.service

cat /etc/systemd/system/multi-user.target.wants/mysql.service
LimitNOFILE = 30000
Max open files 300000 300000 files
nano /etc/systemd/system/multi-user.target.wants/mysql.service
root@localhost:~# systemctl daemon-reload
service mysql restart
cat /proc/659295/limits
Max open files 300000 300000 files
Note: mycnf was only 10000 but edited systemd as 3 lkhs.
systctl -p to reload sysctl
pam d to reload etc/security without reboot.

reload /etc/security/limits.conf

edit pam files add session required
use ulimit command set untill restart

before

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

after

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 |
able_open_cache | 58000 set in mycnf
Table_Cache should always – well mostly anyway – be significantly bigger than the total number of tables in the server.
beware the high memory usage unnecessary due to files limit

find the total number of tables in mysql server

Find out total tables of your databases
Run:
SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE=’BASE TABLE’;
mysql> SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE=’BASE TABLE’;
+———-+
| COUNT(*) |
+———-+
| 1010 |
+———-+
1 row in set (0.01 sec)
show global status like ‘%Threads_connected%’;

Table_open_cache = total_tables*Threads_connected

mysql> show global status like ‘%Threads_connected%’;
+——————-+——-+
| Variable_name | Value |
+——————-+——-+
| Threads_connected | 1 |
+——————-+——-+
1 row in set (0.00 sec)
open_files_limit= table_open_cache*2 = 2x
open_files_limit= table_open_cache*1 = x
1010
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 |
| Slow_launch_threads | 0 |
| Threads_cached | 3 |
| Threads_connected | 1 |
| Threads_created | 4 |
| Threads_running | 2 |
+——————————+——-+
8 rows in set (0.00 sec)
show global status like ‘%conn%’;
mysql> show global status like ‘%conn%’;
+——————————————————-+———————+
| Variable_name | Value |
+——————————————————-+———————+
| Aborted_connects | 0 |
| 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 | 216 |
| Locked_connects | 0 |
| Max_used_connections | 4 |
| Max_used_connections_time | 2021-07-30 03:07:36 |
| Mysqlx_connection_accept_errors | 0 |
| Mysqlx_connection_errors | 0 |
| Mysqlx_connections_accepted | 0 |
| Mysqlx_connections_closed | 0 |
| 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.00 sec)
show global variables like ‘%conn%’;
mysql> show global variables like ‘%conn%’;
+———————————————–+——————–+
| Variable_name | Value |
+———————————————–+——————–+
| character_set_connection | utf8mb4 |
| collation_connection | utf8mb4_0900_ai_ci |
| connect_timeout | 10 |
| disconnect_on_expired_password | ON |
| init_connect | |
| max_connect_errors | 100 |
| max_connections | 151 |
| max_user_connections | 0 |
| mysqlx_connect_timeout | 30 |
| mysqlx_max_connections | 100 |
| performance_schema_session_connect_attrs_size | 512 |
+———————————————–+——————–+
11 rows in set (0.01 sec)
show global variables like ‘%Thread%’;
thread_cache_size | 9 |
| thread_handling | one-thread-per-connection |
| thread_stack | 286720

show global variables like ‘table_definition_cache’;

mysql> show global variables like ‘table_definition_cache’;
+————————+——-+
| Variable_name | Value |
+————————+——-+
| table_definition_cache | 2000 |
+————————+——-+
1 row in set (0.00 sec)
table_definition_cache=currentvalue+table_open_cache/2
2000+1010/2= 2505

table_open_cache_instances:

default 1 it shoulbe bigger than one and upto cores.
show global variables like ‘table_open_cache_instances’;
mysql> show global variables like ‘table_open_cache_instances’;
+—————————-+——-+
| Variable_name | Value |
+—————————-+——-+
| table_open_cache_instances | 16 |
+—————————-+——-+
1 row in set (0.00 sec)
mysql tuning parameters thread cache size
Reducing mysql open file limit due to high memory usage
mysql> select @@open_files_limit;
+——————–+
| @@open_files_limit |
+——————–+
| 30000 |
+——————–+
1 row in set (0.00 sec)
cat /etc/systemd/system/multi-user.target.wants/mysql.service

[Install]
WantedBy=multi-user.target

[Service]
User=mysql
Group=mysql
Type=notify
PermissionsStartOnly=true
ExecStartPre=/usr/share/mysql-8.0/mysql-systemd-start pre
ExecStart=/usr/sbin/mysqld
TimeoutSec=0
LimitNOFILE = 30000
Restart=on-failure
RestartPreventExitStatus=1

Max open files 300000 300000 files
nano /etc/systemd/system/multi-user.target.wants/mysql.service
changed to 2000
root@localhost:~# systemctl daemon-reload
mysql> show global variables like ‘%open%’;
+—————————-+——-+
| Variable_name | Value |
+—————————-+——-+
| have_openssl | YES |
| innodb_open_files | 300 |
| mysqlx_port_open_timeout | 0 |
| open_files_limit | 2000 |
| table_open_cache | 100 |
| table_open_cache_instances | 16 |
+—————————-+——
mysql> show global variables like ‘table_definition_cache’;
+————————+——-+
| Variable_name | Value |
+————————+——-+
| table_definition_cache | 450 |
+————————+——-
mysql set global table_definition_cache=100;
SET GLOBAL table_definition_cache=100;
mysql> SET GLOBAL table_definition_cache=100;
Query OK, 0 rows affected, 1 warning (0.00 sec)

Related topics:

  1. increase open file limit linux File descriptor ft nginx , mysql, lemp
  2. mysql configuration file location linux , windows , mac
  3. mysql max connections limit check increase how to decide thread cache size
  4. mysql memory limit setting increase or decrease allocation
  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