Discover Centmin Mod today
Register Now

MariaDB Mysqltuner

Discussion in 'Nginx, PHP-FPM & MariaDB MySQL' started by Revenge, Mar 22, 2016.

  1. Revenge

    Revenge Active Member

    288
    64
    28
    Feb 21, 2016
    Portugal
    Ratings:
    +227
    Local Time:
    7:53 PM
    1.9.x
    10.1.x
    Finally, mysqltuner 1.6.7 recommends disabling the Query Cache. It's better later than never.
    Code:
    [OK] Query cache is disabled by default due to mutex contention.

     
    • Like Like x 1
  2. pamamolf

    pamamolf Well-Known Member

    2,720
    242
    63
    May 31, 2014
    Ratings:
    +433
    Local Time:
    9:53 PM
    Nginx-1.13.x
    MariaDB 10.1.x
    Why is better to have a cache disabled?
     
  3. Revenge

    Revenge Active Member

    288
    64
    28
    Feb 21, 2016
    Portugal
    Ratings:
    +227
    Local Time:
    7:53 PM
    1.9.x
    10.1.x
    • Informative Informative x 3
  4. pamamolf

    pamamolf Well-Known Member

    2,720
    242
    63
    May 31, 2014
    Ratings:
    +433
    Local Time:
    9:53 PM
    Nginx-1.13.x
    MariaDB 10.1.x
    Do you have it in a real world scenario on a huge forum with many concurrent users and it works better disabled than have it enabled?
     
    Last edited: Mar 22, 2016
  5. Revenge

    Revenge Active Member

    288
    64
    28
    Feb 21, 2016
    Portugal
    Ratings:
    +227
    Local Time:
    7:53 PM
    1.9.x
    10.1.x
    Yes, in a board with many concurrent requests that i maintain, the performance is better and load is lower.
    You will notice this more in a forum like this one, where the query results are always changing, so the cache needs also to be changed ;)
     
    • Informative Informative x 1
  6. pamamolf

    pamamolf Well-Known Member

    2,720
    242
    63
    May 31, 2014
    Ratings:
    +433
    Local Time:
    9:53 PM
    Nginx-1.13.x
    MariaDB 10.1.x
    Ok i will disable it and try to see if it is better :)
     
    Last edited: Mar 22, 2016
  7. pamamolf

    pamamolf Well-Known Member

    2,720
    242
    63
    May 31, 2014
    Ratings:
    +433
    Local Time:
    9:53 PM
    Nginx-1.13.x
    MariaDB 10.1.x
    It seems that is working nice :)
     
    Last edited: Mar 22, 2016
  8. Revenge

    Revenge Active Member

    288
    64
    28
    Feb 21, 2016
    Portugal
    Ratings:
    +227
    Local Time:
    7:53 PM
    1.9.x
    10.1.x
    Just another thing for us to understand better. This post that im making, will go to the Buffer Pool. This post also change the result set of a lot of queries in the Query_Cache. For example, the querie responsible for the New Posts button.
    The Query Cache don't have a cache of this new post, so they will ask the buffer pool and then it will need to update every query that this new post will mess with.
    Now imagine a forum with a lot of new posts every minute, and the Query Cache always competing with the buffer pool because of that ;)

    From my knowledge, the Query Cache is good in sites where almost there is no updates or inserts to the Database. For example a Wordpress Blog with comments disabled.
     
    • Informative Informative x 2
  9. negative

    negative Member

    216
    21
    18
    Apr 11, 2015
    Ratings:
    +49
    Local Time:
    9:53 PM
    1.9.10
    10.1.11
    I've disabled the query_cache (it was 512M) and i ll see what will happen.
     
  10. Revenge

    Revenge Active Member

    288
    64
    28
    Feb 21, 2016
    Portugal
    Ratings:
    +227
    Local Time:
    7:53 PM
    1.9.x
    10.1.x
    In version 1.6.11 of Mysqltuner, its again recommending to turn on Query Cache if MariaDB version is superior than 10.1.2.

    You can check it here: possible wrong suggestion in newer versions: [!!] Query cache should be disabled by default due to mutex contention. · Issue #190 · major/MySQLTuner-perl · GitHub

    I checked some benchmarks, and it really seems MariaDB have done very big improvements regarding the Query Cache in 10.1.

    [​IMG]
    [​IMG]
    While in Mysql 5.7 the performance is degraded when we enable Query Cache, that does not happen with MariaDB, in fact its the opposite.

    MariaDB 10.1 vs MySQL 5.7: Real-World Performances - Softizy Blog
     
    • Informative Informative x 3
  11. negative

    negative Member

    216
    21
    18
    Apr 11, 2015
    Ratings:
    +49
    Local Time:
    9:53 PM
    1.9.10
    10.1.11
    I was re-enabled it after 2 days of disabled. Query cache is increasing the performans as i see the real time loading of website. Now, tests are correcting that.
     
    • Informative Informative x 1
  12. eva2000

    eva2000 Administrator Staff Member

    30,152
    6,782
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,133
    Local Time:
    4:53 AM
    Nginx 1.13.x
    MariaDB 5.5
    thanks @negative interesting benchmarks !
     
  13. pamamolf

    pamamolf Well-Known Member

    2,720
    242
    63
    May 31, 2014
    Ratings:
    +433
    Local Time:
    9:53 PM
    Nginx-1.13.x
    MariaDB 10.1.x
    @Revenge

    How did you do the test and how do you get the nice graphs?
     
  14. eva2000

    eva2000 Administrator Staff Member

    30,152
    6,782
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,133
    Local Time:
    4:53 AM
    Nginx 1.13.x
    MariaDB 5.5
    • Like Like x 1
  15. pamamolf

    pamamolf Well-Known Member

    2,720
    242
    63
    May 31, 2014
    Ratings:
    +433
    Local Time:
    9:53 PM
    Nginx-1.13.x
    MariaDB 10.1.x
    I was wondering how they get that nice graphs at the end :)
     
  16. eva2000

    eva2000 Administrator Staff Member

    30,152
    6,782
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,133
    Local Time:
    4:53 AM
    Nginx 1.13.x
    MariaDB 5.5
    excel or google sheet charts works nice :)
     
  17. pamamolf

    pamamolf Well-Known Member

    2,720
    242
    63
    May 31, 2014
    Ratings:
    +433
    Local Time:
    9:53 PM
    Nginx-1.13.x
    MariaDB 10.1.x
    Yes but don't know how to :p
     
  18. eva2000

    eva2000 Administrator Staff Member

    30,152
    6,782
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,133
    Local Time:
    4:53 AM
    Nginx 1.13.x
    MariaDB 5.5
    google for tutorials ;)