SMF as slow as hell on an VPS plan! - can't find the problem

[jyajay]

SMF Version: SMF 1.1 RC2
Hi,

right now i am a kind of helpless with this situation. I just moved my whole board from phpBB to SMF in intention to decrease the server load on my VPS.

So far, that was my plan. Now two days later i have the same problems with the server load as i had with phpBB.

Already had some conversation with my hoster but its always the same answer: We do not offer support to 3rd party software, and please do upgrade your VPS to an higher plan, bla bla bla.

I am running PHP 5.1.2 and eAccelerator 0.9.5 (SMF incompatible with this version? Caching is maybe not working correct)

With just 25000 posts and about 4000 members.

For mor informations contact me please because i am running an adult board and i don't want to provide the url to it here.

[JayBachatero]

README: Checklist for performance problems

[jyajay]

That was the first topic i read.

I did everything possible, thats why i am searching for help here.

[redone]

Well, there are only a few around who are up to the job of configuring a server. Having used a VPS before myself I suggest you look at the figures you are getting and maybe contact the VPS supplier? I have known it to be possible before that someone else on the VPS is maybe using there fair share of resources and your maybe not getting the performance you want.

Maybe they will move you to a VPS with lighter load? Unless that is you can prove that it's your SMF install that needs work. How big is your forum?

[Ben_S]

README: Requesting help with server configuration.

[jyajay]

The load of the server is quiet ok i think, currently at 28%

As i said, my board is not really a big one with just 20-50 users surfing at one time, also i am using the default theme.

I don't think my provider will me move.

Right now there are 20 people surfing and the load is aceptable. But it looks like there is not always a connection between users surfing and the load because right after the setup of SMF i had about 80 people browsing my site and the load was at 0.50-0.70 all the time. Its an riddle to me.

Mods installed:

2. CAPTCHA (code verification) 0.1 [ Uninstall ]
3. TinyPortal 0.86 [ Uninstall ]
4. Sticky First Post (extends sticky topic) 1.2.1 [ Uninstall ]
5. Custom Profile Mod 2.13 [ Uninstall ]
6. Search Topic 1.0 [ Uninstall ]
7. Read Topic Permission Mod 1.0.3 [ Uninstall ]
8. Topic Digest 1.1.2 [ Uninstall ]
9. Merge Double Posts 1.05

Operating System: CentOS release 3.7 (Final) 
Processor: Intel® Xeon(TM) CPU 3.00GHz (3000.183MHz)
Load averages: 1.32, 2.68, 3.07
Current processes: 116 (115 sleeping, 1 running, 0 zombie)
Processes by CPU: httpd (16) 2.5%, (other) (28) 0.0% 
Memory usage: 99.737% (8244892k / 8266652k)
Swap: 10.667% (3578180k / 33543552k) 

MySQL processes
Total processes: 13 (12 sleeping, 1 running, 0 locked)

MySQL Statistics
MySQL 4.1.x
Connections per second:  0.0847 
Kilobytes received per second:  2.5466 
Kilobytes sent per second:  21.8901 
Queries per second:  13.5006 
Percentage of slow queries:  0 
Opened vs. Open tables:
(table_cache)  1.7758 (should be <= 80) 
Table cache usage:
(table_cache)  0.6445 (should be >= 0.5 and <= 0.9) 
Key buffer read hit rate:
(key_buffer_size)  0.0009 (should be <= 0.01) 
Key buffer write hit rate:
(key_buffer_size)  0.5715 (should be <= 0.5) 
Thread cache hit rate:
(thread_cache_size)  12.7059 (should be >= 30 ) 
Thread cache usage:
(thread_cache_size)  0.9063 (should be >= 0.7 and <= 0.9) 
Temporary table disk usage:
(tmp_table_size)  0.978 (should be <= 0.5) 
Sort merge pass rate:
(sort_buffer)  0 (should be <= 0.001) 
Query cache enabled:
(query_cache_type)  1 (should be >= 1 and <= 1) 
Query cache miss rate:
(query_cache_limit)  0 (should be <= 0.1) 
Query cache prune rate:
(query_cache_size)  0 (should be <= 0.05) 

[Ben_S]

Without

• A link to your status.php - You can download it from README: Checklist for performance problems
• Is this a dedicated server or shared host. If it is a dedicated server, what spec is the server and what else runs on the server? If it is a shared host /vps post a link to the hosts site the package you are on

