Learn about Centmin Mod LEMP Stack today
Become a Member

MariaDB Optimizing MySQL (MariaDB)

Discussion in 'Nginx, PHP-FPM & MariaDB MySQL' started by Oxide, Jun 26, 2019.

  1. Oxide

    Oxide Active Member

    516
    29
    28
    Mar 19, 2015
    Ratings:
    +54
    Local Time:
    5:59 AM
    Hi,

    Are there any preset my.cnf templates I could use?
    Hi,

    Are there any preset my.cnf templates I could use?

    I have 48GB of DDR4 Ram, and I have been experiencing some issues with my database crashing under high load, we have lots of CPU power as well.

    I remember a couple of years ago there was like my.cnf for 8, 16, 32 & 64GB Ram. Are there any updated ones for that?
    I have 48GB of DDR4 Ram, and I have been experiencing some issues with my database crashing under high load, we have lots of CPU power as well.

    I remember a couple of years ago there was like my.cnf for 8, 16, 32 & 64GB Ram. Are there any updated ones for that?

    Here is the config we have currently: http://octolus.net/ahaehaeh/my.cnf
    MySQL Tuner Result: [Bash] >> MySQLTuner 1.7.1 - Major Hayden <[email protected]> >> Bug reports, feature - Pastebin.com

    (After upgrading to latest MariaDB etc, we had a issue where it used 100% CPU of the server and had to reinstall MySQL Completely, and go back to older version)

    Would appreciate if someone could point out things I could do to optimize.

    I run some big databases, one is a Geo IP Service loaded from MySQL (API).
     
  2. eva2000

    eva2000 Administrator Staff Member

    40,634
    9,023
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +13,891
    Local Time:
    5:59 AM
    Nginx 1.15.x
    MariaDB 5.5/10.x
    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.
     
  3. negative

    negative Active Member

    297
    30
    28
    Apr 11, 2015
    Ratings:
    +66
    Local Time:
    10:59 PM
    1.9.10
    10.1.11
    @eva2000 BTW I have a question about your comment.
    If we change the server hardware after centminmod installed (for example changing the hdd, increasing the ram) Can we run that optimizations manually again with like a custom script etc?

    We would like to see a script in cmdir/tools though :)
     
    style="display:inline-block;min-width:400px;max-width:970px;width:95%;height:90px" data-ad-client="ca-pub-6669518204467592" data-ad-slot="4024536743" data-ad-format="auto">
  4. eva2000

    eva2000 Administrator Staff Member

    40,634
    9,023
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +13,891
    Local Time:
    5:59 AM
    Nginx 1.15.x
    MariaDB 5.5/10.x
    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
    
     
    • Informative Informative x 1
  5. steph40

    steph40 Premium Member Premium Member

    35
    8
    8
    Jan 28, 2019
    Ratings:
    +16
    Local Time:
    3:59 PM
    1.1.5
    mariadb 10
    You could run the setio tool:
    Code:
    cd /usr/local/src/centminmod/tools
    ./setio.sh
    
    As far as I know it finds best settings for database.
    PS not a pro here.
     
    • Informative Informative x 2
  6. steph40

    steph40 Premium Member Premium Member

    35
    8
    8
    Jan 28, 2019
    Ratings:
    +16
    Local Time:
    3:59 PM
    1.1.5
    mariadb 10
    Dam you beat me
     
    • Funny Funny x 1