47 {{ upvoteCount | shortNum }}
47 {{ upvoteCount | shortNum }}

LAMP Performance Tuning

by khtm
40 replies
We're looking to hire someone to help performance tune a LAMP server running Elgg CMS (PHP/MySQL).

It's a dedicated box and should have good enough hardware but has been running slower than normal lately.

Let me know if you know someone who can help us out

Cheers,
Kane
#elgg #lamp #mysql #performance #php #tuning
  • Profile picture of the author Matt.McLaughlin
    What type of slow? MySQL queries slower than normal or...More specific? If you want to PM me, please do, I may not check back on this topic.
    {{ DiscussionBoard.errors[4924386].message }}
    • Profile picture of the author khtm
      Page load times are slower than normal. We'd have some complaints too. Wish I had more specific metrics but don't at the moment.
      {{ DiscussionBoard.errors[4924535].message }}
  • Profile picture of the author wasabi20
    Switch to nginx or at least lighttpd !

    Apache suck !

    Simple suggestion
    {{ DiscussionBoard.errors[4926223].message }}
    • Profile picture of the author mojojuju
      Originally Posted by wasabi20 View Post

      Switch to nginx or at least lighttpd !

      Apache suck !

      Simple suggestion
      Apache doesn't suck. It works for a lot of applications just fine.

      Also, how do you know the OP's problem is due to running Apache and not nginx or lighttpd? There's other things that could be causing his problem besides the http server he's using.
      Signature

      :)

      {{ DiscussionBoard.errors[4929025].message }}
  • Profile picture of the author johnnyN
    i know of a good guy to help you.
    he is on elance.com his name is Aaron, his handle on elance is Zebb C , he totally improved speed on our website by optimizing server, even tho most of the problem was all the mysql calls.
    he installed memcache and mysql query caching (those were the main 2)
    {{ DiscussionBoard.errors[4926290].message }}
    • Profile picture of the author khtm
      Awesome, thanks for the advice, I'll contact Aaron

      Originally Posted by johnnyN View Post

      i know of a good guy to help you.
      he is on elance.com his name is Aaron, his handle on elance is Zebb C , he totally improved speed on our website by optimizing server, even tho most of the problem was all the mysql calls.
      he installed memcache and mysql query caching (those were the main 2)
      {{ DiscussionBoard.errors[4943114].message }}
  • Profile picture of the author Matt.McLaughlin
    Not really enough information. What is the URL of the website?

    website optimization
    Try using the above.
    {{ DiscussionBoard.errors[4941343].message }}
    • Profile picture of the author Christian Little
      Originally Posted by wasabi20 View Post

      Switch to nginx or at least lighttpd !

      Apache suck !

      Simple suggestion
      wasabi - simple suggestion for you: don't make blanket statements about something you obviously don't know anything about.



      Originally Posted by DEaFeYe View Post

      The best thing to do would be to have an architect take a look at your system and see if there's a way to split the work to evenly distribute things.

      Off the top of my head, generic tactics could be:
      - instead of doing direct SQL calls directly in the code, separate things into web services that run on independent servers
      - host your database outside of where you host your application
      - split the project into smaller applications, serve each independently behind a proxy, like nginx
      -optimize ****ty code
      -use a load balancing system (Linode i know offers a nice clone / load balancer... I'm sure other services exist elsewhere)
      -serve static files through a cloud system like Amazon's S3
      Moving the database to a second server and linking them together won't necessarily solve the problem. If MySQL isn't setup nicely it will run into the exact same problem.

      And I'm not sure if splitting it into multiple applications is an option as the OP said he's using some CMS system, chances are they don't know how to separate it out (if it's even possible with that specific platforms).





      Originally Posted by khtm View Post

      We're looking to hire someone to help performance tune a LAMP server running Elgg CMS (PHP/MySQL).

      It's a dedicated box and should have good enough hardware but has been running slower than normal lately.

      Let me know if you know someone who can help us out

      Cheers,
      Kane
      Originally Posted by khtm View Post

      Page load times are slower than normal. We'd have some complaints too. Wish I had more specific metrics but don't at the moment.
      You'll need to narrow it down a bit more to get help. I would speculate your problem is with MySQL.

      If the CMS you are using doesn't do any kind of caching by default, then it's really easy to rack up a huge number of mysql queries. Furthermore, if the queries that it runs aren't optimized then you'll run into big performance issues.

      Can you login to mysql and run this command and paste the output here: show status;

      That will output a huge table listing various things about MySQL which I can use to help diagnose your problem.

      Another thing that could be part of the problem is how apache is configured. What is the current min/max server setting? How many clients are enabled?

      How much traffic are you getting every day?
      Signature
      cPanel Shared and Reseller Web Hosting - 30 Day Free Trial Exclusive for Warriors!
      {{ DiscussionBoard.errors[4941429].message }}
      • Profile picture of the author khtm
        We're using a CMS called Elgg (v1.5) with a ton of customizations. Our biggest table has over 30 M records and the MySQL db size is about 40 GB.

        We don't currently have any caching, other than what's built into Elgg. Apparently some Elgg users are using Memcache but it's "unsupported" and apparently buggy to use with Elgg.

        Here's the mysql "show status;":

        Code:
        mysql> show status;
