• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

XF 1.2 bbcode cache slow query

Brent W

Well-known member
#1
I am getting these slow queries on my largest sites.

Code:
# Time: 130821  3:46:41
# User@Host: <redacted>[<redacted>] @ localhost []
# Query_time: 3.648278  Lock_time: 0.000052 Rows_sent: 0  Rows_examined: 0
use ford_xenforo;
SET timestamp=1377074801;
INSERT INTO xf_bb_code_parse_cache^M
                                                                (content_type, content_id, parse_tree, cache_version, cache_date)^M
                                                        VALUES ('post', '18431', 'a:1:{i:0;s:50:\"i meant the 5.0 is still in the other truck hon...\";}', '1377037569', '1377074796')^M
                                                        ON DUPLICATE KEY UPDATE parse_tree = VALUES(parse_tree),^M
                                                                cache_version = VALUES(cache_version),^M
                                                                cache_date = VALUES(cache_date);
Should I be concerned?
 

Brent W

Well-known member
#3
Yes, we have quite a few sites on it. I am going to spend some time cleaning up some scripts and maybe off set some of the cron jobs so that they aren't all triggered at the same time for all the sites.

It is a stout server though. 64GB ram, SSD drive and a quad core hyper threaded processor. Use memcache to store everything xenForo will allow including sessions. MySQL should be able to handle everything else with the horsepower we have.
 

Chris D

XenForo developer
Staff member
#4
I can't see why that query would take 3 seconds...

Maybe @Mike would know if that's sometimes expected. But to me, it looks like it's just inserting a new row into the database or performing an update if the key already exists.
 

Mike

XenForo developer
Staff member
#5
It's possible that it was just waiting for another query or there was some slow I/O on the server. Checking that you have optimized MySQL's I/O is important too.

I'd just turn off the BB code cache if it concerns you (it's off by default now).
 

Brent W

Well-known member
#6
It's possible that it was just waiting for another query or there was some slow I/O on the server. Checking that you have optimized MySQL's I/O is important too.

I'd just turn off the BB code cache if it concerns you (it's off by default now).
Thanks.

Here is my my.cnf. Maybe you or others will have some advice

Code:
[mysqld]
datadir=/var/lib/mysql
innodb_file_per_table=1
slow_query_log=1
slow_query_log_file = /var/log/mysql-slow-log.txt
long_query_time = 2
ft_min_word_len=3

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

table_cache=12288
thread_cache_size=16
thread_concurrency=8
back_log=600
max_connect_errors=10000
open-files=20000
max_connections=300
interactive_timeout=500
wait_timeout=500

max_allowed_packet=64M
tmp_table_size=512M
max_heap_table_size=512M
table_definition_cache = 4000
key_buffer=4G

sort_buffer_size=2M
read_buffer_size=2M
read_rnd_buffer_size=4M
join_buffer_size=4M

innodb_buffer_pool_size=14G
innodb_buffer_pool_instances = 14

myisam_sort_buffer_size=64M

open_files_limit=24808
Intel e5 1620
64 ram
256gb SSD hdd Raid 1