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

MySQL Tuning

Discussion in 'General PHP and MySQL Discussions' started by LPH, Dec 10, 2013.

  1. LPH

    LPH Well-Known Member

    Hi,

    I'm having a heck of a time. I've put this off because of more important issues but now I'm fussing :)

    The sites are hosted on a digital ocean droplet - Ubuntu. Sadly the TTFB is very poor - some times up to 5 seconds. After changing plugins and skins, Pingdom tools is showing the wait time now around 1.5 seconds but it can increase to over 5 seconds.

    Besides MySQL Tuner - what tools are available to figure out why the wait time is so long. After all, many sites are 300 ms and mine are up to ten times that ....

    Any suggestions?

    phpMyAdmin Monitor-1500.PNG

    phpMyAdmin Monitor All Status Variables.PNG

    phpMyAdmin Monitor Graphs.PNG

    phpMyAdmin Monitor - Queries.PNG

    phpMyAdmin Monitor - Live Queries.PNG
     
    Last edited: Dec 10, 2013
  2. DRaver

    DRaver Active Member

  3. Slavik

    Slavik XenForo Moderator Staff Member

    TTFB will probably be an issue with the web server rather than MySQL...
     
  4. LPH

    LPH Well-Known Member

    Thank you both for replying.

    Slow Query log reads as follows:

    Code:
    # Query_time: 2.289339  Lock_time: 0.000096 Rows_sent: 1  Rows_examined: 1931
    SET timestamp=1386917415;
    /*!50000 SELECT IFNULL(SUM(DATA_LENGTH),0) from information_schema.TABLES where ENGINE='InnoDB' */;
    
    
    The tuner script suggested an increase in table_definition_cache to 2048 but otherwise all else was fine.

    I can test a simple html file and it is returned in ms but the index.php is 2 seconds.
     
  5. Alteran Ancient

    Alteran Ancient Well-Known Member

    @LPH - I've been looking at transferring my site over to DigitalOcean. How good would you say their services are?
     
  6. LPH

    LPH Well-Known Member

    I'm very satisfied with Digital Oceans.

    My answer is not a powerful - awesome - because your happiness depends on your own knowledge and willingness to learn.

    The first few months were rough because I really didn't have time to read and learn. However, I took the time this past week to create and destroy numerous droplets so that I could learn how to manage certain tasks. I messed up many times ! PST ... don't use zpanel.

    The Digital Ocean articles are well written and the community is fast with answers. In fact, between here, StackOverflow, and Digital Ocean I was able to get NGINX installed and configured.

    The Digital Ocean support team answers tickets quickly. I had a few moments of head scratching with their 'console' and it's just better to open terminal on a Mac or use Putty or MobaXterm ....

    Anyway, things are not optimized but discovering the slow TTFB helped push me to make the change away from Apache.

    More important, the changes I've been making are starting to improve the load time, bring in traffic again, and start to get better SEO.

    I have not seen this type of layout in Google in years ;)

    Screen Shot 2013-12-18 at 1.58.27 PM.png

    Hope this helps you.
     
  7. Alteran Ancient

    Alteran Ancient Well-Known Member

    I must be doing my SEO right somehow, because explicit and high-probability searches on Google for my own site result in that level of results.

    I'm tempted to start running tests on DO soon. @LPH - Would you be able to tell me what droplet configuration you're using to run your production site on? RAM, CPUs, etc. and if you're load-balancing between multiple droplets?
     
  8. trizz

    trizz Active Member

    what tools did you use for these graphs?
     
  9. LPH

    LPH Well-Known Member

    Those were from one of the versions of phyMyAdmin installed on the droplet.
     
  10. tommydamic68

    tommydamic68 Well-Known Member

    @LPH - Just curious, how did you land that Google page, searching your sites URL direct or a specific key word referencing your sites content?
     
  11. LPH

    LPH Well-Known Member

    Vanity searches really don't do much for traffic except make one feel good :)

    I simply put in the domain name. Cheesy but always interesting to see how a SERP is built. For example, I dislike that Google promotes sites which "evaluate" another website above that particular website. They really need to clean up their algorithm ... but ... that is another story.
     
    tommydamic68 likes this.

Share This Page