[Forum] Large forum, major issues with grinding to halt, need help please..

[sross]

I am not sure what is going on and at my wits end. Host seems unable to help either. Things were running fine for over 5 months. No major changes. One minute the forum is unresponsive, the next lightning fast. These bouts of slowness do not seem to be related to traffic patterns. I was running a 4GB machine at joyent cloud, switched to 8GB 2CPU machine and the issue persists. memcached is running, apc cache, vbulletin search was replaced with Sphinx. If someone could help me troubleshoot this it would be greatly appreciated. forum is nearly 60k users, 1.2 mil posts, at times 1k online. I am not a server guru so suspect my configs could be way off. Here is mysql:

Module Index Help..

MySQL System Variables

Warning - Variables set on this page will be reset to their default values when MySQL is restarted. Permanent settings must be made on the MySQL Server Configuration page.

Show variables matching:

Variable name Current value auto_increment_increment 1 auto_increment_offset 1 autocommit ON automatic_sp_privileges ON back_log 50 basedir /opt/local big_tables OFF binlog_cache_size 32768 binlog_direct_non_transactional_updates OFF binlog_format STATEMENT binlog_stmt_cache_size 32768 bulk_insert_buffer_size 8388608 character_set_client utf8 character_set_connection utf8 character_set_database utf8 character_set_filesystem binary character_set_results utf8 character_set_server utf8 character_set_system utf8 character_sets_dir /opt/local/share/mysql/charsets/ collation_connection utf8_general_ci collation_database utf8_general_ci collation_server utf8_general_ci completion_type NO_CHAIN concurrent_insert AUTO connect_timeout 10 datadir /var/mysql/ date_format %Y-%m-%d datetime_format %Y-%m-%d %H:%i:%s default_storage_engine InnoDB default_week_format 0 delay_key_write ON delayed_insert_limit 100 delayed_insert_timeout 300 delayed_queue_size 1000 div_precision_increment 4 engine_condition_pushdown ON error_count 0 event_scheduler OFF expire_logs_days 0 external_user flush OFF flush_time 0 foreign_key_checks ON ft_boolean_syntax + -><()~*:""&| ft_max_word_len 84 ft_min_word_len 4 ft_query_expansion_limit 20 ft_stopword_file (built-in) general_log OFF general_log_file /var/mysql/564ea4b.log group_concat_max_len 1024 have_compress YES have_crypt YES have_csv YES have_dynamic_loading YES have_geometry YES have_innodb YES have_ndbcluster NO have_openssl NO have_partitioning YES have_profiling YES have_query_cache YES have_rtree_keys YES have_ssl NO have_symlink YES hostname 564ea4b.local identity 0 ignore_builtin_innodb OFF init_connect init_file init_slave innodb_adaptive_flushing ON innodb_adaptive_hash_index ON innodb_additional_mem_pool_size 20971520 innodb_autoextend_increment 8 innodb_autoinc_lock_mode 1 innodb_buffer_pool_instances 1 innodb_buffer_pool_size 1073741824 innodb_change_buffering all innodb_checksums ON innodb_commit_concurrency 0 innodb_concurrency_tickets 500 innodb_data_file_path ibdata1:100M:autoextend innodb_data_home_dir innodb_doublewrite ON innodb_fast_shutdown 1 innodb_file_format Antelope innodb_file_format_check ON innodb_file_format_max Antelope innodb_file_per_table OFF innodb_flush_log_at_trx_commit 2 innodb_flush_method innodb_force_recovery 0 innodb_io_capacity 200 innodb_lock_wait_timeout 50 innodb_locks_unsafe_for_binlog OFF innodb_log_buffer_size 8388608 innodb_log_file_size 104857600 innodb_log_files_in_group 2 innodb_log_group_home_dir ./ innodb_max_dirty_pages_pct 75 innodb_max_purge_lag 0 innodb_mirrored_log_groups 1 innodb_old_blocks_pct 37 innodb_old_blocks_time 0 innodb_open_files 300 innodb_purge_batch_size 20 innodb_purge_threads 0 innodb_read_ahead_threshold 56 innodb_read_io_threads 4 innodb_replication_delay 0 innodb_rollback_on_timeout OFF innodb_rollback_segments 128 innodb_spin_wait_delay 6 innodb_stats_method nulls_equal innodb_stats_on_metadata ON innodb_stats_sample_pages 8 innodb_strict_mode OFF innodb_support_xa ON innodb_sync_spin_loops 30 innodb_table_locks ON innodb_thread_concurrency 0 innodb_thread_sleep_delay 10000 innodb_use_native_aio OFF innodb_use_sys_malloc ON innodb_version 1.1.7 innodb_write_io_threads 4 insert_id 0 interactive_timeout 28800 join_buffer_size 131072 keep_files_on_create OFF key_buffer_size 1048576000 key_cache_age_threshold 300 key_cache_block_size 1024 key_cache_division_limit 100 large_files_support ON large_page_size 0 large_pages OFF last_insert_id 0 lc_messages en_US lc_messages_dir /opt/local/share/mysql/ lc_time_names en_US license GPL local_infile ON lock_wait_timeout 31536000 locked_in_memory OFF log OFF log_bin OFF log_bin_trust_function_creators OFF log_error /var/log/mysql/error.log log_output FILE log_queries_not_using_indexes OFF log_slave_updates OFF log_slow_queries ON log_warnings 1 long_query_time 1.000000 low_priority_updates OFF lower_case_file_system OFF lower_case_table_names 0 max_allowed_packet 25165824 max_binlog_cache_size 18446744073709547520 max_binlog_size 1073741824 max_binlog_stmt_cache_size 18446744073709547520 max_connect_errors 10 max_connections 500 max_delayed_threads 20 max_error_count 64 max_heap_table_size 16777216 max_insert_delayed_threads 20 max_join_size 4294967295 max_length_for_sort_data 1024 max_long_data_size 25165824 max_prepared_stmt_count 16382 max_relay_log_size 0 max_seeks_for_key 4294967295 max_sort_length 1024 max_sp_recursion_depth 0 max_tmp_tables 32 max_user_connections 0 max_write_lock_count 4294967295 min_examined_row_limit 0 multi_range_count 256 myisam_data_pointer_size 6 myisam_max_sort_file_size 2146435072 myisam_mmap_size 4294967295 myisam_recover_options OFF myisam_repair_threads 1 myisam_sort_buffer_size 33554432 myisam_stats_method nulls_unequal myisam_use_mmap OFF net_buffer_length 2048 net_read_timeout 30 net_retry_count 10 net_write_timeout 60 new OFF old OFF old_alter_table OFF old_passwords OFF open_files_limit 10240 optimizer_prune_level 1 optimizer_search_depth 62 optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on, engine_condition_pushdown=on performance_schema OFF performance_schema_events_waits_history_long_size 10000 performance_schema_events_waits_history_size 10 performance_schema_max_cond_classes 80 performance_schema_max_cond_instances 1000 performance_schema_max_file_classes 50 performance_schema_max_file_handles 32768 performance_schema_max_file_instances 10000 performance_schema_max_mutex_classes 200 performance_schema_max_mutex_instances 1000000 performance_schema_max_rwlock_classes 30 performance_schema_max_rwlock_instances 1000000 performance_schema_max_table_handles 100000 performance_schema_max_table_instances 50000 performance_schema_max_thread_classes 50 performance_schema_max_thread_instances 1000 pid_file /var/mysql/mysql.pid plugin_dir /opt/local/lib/mysql/plugin port 3306 preload_buffer_size 32768 profiling OFF profiling_history_size 15 protocol_version 10 proxy_user pseudo_thread_id 16060 query_alloc_block_size 8192 query_cache_limit 1048576 query_cache_min_res_unit 4096 query_cache_size 16777216 query_cache_type ON query_cache_wlock_invalidate OFF query_prealloc_size 8192 rand_seed1 0 rand_seed2 0 range_alloc_block_size 4096 read_buffer_size 1048576 read_only OFF read_rnd_buffer_size 2097152 relay_log relay_log_index relay_log_info_file relay-log.info relay_log_purge ON relay_log_recovery OFF relay_log_space_limit 0 report_host report_password report_port 3306 report_user rpl_recovery_rank 0 secure_auth OFF secure_file_priv server_id 1 skip_external_locking ON skip_name_resolve OFF skip_networking OFF skip_show_database OFF slave_compressed_protocol OFF slave_exec_mode STRICT slave_load_tmpdir /var/tmp slave_net_timeout 3600 slave_skip_errors OFF slave_transaction_retries 10 slave_type_conversions slow_launch_time 2 slow_query_log ON slow_query_log_file /var/log/mysql/slowquery.log socket /tmp/mysql.sock sort_buffer_size 1048576 sql_auto_is_null OFF sql_big_selects OFF sql_big_tables OFF sql_buffer_result OFF sql_log_bin ON sql_log_off OFF sql_low_priority_updates OFF sql_max_join_size 4294967295 sql_mode sql_notes ON sql_quote_show_create ON sql_safe_updates OFF sql_select_limit 18446744073709551615 sql_slave_skip_counter 0 sql_warnings OFF ssl_ca ssl_capath ssl_cert ssl_cipher ssl_key storage_engine InnoDB sync_binlog 0 sync_frm ON sync_master_info 0 sync_relay_log 0 sync_relay_log_info 0 system_time_zone UTC table_definition_cache 400 table_open_cache 256 thread_cache_size 250 thread_concurrency 16 thread_handling one-thread-per-connection thread_stack 131072 time_format %H:%i:%s time_zone SYSTEM timed_mutexes OFF timestamp 1334987402 tmp_table_size 16777216 tmpdir /var/tmp transaction_alloc_block_size 8192 transaction_prealloc_size 4096 tx_isolation REPEATABLE-READ unique_checks ON updatable_views_with_limit YES version 5.5.13-log version_comment Source distribution version_compile_machine i386 version_compile_os solaris11 wait_timeout 28800 warning_count 0

