mardi 24 février 2015

Dealing with a potential bottleneck


So, I'm running a machine on CentOS and MariaDB 10 and I've been noticing low performance on Wordpress / Prestashop and other common PHP CMS's and Stores.


From a webserver perspective everything looks fine, and there aren't also RAM issues, however, and by experience, I guess the performance issue is related to the database, because websites without DB load really fast and fine.


Here are my.cnf settings:



[mysqld]
local-infile = 0
max_connections = 350
key_buffer = 150M
myisam_sort_buffer_size = 64M
join_buffer_size = 3M
read_buffer_size = 3M
sort_buffer_size = 5M
max_heap_table_size = 16M
table_cache = 5000
thread_cache_size = 286
interactive_timeout = 25
wait_timeout = 7000
connect_timeout = 15
max_allowed_packet = 150M
max_connect_errors = 10
query_cache_limit = 3M
query_cache_size = 150MB
query_cache_type = 1
tmp_table_size = 16M

innodb_buffer_pool_size=1024M
key_buffer_size=300M


In order to help me find what's going wrong, I made this query to find out if query_cache is well configured:



SELECT

((( @@GLOBAL.query_cache_size -
(SELECT VARIABLE_VALUE
FROM information_schema.SESSION_STATUS
WHERE VARIABLE_NAME LIKE 'Qcache_free_memory')
) / @@GLOBAL.query_cache_size )*100) as query_cache_usage_percentage ,


(((SELECT VARIABLE_VALUE
FROM information_schema.SESSION_STATUS
WHERE VARIABLE_NAME LIKE 'Qcache_hits')
/
((SELECT VARIABLE_VALUE
FROM information_schema.SESSION_STATUS
WHERE VARIABLE_NAME LIKE 'Qcache_hits')
+
(SELECT VARIABLE_VALUE
FROM information_schema.SESSION_STATUS
WHERE VARIABLE_NAME LIKE 'Qcache_inserts')
+
(SELECT VARIABLE_VALUE
FROM information_schema.SESSION_STATUS
WHERE VARIABLE_NAME LIKE 'Qcache_not_cached')
))*100
) as query_cache_hit_rate_percentage,

( (SELECT VARIABLE_VALUE
FROM information_schema.SESSION_STATUS
WHERE VARIABLE_NAME LIKE 'Qcache_inserts')
/
(SELECT VARIABLE_VALUE
FROM information_schema.SESSION_STATUS
WHERE VARIABLE_NAME LIKE 'Qcache_lowmem_prunes')
) as insert_to_prune_ratio,

((SELECT VARIABLE_VALUE
FROM information_schema.SESSION_STATUS
WHERE VARIABLE_NAME LIKE 'Qcache_hits')
/
(SELECT VARIABLE_VALUE
FROM information_schema.SESSION_STATUS
WHERE VARIABLE_NAME LIKE 'Qcache_inserts')

) as insert_ratio
;


The output of the query is:



"query_cache_usage_percentage": 71.7203776041667,
"query_cache_hit_rate_percentage": 75.4894611997468,
"insert_to_prune_ratio": 6.24426442684076,
"insert_ratio": 3.49756779571875


I guess my config is correct since 75% of the queries are being run over the cache. About the other values, any thoughts?


I've noticed that the most used website on the server works faster than the other websites... most of the time it loads instantaneously. I guess that website queries, since are frequently run, are being cached, but other low traffic websites aren't taking advantage of the cache...


I guess the cache behavior is correct and working fine, however, is there any reason for a clean wordpress website to have a SQL performance impact up to the point where it takes up to 4 or 5 seconds to start loading?


What is wrong in my config? What else can I test?




Server Specs


The server is a VM with 5 GB of RAM, usually 2 or 3 are being used. In terms of CPU it has 3 core of those:



Total processors: 3

Processor #1
Vendor
GenuineIntel
Name
QEMU Virtual CPU version (cpu64-rhel6)
Speed
2659.982 MHz
Cache
4096 KB


The user server load is between 0.5 and 1.2.





Aucun commentaire:

Enregistrer un commentaire