Results 1 to 19 of 19
  1. #1
    Join Date
    Mar 2011
    Posts
    394

    Fine Tuning MySQL and Apache

    I'm running a linux cloud server with the following config
    1.2ghz Processor allocation
    752MB Ram

    The site loads slow and clicking a link almost freezes the page for a second. Also, the page loads could be much faster. We've been running mysqltuner and have pretty much optimized all slow queries. Is there anything we can do to fine tune the server for faster and more responsive?

    Httpd.conf

    Timeout 20
    KeepAlive Off
    MaxKeepAliveRequests 100
    KeepAliveTimeout 5
    <IfModule prefork.c>
    StartServers 2
    MinSpareServers 1
    MaxSpareServers 5
    MaxRequestsPerChild 4000
    ServerLimit 256
    MaxClients 256
    </IfModule>

    ---

    my.cnf

    [mysqld]
    datadir = /var/lib/mysql
    socket = /var/lib/mysql/mysql.sock
    user=mysql
    log-error=/var/log/mysqld.log
    log-slow-queries = /var/log/mysql-slow-queries
    query_cache_size=16M
    old_passwords=1

    [mysqld_safe]
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid

  2. #2
    You can always install Varnish Cache or some other similar accelerator. We're using it and love it.
    [ IncogNET LLC ] Privacy By Design [Liberty Lake, WA][Kansas City, MO][Allentown, PA][Naaldwijk, NL]
    [ Web Hosting | KVM VPS | Dedicated Servers | Domain Names | VPN | Censorship Resistance ]
    Services provided in the United States and Netherlands with privacy and freedom of speech being our top priority.


  3. #3
    Join Date
    Mar 2011
    Posts
    394
    Thanks Curtis. We can't really use an accelerator because most of the content is dynamic. In any case, which cache agent is the best?

    Also, I'm looking for more mysql & httpd tuning advice, improving response & reducing lag.

    Thanks a ton

  4. #4
    Join Date
    Dec 2004
    Location
    New York, NY
    Posts
    10,710
    You can still use the accelerator even if the content is dynamic (that's actually largely the point - it can cache dynamic content).

  5. #5
    Join Date
    Apr 2007
    Posts
    3,531
    I had a few servers that were getting close to needing an upgrade, they were configured with the typical LAMP stack. (Apache and PHP)

    Moved almost everything over to Nignx and PHP-FPM now and I actually canceled and consolidated some servers it reduced the load so much, so you can defiantly improve dynamic website performance.

    Caching is always an option, however your RAM is quite low so your probably not going to get the full benefits from that, defiantly worth a look into though.
    BotWars.io - Code the AI of your Battle Bot!

  6. #6
    Join Date
    Mar 2011
    Posts
    394
    Thanks guys, what's the best? Apc, xcache or eaacellerator?

    Also, I've come across posts with ideal settings for httpd & mysql but nothing for 752mb

  7. #7
    Join Date
    Mar 2011
    Posts
    394
    Also, I don't think Ram is a problem because we rare exceed 450mb + with the above settings

  8. #8
    Join Date
    Nov 2006
    Location
    Pune, India
    Posts
    1,428
    You could try reducing MaxRequestsPerChild further.

    Using cache will increase RAM usage and small caches due to less RAM will be pointless at times, as cache misses will be high. You can always try out all of them , we have had good experience with eAccelerator and Xcache. APC is going to be inbuilt in PHP 6, so they must be doing something good , although we have never tried it.
    LeapSwitch Networks Pvt. Ltd. - Managed VPS / Dedicated Servers India
    CloudJiffy PaaS - Wordpress Cluster Hosting
    █ Shared, Reseller, VPS, Dedicated Servers, Colocation
    AS132335 - India - USA - Germany - Spain - Portugal - Ukraine

  9. #9
    Join Date
    Mar 2011
    Posts
    394
    Great, we'll give eaccelerator and xcache a shot.

    I tuned the apache to the following, seemed to help, let me know if theres anything else i can do


    Timeout 20
    KeepAlive On
    MaxKeepAliveRequests 100
    KeepAliveTimeout 3
    <IfModule prefork.c>
    StartServers 3
    MinSpareServers 3
    MaxSpareServers 10
    MaxRequestsPerChild 1000
    ServerLimit 256
    MaxClients 256
    </IfModule>

  10. #10
    Join Date
    Apr 2011
    Location
    [root@host ~]
    Posts
    108
    Revo4 - The Revolution In Clustered Hosting.
    Redundant H-Sphere Linux Hosting w/ True 99.9% Uptime.
    Why Not Get Started Today?
    corey @ revo4.com

  11. #11
    Join Date
    Dec 2005
    Posts
    3,110
    Have you thought about dropping MPM Prefork for MPM Worker instead? You will have much more consistent memory usage which is important, especially for a system with a small amount of RAM.

    Prefork is good, but can use up a lot of memory when you get a burst of staffic and can lead to instability.

  12. #12
    Join Date
    Mar 2011
    Posts
    394
    Ill check out apc. Thanks

    About using worker, I thought that wasn't advisable on a cloud vps, isn't worker more for high end threaded servers?

    We upgraded our cloud a bit. We're now at 1.8ghz with 1.2gigs of ram. However, I don't see much improvement in speed.

    We've set max-connections to 256 for mysql and the httpd has been configured for 256 maxserver and 2000 max requests per child.

    We're montoring tuner & primer for mysql recommendations but I'm wondering what the ideal config for httpd should be. its tricky since httpd & mysql share the ram.

  13. #13
    Join Date
    Mar 2011
    Posts
    394
    I think somethings off

    this is the output of pc

    [root@server ~]# ps -ylC httpd --sort:rss
    S UID PID PPID C PRI NI RSS SZ WCHAN TTY TIME CMD
    S 48 27795 27794 0 78 0 4380 59417 494150 ? 00:00:00 httpd
    S 48 27799 27794 0 75 0 12608 79886 semtim ? 00:00:00 httpd
    S 48 27856 27794 0 75 0 12644 79882 - ? 00:00:00 httpd
    S 48 27806 27794 0 75 0 12684 79884 semtim ? 00:00:00 httpd
    S 48 27798 27794 0 75 0 12756 79884 - ? 00:00:00 httpd
    S 48 27797 27794 0 76 0 12996 79948 429493 ? 00:00:00 httpd
    S 48 27800 27794 0 75 0 13276 80023 semtim ? 00:00:00 httpd
    S 48 27808 27794 0 75 0 13280 79961 semtim ? 00:00:00 httpd
    S 48 27805 27794 0 75 0 13396 80030 semtim ? 00:00:00 httpd
    S 0 27794 1 0 78 0 16212 79597 - ? 00:00:00 httpd
    Output of free -m is

    [root@server ~]# free -m
    total used free shared buffers cached
    Mem: 1128 1069 58 0 222 507
    -/+ buffers/cache: 339 788
    Swap: 203 0 203
    Our current httpd.conf config is

    KeepAlive = On
    MaxKeepAliveRequests = 80
    KeepAliveTimeout = 2
    Timeout = 20
    <IfModule prefork.c>
    StartServers 3
    MinSpareServers 3
    MaxSpareServers 8
    ServerLimit 100
    MaxClients 100
    MaxRequestsPerChild 2000
    </IfModule>

    and my.cnf is

    mysqld]
    datadir = /var/lib/mysql
    socket = /var/lib/mysql/mysql.sock
    user=mysql
    log-error=/var/log/mysqld.log
    log-slow-queries = /var/log/mysql-slow-queries
    log_queries_not_using_indexes=1
    query_cache_size=16M
    max_connections=125
    long_query_time=5

    old_passwords=1
    table_cache=512
    read_buffer_size=1M
    join_buffer_size=2097152
    thread_cache_size=4


    [mysqld_safe]
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid
    ~

    Help appreciated

  14. #14
    Join Date
    Oct 2010
    Location
    Copenhagen
    Posts
    252
    Quote Originally Posted by zahirw View Post
    Ill check out apc. Thanks

    About using worker, I thought that wasn't advisable on a cloud vps, isn't worker more for high end threaded servers?

    We upgraded our cloud a bit. We're now at 1.8ghz with 1.2gigs of ram. However, I don't see much improvement in speed.

    We've set max-connections to 256 for mysql and the httpd has been configured for 256 maxserver and 2000 max requests per child.

    We're montoring tuner & primer for mysql recommendations but I'm wondering what the ideal config for httpd should be. its tricky since httpd & mysql share the ram.
    Do you have a link for the site in question?

    IO might be an issue. Does the top command indicate anything int his regard (hih io wait values)?

    How many virtual cpu cores have you access to?

    And what kind of processor architecture are you running on?

  15. #15
    Join Date
    Mar 2011
    Posts
    394
    I have to share this. My current config includes APC and mod_deflate. Mod_deflate is unbelievable. My server is 5x faster, ram commitments down to 50%. Unbelievable performance!

    I'd recommend these to any newbie.
    APC
    mod_deflate
    tuning via mysqltuner.pl and tuningprimer

    Just one question though, mtuner and primer both say
    join_buffer_size (> 4.0M, or always use indexes with joins)

    Are we supposed to increase the buffer size further??

  16. #16
    Join Date
    Jan 2011
    Posts
    453
    Hello,

    Ofnote, to the above given suggestions; If you are using Suphp, APC, Eaccelerator and other caching systems are of no use. They won't work with Suphp.

    Yes, you need to increase join_buffer_size according to the results. Also please have a check on the below given mysql optimization tutorial which will help you a lot in understanding mysql optimization.

    http://www.cpanel.net/videos/mysql-optimization/
    " Your work is to discover your work and then with all your heart to give yourself to it. "

    That's the mark of a true professional !

  17. #17
    Join Date
    Mar 2011
    Posts
    394
    Quote Originally Posted by cptechie View Post
    Hello,

    Ofnote, to the above given suggestions; If you are using Suphp, APC, Eaccelerator and other caching systems are of no use. They won't work with Suphp.

    Yes, you need to increase join_buffer_size according to the results. Also please have a check on the below given mysql optimization tutorial which will help you a lot in understanding mysql optimization.

    http://www.cpanel.net/videos/mysql-optimization/
    Dude! Brilliant vid! Thank you.

  18. #18
    Join Date
    Jan 2011
    Posts
    453
    Hello,

    No Problem Boss ! Good Luck !
    " Your work is to discover your work and then with all your heart to give yourself to it. "

    That's the mark of a true professional !

  19. #19
    Join Date
    Mar 2011
    Posts
    394
    That woman is good but she needs to cut back on the coffee & see what the outside world looks like!

    Surprising no mention of tuningprimer

Similar Threads

  1. Replies: 16
    Last Post: 04-21-2011, 10:21 AM
  2. Recommendations For Apache/MySQL/PHP Tuning Hire
    By mrbt in forum Running a Web Hosting Business
    Replies: 3
    Last Post: 03-27-2009, 11:28 PM
  3. Fine tuning my.cnf MySQL version 4.0.20
    By rizwan65 in forum Hosting Security and Technology
    Replies: 1
    Last Post: 11-05-2004, 05:20 PM
  4. Apache Fine Tuning
    By domingo in forum Other Offers & Requests
    Replies: 1
    Last Post: 12-13-2003, 12:35 PM
  5. Fine Tuning Apache for Speed and Security
    By domingo in forum Hosting Security and Technology
    Replies: 0
    Last Post: 12-13-2003, 03:06 AM

Posting Permissions

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