Learn about Centmin Mod LEMP Stack today
Register Now

MariaDB May Increasing the query_cache size over 128M reduce performance?

Discussion in 'MariaDB & General MySQL news & discussions' started by negative, Feb 3, 2016.

  1. negative

    negative Premium Member Premium Member

    222
    23
    18
    Apr 11, 2015
    Ratings:
    +51
    Local Time:
    6:33 AM
    1.9.10
    10.1.11
    Hello
    I'm optimizing the my mysql (mariadb 10.1.11) server and mysqltuner.pl offering non-stop the increase query_cache_size variable.

    For now, it is 512M but it still offer. I did 768M and same, so should i increase to about 1G ?

    I' m using mysql service separately so my database server is different from Web. It works just for database.

    My server has 48GB DDR3 1333 MHZ ECC Ram and 2 x Intel Xeon X5650 Cpu (total 24 core - 12 mb cache). Also, 4X300 GB SAS HDD @ Raid 10

    So, i can increase all of my limits for run mysql optimized and server must be use all sources for mysql. But, mysqltuner says "Increasing the query_cache size over 128M may reduce performance" What mean this ?

    Thanks.
     
  2. eva2000

    eva2000 Administrator Staff Member

    36,061
    7,912
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +12,195
    Local Time:
    1:33 PM
    Nginx 1.15.x
    MariaDB 5.5/10.x
    mysqltuner.pl is only a general guide and some suggestions are outdated or have proviso notes for certain situations. query_cache_size and memory usage are 2 such examples of such. Best to read up on query_cache and InnoDB table notes ;)
     
  3. negative

    negative Premium Member Premium Member

    222
    23
    18
    Apr 11, 2015
    Ratings:
    +51
    Local Time:
    6:33 AM
    1.9.10
    10.1.11
    Do you advice the query_cache parameter up top 1GB so ? It is no problem anymore ?
    If i have max connections 500 and query cache is 1GB so max usable memory 50GB and it is my total RAM. I think it calculating like this :)

    P.S: All tables are innodb

    innodb_buffer_pool_size = 16G
    innodb_buffer_pool_instances = 16
    key_buffer_size = 256M
    query_cache_size = 1G

    I'm not sure from that on mysql optimization, caching is always good ? Caches may slow down the database server too if we look some articles.
     
  4. eva2000

    eva2000 Administrator Staff Member

    36,061
    7,912
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +12,195
    Local Time:
    1:33 PM
    Nginx 1.15.x
    MariaDB 5.5/10.x
    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.

    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 :)

    MySQL optimisation is a continuous process of monitoring and fine tuning for optimal settings for your web app usage requirements. It's why my paid server optimisations span a minimum of at least 4 days :)
     
  5. negative

    negative Premium Member Premium Member

    222
    23
    18
    Apr 11, 2015
    Ratings:
    +51
    Local Time:
    6:33 AM
    1.9.10
    10.1.11
    Thank you, i agree that. It is no problem.

    I' m still waiting just an answer from you.
    Caching is always good in mysql side ? Or sometimes, queries should call in real time is better ?
     
  6. eva2000

    eva2000 Administrator Staff Member

    36,061
    7,912
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +12,195
    Local Time:
    1:33 PM
    Nginx 1.15.x
    MariaDB 5.5/10.x
    caching only good if it improves mysql performance so you need to monitor, measure and benchmark changes to see if they improve or regress performance
     
    • Like Like x 1
  7. negative

    negative Premium Member Premium Member

    222
    23
    18
    Apr 11, 2015
    Ratings:
    +51
    Local Time:
    6:33 AM
    1.9.10
    10.1.11
    How can i measure the performance difference between query_cache_size = 128M with query_cache_size = 1G
     
  8. eva2000

    eva2000 Administrator Staff Member

    36,061
    7,912
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +12,195
    Local Time:
    1:33 PM
    Nginx 1.15.x
    MariaDB 5.5/10.x
    You can look at monitored/measure query response times for your commonly used mysql queries.

    my mysqlmymonlite.sh has some form of mysql stats mysqlmymonlite.sh Addon as does Percona PMM Percona Monitoring and Management for Databases

    mysqlmymonlite.sh I wrote years ago from my vBulletin tech support/consultation days for helpng with optimising vBulletin customers servers and gather client server environment info quickly has a query cache stats section you can use to evaluate effectiveness of query caching
    Code (Text):
    ./mysqlmymonlite.sh mysqlreport
    
    -------------------------------------------------------------
    System MySQL monitoring stats
    mysqlmymonlite.sh - 0.5.6 mysqlmymon.com
    compiled by George Liu (eva2000) centminmod.com
    -------------------------------------------------------------
    
    Report Generated:
    Wed Sep 19 00:04:38 UTC 2018
    
    ----------------------------
    mysqlreport output
    ----------------------------
    
    MariaDB 10.1.36-MariaDB     uptime 7 6:33:56    Wed Sep 19 00:04:38 2018
    
    __ Key _________________________________________________________________
    Buffer used         0 of  512.00M   %Used:   0.00
      Current      93.37M              %Usage:  18.24
    Write hit       0.00%
    Read hit        0.00%
    
    __ Questions ___________________________________________________________
    Total             741       0.0/s
      DMS             396       0.0/s  %Total:  53.44
      Com_            253       0.0/s           34.14
      COM_QUIT         92       0.0/s           12.42
      QC Hits          62       0.0/s            8.37
      -Unknown         62       0.0/s            8.37
    Slow 1 s            0         0/s            0.00  %DMS:   0.00 Log:
    DMS               396       0.0/s           53.44
      SELECT          334       0.0/s           45.07         84.34
      UPDATE           62       0.0/s            8.37         15.66
      REPLACE           0         0/s            0.00          0.00
      DELETE            0         0/s            0.00          0.00
      INSERT            0         0/s            0.00          0.00
    Com_              253       0.0/s           34.14
      set_option      132       0.0/s           17.81
      change_db        44       0.0/s            5.94
      show_fields      33       0.0/s            4.45
    
    __ Rows ________________________________________________________________
    Rows           20.37k       0.0/s
      Using idx       233       0.0/s  %Index:   1.14
    Rows/question   27.50
    
    __ SELECT and Sort _____________________________________________________
    Scan              124       0.0/s %SELECT:  37.13
    Range               0         0/s            0.00
    Full join           0         0/s            0.00
    Range check         0         0/s            0.00
    Full rng join       0         0/s            0.00
    Sort scan           0         0/s
    Sort range          0         0/s
    Sort mrg pass       0         0/s
    
    __ Query Cache _________________________________________________________
    Memory usage   44.70k of  160.00M  %Usage:   0.03
    Block Fragmnt   7.14%
    Hits               62       0.0/s
    Inserts           202       0.0/s
    Insrt:Prune     202:1       0.0/s
    Hit:Insert     0.31:1
    
    __ Table Locks _________________________________________________________
    Waited              0         0/s  %Total:   0.00
    Immediate         285       0.0/s
    
    __ Tables ______________________________________________________________
    Open               95 of   8192    %Cache:   1.16
    Opened            101       0.0/s
    
    __ Connections _________________________________________________________
    Max used            1 of    900      %Max:   0.11
    Total              94       0.0/s
    
    __ Created Temp ________________________________________________________
    Disk table         45       0.0/s   %Disk:  26.32
    Table             171       0.0/s    Size: 768.0M
    File                6       0.0/s
    
    __ Threads _____________________________________________________________
    Running             1 of      1
    Created             1       0.0/s
    Slow                0         0/s
    Cached              0 of    384      %Hit:  98.94
    
    __ Aborted _____________________________________________________________
    Clients             0         0/s
    Connects            0         0/s
    
    __ Bytes _______________________________________________________________
    Sent            1.55M       2.5/s
    Received      206.87k       0.3/s
    
    __ InnoDB Buffer Pool __________________________________________________
    Usage           7.06M of    4.00G  %Usage:   0.17
    Read hit       94.24%
    Pages
      Free        261.69k              %Total:  99.83
      Data            451                        0.17  %Drty:   0.00
      Misc              1                        0.00
      Latched           0                        0.00
    Reads           7.76k       0.0/s
      From disk       447       0.0/s   %Disk:   5.76
      Ahead Rnd         0         0/s
    Writes            725       0.0/s
    Flushes           322       0.0/s
    Wait Free           0         0/s   %Wait:   0.00
    
    __ InnoDB Lock _________________________________________________________
    Waits               0         0/s
    Current             0
    Time acquiring
      Total             0 ms
      Average           0 ms
      Max               0 ms
    
    __ InnoDB Data, Pages, Rows ____________________________________________
    Data
      Reads           474       0.0/s
      Writes          465       0.0/s
      fsync           126       0.0/s
      Pending
        Reads           0
        Writes          0
        fsync           0
    
    Pages
      Created           5       0.0/s
      Read            446       0.0/s
      Written         322       0.0/s
    
    Rows
      Deleted           0         0/s
      Inserted          0         0/s
      Read          4.07k       0.0/s
      Updated          62       0.0/s
    
    __ Aria Pagecache ______________________________________________________
    Buffer used    16.00k of    1.00G   %Used:   0.00
      Current      42.30M              %Usage:   4.13
    Write hit     100.00%
    Read hit       83.33%
    
    Report Complete:
    Wed Sep 19 00:04:38 UTC 2018
    ----------------------------
    

    Percona PMM

    percona-pmm-system-02.png

    percona-pmm-08.png
     
    • Informative Informative x 1
..