+-----------------------------------+------------+
| Variable_name                     | Value      |
+-----------------------------------+------------+
| Aborted_clients                   | 48         | 
| Aborted_connects                  | 0          | 
| Binlog_cache_disk_use             | 0          | 
| Binlog_cache_use                  | 0          | 
| Bytes_received                    | 115        | 
| Bytes_sent                        | 181        | 
| Com_admin_commands                | 0          | 
| Com_alter_db                      | 0          | 
| Com_alter_table                   | 0          | 
| Com_analyze                       | 0          | 
| Com_backup_table                  | 0          | 
| Com_begin                         | 0          | 
| Com_call_procedure                | 0          | 
| Com_change_db                     | 0          | 
| Com_change_master                 | 0          | 
| Com_check                         | 0          | 
| Com_checksum                      | 0          | 
| Com_commit                        | 0          | 
| Com_create_db                     | 0          | 
| Com_create_function               | 0          | 
| Com_create_index                  | 0          | 
| Com_create_table                  | 0          | 
| Com_create_user                   | 0          | 
| Com_dealloc_sql                   | 0          | 
| Com_delete                        | 0          | 
| Com_delete_multi                  | 0          | 
| Com_do                            | 0          | 
| Com_drop_db                       | 0          | 
| Com_drop_function                 | 0          | 
| Com_drop_index                    | 0          | 
| Com_drop_table                    | 0          | 
| Com_drop_user                     | 0          | 
| Com_execute_sql                   | 0          | 
| Com_flush                         | 0          | 
| Com_grant                         | 0          | 
| Com_ha_close                      | 0          | 
| Com_ha_open                       | 0          | 
| Com_ha_read                       | 0          | 
| Com_help                          | 0          | 
| Com_insert                        | 0          | 
| Com_insert_select                 | 0          | 
| Com_kill                          | 0          | 
| Com_load                          | 0          | 
| Com_load_master_data              | 0          | 
| Com_load_master_table             | 0          | 
| Com_lock_tables                   | 0          | 
| Com_optimize                      | 0          | 
| Com_preload_keys                  | 0          | 
| Com_prepare_sql                   | 0          | 
| Com_purge                         | 0          | 
| Com_purge_before_date             | 0          | 
| Com_rename_table                  | 0          | 
| Com_repair                        | 0          | 
| Com_replace                       | 0          | 
| Com_replace_select                | 0          | 
| Com_reset                         | 0          | 
| Com_restore_table                 | 0          | 
| Com_revoke                        | 0          | 
| Com_revoke_all                    | 0          | 
| Com_rollback                      | 0          | 
| Com_savepoint                     | 0          | 
| Com_select                        | 1          | 
| Com_set_option                    | 0          | 
| Com_show_binlog_events            | 0          | 
| Com_show_binlogs                  | 0          | 
| Com_show_charsets                 | 0          | 
| Com_show_collations               | 0          | 
| Com_show_column_types             | 0          | 
| Com_show_create_db                | 0          | 
| Com_show_create_table             | 0          | 
| Com_show_databases                | 0          | 
| Com_show_errors                   | 0          | 
| Com_show_fields                   | 0          | 
| Com_show_grants                   | 0          | 
| Com_show_innodb_status            | 0          | 
| Com_show_keys                     | 0          | 
| Com_show_logs                     | 0          | 
| Com_show_master_status            | 0          | 
| Com_show_ndb_status               | 0          | 
| Com_show_new_master               | 0          | 
| Com_show_open_tables              | 0          | 
| Com_show_privileges               | 0          | 
| Com_show_processlist              | 0          | 
| Com_show_slave_hosts              | 0          | 
| Com_show_slave_status             | 0          | 
| Com_show_status                   | 1          | 
| Com_show_storage_engines          | 0          | 
| Com_show_tables                   | 0          | 
| Com_show_triggers                 | 0          | 
| Com_show_variables                | 0          | 
| Com_show_warnings                 | 0          | 
| Com_slave_start                   | 0          | 
| Com_slave_stop                    | 0          | 
| Com_stmt_close                    | 0          | 
| Com_stmt_execute                  | 0          | 
| Com_stmt_fetch                    | 0          | 
| Com_stmt_prepare                  | 0          | 
| Com_stmt_reset                    | 0          | 
| Com_stmt_send_long_data           | 0          | 
| Com_truncate                      | 0          | 
| Com_unlock_tables                 | 0          | 
| Com_update                        | 0          | 
| Com_update_multi                  | 0          | 
| Com_xa_commit                     | 0          | 
| Com_xa_end                        | 0          | 
| Com_xa_prepare                    | 0          | 
| Com_xa_recover                    | 0          | 
| Com_xa_rollback                   | 0          | 
| Com_xa_start                      | 0          | 
| Compression                       | OFF        | 
| Connections                       | 324473     | 
| Created_tmp_disk_tables           | 0          | 
| Created_tmp_files                 | 11         | 
| Created_tmp_tables                | 1          | 
| Delayed_errors                    | 0          | 
| Delayed_insert_threads            | 1          | 
| Delayed_writes                    | 128002     | 
| Flush_commands                    | 1          | 
| Handler_commit                    | 0          | 
| Handler_delete                    | 0          | 
| Handler_discover                  | 0          | 
| Handler_prepare                   | 0          | 
| Handler_read_first                | 0          | 
| Handler_read_key                  | 0          | 
| Handler_read_next                 | 0          | 
| Handler_read_prev                 | 0          | 
| Handler_read_rnd                  | 0          | 
| Handler_read_rnd_next             | 0          | 
| Handler_rollback                  | 0          | 
| Handler_savepoint                 | 0          | 
| Handler_savepoint_rollback        | 0          | 
| Handler_update                    | 0          | 
| Handler_write                     | 132        | 
| Innodb_buffer_pool_pages_data     | 29082      | 
| Innodb_buffer_pool_pages_dirty    | 7          | 
| Innodb_buffer_pool_pages_flushed  | 1440413    | 
| Innodb_buffer_pool_pages_free     | 1          | 
| Innodb_buffer_pool_pages_misc     | 3685       | 
| Innodb_buffer_pool_pages_total    | 32768      | 
| Innodb_buffer_pool_read_ahead_rnd | 22         | 
| Innodb_buffer_pool_read_ahead_seq | 221        | 
| Innodb_buffer_pool_read_requests  | 1306057212 | 
| Innodb_buffer_pool_reads          | 19008      | 
| Innodb_buffer_pool_wait_free      | 0          | 
| Innodb_buffer_pool_write_requests | 6065478    | 
| Innodb_data_fsyncs                | 924386     | 
| Innodb_data_pending_fsyncs        | 0          | 
| Innodb_data_pending_reads         | 0          | 
| Innodb_data_pending_writes        | 0          | 
| Innodb_data_read                  | 486690816  | 
| Innodb_data_reads                 | 22980      | 
| Innodb_data_writes                | 2214825    | 
| Innodb_data_written               | 570920960  | 
| Innodb_dblwr_pages_written        | 1440413    | 
| Innodb_dblwr_writes               | 68582      | 
| Innodb_log_waits                  | 0          | 
| Innodb_log_write_requests         | 450766     | 
| Innodb_log_writes                 | 751836     | 
| Innodb_os_log_fsyncs              | 787502     | 
| Innodb_os_log_pending_fsyncs      | 0          | 
| Innodb_os_log_pending_writes      | 0          | 
| Innodb_os_log_written             | 597989888  | 
| Innodb_page_size                  | 16384      | 
| Innodb_pages_created              | 640        | 
| Innodb_pages_read                 | 29572      | 
| Innodb_pages_written              | 1440413    | 
| Innodb_row_lock_current_waits     | 0          | 
| Innodb_row_lock_time              | 26898      | 
| Innodb_row_lock_time_avg          | 171        | 
| Innodb_row_lock_time_max          | 15695      | 
| Innodb_row_lock_waits             | 157        | 
| Innodb_rows_deleted               | 50343      | 
| Innodb_rows_inserted              | 100996     | 
| Innodb_rows_read                  | 3949466243 | 
| Innodb_rows_updated               | 533384     | 
| Key_blocks_not_flushed            | 0          | 
| Key_blocks_unused                 | 0          | 
| Key_blocks_used                   | 115980     | 
| Key_read_requests                 | 2809187346 | 
| Key_reads                         | 1728467    | 
| Key_write_requests                | 8358111    | 
| Key_writes                        | 7293883    | 
| Last_query_cost                   | 0.000000   | 
| Max_used_connections              | 35         | 
| Not_flushed_delayed_rows          | 0          | 
| Open_files                        | 423        | 
| Open_streams                      | 0          | 
| Open_tables                       | 324        | 
| Opened_tables                     | 0          | 
| Prepared_stmt_count               | 0          | 
| Qcache_free_blocks                | 3879       | 
| Qcache_free_memory                | 124988128  | 
| Qcache_hits                       | 24313773   | 
| Qcache_inserts                    | 43212497   | 
| Qcache_lowmem_prunes              | 82194      | 
| Qcache_not_cached                 | 1846742    | 
| Qcache_queries_in_cache           | 6958       | 
| Qcache_total_blocks               | 17871      | 
| Queries                           | 74285949   | 
| Questions                         | 2          | 
| Rpl_status                        | NULL       | 
| Select_full_join                  | 0          | 
| Select_full_range_join            | 0          | 
| Select_range                      | 0          | 
| Select_range_check                | 0          | 
| Select_scan                       | 1          | 
| Slave_open_temp_tables            | 0          | 
| Slave_retried_transactions        | 0          | 
| Slave_running                     | OFF        | 
| Slow_launch_threads               | 0          | 
| Slow_queries                      | 0          | 
| Sort_merge_passes                 | 0          | 
| Sort_range                        | 0          | 
| Sort_rows                         | 0          | 
| Sort_scan                         | 0          | 
| Table_locks_immediate             | 159563874  | 
| Table_locks_waited                | 28507      | 
| Tc_log_max_pages_used             | 0          | 
| Tc_log_page_size                  | 0          | 
| Tc_log_page_waits                 | 0          | 
| Threads_cached                    | 28         | 
| Threads_connected                 | 8          | 
| Threads_created                   | 35         | 
| Threads_running                   | 2          | 
| Uptime                            | 349555     | 
+-----------------------------------+------------+
225 rows in set (0.00 sec)
        I'll have to get back to you on the other Q's later

        Thanks for your help!

        Originally Posted by Christian Little View Post

        And I'm not sure if splitting it into multiple applications is an option as the OP said he's using some CMS system, chances are they don't know how to separate it out (if it's even possible with that specific platforms).

        You'll need to narrow it down a bit more to get help. I would speculate your problem is with MySQL.

        If the CMS you are using doesn't do any kind of caching by default, then it's really easy to rack up a huge number of mysql queries. Furthermore, if the queries that it runs aren't optimized then you'll run into big performance issues.

        Can you login to mysql and run this command and paste the output here: show status;

        That will output a huge table listing various things about MySQL which I can use to help diagnose your problem.
        {{ DiscussionBoard.errors[4943237].message }}
      • Profile picture of the author khtm
        Originally Posted by Christian Little View Post

        Another thing that could be part of the problem is how apache is configured. What is the current min/max server setting? How many clients are enabled?
        I'm not sure how to determine what you're asking, but I did notice in httpd.conf it says:
        Code:
        RLimitCPU 240
        Is that a normal setting?
        {{ DiscussionBoard.errors[4944186].message }}
    • Profile picture of the author khtm
      It's SocialAdr.

      Thanks, I'll check out that website optimization service.

      Originally Posted by Matt.McLaughlin View Post

      Not really enough information. What is the URL of the website?

      website optimization
      Try using the above.
      {{ DiscussionBoard.errors[4943192].message }}
  • Profile picture of the author mojojuju
    Originally Posted by khtm View Post

    Page load times are slower than normal. We'd have some complaints too. Wish I had more specific metrics but don't at the moment.
    That's not enough information for anybody here to help you. You haven't even revealed the site that people are experiencing slow load times with. All that people here can do is speculate on a number of factors which may not have anything to do with your problem.

    You might want to start by posting a URL.
    Signature

    :)

    {{ DiscussionBoard.errors[4941665].message }}
  • Profile picture of the author Matt.McLaughlin
    Good idea. Start with the basics before you pay someone to do a service you may not require.

    There are just too many "what if's" that only you can answer. New files, code changes, and, most important, your logs. Start there and see what is sucking up your resources. There are tons of programs/addons that will give you great stats on your server, but not knowing what you use to track your server's performance...it just makes this question one for someone you trust with server access.

    Just looking at your website, you have lots of stuff going on.
    98 page objects (http requests like this are most likely the cause of "slowness") 91 of which, images.
    {{ DiscussionBoard.errors[4943216].message }}
    • Profile picture of the author Ben Roy
      Thanks for suggestions and ideas so far. A few details: this is a relatively high-volume site with an Alexa around 5k or so. We've done a lot of work on speed/performance already. We use caching, pre-calculation in some areas, etc.

      I think the vexing part of the current performance issues is the lack of any resource-bounding. In other words, if we look at the monitors we have plenty of CPU lying around, plenty of memory available, but the pages are sometimes running slow anyway. That tend to lead me to think that splitting to multiple boxes won't really help, or at least not in a significant way.
      Signature
      {{ DiscussionBoard.errors[4943251].message }}
      • Profile picture of the author Christian Little
        Code:
        | Qcache_free_blocks                | 3879       | 
