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

negative · Feb 3, 2016

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.

eva2000 · Feb 3, 2016

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

negative · Feb 3, 2016

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.

eva2000 · Feb 3, 2016

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

negative · Feb 3, 2016

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 ?

eva2000 · Feb 3, 2016

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

negative · Sep 19, 2018

eva2000 said: ↑

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
Click to expand...

How can i measure the performance difference between query_cache_size = 128M with query_cache_size = 1G

eva2000 · Sep 19, 2018

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

Log in / Register

Forums

Want to subscribe to topics you're interested in?

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

negative Active Member

eva2000 Administrator Staff Member

negative Active Member

eva2000 Administrator Staff Member

negative Active Member

eva2000 Administrator Staff Member

negative Active Member

eva2000 Administrator Staff Member

.

Log in / Register

Useful Searches

Want to subscribe to topics you're interested in?

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

negative Active Member

eva2000 Administrator Staff Member

negative Active Member

eva2000 Administrator Staff Member

negative Active Member

eva2000 Administrator Staff Member

negative Active Member

eva2000 Administrator Staff Member

.