1. 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

Discussion in 'Troubleshooting and Problems' started by BamaStangGuy, Aug 21, 2013.

  1. BamaStangGuy

    BamaStangGuy Well-Known Member

    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?
     
  2. Chris D

    Chris D XenForo Developer Staff Member

  3. BamaStangGuy

    BamaStangGuy Well-Known Member

    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.
     
  4. Chris D

    Chris D XenForo Developer Staff Member

    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.
     
  5. Mike

    Mike XenForo Developer Staff Member

    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).
     
  6. BamaStangGuy

    BamaStangGuy Well-Known Member

    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
     
  7. Mike

    Mike XenForo Developer Staff Member

Share This Page