XF 1.2 bbcode cache slow query

[Brent W]

I am getting these slow queries on my largest sites.

# 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?

 

[Chris D]

That is why this suggestion is pretty necessary:
http://xenforo.com/community/threads/cache-rebuilder-for-bb-code-parse-cache.55108/

Though to answer your question, maybe you should be concerned...

I can't see why that would be a slow query. Is this the same server that was giving you slow queries on the unread post count and attachment block?

 

[Brent W]

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]

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]

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]

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

[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

 

[Mike]

I always point people here: http://www.mysqlperformanceblog.com/2007/11/01/innodb-performance-optimization-basics/