mysql query cache
mysql query cache depricated in 5.7 & removed from mysql 8 version, due to negative impact rather than performance in many studies.
what exactly query cache is:
sending queries to database to fetch the results.
if you sending same query multiple Times, It’s a better idea to cache in memory (ram) rather than fetching every time from disk.
why negative impact?
the query cache expiry time
the holding memory
flushing frequency
if data gets updates it’s has to flush everything. If data gets frequent updates, it’s busy with flushing.
query caching with redis
user database query to (mysql in memory) myisam keysize or innodb buffer pool . then
db on disk.(MySQL on disk)
if redis first goes through redis and then above process.
object cache in wp cache plugins
Object caching. storing database queries so that the next time a piece of data is needed, it is delivered from cache without having to query the database.
MySQL query cache results in memory.
mysql query cache vs innodb buffer pool
innodb_buffer_pool_size = caches the tables & indexes in memory for better response times
memory allocation may greater than databae size.
ex: 1gb database buffer pool memory will be 1gb.
or you can measure the efficiency, to decide exact value.
if you are using myisam as engine you should go for key size.
innodb_max_dirty_pages_pct helps to lru caching.
query_cache = for remembering queries to get them faster the next time you execute the same query
mysql buffer pool vs memcached
(why should we use memcached / redis along with buffer pool cache)
quick answer:
buffer pool size: loads database into memory
faster than disk.
redis/memcached: caches important & repeated queries /information in memory, ability to manage flush according to rules.
database size, innodb buffer pool size (Ram)
and redis memory (RAM) should be measure carefully. once configuration.
memcached stores mysql data
in key value format later objects in memory.
InnoDB will cache all data and index pages accessed, while memcached will store the same user data.
Memcached will not store the index page information that InnoDB will have in its buffer pool.
Memcached will store only the exact pieces of data needed
memcached
parsing, planning
updates through memcached
Subsequent requests for the sa
me data is served from the InnoDB buffer pool
memcached key value lookups faster than innodb primary key lookups
memcached is to limit by memory
Note:balance the memory allocation between those.
ex: 8GB for memcached 6GB for buffer pool while db 6GB*
measure the actual memory usage on allocated memory, and adjust accordingly. (otherwise memory wasted)
memcached vs redis
Memcached is multithreaded Redis single threaded
redis also called as memcached on steroids. redis ported from memcached it’s got better than memcached.
redis has 5 primary data structures
list, array, sets and sorted sets
memcached key value.
data eviction: memcached LRU algorithms
redis: 6 different polices
key value: 1 MB redis 512MB.
persistent to disk.
how to know MySQL connections per second?
query throughout
slow query logs.
enable fastcgi in memory..