Cloudron makes it easy to run web apps like WordPress, Nextcloud, GitLab on your server. Find out more or install now.


  • Categories
  • Recent
  • Tags
  • Popular
  • Bookmarks
Skins
  • Light
  • Cerulean
  • Cosmo
  • Flatly
  • Journal
  • Litera
  • Lumen
  • Lux
  • Materia
  • Minty
  • Morph
  • Pulse
  • Sandstone
  • Simplex
  • Sketchy
  • Spacelab
  • United
  • Yeti
  • Zephyr
  • Dark
  • Cyborg
  • Darkly
  • Quartz
  • Slate
  • Solar
  • Superhero
  • Vapor

  • Default (No Skin)
  • No Skin
Collapse

Cloudron Forum

Apps | Demo | Docs | Install

MySQL tuning with my.cnf settings optimisation

Scheduled Pinned Locked Moved Feature Requests
mysqladdonsperformance
11 Posts 5 Posters 488 Views
    • Oldest to Newest
    • Newest to Oldest
    • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • marcusquinnM Offline
    marcusquinnM Offline
    marcusquinn
    wrote on last edited by girish
    #1

    I did a bunch of research on this a while back for MySQL my.cnf settings best-practice for optimal RAM & CPU usage, and wrote up here sample settings with typical VPS configs for reference here:

    • https://gitlab.com/d2c.io/wordpress-scalable-lemp-single-server/-/blob/master/configs/my.cnf - Reference

    There's a few things in there I think could be automated based on knowing the VPS RAM & CPU count.

    And a few things that could be admin panel settings, like enabling SQL Profiling and accessing the slow query logs for review.

    TBH, not looked into what you already do but maybe some ideas in here to test. Guessing similar applies to other DBs but I only looked at MySQL & InnoDB as our primary environment.

    Further Reading

    • https://www.wpintense.com/2017/06/12/installing-and-configuring-fastest-possible-wordpress-stack-digital-ocean/
    • http://www.mysqlcalculator.com/
    • https://bugs.mysql.com/bug.php?id=82577
    • https://launchpad.net/mysql-tuning-primer
    • https://www.percona.com/blog/2014/01/28/10-mysql-performance-tuning-settings-after-installation/
    • https://www.percona.com/resources/technical-presentations/optimizing-mysql-configuration-percona-mysql-university-montevideo
    • https://www.wpintense.com/2016/07/26/enable-slow-query-log-and-identify-slow-queries-percona-db/

    We're not here for a long time - but we are here for a good time :)
    Jersey/UK
    Work & Ecommerce Advice: https://brandlight.org
    Personal & Software Tips: https://marcusquinn.com

    LonkleL rmdesR 2 Replies Last reply
    1
  • LonkleL Offline
    LonkleL Offline
    Lonkle
    replied to marcusquinn on last edited by Lonkle
    #2

    @marcusquinn Bookmarked this as a resource, thanks for collecting all of this!

    marcusquinnM 1 Reply Last reply
    1
  • marcusquinnM Offline
    marcusquinnM Offline
    marcusquinn
    replied to Lonkle on last edited by
    #3

    @Lonk Interesting isn't it. Often I get "received wisdom" saying defaults are usually best - but in this case, you might be scaling your VPS to 32 cores and still only using 1 without knowing it if you didn't sanity-check those defaults.

    We're not here for a long time - but we are here for a good time :)
    Jersey/UK
    Work & Ecommerce Advice: https://brandlight.org
    Personal & Software Tips: https://marcusquinn.com

    fbartelsF 1 Reply Last reply
    0
  • fbartelsF Offline
    fbartelsF Offline
    fbartels App Dev
    replied to marcusquinn on last edited by
    #4

    @marcusquinn don't forget the good old http://mysqltuner.pl/

    LonkleL 1 Reply Last reply
    4
  • robiR Offline
    robiR Offline
    robi
    wrote on last edited by
    #5

    About a decade ago, there was an idea for a project to do this app wide.

    As in have a profiler be able to tune any App (via config files and runtime params) it was aware of dynamically during various user loads.

    Initially meant for cluster Apps, but easily applies to single apps too.

    Feedback loops handle all the parameters automagically.

    Life of sky tech

    LonkleL 1 Reply Last reply
    0
  • LonkleL Offline
    LonkleL Offline
    Lonkle
    replied to robi on last edited by
    #6

    @robi said in MySQL tuning with my.cnf settings optimisation:

    About a decade ago, there was an idea for a project to do this app wide.

    As in have a profiler be able to tune any App (via config files and runtime params) it was aware of dynamically during various user loads.

    Initially meant for cluster Apps, but easily applies to single apps too.

    Feedback loops handle all the parameters automagically.

    Did anything come of it?

    robiR 1 Reply Last reply
    0
  • LonkleL Offline
    LonkleL Offline
    Lonkle
    replied to fbartels on last edited by
    #7

    @fbartels said in MySQL tuning with my.cnf settings optimisation:

    @marcusquinn don't forget the good old http://mysqltuner.pl/

    Never saw this before, when do we run / apply it? Anytime or only during installation?

    fbartelsF 1 Reply Last reply
    0
  • robiR Offline
    robiR Offline
    robi
    replied to Lonkle on last edited by
    #8

    @lonk It was architected but not implemented. Life happened.

    Still a good idea.

    Life of sky tech

    1 Reply Last reply
    0
  • rmdesR Offline
    rmdesR Offline
    rmdes
    replied to marcusquinn on last edited by
    #9

    @marcusquinn In the cloudron context, where does my.cnf goes to be taken into account ?

    1 Reply Last reply
    0
  • fbartelsF Offline
    fbartelsF Offline
    fbartels App Dev
    replied to Lonkle on last edited by
    #10

    @lonk said in MySQL tuning with my.cnf settings optimisation:

    when do we run / apply it?

    Mysql tuning is best applied after a few days of database usage. Applications usually have different load patterns.

    LonkleL 1 Reply Last reply
    2
  • LonkleL Offline
    LonkleL Offline
    Lonkle
    replied to fbartels on last edited by
    #11

    @fbartels said in MySQL tuning with my.cnf settings optimisation:

    @lonk said in MySQL tuning with my.cnf settings optimisation:

    when do we run / apply it?

    Mysql tuning is best applied after a few days of database usage. Applications usually have different load patterns.

    Gotcha, thanks for the tip! ☺️

    1 Reply Last reply
    0

  • Login

  • Don't have an account? Register

  • Login or register to search.
  • First post
    Last post
0
  • Categories
  • Recent
  • Tags
  • Popular
  • Bookmarks
  • Login

  • Don't have an account? Register

  • Login or register to search.