Results 1 to 20 of 20
  1. #1

    10/sec+ load time on MySQL pages, how I I make them load faster?

    I have a VPS1 (256MB) with LiquidWeb and I am noticing that my DB sites are taking 5-20 seconds to load. (vbulletin/Drupal (testing)). Since traffic is almost none existent shouldn't this VPS be able to handle MySQL with quick loading time? My PHP pages with includes and some dynamic code load instantly. How can I optimize the VPS so MySQL driven pages load instantly as well. I am using it for my own use so it's not a host and have about one e-mail check every 2-5 seconds. It's running cPanel Stable, but it's only MySQL that is dog slow.

    So again how can I optimize MySQL so page loading is faster (I am moving my site to MySQL backend so I need that instant loading like I get with php only pages now).

    Thanks

  2. #2
    Join Date
    Feb 2005
    Location
    Australia
    Posts
    233
    Any links you can give to a sample site would be helpful for people to see the types of scripts and operations involved.
    Koroush Ghazi
    Owner/Author
    TweakGuides.com
    Currently Hosted by: EuroVPS

  3. #3

  4. #4
    Join Date
    Jul 2005
    Location
    Australia
    Posts
    105
    Your forum loads fine in Aust..

  5. #5
    Running fast on the east coast..

  6. #6
    Join Date
    Sep 2002
    Location
    Top Secret
    Posts
    14,135
    Seems to be loading fine here, no problems at all.
    To test the true load time of your server, well, it's really easy:

    Before anything is called in your head includes
    Code:
    $time = microtime();
    $time = explode(" ", $time);
    $time = $time[1] + $time[0];
    $start = $time;
    At the very end of your document
    Code:
    $time = microtime();
    $time = explode(" ", $time);
    $time = $time[1] + $time[0];
    $finish = $time;
    $totaltime = ($finish - $start);
    printf ("This page took %f seconds to load.", $totaltime);
    This will tell you the precise time it took to compile and load your page. That's not going to count for network differences , etc, but it will at least give you a chance to see how long it takes to compile your page
    Tom Whiting, WHMCS Guru extraordinaire
    Linux problems? WHMCS Problems? Give me a shout
    Check out my WHMCS Addons

  7. #7
    Now it's starting to feel fast again. Could be the an oposite effect with not enough access the data isn't cached therefore making retrieval slower then accessing plain php files from disk (with live date, stock quote, and includes)?

  8. #8
    Join Date
    Mar 2006
    Location
    Albuquerque, New Mexico
    Posts
    143
    Its taking about 4.7s to load here. You might need to tune your my.cnf file. You might want to turn the amount of caching occuring from mysql. Also, if you can implement caching in your site, it would probably really help out a lot. When the site gets slow, whats your load average by the way?
    Nick Devito
    Atarack Communications, Inc - Xen-based VPS Services

  9. #9
    Join Date
    Jun 2006
    Location
    NYC / Memphis, TN
    Posts
    1,454
    Could also test persistent connections on and off depending on the amount of hits your taking and just see what difference it makes in performance.

    I have noticed this with some VPS' in the past with optimal setups and the problem was usually that the VPS load was going above 1.0+ and the VPS was slowing down so bad it was affecting MYSQL queries. What is the load average on your VPS? Could possibly just be the node you are on is taking a high load as well.
    PeakVPN.Com | Complete Privacy VPN | Cloud Hosting | Guaranteed Security | 1Gbps-10Gbps Unmetered
    PeakVPN | 31 VPN Servers | 17-Years Experience | Emergency 24/7 Support
    Visit us @ PeakVPN.Com (Coming SOON) | ASN: 3915

  10. #10
    Join Date
    Sep 2005
    Posts
    507
    Quote Originally Posted by serverorigin
    What is the load average on your VPS? Could possibly just be the node you are on is taking a high load as well.
    How do you test load on a particular node?

  11. #11
    from a load of 0.03 I just loaded drupal.macwrite.com which was slow loading like 5sec+ and then checked load and it's spiked to 0.13. This is a 256MB VPS SCSI drives, running cPanel. Logged in and WOW it just hit 0.22 (kept hitting uptime while the page was loading). This is looking really bad. I can't afford to upgrade to a 512MB VPS ($40/month more) without more site income.

  12. #12
    Join Date
    Dec 2004
    Location
    New York, NY
    Posts
    10,710
    Move to a shared hosting account. You will get better performance than on a VPS more often than not.

  13. #13
    Quote Originally Posted by layer0
    Move to a shared hosting account. You will get better performance than on a VPS more often than not.
    Problem is I have lots of sites and need the security controlling my own server has. Also I am reselling to a friend. Would going to 512MB solve the preformance issue (rather reduce it until I can go 2x512MB VPS's then finally to a SCSI RAIDed dedicated)?

    My sites are only 25MB total for DB space. Maybe it's Drupal (testing) and it's not the right choice. How do I configure the my.con file?

  14. #14
    Join Date
    Jun 2006
    Location
    NYC / Memphis, TN
    Posts
    1,454
    That load you are seeing is actually due to processor usage and not memory. I would ask your provider how they give out CPU. I would find a provider that allows fair share which is usually the best way. My guess is that your current provider is limiting your CPU and you can check if you have CPanel by viewing under "Service Status" - "Server Information" (What does the line: "Processor Speed:" read?). That will show the speed of your VPS. My guess is your probably only getting 200mhz or less. Fair share is a much better way to go in terms of performance. (If your host does proactive monitoring)
    Last edited by PeakVPN-KH; 10-15-2006 at 05:59 AM.
    PeakVPN.Com | Complete Privacy VPN | Cloud Hosting | Guaranteed Security | 1Gbps-10Gbps Unmetered
    PeakVPN | 31 VPN Servers | 17-Years Experience | Emergency 24/7 Support
    Visit us @ PeakVPN.Com (Coming SOON) | ASN: 3915

  15. #15
    Join Date
    Sep 2006
    Location
    Cardiff - United Kingdom
    Posts
    1,569
    Very slow in the United Kingdom. Yeah try linux tech's suggestion of measuring the time. You could also modify it a bit to measure the loading time of various lines/parts of your code - maybe the slow loading times are due to connecting to the MySQL database (etc)?

  16. #16
    Join Date
    Jun 2006
    Location
    NYC / Memphis, TN
    Posts
    1,454
    maybe the slow loading times are due to connecting to the MySQL database (etc)?
    The user stated in the first post it was MYSQL queries that were slow....
    PeakVPN.Com | Complete Privacy VPN | Cloud Hosting | Guaranteed Security | 1Gbps-10Gbps Unmetered
    PeakVPN | 31 VPN Servers | 17-Years Experience | Emergency 24/7 Support
    Visit us @ PeakVPN.Com (Coming SOON) | ASN: 3915

  17. #17
    Join Date
    Sep 2006
    Location
    Cardiff - United Kingdom
    Posts
    1,569
    Quote Originally Posted by serverorigin
    The user stated in the first post it was MYSQL queries that were slow....
    Ah sorry about that, nevermind me then..

  18. #18
    Ben H of Liquid Web support optimized it a bit last night (he was helping me with the performance of MySQL issues yesturday so we did allot of back n forth e-mail on the issue and suggestions). Seems vbulletin loads faster (http://forums.ferriesofbc.com) while drupal is the dog (http://drupal.macwrite.com). Also Ben said (excellent help as ussual Liquid Web) that it's a 300mhz CPU equiv. for $40 more I could go to a 512MB VPS which would also be 600mhz equiv I am guessing. Liquid Web puts there VPS on Dual Opteron 246's with 4GB of RAM split 16 ways for the 256MB and 8 ways for the 512MB. Ben H Liquid Web support said I should maybe consider a dedicated ($165/month) but then I would have no RAID just a backup SATA drive but 1GB RAM. I am not ready for that expense yet.
    Last edited by Mac Write; 10-15-2006 at 03:41 PM.

  19. #19
    Join Date
    Sep 2005
    Posts
    507
    Quote Originally Posted by Mac Write
    Ben H of Liquid Web support optimized it a bit last night (he was helping me with the performance of MySQL issues yesturday so we did allot of back n forth e-mail on the issue and suggestions). Seems vbulletin loads faster (http://forums.ferriesofbc.com) while drupal is the dog (http://drupal.macwrite.com). Also Ben said (excellent help as ussual Liquid Web) that it's a 300mhz CPU equiv. for $40 more I could go to a 512MB VPS which would also be 600mhz equiv I am guessing. Liquid Web puts there VPS on Dual Opteron 246's with 4GB of RAM split 16 ways for the 256MB and 8 ways for the 512MB. Ben H Liquid Web support said I should maybe consider a dedicated ($165/month) but then I would have no RAID just a backup SATA drive but 1GB RAM. I am not ready for that expense yet.
    So you are on LW's shared hosting now? Had a look at both your sites and they don't seem that high in activity. So the suggestions was to go to a dedicated web server when you need to run drupal? wow.

    Also, the bcferries site wasn't super fast to load either. I wonder if dedicated is the way to go when you need a significant step up from a shared environment.

  20. #20
    Quote Originally Posted by mealto
    So you are on LW's shared hosting now? Had a look at both your sites and they don't seem that high in activity. So the suggestions was to go to a dedicated web server when you need to run drupal? wow.

    Also, the bcferries site wasn't super fast to load either. I wonder if dedicated is the way to go when you need a significant step up from a shared environment.
    No I am on VPS1, but VPS 2 might be faster (thought I did run AMP on Jaguar on my Beige G3 266 with 768MB RAM when I was testing at home (now just use VPS). LW should offer a 1GB VPS at $150 range for people who want SCSI RAID but can't afford the $300+ for that yet.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •