WordPress.org

Ready to get started?Download WordPress

Forums

Help! Have tried everything - Site still EXTREMELY slow at peak times!! (5 posts)

  1. SouthBeach030
    Member
    Posted 7 years ago #

    I know this is a long post, but I'd appreciate it greatly if some genius from the WP community could just take a quick look at it!

    I have tried and tried to get some kind of answer to my woes.

    I have a relatively popular site (about 750k - 1mm PV per month). It's a beefy home page, and the site has quite a few plugins.

    Can someone please take a look at my configuration settings to see if you can find some OBVIOUS errors that I could tweak to attain a temporary improvement in performance?

    I have every known enhancement:

    • WP-Cache
    • eAccelerator
    • SQL cache
    • Varnish HTTP proxy cache

    I have tried modifying my server settings based on what I've learned on the web. My.cnf file, http.conf, and more.

    I have a dedicated Xeon 2.8ghz, 2GB ram, (2) SATA 80GB hard drives.

    I also run OpenAds PHP banner software on the same server. Server up over 2MM ads per month. Not from the PHP database either, they're local server based to relieve database congestion.

    I'm running the following versions of the products:

    • WP 2.0.11 (RC2)
    • MySQL 4.1.22
    • PHP 4.4.4
    • Apache 1.3.37 (I think)
    • Linux 2.6.9-42.0.10.ELsmp

    I run the following Plugins:

    - Adhesive
    - Admin Drop down Menu
    - Adsense-Deluxe
    - AHCommentCentral (may not be needed going forward)
    - Ajaxified Comment Preview (will accept anything similar)
    - Akismet
    - Audio Player (will probably be removed in to use external tools)
    - Bannage (needed, but doesn't always work properly)
    - Breadcrumb Navigation
    - Category Visibility (1.0.0b)
    - Comment link target Blanker
    - Customizable Post Listings (would want similar functionality)
    - Democracy Poll (2.01) integral part of the site
    - Email Users (used maybe 2x a year)
    - Event Calendar - Integral part of the site
    - Geo Press using Yahoo! - Integral part of the site
    - Get recent comments - Integral part of the site
    - Google (XML) sitemaps - needed.. using 3.0b7 now
    - Inline RSS (kinda buggy, would take a viable alternative)
    - Keyword Tags (.80) customized it heavily to make it work
    - Link Trucator - to keep comment section from breaking the theme
    - Live Calendar (may get removed)
    - Nice Categories (inherent with the theme I use)
    - Paged comments (must have, since many posts have over 500 comments)
    - Post Levels (may remove this eventually)
    - Post Query Accelerator (needed to keep performance acceptable, but will remove if 2.2 and HyperDB solves the problem)
    - Quoter 1.1 - MUST HAVE, as it's used 100's of times daily. An alternative or improved code would be needed
    - Related Posts - needed.. keeps readers interested in archived posts
    - Rock You (used infrequently)
    - RunPHP - must have for inserting certain ad-code in the posts
    - Share This - included with every post
    - Show Top Commentators - keeps users competitive
    - Smiley Javascript buttons - also used daily by readers
    - Text Link Ads - in the sidebar
    - Themed login and Register - keeps readers from seeing the static wordpress landing page, and away from the dashboard
    - Weasel's login redirect / text change - see above
    - WordPress Database backup
    - WP-AddQuickTag - makes my posting easier
    - wp-Cache - MUST HAVE to improve perfomance
    - WP-MostCommentedPosts - another important feature
    - WP-PostRatings - required

    Lastly, here is my current My.CNF config settings.

    Do you see anything in here that might be causing issues? Something BLATANTLY wrong with the settings that could be either bloating my mem usage or not using enough? MySQL is definitely the memory and speed hog, and I'd love to know if there is something here that looks wrong to you.

    I know this is a long post, but I'd appreciate it greatly if some genius from the WP community could just take a quick look at it!

    MY.CNF:
    --------------------------
    [mysqld]
    old_passwords=1
    datadir=/var/lib/mysql
    skip-locking
    skip-innodb
    safe-show-database
    query_cache_limit=6M
    query_cache_size=32M ## 32MB for every 1GB of RAM
    query_cache_type=1
    max_user_connections=512
    max_connections=2048 # four times max_user_connections, to support the ad software

    #delayed_insert_timeout=40

    interactive_timeout=10
    wait_timeout=15
    connect_timeout=20
    thread_cache_size=512
    key_buffer=64M ## 32MB for every 1GB of RAM
    myisam_sort_buffer_size=64M
    join_buffer_size=8M
    max_connect_errors=20
    max_allowed_packet=64M
    table_cache=2048
    tmp_table_size=64M
    max_heap_table_size=64M
    record_buffer=2M
    sort_buffer_size=2M ## 1MB for every 1GB of RAM
    read_buffer_size=2M ## 1MB for every 1GB of RAM
    read_rnd_buffer_size=2M ## 1MB for every 1GB of RAM
    thread_concurrency=2 ## Number of CPUs x 2
    server-id=1

    long_query_time=1
    #log-slow-queries=/var/log/mysql-slow.log
    #log=/var/log/mysql-query.log

    [mysql.server]
    user=mysql
    basedir=/var/lib

    [safe_mysqld]
    err-log=/var/log/mysqld.log
    pid-file=/var/lib/mysql/mysql.pid
    open_files_limit=8192

    [mysqldump]
    quick
    max_allowed_packet=16M

    [mysql]
    no-auto-rehash

    -------------------------

    Appreciate any help or advice anyone can provide. I've looked at all the different MySQL sites, and I cannot get consistent information, etc.

    Thank you!

  2. SouthBeach030
    Member
    Posted 7 years ago #

    FYI, this isn't peak time anymore, but here's a look at MySQL processes...

    Do you see anything out of whack?

    +----------------------------+------------+
    | Variable_name | Value |
    +----------------------------+------------+
    | Aborted_clients | 70 |
    | Aborted_connects | 6 |
    | Binlog_cache_disk_use | 0 |
    | Binlog_cache_use | 0 |
    | Bytes_received | 248678028 |
    | Bytes_sent | 2040588199 |
    | Com_admin_commands | 0 |
    | Com_alter_db | 0 |
    | Com_alter_table | 0 |
    | Com_analyze | 0 |
    | Com_backup_table | 0 |
    | Com_begin | 0 |
    | Com_change_db | 108826 |
    | 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_dealloc_sql | 0 |
    | Com_delete | 623 |
    | 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 | 6195 |
    | Com_insert_select | 0 |
    | Com_kill | 0 |
    | Com_load | 0 |
    | Com_load_master_data | 0 |
    | Com_load_master_table | 0 |
    | Com_lock_tables | 379 |
    | Com_optimize | 134 |
    | 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 | 377431 |
    | Com_set_option | 91500 |
    | Com_show_binlog_events | 0 |
    | Com_show_binlogs | 1 |
    | Com_show_charsets | 30 |
    | Com_show_collations | 30 |
    | Com_show_column_types | 0 |
    | Com_show_create_db | 0 |
    | Com_show_create_table | 0 |
    | Com_show_databases | 30 |
    | Com_show_errors | 0 |
    | Com_show_fields | 14 |
    | Com_show_grants | 25 |
    | Com_show_innodb_status | 0 |
    | Com_show_keys | 14 |
    | 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 | 2761 |
    | Com_show_slave_hosts | 0 |
    | Com_show_slave_status | 0 |
    | Com_show_status | 2625 |
    | Com_show_storage_engines | 0 |
    | Com_show_tables | 108 |
    | Com_show_variables | 82 |
    | Com_show_warnings | 0 |
    | Com_slave_start | 0 |
    | Com_slave_stop | 0 |
    | Com_stmt_close | 0 |
    | Com_stmt_execute | 0 |
    | Com_stmt_prepare | 0 |
    | Com_stmt_reset | 0 |
    | Com_stmt_send_long_data | 0 |
    | Com_truncate | 51 |
    | Com_unlock_tables | 379 |
    | Com_update | 82739 |
    | Com_update_multi | 2 |
    | Connections | 108949 |
    | Created_tmp_disk_tables | 28563 |
    | Created_tmp_files | 14 |
    | Created_tmp_tables | 42996 |
    | Delayed_errors | 0 |
    | Delayed_insert_threads | 0 |
    | Delayed_writes | 0 |
    | Flush_commands | 1 |
    | Handler_commit | 0 |
    | Handler_delete | 436 |
    | Handler_discover | 0 |
    | Handler_read_first | 168 |
    | Handler_read_key | 860617083 |
    | Handler_read_next | 1045433842 |
    | Handler_read_prev | 98 |
    | Handler_read_rnd | 5506672 |
    | Handler_read_rnd_next | 1619630869 |
    | Handler_rollback | 0 |
    | Handler_update | 475236933 |
    | Handler_write | 42462448 |
    | Key_blocks_not_flushed | 0 |
    | Key_blocks_unused | 53866 |
    | Key_blocks_used | 10477 |
    | Key_read_requests | 2143416861 |
    | Key_reads | 547649 |
    | Key_write_requests | 40744743 |
    | Key_writes | 7974 |
    | Max_used_connections | 93 |
    | Not_flushed_delayed_rows | 0 |
    | Open_files | 295 |
    | Open_streams | 0 |
    | Open_tables | 227 |
    | Opened_tables | 2468 |
    | Qcache_free_blocks | 999 |
    | Qcache_free_memory | 16225912 |
    | Qcache_hits | 1704671 |
    | Qcache_inserts | 339029 |
    | Qcache_lowmem_prunes | 53193 |
    | Qcache_not_cached | 38340 |
    | Qcache_queries_in_cache | 5017 |
    | Qcache_total_blocks | 11319 |
    | Questions | 2485169 |
    | Rpl_status | NULL |
    | Select_full_join | 3148 |
    | Select_full_range_join | 0 |
    | Select_range | 11354 |
    | Select_range_check | 0 |
    | Select_scan | 12771 |
    | Slave_open_temp_tables | 0 |
    | Slave_retried_transactions | 0 |
    | Slave_running | OFF |
    | Slow_launch_threads | 7 |
    | Slow_queries | 17593 |
    | Sort_merge_passes | 7 |
    | Sort_range | 109414 |
    | Sort_rows | 5131944 |
    | Sort_scan | 46120 |
    | Table_locks_immediate | 536182 |
    | Table_locks_waited | 3135 |
    | Threads_cached | 81 |
    | Threads_connected | 12 |
    | Threads_created | 93 |
    | Threads_running | 1 |
    | Uptime | 42514 |
    +----------------------------+------------+

  3. omgitztrey
    Member
    Posted 7 years ago #

    A dedicated server shouldn't be causing those problems. I would seriously lighten up on the plugins though. For every plugin you add more strain to the server. Also check to see your provider's network setup. Also, is the db server located on a different computer?

    You can try changing these to optimize the mysql performance.

    query cache = needs to be drastically increased. It'll save the server trouble from having to query each time. 6mb is not enough for a site that big.

    query cache size = >84mb
    key buffer = >129mb

  4. SouthBeach030
    Member
    Posted 7 years ago #

    Thanks omgitztrey.. However, let me explain..

    The Query Cache LIMIT is 6MB..
    The Query Cache SIZE is 32MB... I never use the whole cache.. for instance it's now 17MB FREE. I reduced it from 128MB to 32MB...

    How can I find out which plugins are using hogging OUTER joins instead of INNER or LEFT joins? And if so, how can I re-write the plugin to be more efficient and prevent exponential server performance degradations?

    It seems the larger my site gets, the more and more slow it will become, because of the ill-written plugins...

    Thanks!

  5. omgitztrey
    Member
    Posted 7 years ago #

    Hmmm. Figuring that out would take days. About how many unique hits are you getting a day? Below is the list you posted above. I commented on some and removed some from the list that I knew nothing about. Also, have you tried using the throttle plugin? It works really great for heavy sites.

    - Admin Drop down Menu *Don't need*
    - Adsense-Deluxe <-- I thought you were using OpenAds
    - Ajaxified Comment Preview *Don't need* normal comment box works fine
    - Akismet - LOVE IT
    - Audio Player - outsource this to other providers
    - Breadcrumb Navigation - What is this for?
    - Customizable Post Listings (what is this for?
    - Democracy Poll (2.01) integral part of the site
    - Email Users -- this one can stay.
    - Google (XML) sitemaps - needed.. using 3.0b7 now
    - Inline RSS (kinda buggy, would take a viable alternative)
    - Keyword Tags (.80) You won't need this after 2.4 is released. Resource whore.
    - Live Calendar (may get removed. Good.)
    - Nice Categories (inherent with the theme I use)
    - Paged comments (must have, since many posts have over 500 comments)
    - Post Levels (may remove this eventually)
    - Post Query Accelerator (needed to keep performance acceptable, but will remove if 2.2 and HyperDB solves the problem)
    - Quoter 1.1 - MUST HAVE, as it's used 100's of times daily. An alternative or improved code would be needed
    - Related Posts - needed.. keeps readers interested in archived posts
    - Rock You (used infrequently)
    - RunPHP - must have for inserting certain ad-code in the posts
    - Share This - included with every post
    - Show Top Commentators - keeps users competitive
    - Smiley Javascript buttons - also used daily by readers
    - Text Link Ads - in the sidebar
    - Themed login and Register - keeps readers from seeing the static wordpress landing page, and away from the dashboard
    - Weasel's login redirect / text change - see above
    - WordPress Database backup
    - WP-AddQuickTag - ?? Is this another tagging plugin
    - wp-Cache - MUST HAVE to improve perfomance
    - WP-MostCommentedPosts - another important feature
    - WP-PostRatings - required

Topic Closed

This topic has been closed to new replies.

About this Topic