It seems mysql cpu is spiking so I thought I would start with trying to get the above in order?

Thanks for any help

[sross]

another odd thing is that i'll click to view the forums. It "thinks" for about 20-30 seconds, then kicks in, then it's fast for a bit.

Another observation. when these strange issues happen if i try to post i get a warning "are you sure you want to leave this page"

Now.. if I then run an optimize on the db, the warning goes away and posting returns to normal. This is all very strange.. *edit ok, the optimize didn't seem to help this time. disabling google yui seems to have stopped the "leave this page" error.

Another thing is I have another vbull forum on the same server. This forum has no issues at all, lightning quick. When my main vbull site grinds to a halt and starts hanging, the other site on the same server appears unaffected.

*update.. disabled plugin-hook system and it seems to be running ok with all hooks disabled. Strange. Will try to track down a bad plugin..

ok even with plugins disabled it just keeps hanging. if i restart mysql it works for a minute then hangs. fml

[digitalpoint]

If you can get into MySQL via shell, you may be able to track down what query might be causing it...

If you run this while it's hanging (from within MySQL), you should be able to track down any nasty queries that are running... might be helpful for finding the culprit (if it's an addon).

show processlist;

[sross]

it might be my sphinx that I setup the other day. everything was running good with hooks off, i re-enabled hooks with sphinx plugin. still running ok. I started sphinx and searchd then site hung again. Trying to get back in to disable again and see if ti comes back.

