High Load, after server restart !!

  • Filter
  • Time
  • Show
Clear All
new posts

  • High Load, after server restart !!

    Hello Scott and the rest of the techs on the forum,

    Please assist me in my ongoing issues with my server, especially after reboot

    Lately, I have captured queries running over 60 secs and i am not sure, what to do with the out, and the same is pasted below, for your suggestion :-

    [root@Newdbsrv home]# pt-kill --busy-time 60 --print
    # 2013-10-07T09:57:06 KILL 27655 (Query 491 sec) SELECT vtiger_leadscf.cf_780, vtiger_leaddetails.firstname, vt
    iger_leaddetails.lastname, vtiger_leadaddress.mobile, vtiger_leadscf.cf_687, vtiger_leadscf.cf_682, vtiger_crme
    ntity.description, vtiger_leaddetails.leadid FROM vtiger_leaddetails INNER JOIN vtiger_crmentity ON vtiger_lea
    ddetails.leadid = vtiger_crmentity.crmid INNER JOIN vtiger_leadscf ON vtiger_leaddetails.leadid = vtiger_leadsc
    f.leadid INNER JOIN vtiger_leadaddress ON vtiger_leaddetails.leadid = vtiger_leadaddress.leadaddressid LEFT JOI
    N vtiger_users ON vtiger_crmentity.smownerid = vtiger_users.id LEFT JOIN vtiger_groups ON vtiger_crmentity.smow
    nerid = vtiger_groups.groupid INNER JOIN vt_tmp_u1470 vt_tmp_u1470 ON vt_tmp_u1470.id = vtiger_crmentity.smowne
    rid WHERE vtiger_crmentity.deleted=0 and vtiger_leaddetails.converted=0 AND (( vtiger_users.user_name = 'bdt1
    563' or vtiger_groups.groupname = 'bdt1563') ) AND vtiger_leaddetails.leadid > 0 LIMIT 0, 25
    Secondly, I read that setting the caching options on the server may help, but i already have the same enabled on my server, which i had referred at :-
    HTML Code:
    Likewise, the ouput of my server is as follows :-

    mysql> show global status like 'qcache%';
    | Variable_name | Value |
    | Qcache_free_blocks | 1527 |
    | Qcache_free_memory | 257336400 |
    | Qcache_hits | 151168 |
    | Qcache_inserts | 47771 |
    | Qcache_lowmem_prunes | 0 |
    | Qcache_not_cached | 2981 |
    | Qcache_queries_in_cache | 8337 |
    | Qcache_total_blocks | 18371 |

    mysql> show global variables like 'query_cache%';
    | Variable_name | Value |
    | query_cache_limit | 4194304 |
    | query_cache_min_res_unit | 4096 |
    | query_cache_size | 268435456 |
    | query_cache_strip_comments | OFF |
    | query_cache_type | ON |
    | query_cache_wlock_invalidate | OFF |
    6 rows in set (0.00 sec)

    Please let me know what variables i need to adjust for my servers to behave normally after they come up from reboot

    Please assist !!!

    Thank you,

  • #2
    Hello once again !!

    Can some one let me know, How can i find out if MySQL is spawning its own connections. ?

    Thank you


    • #3
      Sounds like two issues: query tuning, and warming up the buffer pool. For query tuning, you just need to pull a slow query log and start going over it with pt-query-digest to see what you can improve as far as modifying queries and adding/removing/modifying indexes.

      Secondly you could look into warming up the buffer pool using the special feature from Percona:

      This could potentially help get things going quicker after a restart, but that is highly situational so you would need to test it out with your workload.

      As for MySQL spawning it's own connections, what is likely happening is there is a query or queries that are getting locked up, which causes a backlog of connections to form. This is when you get a large number of connections that has the potential to bring the server down, which appears to be happening with you. This could also be caused by a "cache stampede", which happens when one or more large caches (i.e. from an ORM like Hibernate or a caching solution like memcached) get refreshed at the same time, overloading the database.
      Last edited by scott.nemes; 10-07-2013, 10:39 AM.
      Scott Nemes


      • #4

        thank you so very much for your suggestion as always

        Can you also let me know, how can i find out which queries that are getting locked up ? What is the command line syntax for the same ?

        Secondly, Is there a way to figure out if there is 'cache stampede' in process ?, this would be really helpful insight !!

        Although the server has been running fine since yesterday's reboot a couple of times and that has caused my replication to stop working.

        i have posted a thread for it too, If you too could look into it and suggest your views, that would be really helpful.

        Thank you !!


        • #5
          You'll want to look for threads that are waiting on locks, and try to track down what is blocking that thread. If it is a single thread locking everything up, then you will likely see a bunch of different threads waiting on one thread. Below is a simple example showing a thread waiting on a lock from another thread:

          Trx id counter 9498
          Purge done for trx's n:o < 9494 undo n:o < 0 state: running but idle
          History list length 4
          ---TRANSACTION 9494, not started
          MySQL thread id 1, OS thread handle 0x12ff05000, query id 33 localhost root init
          show engine innodb status
          ---TRANSACTION 9497, ACTIVE 5 sec starting index read
          mysql tables in use 1, locked 1
          LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
          MySQL thread id 3, OS thread handle 0x12ffab000, query id 32 localhost root updating
          update mytable set a = 6 where a = 1
          RECORD LOCKS space id 6 page no 3 n bits 72 index `GEN_CLUST_INDEX` of table `test`.`mytable` trx id 9497 lock_mode X waiting
          Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
          0: len 6; hex 000000000200; asc ;;
          1: len 6; hex 000000002517; asc % ;;
          2: len 7; hex 13000001410110; asc A ;;
          3: len 4; hex 80000005; asc ;;
          ---TRANSACTION 9495, ACTIVE 67 sec
          2 lock struct(s), heap size 376, 4 row lock(s), undo log entries 1
          MySQL thread id 2, OS thread handle 0x12ff68000, query id 22 localhost root cleaning up
          You can see three threads here:

          1) Transaction 9494; this thread ran the "show engine innodb status \G" command, which gave me the output.
          2) Transaction 9497; this thread is attempting to update a record in "mytable", which is locked. Note the "TRX HAS BEEN WAITING 5 SEC FOR THIS LOCK TO BE GRANTED" line, which tells you what is being blocked.
          3) Transaction 9495; this thread is performing an update on the same "mytable", which is blocking the thread listed above. You can tell it is this one because it says "ACTIVE 5 sec" after the transaction number, meaning that the transaction is actively running (and not blocked like the previous one).

          So basically you need to weed through your output and try to determine what (if anything) is blocking the other queries and causing the backup in your server.

          As for the cache stampede, what you would need to do there is going through the same output and look for queries that you know are populating a cache. So if you see a ton of cache queries, that could mean there is a cache stampede occurring. This is subjective, as the caches will likely be populating throughout the day in many cases. But usually there should not be a lot at once, as the nature of a caching query means it is pulling a lot of data normally, so having a bunch at once can cause heavy load.
          Last edited by scott.nemes; 10-08-2013, 10:53 AM.
          Scott Nemes