| Qcache_free_memory                | 124988128  | 
| Qcache_hits                       | 24313773   | 
| Qcache_inserts                    | 43212497   | 
| Qcache_lowmem_prunes              | 82194      | 
| Qcache_not_cached                 | 1846742    | 
| Qcache_queries_in_cache           | 6958       | 
| Qcache_total_blocks               | 17871      |
        OK, you have query caching enabled, but it's not running very well. What you need to do is add more space to your query cache, because it's cached 6958 queries but dropped 82,194 queries from the cache (which happens when your cache isn't big enough to hold everything).

        Code:
        | Threads_cached                    | 28         | 
| Threads_connected                 | 8          | 
| Threads_created                   | 35         | 
| Threads_running                   | 2          |
        You also have thread caching enabled, but it seems to be working fine with it's current settings so you are good there.

        Code:
        | Queries                           | 74285949   |
        Code:
        | Uptime                            | 349555     |
        OK...here is your problem. Your server (or just MySQL) was rebooted 349555 seconds ago (about 4 days ago). Since then, it has run 74 MILLION queries.

        That means on average you are running 18.5 million queries per day (that's 770,833 queries per hour or 12,847 queries per minute, or 214 per second).

        I can tell with reasonable confidence that is your problem with your existing hardware. The X3440 is a good processor, but that's too much for that server. We were running those processors on a lot of our servers and the server would just grind to a hault once you hit the number of queries you are running.

        So there's 2 solutions I can suggest off the tpo of my head:

        1) Increase your query cache. Right now your server has dumped 82,194 queries from the query cache since the last reboot and has currently cached 6,958 queries. That means it's only able to cache about 1 in 15 queries that it could cache.

        2) Upgrade your hardware. The X3440 isn't going to cut it, especially if you are growing. We recently upgraded all of our X3440 servers to E5620's and it made a massive difference. Faster bus, and since it's a dual quad core you get effectively 16 CPU's compared to the 8 you have now. The site you linked didn't say what hard drive you have, but you might want to look at a faster hard drive as well to improve read rates.

        Also - sorry to say but you are overpaying for your X3440 server by a significant amount. I can get the same server for $110/month. You can PM me for the data center we use, they have some really good rates and we've been with them for the past 15 months or so.

        You said you are monitoring CPU, how much CPU % does MySQL use on average? 30%? 50%? 100%? If it's 30% or higher you need to do what I've suggested above. When we did the upgrade to the E5620's and tweaked the caching, MySQL went from consuming 60% CPU constantly to about 5% CPU now.
        Signature
        cPanel Shared and Reseller Web Hosting - 30 Day Free Trial Exclusive for Warriors!
        {{ DiscussionBoard.errors[4943515].message }}
        • Profile picture of the author khtm
          Originally Posted by Christian Little View Post

          You said you are monitoring CPU, how much CPU % does MySQL use on average? 30%? 50%? 100%? If it's 30% or higher you need to do what I've suggested above. When we did the upgrade to the E5620's and tweaked the caching, MySQL went from consuming 60% CPU constantly to about 5% CPU now.
          Right now according to WHM "Process Manager" mysql is using 23 % CPU and 10% memory.

          But under Main > Server Status > Daily Process Log it shows mysql daily average as using 3% CPU and 10% memory.
          {{ DiscussionBoard.errors[4944455].message }}
  • Profile picture of the author khtm
    We're in the process of installing Munin too, so we can better monitor the performance and know for sure if changes we make are helping.

    Also, if it helps, this is our server - http://www.servint.net/v_dedicatedservers.php - "Solo Pro 850".
    {{ DiscussionBoard.errors[4943244].message }}
  • Profile picture of the author Christian Little
    Something else that jumped to mind, if you have tables with millions of rows are you frequently optimizing them? That makes a big difference when you get massive tables.

    I have a script I can send that checks all your tables and optimizes them as needed. Works wonders when you run it under cron.
    Signature
    cPanel Shared and Reseller Web Hosting - 30 Day Free Trial Exclusive for Warriors!
    {{ DiscussionBoard.errors[4943549].message }}
  • Profile picture of the author Christian Little
    That's the average it uses throughout the day. Try logging in via SSH and running the "top" command a few times throughout the day and see how much CPU "mysql" is using.
    Signature
    cPanel Shared and Reseller Web Hosting - 30 Day Free Trial Exclusive for Warriors!
    {{ DiscussionBoard.errors[4945283].message }}
    • Profile picture of the author khtm
      Ok, I did this and on average it seems fairly low, around the 3% that WHM "Daily Process Log" shows. I saw it bounce around a bit, the highest being around 12% and the lowest 1%.

      Originally Posted by Christian Little View Post

      That's the average it uses throughout the day. Try logging in via SSH and running the "top" command a few times throughout the day and see how much CPU "mysql" is using.
      {{ DiscussionBoard.errors[4950518].message }}
  • Profile picture of the author Christian Little
    Hmm...that's strange. What about apache, how many servers are setup?
    Signature
    cPanel Shared and Reseller Web Hosting - 30 Day Free Trial Exclusive for Warriors!
    {{ DiscussionBoard.errors[4951131].message }}
    • Profile picture of the author khtm
      AFAIK only one Apache server is setup. Check out the attachment.

      So I've got Munin installed now. My plan is to start doing the things you suggested and monitor it to see how much improvement each change makes. Does that sound ok?

      Cheers,
      Kane

      Originally Posted by Christian Little View Post

      Hmm...that's strange. What about apache, how many servers are setup?
      {{ DiscussionBoard.errors[4952073].message }}
  • Profile picture of the author khtm
    Ok I bumped up the MySQL "query_cache_size" from 128M to 512M. Hopefully that helps.

    Also, our OS is CENTOS 5.7 i686. Would we benefit from having a 64-bit OS?
    {{ DiscussionBoard.errors[4954991].message }}
  • Profile picture of the author johnnyN
    khtm, I think changing OS may help, but a bit too late, I would stick with it a while.
    I think Christian is right your problems are db, but normally "top" will show mysql sucking up some memory & cpu.
    can you spare any more memory to the query cache? maybe 1G?
    optimizing a lamp install can get expensive, but it can be worth it.
    The other option is to optimize the queries at the PHP level (which can also be expensive).

    If it were me, my first step would be to get someone to narrow down the exact slowdown, with some performance tools. (Maybe you could pay Christian), then address the issue.
    It may be that you need to move the db to a separate server? or something like that.
    {{ DiscussionBoard.errors[4960559].message }}
    • Profile picture of the author khtm
      Thanks Johnny, I updated query_cache_size to 1024M.

      Some more info. I ran the mysqltuner.pl script and here's the results:

      Code:
       >>  MySQLTuner 1.2.0 - 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
