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
Slow queries
-
store mysql server on different disk/ server
4 disable swappiness
5.MySQL Max Connections
6.thread_cache_size
-
MySQL idle Connections
-
Right MySQL Filesystem
-
MySQL max_allowed_packet
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)
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
| Variable_name | Value |
+————————-+———–+
| innodb_buffer_pool_size | 134217728 |
+————————-+———–+
free -m
used free shared buff/cache available
Mem: 3693
Swap: 0 0
actually 2091 mb avilable there,
-
Innodb_buffer_pool_pages_total Total number of pages in the buffer pool
-
Buffer pool utilization >> Ratio of used to total pages in the buffer pool
-
innodb_buffer_pool_read_requests >>>Requests made to the buffer pool
-
Innodb_buffer_pool_reads >> Requests the buffer pool could not fulfill
+——————————-+———–+
| Variable_name | Value |
+——————————-+———–+
| innodb_buffer_pool_chunk_size | 134217728 |
+——————————-+———–+
1 row in set (0.01 sec)
+——————————+——-+
| Variable_name | Value |
+——————————+——-+
| innodb_buffer_pool_instances | 1 |
+——————————+——-+
1 row in set (0.00 sec)
if you got an error says ‘MySQL Error, Too many connections…’
query_cache_size = 0
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.