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

XF 1.5 SQL Queries 'Writing to Net'

Discussion in 'Troubleshooting and Problems' started by PrinceReborn, Jul 5, 2016.

  1. PrinceReborn

    PrinceReborn Member

    We've recently just wiped our hard drive, reinstalled CentOS 7, Apache2, all the Xenforo requirements and restored our website/forum files. Only to discover that any pages using SQL are incredibly slow, sometimes taking over 10 seconds to load. Whereas pages that don't use SQL are loading instantly.

    I have checked the SQL processlist and noticed tons of queries that are 'writing to net':
    Code:
    | 42573507 | xenforo              | 10.4.14.218:59470                        | xenforo    | Sleep   |    1 |                  | NULL                                                                                                 |    0.000 |
    | 42573508 | xenforo              | 10.4.14.218:59524                        | xenforo    | Sleep   |    1 |                  | NULL                                                                                                 |    0.000 |
    | 42573509 | xenforo              | 10.4.14.218:59432                        | xenforo    | Execute |    2 | Writing to net   | SELECT title, template_compiled
                            FROM xf_template_compiled
                            WHERE title IN ('page_nav', 'bb_co |    0.000 |
    | 42573511 | xenforo              | 10.4.14.218:59536                        | xenforo    | Sleep   |    2 |                  | NULL                                                                                                 |    0.000 |
    | 42573512 | xenforo              | 10.4.14.218:59542                        | xenforo    | Sleep   |    0 |                  | NULL                                                                                                 |    0.000 |
    | 42573513 | xenforo              | 10.4.14.218:59554                        | xenforo    | Sleep   |    0 |                  | NULL                                                                                                 |    0.000 |
    | 42573515 | xenforo              | 10.4.14.218:59496                        | xenforo    | Sleep   |    2 |                  | NULL                                                                                                 |    0.000 |
    | 42573516 | xenforo              | 10.4.14.218:59638                        | xenforo    | Execute |    3 | Writing to net   | SELECT title, template_compiled
                            FROM xf_template_compiled
                            WHERE title IN ('page_nav', 'bb_co |    0.000 |
    | 42573517 | xenforo              | 10.4.14.218:59708                        | xenforo    | Sleep   |    2 |                  | NULL                                                                                                 |    0.000 |
    | 42573518 | xenforo              | 10.4.14.218:59714                        | xenforo    | Execute |    9 | Writing to net   | SELECT data_key, data_value
                            FROM xf_data_registry
                            WHERE data_key IN ('options', 'languages', |    0.000 |
    | 42573519 | xenforo              | 10.4.14.218:59890                        | xenforo    | Sleep   |    5 |                  | NULL                                                                                                 |    0.000 |
    | 42573520 | xenforo              | 10.4.14.218:59894                        | xenforo    | Execute |    8 | Writing to net   | SELECT data_key, data_value
                            FROM xf_data_registry
                            WHERE data_key IN ('options', 'languages', |    0.000 |
    | 42573521 | xenforo              | 10.4.14.218:59932                        | xenforo    | Execute |    7 | Writing to net   | SELECT data_key, data_value
                            FROM xf_data_registry
                            WHERE data_key IN ('options', 'languages', |    0.000 |
    | 42573522 | xenforo              | 10.4.14.218:59940                        | xenforo    | Execute |    7 | Writing to net   | SELECT data_key, data_value
                            FROM xf_data_registry
                            WHERE data_key IN ('options', 'languages', |    0.000 |
    | 42573523 | xenforo              | 10.4.14.218:59956                        | xenforo    | Execute |    7 | Writing to net   | SELECT data_key, data_value
                            FROM xf_data_registry
                            WHERE data_key IN ('options', 'languages', |    0.000 |
    | 42573524 | xenforo              | 10.4.14.218:59976                        | xenforo    | Execute |    6 | Writing to net   | SELECT data_key, data_value
                            FROM xf_data_registry
                            WHERE data_key IN ('options', 'languages', |    0.000 |
    | 42573525 | xenforo              | 10.4.14.218:60110                        | xenforo    | Execute |    5 | Writing to net   | SELECT data_key, data_value
                            FROM xf_data_registry
                            WHERE data_key IN ('options', 'languages', |    0.000 |
    | 42573526 | xenforo              | 10.4.14.218:60112                        | xenforo    | Execute |    5 | Writing to net   | SELECT data_key, data_value
                            FROM xf_data_registry
                            WHERE data_key IN ('options', 'languages', |    0.000 |
    | 42573527 | xenforo              | 10.4.14.218:60146                        | xenforo    | Execute |    4 | Writing to net   | SELECT data_key, data_value
                            FROM xf_data_registry
                            WHERE data_key IN ('options', 'languages', |    0.000 |
    | 42573528 | xenforo              | 10.4.14.218:60156                        | xenforo    | Execute |    3 | Writing to net   | SELECT data_key, data_value
                            FROM xf_data_registry
                            WHERE data_key IN ('options', 'languages', |    0.000 |
    | 42573529 | xenforo              | 10.4.14.218:60258                        | xenforo    | Execute |    2 | Writing to net   | SELECT data_key, data_value
                            FROM xf_data_registry
                            WHERE data_key IN ('options', 'languages', |    0.000 |
    | 42573531 | xenforo              | 10.4.14.218:60344                        | xenforo    | Execute |    2 | Writing to net   | SELECT data_key, data_value
                            FROM xf_data_registry
                            WHERE data_key IN ('options', 'languages', |    0.000 |
    | 42573532 | xenforo              | 10.4.14.218:60230                        | xenforo    | Execute |    2 | Writing to net   | SELECT data_key, data_value
                            FROM xf_data_registry
    Any advice?
     
  2. Mike

    Mike XenForo Developer Staff Member

    This generally indicates its stuck in the networking phase. Are there networking problems between your web server and database server? (They don't appear to be on the same server based on IP, though I suppose it could be Docker communication?)
     
  3. PrinceReborn

    PrinceReborn Member

    I cannot see any networking issues between the web server and MySQL server. However, these types of queries seem to have disappeared now, maybe I ran the processlist at a time when nothing much was showing.

    What is odd is that before we formatted the web server, we did not experience any issues, and the MySQL server & config has not been touched at all since re got everything back up and running.

    This time I ran the processlist it now seems to be showing a lot of SQL queries on 'Waiting for table level lock'. Below is part of the processlist, when I ran this one there were approximately 100 queries like this.

    Code:
    | 42665102 | xenforo    | 10.4.14.218:33962                        | xenforo    | Execute |    3 | Waiting for table level lock | INSERT INTO xf_session_activity
                                            (user_id, unique_key, ip, controller_name, controller_action, v |    0.000 |
    | 42665104 | xenforo    | 10.4.14.218:33968                        | xenforo    | Execute |   10 | Waiting for table level lock | INSERT INTO xf_session_activity
                                            (user_id, unique_key, ip, controller_name, controller_action, v |    0.000 |
    | 42665109 | xenforo    | 10.4.14.218:33982                        | xenforo    | Execute |    8 | Waiting for table level lock | INSERT INTO xf_session_activity
                                            (user_id, unique_key, ip, controller_name, controller_action, v |    0.000 |
    | 42665112 | xenforo    | 10.4.14.218:33998                        | xenforo    | Execute |    9 | Waiting for table level lock | INSERT INTO xf_session_activity
                                            (user_id, unique_key, ip, controller_name, controller_action, v |    0.000 |
    | 42665114 | xenforo    | 10.4.14.218:34000                        | xenforo    | Execute |    2 | Waiting for table level lock | INSERT INTO xf_session_activity
                                            (user_id, unique_key, ip, controller_name, controller_action, v |    0.000 |
    | 42665117 | xenforo    | 10.4.14.218:34016                        | xenforo    | Execute |    7 | Waiting for table level lock | INSERT INTO xf_session_activity
                                            (user_id, unique_key, ip, controller_name, controller_action, v |    0.000 |
    | 42665121 | xenforo    | 10.4.14.218:34024                        | xenforo    | Execute |    7 | Waiting for table level lock | INSERT INTO xf_session_activity
                                            (user_id, unique_key, ip, controller_name, controller_action, v |    0.000 |
    | 42665124 | xenforo    | 10.4.14.218:34036                        | xenforo    | Execute |    4 | Waiting for table level lock | INSERT INTO xf_session_activity
                                            (user_id, unique_key, ip, controller_name, controller_action, v |    0.000 |
    | 42665131 | xenforo    | 10.4.14.218:34056                        | xenforo    | Execute |    3 | Waiting for table level lock | INSERT INTO xf_session_activity
                                            (user_id, unique_key, ip, controller_name, controller_action, v |    0.000 |
    | 42665134 | xenforo    | 10.4.14.218:34068                        | xenforo    | Execute |    8 | Waiting for table level lock | INSERT INTO xf_session_activity
                                            (user_id, unique_key, ip, controller_name, controller_action, v |    0.000 |
    | 42665136 | xenforo    | 10.4.14.218:34072                        | xenforo    | Execute |    8 | Waiting for table level lock | INSERT INTO xf_session_activity
                                            (user_id, unique_key, ip, controller_name, controller_action, v |    0.000 |
    | 42665139 | xenforo    | 10.4.14.218:34084                        | xenforo    | Execute |    2 | Waiting for table level lock | INSERT INTO xf_session_activity
                                            (user_id, unique_key, ip, controller_name, controller_action, v |    0.000 |
    | 42665147 | xenforo    | 10.4.14.218:34106                        | xenforo    | Execute |    5 | Waiting for table level lock | INSERT INTO xf_session_activity
                                            (user_id, unique_key, ip, controller_name, controller_action, v |    0.000 |
    | 42665150 | xenforo    | 10.4.14.218:34116                        | xenforo    | Execute |    3 | Waiting for table level lock | INSERT INTO xf_session_activity
                                            (user_id, unique_key, ip, controller_name, controller_action, v |    0.000 |
    | 42665151 | xenforo    | 10.4.14.218:34120                        | xenforo    | Execute |    3 | Waiting for table level lock | INSERT INTO xf_session_activity
                                            (user_id, unique_key, ip, controller_name, controller_action, v |    0.000 |
    | 42665153 | xenforo    | 10.4.14.218:34124                        | xenforo    | Execute |    5 | Waiting for table level lock | INSERT INTO xf_session_activity
                                            (user_id, unique_key, ip, controller_name, controller_action, v |    0.000 |
    | 42665158 | xenforo    | 10.4.14.218:34154                        | xenforo    | Execute |    3 | Waiting for table level lock | INSERT INTO xf_session_activity
                                            (user_id, unique_key, ip, controller_name, controller_action, v |    0.000 |
    | 42665160 | xenforo    | 10.4.14.218:34170                        | xenforo    | Execute |    4 | Waiting for table level lock | INSERT INTO xf_session_activity
                                            (user_id, unique_key, ip, controller_name, controller_action, v |    0.000 |
    | 42665161 | xenforo    | 10.4.14.218:34178                        | xenforo    | Execute |    5 | Waiting for table level lock | INSERT INTO xf_session_activity
                                            (user_id, unique_key, ip, controller_name, controller_action, v |    0.000 |
    | 42665164 | xenforo    | 10.4.14.218:34184                        | xenforo    | Execute |    3 | Waiting for table level lock | INSERT INTO xf_session_activity
                                            (user_id, unique_key, ip, controller_name, controller_action, v |    0.000 |
    | 42665165 | xenforo    | 10.4.14.218:34190                        | xenforo    | Execute |    7 | Waiting for table level lock | INSERT INTO xf_session_activity
                                            (user_id, unique_key, ip, controller_name, controller_action, v |    0.000 |
    | 42665167 | xenforo    | 10.4.14.218:34196                        | xenforo    | Execute |    6 | Waiting for table level lock | SELECT session_activity.*
                                            ,
                                            user.*
                                    FROM xf_session_activity AS session_activity
                     |    0.000 |
    | 42665169 | xenforo    | 10.4.14.218:34206                        | xenforo    | Execute |    9 | Waiting for table level lock | SELECT session_activity.*
                                            ,
                                            user.*
                                    FROM xf_session_activity AS session_activity
                     |    0.000 |
    
     
  4. PrinceReborn

    PrinceReborn Member

    Here is a screenshot showing the query taking the longest, which is always 'writing to net' using xf_session_activity table query, followed by a waiting for table level lock INSERT INTO xf_session_activity table query, followed by multiple SELECT xf_session_activity table queries.

    I can now fully confirm that the forum lag is related to these queries, and that nobody can load any forum pages until they eventually disappear from the process list.

    [​IMG]
     
  5. Tracy Perry

    Tracy Perry Well-Known Member

    Try placing your sessions into cache (memcached/redis) instead of into mySQL.
    Have you optimized your my.cnf for the server?
     
  6. PrinceReborn

    PrinceReborn Member

    We are caching everything at the moment. However, just realised the issue, and it is network related. Asking my host to reconfigure the private network as ping times between the web server and sql server are fluctuating like crazy.
     

Share This Page