I can't really begin to help you. A copy and paste from status.php isn't particulally easy to read and misses out a lot of the important variables. Likewise, given it's a vps, I have no idea how much ram etc you have to work with.

[jyajay]

You got mail.

[jyajay]

When SMF is not detecting eAccelerator but it is installed should i activate Caching anway or better turn it off?

[Ben_S]

If SMF is not detecting eA, then it isn't installed properly, any mention of it in php.ini?

[jyajay]

Sure, you can take a look when you go to the status.php that i gave you, just type ?php at the end of it.

My webhost installed it for me and double checked it so i think it is installed correct.

[H]

I don't believe eA is compatible with php 5.1

[Ben_S]

Odd, SMF should detect it then, personally I'd go with APC - http://pecl.php.net/package/APC - instead.

[jyajay]

After installation nothing is working:

Fatal error: Code from this file can only be run in conjunction with files encoded by
<
AôS|@ in /home/iv9luukg/public_html/forums/Settings.php on line 44

Whats that? Phpinfo is telling me that APC is enabled.

[Ben_S]

Odd, no idea why that would happen. .

[Niteblade]

Phpinfo tells me that I have eaccelerator v0.9.5-beta2 installed.

My php version is 5.1.2.
I'm on a shared host.
My Php is being run as a fastcgi.

SMF v1.1Rc2 doesn't automatically recogize that eaccelerator is installed.

This program makes use of the Zend Scripting Language Engine:
Zend Engine v2.1.0, Copyright (c) 1998-2006 Zend Technologies
    with eAccelerator v0.9.5-beta2, Copyright (c) 2004-2006 eAccelerator, by eAccelerator

edit:

although phpinfo said eaccelerator was installed and working, smf did not recognize that eaccelerator existed. Problem was resolved with smf recognition of an accelerator when I went with APC.

[Ben_S]

Start a new topic with all the info asked for in the sticky thread.

[BryanSD]

I would check to see if the verison of eAccelerator you are using is compatible with FastCGI...I know it's not with CGI.

Either way, I also have some other theories for the slow downs on VPS.  Out of curiosity to those that are seeing slowdowns on a VPS...are you using a control panel for your VPS.  If so, which one...Plesk, Cpanel, etc.  I won't say anymore until I hear back from a few people.

-Bryan

[jyajay]

Ok its time for some update.. 

After downgrading from PHP 5 back to the latest 4 version, installing MySQL 5 and disabling persistent connections it looks like my load looks fine.

I got that tip from here: http://arjen-lentz.livejournal.com/39717.html

*edit*

There is one question, what does that mean:

Query cache miss rate:
(query_cache_limit)  2120 (should be <= 0.1) 
--
Query cache prune rate:
(query_cache_size)  16132 (should be <= 0.05)

Could it be that it is because of SMF still not recognising eAcceleraot (currently 0.95.beta2 installed).

Which version of eAccelerator is for sure compatible with the latest SMF?

[Ben_S]

