Follow BlogTips via RSS Get BlogTips updates via Email Follow @SM4NP - Social Media for NonProfit

My blogging life for the past four days.
In one picture.

Posted on Jan 14th, 2011 by
debugging screens

As my friends would say: "Get a life! Get a real job!"

It was 3:30 am last night, well, this morning, when I finally found that the “MySQL server has gone away” error, can be cured by increasing the “wait_timeout parameter” from 30 to 250.

Left to do, is checking if the “key_buffer_size” and “table_cache” will reduce the CPU load, so I can actually have less PHP processes waiting in queue.

The things one will endure for a fast blog. Dah. :)

(If you are really curious, you can always click on the picture for a hires snapshot of my life…)




12 Comments to “My blogging life for the past four days.
In one picture.”

  1. Terence says:

    I would be very pleased to have some more information about what you were using and how you were trying to obtain these improvements.

    • Peter says:

      @Terence:
      I will write a post about it. In short: I found some of my sites were too intense on SQL queries and several of my sites returned an “SQLserver has gone away”-error. So I was tuning my server’s SQL parameters using Unix’ “TOP” command and PHPMyadmin to improve the caching and timeouts.

      Peter.

  2. Terence says:

    I must be going at this the wrong way since I don’t seem to be able to restart SQL if I alter the etc/my.cnf file in any way.

    • Peter says:

      @Terence:
      Yep, that is exactly what I was doing: changing the parameters in etc/my.cnf

      Any particular error messages?

  3. Terence says:

    Have you tried using mysqltuner.pl? (wget mysqltuner.pl)

    • Peter says:

      No, I found a reference to mysqltuner this weekend, when I was trying to figure out how to best tune SQL (I am by no means a system’s person). But have not tried it.

      My combined blog/system problems were pretty straight forward:
      - a problem with a WordPress plugin (earlier post)
      - a problem with a Drupal module (described in an upcoming post)
      - wait_timeout was too small (FeedAPI and FeedWordPress were timing out with “SQL server went away”)
      - also increased tmp_table_size table_cache and sort_buffer_size (which are stressed for the type of traffic I have)…

      p.

  4. Terence says:

    Sorry, I didn’t see your question there. It turned out to be pilot error. I had entered an incorrect parameter. I am just now waiting for 24 hrs after restarting SQL so that I can try and get some meaningful readings from mysqltuner.pl.

  5. Terence says:

    My SQL Tuner is quick to download (wget mysqltuner.pl) and easy to use (perl mysqltuner.pl) and provides easy to use instructions on which parameters to alter. My SQL Tuner doesn’t actually alter anything on your server by itself; it just monitors Apache/SQL activity and analyses the correct my.cnf to use for the given loading and traffic types. Looks pretty good right now, but I am not finished yet. You need to leave it about 24 hours in between each SQL restart for it to monitor enough traffic that it can make accurate analysis. The one thing I am missing and have not yet been able to find is an SQL Optimization for Dummys PDF or page anywhere. Everything I have found so far is either too techy or too simplistic. Any ideas?

    • Peter says:

      Yep, downloaded it last night. Could not be easier ;)

      I am not sure about your technical capability, but if it is like mine, I google’d several links that gave me some fragmentary insights here and there, but overall, don’t think mysql is for dummies to touch much. :)

  6. Terence says:

    I am quite technical, but even so, much of what I read went over my head. Its certainly not a simple subject but even Dummies have problems they can’t fix without help, and everyone has to start somewhere.

    • Peter says:

      True, true… And often sysadmins will hide behind the shield of the complexity not willing to reveal the “start of wisdom”.

      Luckily I have several sysadmins amongst my friends, and the common advice (on SQL) is though: “Beware of making changes. Sometimes by taking one bottleneck away, you will create another, and a worse.”…
      One told me “it is impossible to give a boiler plate tuning menu, as it really depends on what you do with SQL. Is like tuning a car. What do you want to do with this car? A truck won’t do if all you do is take it downtown to go shopping.”

      So what I do right now, is:
      - I limit myself to the number of parameters I would consider changing. I have about 5.
      - I read up on those parameters, what they represent, and what could cause statistics related to them to be too high/too low. — Sometimes, stats will give an abnormal number, but that might be just related to the kind of access you have (e.g. my db is 10 Gb, and I get hit a lot by search engines, so there will be a lot of random posts hit, that caches don’t get. On some of my sites, I do aggressive sitemapping, for 10,000 posts at a time. That will generate a lot of sequential access, which will disturb the ‘generic’ stats).
      - Once I am sure that the variable considered will have an effect on the stats, and is not due to the kind of traffic I have, I change one at a time. Wait for 24h (like you), and see what differences I see. If I don’t see any differences, I change it back to the previous setting.
      - I log every single change I make at server level (server, php, sql,…) so if I want to reverse, i know exactly what I did

      PS: there is actually a book called “SQL for dummies” ;)

Leave a Comment

*