ok restarted mysql and site is back. hmmm will try again to make sure its not a coincidence

[sross]

ok forum is running ok, hooks enabled but all products disabled. I just enabled your Sphinx plugin. Site is still running fine but sphinx is not running on the server. Is there a better way to start Sphinx? are there 2 components to start or is it just "searchd" to start? Do I need to do svcadm enable sphinx too?

wait scratch that site seems to be hanging again -now its back, ugh this is doing my head in. which i was a guru :/

[punchbowl]

ok forum is running ok, hooks enabled but all products disabled. I just enabled your Sphinx plugin. Site is still running fine but sphinx is not running on the server. Is there a better way to start Sphinx? are there 2 components to start or is it just "searchd" to start? Do I need to do svcadm enable sphinx too?

wait scratch that site seems to be hanging again -now its back, ugh this is doing my head in. wish i was a guru :/

You will be by the end of this!

I'm surprised it's sphinx but it looks like it is. Maybe you've misconfigured it at some stage.

When I initially arrived at the thread my first piece of advice was to just give mysql more memory. I run a similar sized vb3.8 forum and searches were clogging the forum completely until I basically gave mysql all the memory possible. imo you need a bit more memory than the size of your db to solve it that way, so basically you're moving the db into memory.

[sross]

well now it might not be sphinx, i have hooks disabled and all plugins disabled, sphinx shut down. the site still does it's weird thing it hangs for several minutes then it's back like nothing ever happened. Right now it's hung but it will come back to life in a couple minutes for a bit then hang again. I am trying to figure out how to get into mysql console. I am willing to pay a guru here to help me. I've never seen anything like this, now the forum is back again. it's yoyo'ing