[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB +Federated +InnoDB -ISAM -NDBCluster 
[--] Data in MyISAM tables: 37G (Tables: 256)
[--] Data in InnoDB tables: 241M (Tables: 18)
[--] Data in MEMORY tables: 0B (Tables: 6)
[!!] Total fragmented tables: 30

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 8h 43m 6s (23M q [203.630 qps], 100K conn, TX: 2B, RX: 3B)
[--] Reads / Writes: 93% / 7%
[--] Total buffers: 1.3G global + 20.4M per thread (1000 max threads)
[!!] Allocating > 2GB RAM on 32-bit systems can cause system instability
[!!] Maximum possible memory usage: 21.2G (273% of installed RAM)
[OK] Slow queries: 0% (97/23M)
[OK] Highest usage of available connections: 4% (49/1000)
[OK] Key buffer size / total MyISAM indexes: 128.0M/3.9G
[OK] Key buffer hit rate: 99.9% (828M cached / 579K reads)
[OK] Query cache efficiency: 42.1% (9M cached / 22M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 283K sorts)
[OK] Temporary tables created on disk: 3% (6K on disk / 175K total)
[OK] Thread cache hit rate: 99% (49 created / 100K connections)
[OK] Table cache hit rate: 39% (412 open / 1K opened)
[OK] Open file limit used: 5% (456/9K)
[OK] Table locks acquired immediately: 99% (48M immediate / 48M locks)
[OK] InnoDB data size / buffer pool: 241.5M/512.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
      Guess I should make those two changes

      Cheers,
      Kane

      Originally Posted by johnnyN View Post

      khtm, I think changing OS may help, but a bit too late, I would stick with it a while.
      I think Christian is right your problems are db, but normally "top" will show mysql sucking up some memory & cpu.
      can you spare any more memory to the query cache? maybe 1G?
      optimizing a lamp install can get expensive, but it can be worth it.
      The other option is to optimize the queries at the PHP level (which can also be expensive).

      If it were me, my first step would be to get someone to narrow down the exact slowdown, with some performance tools. (Maybe you could pay Christian), then address the issue.
      It may be that you need to move the db to a separate server? or something like that.
      {{ DiscussionBoard.errors[4961014].message }}
  • Profile picture of the author Christian Little
    OK, try this:

    In WHM, click on Apache Configuration, then on Global Configuration. Scroll down until you see "Minimum Spare Servers" and "Maximum Spare Servers". What are they set to?
    Signature
    cPanel Shared and Reseller Web Hosting - 30 Day Free Trial Exclusive for Warriors!
    {{ DiscussionBoard.errors[4962359].message }}
    • Profile picture of the author khtm
      Min = 5, Max = 10

      Is that normal?

      Originally Posted by Christian Little View Post

      OK, try this:

      In WHM, click on Apache Configuration, then on Global Configuration. Scroll down until you see "Minimum Spare Servers" and "Maximum Spare Servers". What are they set to?
      {{ DiscussionBoard.errors[4963522].message }}
      • Profile picture of the author Christian Little
        Originally Posted by khtm View Post

        Min = 5, Max = 10

        Is that normal?
        Those are the default settings. You could increase it, but it may not make much of a difference as this is a mysql problem. The Apache Servers settings basically controls how many connections it can handle at one time.

        But I think you're going to need somebody to go into your database and start narrowing the problem down, as said above there's probably a lot of inefficient queries running, and the hardware you have simply might not be up for it.
        Signature
        cPanel Shared and Reseller Web Hosting - 30 Day Free Trial Exclusive for Warriors!
        {{ DiscussionBoard.errors[4967107].message }}
        • Profile picture of the author khtm
          Good point, Chris. We're looking at our server upgrade options right now. Might be going for the X5650 processor and possibly an SSD drive for the database

          Thanks for your help!

          Cheers,
          Kane

          Originally Posted by Christian Little View Post

          Those are the default settings. You could increase it, but it may not make much of a difference as this is a mysql problem. The Apache Servers settings basically controls how many connections it can handle at one time.

          But I think you're going to need somebody to go into your database and start narrowing the problem down, as said above there's probably a lot of inefficient queries running, and the hardware you have simply might not be up for it.
          {{ DiscussionBoard.errors[4975455].message }}
  • Profile picture of the author BAcheson
    I'd bet your bottleneck is MySQL related as some others have stated. I'd bet the farm you have a lot of queries that have not been indexed correctly, if at all. I've seen this a thousand times.

    It's quite easy to fix your issues, it simply requires a methodical approach.

    -Monitor slow queries (disable logging queries not using index)
    -Find each of these queries in your application code
    -Run a describe on each query
    -Fix index issues etc.

    You have a 40GB database. Randomly tweaking settings is not the solution to your problems at this point. You have to find and address each bottleneck 1 by 1. I find it's easier to start at the database level and work your way out to the webserver.

    Good luck!
    {{ DiscussionBoard.errors[4964017].message }}
  • Profile picture of the author wasabi20
    The only suggestion I can give you: NGINX ALL THE WAY
    {{ DiscussionBoard.errors[5025643].message }}
    • Profile picture of the author khtm
      Interesting, I hadn't even heard of NGINX but I did some searching and their are Elgg implementations that use it with noticeable performance increases....

      hmmmm...gives me something to think about!

      Originally Posted by wasabi20 View Post

      The only suggestion I can give you: NGINX ALL THE WAY
      {{ DiscussionBoard.errors[5030346].message }}
      • Profile picture of the author DEaFeYe
        Banned
        [DELETED]
        {{ DiscussionBoard.errors[5031367].message }}
        • Profile picture of the author khtm
          Good point, I must have overlooked it!

          Originally Posted by DEaFeYe View Post

          P.S. Nginx was like one of the first things mentioned in this thread
          {{ DiscussionBoard.errors[5032349].message }}
  • Profile picture of the author Michael71
    LAMP is Linux Apache MySQL PHP...

    So no N ginx in there ... LNMP ... sounds bad.

    OK, back to the post...

    I have experience in tweaking Apache with different techniques.

    Check out my blog @ www.cookielessdomain.com
    Signature

    HTML/CSS/jQuery/ZURB Foundation/Twitter Bootstrap/Wordpress/Frontend Performance Optimizing
    ---
    Need HTML/CSS help? Skype: microcosmic - Test Your Responsive Design - InternetCookies.eu

    {{ DiscussionBoard.errors[5029215].message }}
  • Profile picture of the author RobKonrad
    Originally Posted by khtm View Post

    We're looking to hire someone to help performance tune a LAMP server running Elgg CMS (PHP/MySQL).

    It's a dedicated box and should have good enough hardware but has been running slower than normal lately.

    Let me know if you know someone who can help us out

    Cheers,
    Kane
    Hey Kane,

    do you mean "tuning" = making the pages load faster on the customer's side?

    can't help you with "tuning" a LAMP stack/server - but when we started a page with a huge load of (paid) traffic, I was very concerned about performance and tried a lot of things to get the page load time down (was: 8 secs). What helped me most: Using good caching tools (for WP CMS) and, mos timportant: Host all images ona cdn.

    We're down to <1sec now....

    Cheers,
    Rob Konrad
    Signature
    ================================================== ===
    This blog is awesome: http://www.robkonrad.com/blog. Read it.
    ================================================== ===
    {{ DiscussionBoard.errors[5029418].message }}
    • Profile picture of the author khtm
      Yeah, as well as improve performance with our back-end jobs. Thanks for your suggestions.

      Originally Posted by RobKonrad View Post

      Hey Kane,

      do you mean "tuning" = making the pages load faster on the customer's side?

      can't help you with "tuning" a LAMP stack/server - but when we started a page with a huge load of (paid) traffic, I was very concerned about performance and tried a lot of things to get the page load time down (was: 8 secs). What helped me most: Using good caching tools (for WP CMS) and, mos timportant: Host all images ona cdn.

      We're down to <1sec now....

      Cheers,
      Rob Konrad
      {{ DiscussionBoard.errors[5030374].message }}
  • Profile picture of the author joanah
    Apache works great for this. It is a highly configurable piece of software. It has a lot of features, but each one comes at a price. Tuning Apache is partially an exercise in proper allocation of resources, and involves stripping down the configuration to only what's needed.
    Signature
    ediscovery software
    electronic discovery
    e-discovery
    {{ DiscussionBoard.errors[5143975].message }}
  • Profile picture of the author Terry Crim
    One of my hosts recently switched from Apache to Lightspeed Webserver, apparently it is much more efficient and is highly optimized giving much better performance and security.

    All I know from a user stand point, I see no difference in functionality. htaccess and custom php.ini works but now I do not need any reference to php.ini in my htaccess file. My sites load quickly and cpanel works the same as it did before under apache.

    Maybe it is just my host but several new php modules and features were added which make things a little but easier from a webdesign and programming stand point.

    As long as I can do what it is I want to in the way I want to do it then I don't really care what is being used on the backend. Basically I see no downside to the switch my host did from apache to lightspeed. Google it, they have a lot of interesting features that standard apache doesn't.

    - T
    {{ DiscussionBoard.errors[5144957].message }}
  • Profile picture of the author GioSec
    I'd listen to Christian...

    From the looks of it, the problem seems your database and the queries. I'd hire a database optimizer to take a look at your database and the queries that you're passing to it. It won't matter if you upgrade to nginx if the database is the problem.
    {{ DiscussionBoard.errors[5154189].message }}
  • Profile picture of the author khtm
    Just an update:

    We've hired a fella off oDesk to help analyze and configure our server better. And we're in the midst of moving hosts to WiredTree and from a single dedicted box to a more distributed configuration.

    Thanks everyone for the suggestions so far! I'll keep this thread updated as we move along.
    {{ DiscussionBoard.errors[5154200].message }}

Trending Topics