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

[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).