0

i was facing heavy load on my server approx two years back and that time i have tried to optimize every single issues like query raid battery my.cnf etc and after that the server load was always under one inspite of heavy load. for last one year i was regularly handling more than 10000 users at any point of time without any issue but suddenly everthing changed last week. whm started showing heavy load on server.i guessed that raid battery backup is not working and requested my host to replace it and they replaced it and after that i was expecting the situation to improve but nothing changed and my host informed that your battery was fine but we have replaced it. then i thought my temp might be getting exhausted but its completely under control

   Every 0.5s: df -h /tmp                                                                                                                                     Sat Mar  8 03:51:29 2014

     Filesystem      Size  Used Avail Use% Mounted on
     /usr/tmpDSK     4.0G  376M  3.4G  10% /tmp

used slow.log to register the slow query and only few query are showing however i have set the slow log time to 5 sec. then i tried mysqltuner.pl and as i said i had already optimized to best of my knowledge two years back now its not recommending anything

my raid array was at 96% few days back and today it is showing 80%

      Unit  UnitType  Status         %RCmpl  %V/I/M  Stripe  Size(GB)  Cache  AVrfy
      ------------------------------------------------------------------------------
      u0    RAID-5    VERIFYING      -       80%     256K    3725.27   RiW    ON

       VPort Status         Unit Size      Type  Phy Encl-Slot    Model
       ------------------------------------------------------------------------------
       p0    OK             u0   1.82 TB   SATA  0   -            WDC xxxxxxxxxx-02W3
       p1    OK             u0   1.82 TB   SATA  1   -            WDC xxxxxxxxxx-01U1
       p2    OK             u0   1.82 TB   SATA  2   -            WDC xxxxxxxxxx-02W0

      Name  OnlineState  BBUReady  Status    Volt     Temp     Hours  LastCapTest
      ---------------------------------------------------------------------------
       bbu   On           Yes       OK        OK       OK       0      xx-xxx-xxxx

i am getting this from whm which shows heavy load from mysql

  20155 (Trace) (Kill)  mysql   0     181.3   19.9  /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --log-error=/var/lib/mysql/server.xxxxxxxx.com.err --open-files-limit=8376 --pid-file=/var/lib/mysql/server.xxxxxxx.com.pid

any suggestion to resolve this issue.

1

Please pay attention to the raid status: VERIFYING. The progress is displayed as %V/I/M. You can see the verify schedule with the show verify command parameter. As long as the raid is being verified I assume the io performance isn't great. You should use iostat or vmstat to get a better picture what is happening.

The cache is enabled for your raid unit. You might want to test if the overall performance is better if you only enable the write cache but disable the read cache Furthermore the bbu status seems to wrong - the battery has 0 hours estimated backup capacity.

  • i have seen this verifying status but its updating very slow and i dont think there is any effect of this on server load as you can see BBU ready is yes – Steeve Mar 8 '14 at 16:49
  • Verifying means: all data is being read and compared to the data on the other disks. This can have huge performance impact. – Jens W. Mar 8 '14 at 17:26
  • @JensW. makes a very good point. Verifying the RAID array would not only potentially make performance terrible, it could also give the illusion that MySQL was the cause when in fact it's only a symptom of the impaired I/O performance. Until the storage array is in a fully normal state, with everything it displays being correctly understood, tinkering with MySQL will be a waste of time. – Michael - sqlbot Mar 9 '14 at 0:37
  • 1
    @JensW. 100% agree friend i was doing my.cnf tinkering and as you you said once the raid stablized the performance normalized automatically – Steeve Mar 9 '14 at 6:48
  • Be aware that this issue will occur each time the raid controller does an auto verify. Take control and adjust the schedules. Maintenance windows are there for a good reason (or maybe to decide to turn off verify).And keep an eye on the estimated backup capacity. You don't want to loose data in case of an power surge. – Jens W. Mar 9 '14 at 11:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.