just noticed i had not disabled sphinx cron, disabled now and will see if the 5min interval was why the site was hanging?

[digitalpoint]

Why not just run the command I gave in previous post to see what queries exactly are hanging when it happens?

[sross]

Why not just run the command I gave in previous post to see what queries exactly are hanging when it happens?

I just don't have much in way of linux skills. How do i get to the console? I'll give it a try. Would you be interested in root access as hired for a job to help? Beyond desperate here :/ just let me know how to IM you if interested.

[punchbowl]

I just don't have much in way of linux skills. How do i get to the console? I'll give it a try. Would you be interested in root access as hired for a job to help? Beyond desperate here :/ just let me know how to IM you if interested.

you use something like putty http://www.chiark.greenend.org.uk/~sgtatham/putty/

do everything dp tells you to!

[sross]

you use something like putty http://www.chiark.greenend.org.uk/~sgtatham/putty/

do everything dp tells you to!

I have putty and root. I can do some basic stuff but I've never gotten into the mysql prompt. dp is probably the only thing that can save me. I haven't left my chair for days. Feeling horrible, this forum is all I got to support my family.

[digitalpoint]

Well, I know you can get to the shell (at least I assume since you were able to start searchd). Assuming you can, type: mysql --user=xxxx -p (xxxx being your MySQL username). That should get you into MySQL... from there, typing this will show you the currently running processes/queries:

SHOW PROCESSLIST;

[sross]

at the time of being hung:

mysql> show processlist;
+------+------------+-----------+----------+---------+------+------------------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+------------+-----------+----------+---------+------+------------------------------+------------------------------------------------------------------------------------------------------+
| 1930 | chris_sean | localhost | chris_vb | Query | 0 | NULL | show processlist |
| 2064 | chris_sean | localhost | chris_vb | Query | 14 | Waiting for table level lock | SELECT
post.*, post.username AS postusername, post.ipaddress AS ip, IF(post.visible = 2, 1, 0) AS |
| 2088 | chris_sean | localhost | chris_vb | Query | 22 | Waiting for table level lock | SELECT
post.*, post.username AS postusername, post.ipaddress AS ip, IF(post.visible = 2, 1, 0) AS |
| 2090 | chris_sean | localhost | chris_vb | Query | 33 | Waiting for table level lock | UPDATE user
SET lastactivity = 1335008898
WHERE userid = 7462 |
| 2093 | chris_sean | localhost | chris_vb | Query | 33 | Sending data | SELECT user.*,usertextfield.*,userfield.*, user.userid, options,
IF(displaygroupid=0, user.usergr |
| 2099 | chris_sean | localhost | chris_vb | Query | 32 | Waiting for table level lock | SELECT
post.*, post.username AS postusername, post.ipaddress AS ip, IF(post.visible = 2, 1, 0) AS |
| 2105 | chris_sean | localhost | chris_vb | Query | 29 | Waiting for table level lock | SELECT post.postid, post.pagetext, IFNULL( user.username , post.username ) AS username, dateline
F |
| 2106 | chris_sean | localhost | chris_vb | Query | 28 | Waiting for table level lock | SELECT
post.*, post.username AS postusername, post.ipaddress AS ip, IF(post.visible = 2, 1, 0) AS |
| 2108 | chris_sean | localhost | chris_vb | Sleep | 27 | | NULL |
| 2109 | chris_sean | localhost | chris_vb | Query | 27 | Waiting for table level lock | SELECT
userfield.*, usertextfield.*, user.*, UNIX_TIMESTAMP(passworddate) AS passworddate, user. |
| 2110 | chris_sean | localhost | chris_vb | Query | 26 | Waiting for table level lock | SELECT
userfield.*, usertextfield.*, user.*, UNIX_TIMESTAMP(passworddate) AS passworddate, user. |
| 2111 | chris_sean | localhost | chris_vb | Query | 26 | Waiting for table level lock | UPDATE user
SET lastactivity = 1335008907
WHERE userid = 7462 |
| 2112 | chris_sean | localhost | chris_vb | Query | 25 | Waiting for table level lock | SELECT
post.*, post.username AS postusername, post.ipaddress AS ip, IF(post.visible = 2, 1, 0) AS |
| 2115 | chris_sean | localhost | chris_vb | Query | 23 | Waiting for table level lock | SELECT
post.*, post.username AS postusername, post.ipaddress AS ip, IF(post.visible = 2, 1, 0) AS |
| 2116 | chris_sean | localhost | chris_vb | Query | 23 | Waiting for table level lock | SELECT
userfield.*, usertextfield.*, user.*, UNIX_TIMESTAMP(passworddate) AS passworddate, user. |
| 2119 | chris_sean | localhost | chris_vb | Query | 18 | Waiting for table level lock | SELECT
post.*, post.username AS postusername, post.ipaddress AS ip, IF(post.visible = 2, 1, 0) AS |
| 2127 | chris_sean | localhost | chris_vb | Query | 14 | Waiting for table level lock | SELECT
post.*, post.username AS postusername, post.ipaddress AS ip, IF(post.visible = 2, 1, 0) AS |
| 2130 | chris_sean | localhost | chris_vb | Query | 13 | Waiting for table level lock | SELECT
post.*, post.username AS postusername, post.ipaddress AS ip, IF(post.visible = 2, 1, 0) AS |
| 2131 | chris_sean | localhost | chris_vb | Query | 12 | Waiting for table level lock | SELECT
post.*, post.username AS postusername, post.ipaddress AS ip, IF(post.visible = 2, 1, 0) AS |
| 2134 | chris_sean | localhost | chris_vb | Query | 9 | Waiting for table level lock | SELECT
post.*, post.username AS postusername, post.ipaddress AS ip, IF(post.visible = 2, 1, 0) AS |
| 2135 | chris_sean | localhost | chris_vb | Query | 6 | Waiting for table level lock | SELECT
userfield.*, usertextfield.*, user.*, UNIX_TIMESTAMP(passworddate) AS passworddate, user. |
+------+------------+-----------+----------+---------+------+------------------------------+------------------------------------------------------------------------------------------------------+

Site is working at this moment but it is yoyo up and down..

[digitalpoint]

Looks like the query causing issues there is from someone on your memberlist. Disable your memberlist... It really serves no useful purpose other than doing nasty things to you db server anytime someone looks at it.

Any vb site that is beyond being small should disable the memberlist.

[sross]

Thanks man, hope that helps. Also was meaning to ask you re: Sphinx. Do I need to run "searchd" and also run svcadm enable sphinx ?