MariaDB Optimizing MySQL (MariaDB)

Which versions did you upgrade from/to ? did you change any /etc/my.cnf settings before and after upgrade ?

Centmin Mod 123.09beta01 has base /etc/my.cnf templates at centminmod/centminmod but these are just base templates which get dynamically altered and adjusted at initial 123.09beta01 install time based on server's detected resources, memory available, disk I/O performance etc. So resulting /etc/my.cnf you use for your Centmin Mod 123.09beta01 server will be one optimal for your server resources. This is different from optimal for your web app's required usage which is what you are asking about.

Centmin Mod is provide as is, so short of scripted related bugs or issues, any further optimisation to the web stack components - nginx, php-fpm, mariadb mysql, csf firewall etc or web app specific configurations are left to the Centmin Mod user to deal with. So I do not provide any free support for such specific app usage optimisations.

However, Centmin Mod users are free to help each other out and ask questions or give answers on this community forum. My hopes are that this community forum evolves so that more veteran long time Centmin Mod users help new Centmin Mod users out

If running Centmin Mod 123.09beta01 or higher, you will have access to a cminfo top command which can also provide a summary of statistics - some of which are explained in info and linked articles below.

Centmin Mod is provided as is, so troubleshooting issues and optimisations is left to end user to do. However, there's many linux tools and scripts that can help you figure out what was causing the load issues and when.

If you're providing info on this forum, more info might be helpful

1. What version of Centmin Mod ? .08 stable or .09 beta ? If .09 beta when was it installed and when was last time you updated ?
2. What's your VPS/Server hardware specifications ? cpu type ? memory available ? disk space ?
3. Who's your web host ? Different hosts have different limits for server resource usage and some are more restrictive that others so it could just be their resource usage policy you tripped which may or may not be restrictive. If restrictive, then real solution would be finding a better web host. If not restrictive, then it's finding out what caused high cpu load.
4. If running Centmin Mod 123.09beta01 or higher, you will have access to a cminfo top command which can also provide a summary of statistics - some of which are explained in info and linked articles below.

Tools and commands you will want to read up on and learn for basic system admin tasks and troubleshooting.

• 20 Command Line Tools to Monitor Linux Performance
• 13 Linux Performance Monitoring Tools - Part 2
• 80 Linux Monitoring Tools for SysAdmins - Server Density Blog
• Amazing ! 25 Linux Performance Monitoring Tools
• 20 Linux System Monitoring Tools Every SysAdmin Should Know
• My mysqlmymonlite.sh script can help with that mysqlmymonlite.sh Addon | Centmin Mod Community README text file has instructions for setting up a cronjob to email you reports i.e. every 10/15min or every hour.

 

Not all but some can be done.

For PHP-FPM, you can recompile PHP via centmin.sh menu option 5 so auto redetects memory and server resources and manually re-run /usr/local/src/centminmod/tools/setio.sh as follows

Code (Text):

/usr/local/src/centminmod/tools/setio.sh set

which will recalculate MariaDB MySQL innodb I/O variable values to set

i.e.

Code (Text):

/usr/local/src/centminmod/tools/setio.sh set
Full Reads: 24975
Full Writes: 59362

set innodb_flush_neighbors = 0

innodb_io_capacity = 2900

+------------------------+-------+
/etc/my.cnf adjustment
+------------------------+-------+
existing value:
Variable_name   Value
innodb_io_capacity      2700
innodb_io_capacity_max  5400
new value:
Variable_name   Value
innodb_io_capacity      2900
innodb_io_capacity_max  5800

+------------------------+-------+
innodb io threads adjustment
+------------------------+-------+
existing value:
+------------------------+-------+
innodb_read_io_threads = 2
innodb_write_io_threads = 2
+------------------------+-------+
new value:
+------------------------+-------+
innodb_read_io_threads = 2
innodb_write_io_threads = 2
+------------------------+-------+

Restart MySQL server for io thread changes