This command closes all open tables and locks all tables for all databases with a global read lock
FLUSH TABLES or RELOAD privilege is required for this operation.
To release the lock, use UNLOCK TABLES. This command implicitly commits any active transaction only if any tables currently have been locked with LOCK TABLES.
Inserting rows into the log tables is not prevented with FLUSH TABLES WITH READ LOCK.
Cheers!
The amount of resources that can be used can be controlled from the operating system perspective. Each user has limits that are set, but for that particular user, the limits are applied individually to each of its processes.
Limits can either be hard or soft. Only the root user can set the Hardlimits. Other users can set Soft limits, but it cannot be more than the hard limit.
The default value of ulimit open files limit is 1024. This is very low for a typical web server environment that hosts many have database-driven sites.
MySQL/MariaDB also uses this setting. The open_files_limit is set by MySQL/MariaDB to the system’s ulimit. Default is 1024.
NOTE: MySQL/MariaDB can only set its open_files_limit lower than what is specified under ulimit ‘open files’. It cannot be set higher than that.
Examine Current Limits
To inspect current limits
ulimit -a # -a will show all current limits including hard, soft, open files, etc.
To inspect the current hard and soft limits.
# Hard Limits ulimit -Ha # Soft Limits ulimit -Sa # H for hard limits, or S for soft limits.
To check current open file limits.
ulimit -n # –n for number of open files limits
Set ‘open files’ Limit Persistently
Open /etc/security/limits.conf using the text editor of your choice, and add the following lines, then save it.
* soft nofile 102400 * hard nofile 102400 * soft nproc 10240 * hard nproc 10240
Edit the file /etc/security/limits.d/90-nproc.conf using the text editor of your choice, and add the following lines, then save it.
* soft nofile 1024000 * hard nofile 1024000 * soft nproc 10240 * hard nproc 10240 root soft nproc unlimited
Set open_files_limit in my.cnf (MySQL)
Open and edit /etc/my.cnf
Insert the lines below under [mysql], then save it.
[mysqld] open_files_limit = 102400
Run the command below to see if there are any .conf files being used by MySQL that overrides the values for open limits.
systemctl status mysqld
Below is something you will see after running the command above.
/etc/systemd/system/mariadb.service.d └─limits.conf
This means that there is a /etc/systemd/system/mariadb.service.d/limts.conf file, whicv is loaded with MySQL. Now, edit that file as well.
[Service] LimitNOFILE=102400
Execute the command below for the changes to take effect.
systemctl daemon-reload && /scripts/restartsrv_mysqlPerform Server Reboot
Run the command below in MySQL to see the value of open_files_limit.
SHOW VARIABLES LIKE 'open_files_limit';
Output:
+------------------+--------+ | Variable_name | Value | +------------------+--------+ | open_files_limit | 102400 | +------------------+--------+ 1 row in set (0.00 sec)
Cheers!
