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

Super Slow Query

Discussion in 'Blogging' started by playercool, Jan 1, 2010.

  1. playercool

    playercool Junior Member

    Joined:
    Jan 4, 2009
    Messages:
    159
    Likes Received:
    31
    Back in May I started a blog and over time it just went nuts. Every month I was doubling in size. Every day more and more visitors and more and more money. It was on a pace that one single site that I hadn't touched since May could start bringing in big bucks. I had to switch over to a VPS which was fine because the site was easily paying for it. Then things went to shit. My site was constantly crashing and google took notice. They started to deindex the site slowly. Traffic went from 6k UV a day down to 2k a day. It was a horrible feeling and they are still slowly deindexing the site. If I knew what I was doing I think the site would be over 10k UV a day by now. Ohh well what can you do.

    The main problem is the slow query. It looks something like this:

    # Query_time: 14 Lock_time: 0 Rows_sent: 1 Rows_examined: 22406

    So it is searching the entire database for one single thing. This is taking the site forever and thus crashing it. My VPS guy says there is nothing I can do but fix the problem. He even upgraded my server big time for 24 hours and he said it was like sweeping the dust under a rug. It did no good. The spikes were so great it would still kill the server.

    Does anyone have any advice or input for me? I have no idea about all this technical stuff. He says:

    It is not necessary to rewrite your entire database, even if you just add indexing you can greatly speed up these queries.

    That is the guy from the VPS advice. That is great, the problem is I have no idea how to add indexing.

    I hate to see this site go under as google still views it as an authority. Anything I post gets indexed in a matter of minutes. I know I can use it as a launchpad for so many things. It crashes every other day though and it is just starting to drive me batty.

    What can I do?
     
  2. plunge

    plunge BANNED BANNED

    Joined:
    Dec 22, 2008
    Messages:
    162
    Likes Received:
    45
    hit me up on aim i'll give you some mysql stuff to change, 22000 rows is nothing, even for vps. :)
     
  3. xpressioniz

    xpressioniz Junior Member

    Joined:
    Jun 4, 2008
    Messages:
    121
    Likes Received:
    15
    I think the first thing you should do is look at your database structure and see if you can optimize the index, rewriting query should solve the problem if you can't change the structure. 22406 rows is pretty low for vps IMHO.
     
  4. plunge

    plunge BANNED BANNED

    Joined:
    Dec 22, 2008
    Messages:
    162
    Likes Received:
    45
    playercool asked me to help him out here so everyone can benefit.. everyone should thank him for that :)

    1. What software/scripts are you running? Wordpress/Joomla/etc?

    2. Can you enable general query logging? From the MySQL CLI:
    Code:
    SET GLOBAL general_log = 'ON';
    This will write a file with ALL the queries that are being run, then we can identify the culprits.

    If you're running Wordpress, install http://wordpress.org/extend/plugins/wp-super-cache/