Nothing to do with eAccelerator (I'd use APC personally).

Whats your query cache settings set to?

[jyajay]

query_alloc_block_size: 8192
query_cache_limit: 2097152
query_cache_min_res_unit: 4096
query_cache_size: 8388608
query_cache_type: ON
query_cache_wlock_invalidate: OFF
query_prealloc_size: 8192
range_alloc_block_size: 2048

[jyajay]

I increased the table_cache but no change at all, still thousands of query missed.

[jyajay]

Are you still there Ben? I have now set up an cronjob to restart httpd and mysqld every three days because after a few days there are about 200 processes. After the restart everything is fine and runs very fast.

Btw the query issue is still there, tried it with every cache level but it makes no difference - eAccelerator is now properly detected.

[exrace]

I ran into a problem with a setup of SMF with the option of resizing avatars and the GD module.

It seemed with certain avatars (this one happened to be a remote animated GIF) the GD module was causing long page delays and excessive CPU when this avatar was present on page and it was resized. The problem disappeared when the offending avatar was removed from the person profile.

Check your settings for attachment and avatar resizing and see if it helps.

[jyajay]

Nope, I had resizing disabled from the very beginning.

Currently these entries are making trouble:

Table cache usage:
(table_cache)  0.37 (should be >= 0.5 and <= 0.9)
Key buffer write hit rate:
(key_buffer_size)  0.677 (should be <= 0.5) 
Thread cache usage:
(thread_cache_size)  0.2733 (should be >= 0.7 and <= 0.9)   
Query cache miss rate:
(query_cache_limit)  2325 (should be <= 0.1)

The rest looks good right now.

[exrace]

Do you use persistent connections setting for mysql in the smf config?

[jyajay]

As I described a few posts to the top I turned that off what reduced the server load a lot. 

----

My forum goes crazy, I cannot post any new threads or replies. Just a long time passes by and then this:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 1' at line 5
File: /home/i29JM/public_html/forums/Sources/Post.php
Line: 576

I didn't changed anything at SMF and yesterday everything worked fine.

[jyajay]

I really need some advice on that:

MySQL Statistics
MySQL 5.0.x
Connections per second:  0.5667 
Kilobytes received per second:  0 
Kilobytes sent per second:  0 
Queries per second:  10.6897 
Percentage of slow queries:  0 
Opened vs. Open tables:
(table_cache)  0 (should be <= 80) 
Table cache usage:
(table_cache)  1 (should be >= 0.5 and <= 0.9) 
Key buffer read hit rate:
(key_buffer_size)  0.0005 (should be <= 0.01) 
Key buffer write hit rate:
(key_buffer_size)  0.2138 (should be <= 0.5) 
Thread cache hit rate:
(thread_cache_size)  1948.5167 (should be >= 30 ) 
Thread cache usage:
(thread_cache_size)  0.3933 (should be >= 0.7 and <= 0.9) 
Temporary table disk usage:
(tmp_table_size)  0 (should be <= 0.5) 
Sort merge pass rate:
(sort_buffer)  0 (should be <= 0.001) 
Query cache enabled:
(query_cache_type)  1 (should be >= 1 and <= 1) 
Query cache miss rate:
(query_cache_limit)  21310 (should be <= 0.1) 
Query cache prune rate:
(query_cache_size)  13100 (should be <= 0.05) 


MySQL status
Aborted_clients: 1572
Aborted_connects: 1
Binlog_cache_disk_use: 0
Binlog_cache_use: 0
Bytes_received: 212
Bytes_sent: 6205
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: 1
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: 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: 1
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: 116911
Created_tmp_disk_tables: 0
Created_tmp_files: 128
Created_tmp_tables: 2
Delayed_errors: 0
Delayed_insert_threads: 0
Delayed_writes: 0
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: 211
Handler_rollback: 0
Handler_savepoint: 0
Handler_savepoint_rollback: 0
Handler_update: 0
Handler_write: 340
Innodb_buffer_pool_pages_data: 496
Innodb_buffer_pool_pages_dirty: 11
Innodb_buffer_pool_pages_flushed: 364709
Innodb_buffer_pool_pages_free: 1
Innodb_buffer_pool_pages_latched: 0
Innodb_buffer_pool_pages_misc: 15
Innodb_buffer_pool_pages_total: 512
Innodb_buffer_pool_read_ahead_rnd: 385
Innodb_buffer_pool_read_ahead_seq: 408
Innodb_buffer_pool_read_requests: 55988996
Innodb_buffer_pool_reads: 29508
Innodb_buffer_pool_wait_free: 0
Innodb_buffer_pool_write_requests: 7970909
Innodb_data_fsyncs: 334219
Innodb_data_pending_fsyncs: 0
Innodb_data_pending_reads: 0
Innodb_data_pending_writes: 0
Innodb_data_read: 649400320
Innodb_data_reads: 31748
Innodb_data_writes: 616377
Innodb_data_written: 3902697984
Innodb_dblwr_pages_written: 364709
Innodb_dblwr_writes: 24030
Innodb_log_waits: 0
Innodb_log_write_requests: 1008383
Innodb_log_writes: 274629
Innodb_os_log_fsyncs: 288436
Innodb_os_log_pending_fsyncs: 0
Innodb_os_log_pending_writes: 0
Innodb_os_log_written: 535871488
Innodb_page_size: 16384
Innodb_pages_created: 7598
Innodb_pages_read: 39375
Innodb_pages_written: 364709
Innodb_row_lock_current_waits: 0
Innodb_row_lock_time: 98833
Innodb_row_lock_time_avg: 95
Innodb_row_lock_time_max: 2770
Innodb_row_lock_waits: 1033
Innodb_rows_deleted: 30647
Innodb_rows_inserted: 2554717
Innodb_rows_read: 19871260
Innodb_rows_updated: 283488
Key_blocks_not_flushed: 0
Key_blocks_unused: 19583
Key_blocks_used: 2338
Key_read_requests: 18381792
Key_reads: 10045
Key_write_requests: 52629
Key_writes: 11251
Last_query_cost: 10.499000
Max_used_connections: 60
Not_flushed_delayed_rows: 0
Open_files: 202
Open_streams: 0
Open_tables: 256
Opened_tables: 0
Qcache_free_blocks: 4596
Qcache_free_memory: 16667240
Qcache_hits: 1030412
Qcache_inserts: 520392
Qcache_lowmem_prunes: 13100
Qcache_not_cached: 21310
Qcache_queries_in_cache: 13081
Qcache_total_blocks: 30851
Questions: 2205290
Rpl_status: NULL
Select_full_join: 0
Select_full_range_join: 0
Select_range: 0
Select_range_check: 0
Select_scan: 2
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
Ssl_accept_renegotiates: 0
Ssl_accepts: 0
Ssl_callback_cache_hits: 0
Ssl_cipher: 
Ssl_cipher_list: 
Ssl_client_connects: 0
Ssl_connect_renegotiates: 0
Ssl_ctx_verify_depth: 0
Ssl_ctx_verify_mode: 0
Ssl_default_timeout: 0
Ssl_finished_accepts: 0
Ssl_finished_connects: 0
Ssl_session_cache_hits: 0
Ssl_session_cache_misses: 0
Ssl_session_cache_mode: NONE
Ssl_session_cache_overflows: 0
Ssl_session_cache_size: 0
Ssl_session_cache_timeouts: 0
Ssl_sessions_reused: 0
Ssl_used_session_cache_entries: 0
Ssl_verify_depth: 0
Ssl_verify_mode: 0
Ssl_version: 
Table_locks_immediate: 1671688
Table_locks_waited: 95
Tc_log_max_pages_used: 0
Tc_log_page_size: 0
Tc_log_page_waits: 0
Threads_cached: 59
Threads_connected: 1
Threads_created: 60
Threads_running: 1
Uptime: 206301


MySQL variables
auto_increment_increment: 1
auto_increment_offset: 1
automatic_sp_privileges: ON
back_log: 50
basedir: /
binlog_cache_size: 32768
bulk_insert_buffer_size: 8388608
character_set_client: latin1
character_set_connection: latin1
character_set_database: latin1
character_set_filesystem: binary
character_set_results: latin1
character_set_server: latin1
character_set_system: utf8
character_sets_dir: /usr/share/mysql/charsets/
collation_connection: latin1_swedish_ci
collation_database: latin1_swedish_ci
collation_server: latin1_swedish_ci
completion_type: 0
concurrent_insert: 1
connect_timeout: 30
datadir: /var/lib/mysql/
date_format: %Y-%m-%d
datetime_format: %Y-%m-%d %H:%i:%s
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: OFF
expire_logs_days: 0
flush: OFF
flush_time: 0
ft_boolean_syntax: + -><()~*:""&|
ft_max_word_len: 84
ft_min_word_len: 4
ft_query_expansion_limit: 20
ft_stopword_file: (built-in)
group_concat_max_len: 1024
have_archive: YES
have_bdb: NO
have_blackhole_engine: NO
have_compress: YES
have_crypt: YES
have_csv: NO
have_example_engine: NO
have_federated_engine: NO
have_geometry: YES
have_innodb: YES
have_isam: NO
have_ndbcluster: NO
have_openssl: DISABLED
have_query_cache: YES
have_raid: NO
have_rtree_keys: YES
have_symlink: YES
init_connect: 
init_file: 
init_slave: 
innodb_additional_mem_pool_size: 1048576
innodb_autoextend_increment: 8
innodb_buffer_pool_awe_mem_mb: 0
innodb_buffer_pool_size: 8388608
innodb_checksums: ON
innodb_commit_concurrency: 0
innodb_concurrency_tickets: 500
innodb_data_file_path: ibdata1:10M:autoextend
innodb_data_home_dir: 
innodb_doublewrite: ON
innodb_fast_shutdown: 1
innodb_file_io_threads: 4
innodb_file_per_table: OFF
innodb_flush_log_at_trx_commit: 1
innodb_flush_method: 
innodb_force_recovery: 0
innodb_lock_wait_timeout: 50
innodb_locks_unsafe_for_binlog: OFF
innodb_log_arch_dir: 
innodb_log_archive: OFF
innodb_log_buffer_size: 1048576
innodb_log_file_size: 5242880
innodb_log_files_in_group: 2
innodb_log_group_home_dir: ./
innodb_max_dirty_pages_pct: 90
innodb_max_purge_lag: 0
innodb_mirrored_log_groups: 1
innodb_open_files: 300
innodb_support_xa: ON
innodb_sync_spin_loops: 20
innodb_table_locks: ON
innodb_thread_concurrency: 8
innodb_thread_sleep_delay: 10000
interactive_timeout: 28800
join_buffer_size: 126976
key_buffer_size: 25165824
key_cache_age_threshold: 300
key_cache_block_size: 1024
key_cache_division_limit: 100
language: /usr/share/mysql/english/
large_files_support: ON
large_page_size: 0
large_pages: OFF
license: GPL
local_infile: ON
locked_in_memory: OFF
log: OFF
log_bin: OFF
log_bin_trust_function_creators: OFF
log_error: 
log_slave_updates: OFF
log_slow_queries: OFF
log_warnings: 1
long_query_time: 10
low_priority_updates: OFF
lower_case_file_system: OFF
lower_case_table_names: 0
max_allowed_packet: 1048576
max_binlog_cache_size: 4294967295
max_binlog_size: 1073741824
max_connect_errors: 10
max_connections: 200
max_delayed_threads: 20
max_error_count: 64
max_heap_table_size: 16777216
max_insert_delayed_threads: 20
max_join_size: 18446744073709551615
max_length_for_sort_data: 1024
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
multi_range_count: 256
myisam_data_pointer_size: 6
myisam_max_sort_file_size: 2147483647
myisam_recover_options: OFF
myisam_repair_threads: 1
myisam_sort_buffer_size: 8388608
myisam_stats_method: nulls_unequal
net_buffer_length: 16384
net_read_timeout: 30
net_retry_count: 10
net_write_timeout: 60
new: OFF
old_passwords: OFF
open_files_limit: 10000
optimizer_prune_level: 1
optimizer_search_depth: 62
pid_file: /var/lib/mysql/sql.pid
prepared_stmt_count: 0
port: 3306
preload_buffer_size: 32768
protocol_version: 10
query_alloc_block_size: 8192
query_cache_limit: 1048576
query_cache_min_res_unit: 4096
query_cache_size: 33554432
query_cache_type: ON
query_cache_wlock_invalidate: OFF
query_prealloc_size: 8192
range_alloc_block_size: 2048
read_buffer_size: 126976
read_only: OFF
read_rnd_buffer_size: 262144
relay_log_purge: ON
relay_log_space_limit: 0
rpl_recovery_rank: 0
secure_auth: OFF
server_id: 0
skip_external_locking: ON
skip_networking: OFF
skip_show_database: OFF
slave_compressed_protocol: OFF
slave_load_tmpdir: /tmp/
slave_net_timeout: 3600
slave_skip_errors: OFF
slave_transaction_retries: 10
slow_launch_time: 2
socket: /var/lib/mysql/mysql.sock
sort_buffer_size: 262136
sql_mode: 
sql_notes: ON
sql_warnings: ON
storage_engine: MyISAM
sync_binlog: 0
sync_frm: ON
system_time_zone: CEST
table_cache: 256
table_lock_wait_timeout: 50
table_type: MyISAM
thread_cache_size: 150
thread_stack: 196608
time_format: %H:%i:%s
time_zone: SYSTEM
timed_mutexes: OFF
tmp_table_size: 33554432
tmpdir: 
transaction_alloc_block_size: 8192
transaction_prealloc_size: 4096
tx_isolation: REPEATABLE-READ
updatable_views_with_limit: YES
version: 5.0.21-standard
version_comment: MySQL Community Edition - Standard (GPL)
version_compile_machine: i686
version_compile_os: pc-linux-gnu
wait_timeout: 100

[jyajay]

Where do I have to go to get help? 

[forsakenlad]

I don't believe eA is compatible with php 5.1

Yes eA isn't currently compatible with PHP 5.1, its in beta stage. I don't recomend the use of eA once 0.9.5 goes final (speaking from experiance).