The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Improving Joomla Performance - HELP!

Discussion in 'Workarounds and Optimization' started by sbehrends, Apr 26, 2011.

  1. sbehrends

    sbehrends Member

    Joined:
    Apr 26, 2011
    Messages:
    16
    Likes Received:
    0
    Trophy Points:
    1
    We design and host Joomla websites. There are also just regular PHP and HTML sites and email as well. We have a couple cPanel servers to split up load. I'm now trying to improve our configs and setups on the servers to make sure they are optimized.

    Our setup consists of two of these servers ...

    Server: virtual in VMware
    CPUs: 2 vCPUs
    Memory: 4GB
    OS: CentOS/cPanel
    HD: everything in one big partition


    Any tips on my.cnf for our setup? What about php.ini? Anything else I should be thinking about? At what point do I consider creating a database server just for the Joomla sites? Sounds like a hassle at this point to me. This AM we have our CPUs showing 120 for load due to one sites being hammered! OUCH! I added the lines below to my.cnf (which was all but blank) and it brought the load down to 2. Please shout out if you have any other tips!

    query_cache_limit=2M
    query_cache_size=64M
    query_cache_type=1
    max_connections = 20000
    max_user_connections = 100000
    max_connect_errors = 99999999
     
  2. Infopro

    Infopro cPanel Sr. Product Evangelist
    Staff Member

    Joined:
    May 20, 2003
    Messages:
    14,474
    Likes Received:
    202
    Trophy Points:
    63
    Location:
    Pennsylvania
    cPanel Access Level:
    Root Administrator
    Twitter:
    I tend to doubt this. It may have gone down right after restart sure. You should not make any changes without investigating what they do. For example, I'll just quote a warning seen on many sites:

    There are multiple other threads in this section of the forum where users have gone around and around with these settings and used the tuner to assist in finding proper settings. You should read thru those for sure.
     
  3. sbehrends

    sbehrends Member

    Joined:
    Apr 26, 2011
    Messages:
    16
    Likes Received:
    0
    Trophy Points:
    1
    @infopro, are you saying that just restarting the MySQL process after changing the config is what helped my load? How could that be? I could see at first - since no connections could be made while restarting MySQL, but wouldn't the load just jump right back up?
     
  4. Infopro

    Infopro cPanel Sr. Product Evangelist
    Staff Member

    Joined:
    May 20, 2003
    Messages:
    14,474
    Likes Received:
    202
    Trophy Points:
    63
    Location:
    Pennsylvania
    cPanel Access Level:
    Root Administrator
    Twitter:
    Depends on whats causing that load I guess. Editing the my.cnf blind, is not the best course of action, IMO.
     
  5. sbehrends

    sbehrends Member

    Joined:
    Apr 26, 2011
    Messages:
    16
    Likes Received:
    0
    Trophy Points:
    1
    I agree 100%! That's why I'm coming here for input. I had found some blogs/forums regarding what to set the values at, however, they don't fit my scenario. Do I follow generic Joomla based ones? Do I follow generic cPanel based ones? What kind of resources do they have vs me. There are so many variables involved, again, that's why I'm asking.
     
  6. Infopro

    Infopro cPanel Sr. Product Evangelist
    Staff Member

    Joined:
    May 20, 2003
    Messages:
    14,474
    Likes Received:
    202
    Trophy Points:
    63
    Location:
    Pennsylvania
    cPanel Access Level:
    Root Administrator
    Twitter:
    As suggested, there are multiple threads on this topic, here, and across the Web that if you read thru enough of them you will get a better idea of what you're going into. As one example: http://forums.cpanel.net/f189/http-high-cpu-load-mysql-tuner-question-202021.html


    Personally, I like to use the KISS method on this sort of thing and in this case would have my sights not set on that my.cnf, but that Joomla site itself. Optimizing my.cnf is a good thing to do, don't get me wrong. But, if I was to guess I'd say you've got problems of some sort on that Joomla site, certainly more of a problem than an empty my.cnf


    Managing a server takes a lot more reading than asking. You can ask, and people will answer. But if you just go with the answer and not read up on what needs done and why, you'll always be asking.

    I don't mean for that to sound harsh of course, its just that you need to understand your system and how to tweak it to your own needs. Just because a Joomla site is pounding the server doesn't mean the server is not configured correctly.

    That said I could be completely off base and you just have a very popular Joomla site. Still, I'd be more inclined to start there if I was you.
     
  7. sbehrends

    sbehrends Member

    Joined:
    Apr 26, 2011
    Messages:
    16
    Likes Received:
    0
    Trophy Points:
    1
    thanks for the link. i'm new(er) to LAMP, and I'm just trying to gather data from different areas and then evaluate from there the suggestions, and make changes accordingly. This AM was things I was planning on testing and had to put into action. I didn't have much to lose with 120 for load! The console was hardly responding.

    btw, here is the output from MySQLTuner. As you mentioned, I am WAY too high for possible memory usage for how much memory I have.

    >> MySQLTuner 1.1.2 - Major Hayden <major@mhtx.net>
    >> Bug reports, feature requests, and downloads at http://mysqltuner.com/
    >> Run with '--help' for additional options and output filtering

    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.0.92-community
    [OK] Operating on 64-bit architecture

    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 145M (Tables: 2048)
    [--] Data in InnoDB tables: 224K (Tables: 9)
    [!!] Total fragmented tables: 211

    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 3h 19m 48s (775K q [64.668 qps], 19K conn, TX: 1B, RX: 115M)
    [--] Reads / Writes: 54% / 46%
    [--] Total buffers: 98.0M global + 2.7M per thread (16384 max threads)
    [!!] Maximum possible memory usage: 44.1G (1143% of installed RAM)
    [OK] Slow queries: 0% (0/775K)
    [OK] Highest usage of available connections: 0% (33/16384)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/30.9M
    [OK] Key buffer hit rate: 99.1% (1M cached / 17K reads)
    [OK] Query cache efficiency: 79.9% (484K cached / 606K selects)
    [!!] Query cache prunes per day: 12533
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 13K sorts)
    [!!] Joins performed without indexes: 226
    [!!] Temporary tables created on disk: 39% (1K on disk / 4K total)
    [!!] Thread cache is disabled
    [!!] Table cache hit rate: 0% (64 open / 7K opened)
    [OK] Open file limit used: 0% (127/65K)
    [OK] Table locks acquired immediately: 99% (374K immediate / 374K locks)
    [OK] InnoDB data size / buffer pool: 224.0K/8.0M

    -------- Recommendations -----------------------------------------------------
    General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Reduce your overall MySQL memory footprint for system stability
    Enable the slow query log to troubleshoot bad queries
    Adjust your join queries to always utilize indexes
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Set thread_cache_size to 4 as a starting value
    Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
    *** MySQL's maximum memory usage is dangerously high ***
    *** Add RAM before increasing MySQL buffer variables ***
    query_cache_size (> 64M)
    join_buffer_size (> 128.0K, or always use indexes with joins)
    tmp_table_size (> 32M)
    max_heap_table_size (> 16M)
    thread_cache_size (start at 4)
    table_cache (> 64)
     
  8. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    Could we see your full /etc/my.cnf file first before providing input? You stated the file was all but blank before you added those initial values, but that implies it wasn't entirely blank. As such, I would need to see the full /etc/my.cnf file first. Thanks!
     
  9. sbehrends

    sbehrends Member

    Joined:
    Apr 26, 2011
    Messages:
    16
    Likes Received:
    0
    Trophy Points:
    1
    yeah, sorry. the only thing in there was "old-passwords = 1" :) and i did actually forget the last line somehow. see below. thanks a million for any input you can provide!

    [mysqld]
    old-passwords = 1
    query_cache_limit=2M
    query_cache_size=64M
    query_cache_type=1
    max_connections = 20000
    max_user_connections = 100000
    max_connect_errors = 99999999
    interactive_timeout=300
     
  10. sbehrends

    sbehrends Member

    Joined:
    Apr 26, 2011
    Messages:
    16
    Likes Received:
    0
    Trophy Points:
    1
    oh, and @infopro, you are 100% correct about it's the site as well. my design team is looking into that as well. but it's easy for us to just point fingers at each other, when really we can both improve on our setups!
     
  11. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    I would change to the following, then restart MySQL:

    Code:
    [mysqld]
    interactive_timeout=300
    max_connections=512
    max_user_connections=250
    max_heap_table_size=40M
    old-passwords=1
    query_cache_limit=2M
    query_cache_size=80M
    query_cache_type=1
    table_cache=80
    thread_cache_size=4
    tmp_table_size=40M
    wait_timeout=300
    First, I've moved the variables to be in alphabetical order to facilitate knowing which ones are there (I started doing this due to the high number of variables some people appear to add to their /etc/my.cnf).

    Next, I've added the other variables that mysqltuner.pl suggested other than join_buffer_size as I generally avoid adding that unless necessary. I've removed max_connect_errors as that value is default 10 and I cannot see any reason you'd want to allow that high of a number of errors for the connection. connect_timeout determines if a user is dropped and counted as one of the max_connect_error. In MySQL 5.0, the connect_timeout is set to 5 and any user who cannot complete the connection in 5 seconds would be dropped. If that same user tries 10 times and has a connect_timeout each of those 10 times (so 5 seconds without establishing a working connection), then max_connect_errors would permanently block the user. The purpose of max_connect_errors is to prevent a DoS attack for MySQL and the value that you had before would be extremely high and pose a risk. As such, I've simply removed the value entirely to have it go back to the default 10. If you have an issue with poor connections to the server by several other servers, then increase connect_timeout from 5 to 10 instead (10 is the default for MySQL 5.1, so 10 might be warranted in MySQL 5.0). To read about connect_timeout, you can view this location:

    MySQL :: MySQL 5.1 Reference Manual :: 5.1.3 Server System Variables

    Next, I've decreased max_connections and max_user_connections substantially. What it should be set for max_connections is the same value you have for Apache's MaxClients setting. If you are unsure that value, you should be able to see the MaxClients in WHM > Apache Configuration > Global Configuration area. The max_connections should never be higher than Apache's MaxClients because MySQL cannot establish more connections than Apache has available to it. Of course, if you have higher than 500 or 512 for MaxClients, feel free to increase max_connections again. If you want to re-increase max_user_connections, you can also do that, but having those half of max_connections is a good baseline.

    Finally, I've added wait_timeout since you already have interactive_timeout and set the same value of 300 for it that you have for interactive_timeout variable. For what those settings mean, wait_timeout is the amount of seconds of inactivity before MySQL will close a non-interactive connection, while interactive_timeout works the same but for interactive sessions such as MySQL shell.

    Finally, I wanted to note that I previously did a MySQL Optimization presentation at the cPanel conference last year. You might find it of use:

    MySQL Optimization | cPanel Video Site

    Edit: Oh also, you would want to enable the slow query log. Since you are using MySQL 5.0, here are the steps to do that.

    1. Put the following into /etc/my.cnf file

    Code:
    log-slow-queries=/var/lib/mysql/slow.log
    I would place it alphabetically in that file, then save the file.

    2. Create the slow log file with the right permissions and ownership:

    Code:
    cd /var/lib/mysql && touch slow.log
    chown mysql:mysql slow.log && chmod 660 slow.log
    3. Restart MySQL after adding the logging:

    Code:
    /etc/init.d/mysql restart
    Finally, please wait 24 hours before running mysqltuner.pl again to allow time for it to obtain sufficient MySQL history to provide applicable recommendations.
     
  12. sbehrends

    sbehrends Member

    Joined:
    Apr 26, 2011
    Messages:
    16
    Likes Received:
    0
    Trophy Points:
    1
    Tristan, thanks so much for the detailed information on evertying. I'll look into the settings you offered and watch the video as well!
     
  13. sbehrends

    sbehrends Member

    Joined:
    Apr 26, 2011
    Messages:
    16
    Likes Received:
    0
    Trophy Points:
    1
    do i need to be worried about the 200+ fragmented databases? i'm trying to find a way to (hopefully) easily defrag those without touching every database individually. also, has anyone heard of a database breaking after fragmenting?
     
  14. sbehrends

    sbehrends Member

    Joined:
    Apr 26, 2011
    Messages:
    16
    Likes Received:
    0
    Trophy Points:
    1
    i checked my Apache MaxClients and it was set to 150. Would you recommend upping that?
     
  15. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    I would recommend increasing MaxClients to 500 or 512 along with ServerLimit to that same value in WHM > Apache Configuration > Global Configuration area.

    As for optimizing the databases to defragment them, you can try running this command in root SSH to do so (please note that it might take awhile to run):

    Code:
    mysqlcheck -u root --auto-repair --optimize --all-databases
     
  16. sbehrends

    sbehrends Member

    Joined:
    Apr 26, 2011
    Messages:
    16
    Likes Received:
    0
    Trophy Points:
    1
    a while being a couple hours? several hours? a couple days? a month or two? ;-) i won't hold you to an answer too hard since that's all but impossible for you to nail down.

    awesome info! you've been a huge help tristan!
     
  17. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    Usually less than 30 minutes, but it could be a couple of hours if you have a high number of databases with a lot of tables for it to scan for optimization.
     
  18. sbehrends

    sbehrends Member

    Joined:
    Apr 26, 2011
    Messages:
    16
    Likes Received:
    0
    Trophy Points:
    1
    here is the updated mysqltuner info. looks like they are suggesting upping a few items. also, i still need to up my connections in Apache, but have to run through the rebuild process to do it.

    first ... my.cnf
    [mysqld]
    interactive_timeout=300
    max_connections=512
    max_user_connections=250
    max_heap_table_size=40M
    old-passwords=1
    query_cache_limit=2M
    query_cache_size=80M
    query_cache_type=1
    table_cache=80
    thread_cache_size=4
    tmp_table_size=40M
    wait_timeout=300
    log-slow-queries=/var/lib/mysql/slow.log



    >> MySQLTuner 1.1.2 - Major Hayden <major@mhtx.net>
    >> Bug reports, feature requests, and downloads at http://mysqltuner.com/
    >> Run with '--help' for additional options and output filtering

    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.0.92-community-log
    [OK] Operating on 64-bit architecture

    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 153M (Tables: 2048)
    [--] Data in InnoDB tables: 224K (Tables: 9)
    [!!] Total fragmented tables: 222

    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 3h 22m 3s (269K q [22.232 qps], 9K conn, TX: 368M, RX: 42M)
    [--] Reads / Writes: 63% / 37%
    [--] Total buffers: 138.0M global + 2.7M per thread (512 max threads)
    [OK] Maximum possible memory usage: 1.5G (39% of installed RAM)
    [OK] Slow queries: 0% (0/269K)
    [OK] Highest usage of available connections: 6% (33/512)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/31.6M
    [OK] Key buffer hit rate: 98.8% (1M cached / 14K reads)
    [OK] Query cache efficiency: 59.6% (117K cached / 196K selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 9K sorts)
    [!!] Joins performed without indexes: 402
    [!!] Temporary tables created on disk: 37% (1K on disk / 3K total)
    [OK] Thread cache hit rate: 97% (267 created / 9K connections)
    [!!] Table cache hit rate: 1% (80 open / 5K opened)
    [OK] Open file limit used: 6% (159/2K)
    [OK] Table locks acquired immediately: 99% (237K immediate / 237K locks)
    [OK] InnoDB data size / buffer pool: 224.0K/8.0M

    -------- Recommendations -----------------------------------------------------
    General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Adjust your join queries to always utilize indexes
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
    join_buffer_size (> 128.0K, or always use indexes with joins)
    tmp_table_size (> 40M)
    max_heap_table_size (> 40M)
    table_cache (> 80)
     
  19. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    To increase MaxClients and ServerLimit for Apache, you do not have to recompile Apache. WHM > Apache Configuration > Global Configuration will only rebuild Apache and takes about 2 minutes to run. It basically distills and then processes the /scripts/rebuildhttpdconf command, which only takes the new settings and adds them into the configuration and pulls the data from /var/cpanel/userdata for accounts. I'm just mentioning that in case you believed it would be like the EasyApache recompile process, which can take 25-50 minutes, since rebuilding Apache for MaxClients only takes about 2 minutes at the most.

    As for the settings, please change to the following:

    Code:
    [mysqld]
    interactive_timeout=300
    log-slow-queries=/var/lib/mysql/slow.log
    max_connections=512
    max_user_connections=250
    max_heap_table_size=60M
    old-passwords=1
    query_cache_limit=2M
    query_cache_size=80M
    query_cache_type=1
    table_cache=100
    thread_cache_size=4
    tmp_table_size=60M
    wait_timeout=300
    Also, please wait at least 24 hours this time to re-run mysqltuner.pl instead. The notice indicates it wasn't at least 24 hours since the last run.
     
  20. sbehrends

    sbehrends Member

    Joined:
    Apr 26, 2011
    Messages:
    16
    Likes Received:
    0
    Trophy Points:
    1
    thanks, cPanelTristan. I will wait this time. I thought it was saying to wait 24 hours after you make changes to my.cnf for the data to really update. I was just running occasionally to see if I could almost see it change in real time then. Now I better understand it. Thanks so much!

    Also, I found the following script which optimizes databases only if they are defragmented and skips the clean ones! I ran it on my old retired cPanel server (which the web interface doesn't work anymore, but the databases and stuff are still there) and the script ran in minutes! I have a lot of tables (2000+) but there really isn't that much size (~150MB)

    Optimize only fragmented tables in MySQL | Me in IT
     
Loading...
Similar Threads - Improving Joomla Performance
  1. Takumi80
    Replies:
    1
    Views:
    699

Share This Page