Posting is slow, rest everything is fine

[samvirtual]

I migrated my forum from yabb 2.2, everything works fine, except there is a glitch when you try to make a post, the board loads, and displays fast, but posting a topic is so slow.

[karlbenson]

Type of hosting?
Name of Your host?
Errors in error log?

Can you upload a status.php (you can download from http://www.simplemachines.org/download/?tools )

[samvirtual]

thanks for your reply,
here is the complete output

Code Select Expand

Basic Information
January 04, 2008, 04:08:45 PM
Operating System: CentOS release 4.5 (Final) 
Processor: Intel® Xeon(TM) CPU 3.00GHz (3001.032MHz)
Load averages: 3.87, 4.76, 5.03
Current processes: 384 (377 sleeping, 2 running, 5 zombie)
Processes by CPU: php (14) 63.4%, mysqld (1) 37.4%, httpd (168) 15.1%, exim (28) 1.6%, (other) (59) 0.9% 
Memory usage: 97.087% (4028408k / 4149256k)
Swap: 0.561% (44852k / 7999988k) 


MySQL Statistics
MySQL 5.0.x
Connections per second:  5.9227 
Kilobytes received per second:  9.3361 
Kilobytes sent per second:  4.8512 
Queries per second:  86.9993 
Percentage of slow queries:  0 
Opened vs. Open tables:
(table_cache)  1591.3867 (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.0031 (should be <= 0.01) 
Key buffer write hit rate:
(key_buffer_size)  0.6524 (should be <= 0.5) 
Thread cache hit rate:
(thread_cache_size)  1 (should be >= 30 ) 
Thread cache usage:
(thread_cache_size)  0 (should be >= 0.7 and <= 0.9) 
Temporary table disk usage:
(tmp_table_size)  0.312 (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.3699 (should be <= 0.5) 
Query cache prune rate:
(query_cache_size)  0.1209 (should be <= 0.05) 

MySQL Statistics
MySQL 5.0.x
Connections per second:  5.9233 
Kilobytes received per second:  9.339 
Kilobytes sent per second:  4.9466 
Queries per second:  87.0076 
Percentage of slow queries:  0 
Opened vs. Open tables:
(table_cache)  1592.4258 (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.0031 (should be <= 0.01) 
Key buffer write hit rate:
(key_buffer_size)  0.6523 (should be <= 0.5) 
Thread cache hit rate:
(thread_cache_size)  1 (should be >= 30 ) 
Thread cache usage:
(thread_cache_size)  0 (should be >= 0.7 and <= 0.9) 
Temporary table disk usage:
(tmp_table_size)  0.3123 (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.3699 (should be <= 0.5) 
Query cache prune rate:
(query_cache_size)  0.121 (should be <= 0.05) 


MySQL status
Aborted_clients: 757
Aborted_connects: 999
Binlog_cache_disk_use: 0
Binlog_cache_use: 0
Bytes_received: 3690738782
Bytes_sent: 1954887852
Com_admin_commands: 230153
Com_alter_db: 0
Com_alter_table: 84
Com_analyze: 6
Com_backup_table: 0
Com_begin: 25289
Com_change_db: 3488594
Com_change_master: 0
Com_check: 1397
Com_checksum: 0
Com_commit: 22736
Com_create_db: 49
Com_create_function: 0
Com_create_index: 4
Com_create_table: 3462
Com_dealloc_sql: 0
Com_delete: 600274
Com_delete_multi: 95
Com_do: 0
Com_drop_db: 16
Com_drop_function: 0
Com_drop_index: 0
Com_drop_table: 1967
Com_drop_user: 0
Com_execute_sql: 0
Com_flush: 1987
Com_grant: 6473
Com_ha_close: 0
Com_ha_open: 0
Com_ha_read: 0
Com_help: 0
Com_insert: 1374536
Com_insert_select: 2409
Com_kill: 0
Com_load: 0
Com_load_master_data: 0
Com_load_master_table: 0
Com_lock_tables: 21478
Com_optimize: 2176
Com_preload_keys: 0
Com_prepare_sql: 0
Com_purge: 0
Com_purge_before_date: 0
Com_rename_table: 0
Com_repair: 377
Com_replace: 106872
Com_replace_select: 0
Com_reset: 0
Com_restore_table: 0
Com_revoke: 92
Com_revoke_all: 0
Com_rollback: 38
Com_savepoint: 0
Com_select: 7290975
Com_set_option: 3598910
Com_show_binlog_events: 0
Com_show_binlogs: 67
Com_show_charsets: 2101
Com_show_collations: 986
Com_show_column_types: 0
Com_show_create_db: 0
Com_show_create_table: 3450
Com_show_databases: 3040
Com_show_errors: 0
Com_show_fields: 12972
Com_show_grants: 755
Com_show_innodb_status: 0
Com_show_keys: 607
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: 1298
Com_show_slave_hosts: 0
Com_show_slave_status: 0
Com_show_status: 1386
Com_show_storage_engines: 4
Com_show_tables: 17203
Com_show_triggers: 392
Com_show_variables: 4105
Com_show_warnings: 15
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: 283
Com_unlock_tables: 21477
Com_update: 2232459
Com_update_multi: 9240
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: 2285981
Created_tmp_disk_tables: 193271
Created_tmp_files: 175070
Created_tmp_tables: 618859
Delayed_errors: 0
Delayed_insert_threads: 0
Delayed_writes: 463
Flush_commands: 2
Handler_commit: 0
Handler_delete: 936795
Handler_discover: 0
Handler_prepare: 0
Handler_read_first: 725550
Handler_read_key: 174751114
Handler_read_next: 2317040504
Handler_read_prev: 7310751
Handler_read_rnd: 27097142
Handler_read_rnd_next: 1404811369
Handler_rollback: 0
Handler_savepoint: 0
Handler_savepoint_rollback: 0
Handler_update: 16290655
Handler_write: 80459036
Innodb_buffer_pool_pages_data: 0
Innodb_buffer_pool_pages_dirty: 0
Innodb_buffer_pool_pages_flushed: 0
Innodb_buffer_pool_pages_free: 0
Innodb_buffer_pool_pages_latched: 0
Innodb_buffer_pool_pages_misc: 0
Innodb_buffer_pool_pages_total: 0
Innodb_buffer_pool_read_ahead_rnd: 0
Innodb_buffer_pool_read_ahead_seq: 0
Innodb_buffer_pool_read_requests: 0
Innodb_buffer_pool_reads: 0
Innodb_buffer_pool_wait_free: 0
Innodb_buffer_pool_write_requests: 0
Innodb_data_fsyncs: 0
Innodb_data_pending_fsyncs: 0
Innodb_data_pending_reads: 0
Innodb_data_pending_writes: 0
Innodb_data_read: 0
Innodb_data_reads: 0
Innodb_data_writes: 0
Innodb_data_written: 0
Innodb_dblwr_pages_written: 0
Innodb_dblwr_writes: 0
Innodb_log_waits: 0
Innodb_log_write_requests: 0
Innodb_log_writes: 0
Innodb_os_log_fsyncs: 0
Innodb_os_log_pending_fsyncs: 0
Innodb_os_log_pending_writes: 0
Innodb_os_log_written: 0
Innodb_page_size: 0
Innodb_pages_created: 0
Innodb_pages_read: 0
Innodb_pages_written: 0
Innodb_row_lock_current_waits: 0
Innodb_row_lock_time: 0
Innodb_row_lock_time_avg: 0
Innodb_row_lock_time_max: 0
Innodb_row_lock_waits: 0
Innodb_rows_deleted: 0
Innodb_rows_inserted: 0
Innodb_rows_read: 0
Innodb_rows_updated: 0
Key_blocks_not_flushed: 0
Key_blocks_unused: 44800
Key_blocks_used: 25871
Key_read_requests: 712193062
Key_reads: 2187053
Key_write_requests: 4025302
Key_writes: 2625890
Last_query_cost: 0.000000
Max_used_connections: 87
Not_flushed_delayed_rows: 0
Open_files: 490
Open_streams: 0
Open_tables: 256
Opened_tables: 407661
Qcache_free_blocks: 3372
Qcache_free_memory: 10275976
Qcache_hits: 12418108
Qcache_inserts: 6900218
Qcache_lowmem_prunes: 882385
Qcache_not_cached: 431416
Qcache_queries_in_cache: 7045
Qcache_total_blocks: 18935
Questions: 33579122
Rpl_status: NULL
Select_full_join: 44110
Select_full_range_join: 218
Select_range: 616255
Select_range_check: 291
Select_scan: 1644587
Slave_open_temp_tables: 0
Slave_retried_transactions: 0
Slave_running: OFF
Slow_launch_threads: 0
Slow_queries: 79
Sort_merge_passes: 87535
Sort_range: 411971
Sort_rows: 1795803003
Sort_scan: 714851
Table_locks_immediate: 16283776
Table_locks_waited: 17474
Tc_log_max_pages_used: 0
Tc_log_page_size: 0
Tc_log_page_waits: 0
Threads_cached: 0
Threads_connected: 17
Threads_created: 2285940
Threads_running: 2
Uptime: 385933


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: 5
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_dynamic_loading: YES
have_example_engine: NO
have_federated_engine: NO
have_geometry: YES
have_innodb: DISABLED
have_isam: NO
have_merge_engine: YES
have_ndbcluster: NO
have_openssl: NO
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: 
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: 131072
key_buffer_size: 67108864
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
lc_time_names: en_US
license: GPL
local_infile: ON
locked_in_memory: OFF
log: OFF
log_bin: OFF
log_bin_trust_function_creators: OFF
log_error: 
log_queries_not_using_indexes: OFF
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: 15727616
max_binlog_cache_size: 4294967295
max_binlog_size: 1073741824
max_connect_errors: 100000
max_connections: 1500
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: 50
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: ON
open_files_limit: 7510
optimizer_prune_level: 1
optimizer_search_depth: 62
pid_file: /var/lib/mysql/thetis.lunarmania.com.pid
port: 3306
preload_buffer_size: 32768
prepared_stmt_count: 0
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: 1044480
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: 4194296
sql_big_selects: ON
sql_mode: 
sql_notes: ON
sql_warnings: OFF
ssl_ca: 
ssl_capath: 
ssl_cert: 
ssl_cipher: 
ssl_key: 
storage_engine: MyISAM
sync_binlog: 0
sync_frm: ON
system_time_zone: PST
table_cache: 256
table_lock_wait_timeout: 50
table_type: MyISAM
thread_cache_size: 0
thread_stack: 196608
time_format: %H:%i:%s
time_zone: SYSTEM
timed_mutexes: OFF
tmp_table_size: 33554432
tmpdir: /tmp/
transaction_alloc_block_size: 8192
transaction_prealloc_size: 4096
tx_isolation: REPEATABLE-READ
updatable_views_with_limit: YES
version: 5.0.27-standard
version_comment: MySQL Community Edition - Standard (GPL)
version_compile_machine: i686
version_compile_os: pc-linux-gnu
wait_timeout: 200

[samvirtual]

you can access this at

http://usmle.tv/usmle-forum/status.php

[samvirtual]

the renewed url for the status.php is

http://usmle.tv/forum/status.php

p.s.  this status.php is on the all the older server,

[metallica48423]

the size of your host's mysql server's table cache is far too small for the load its under.  This causes writes to be painfully slow.

They need to increase the table cache size, if i'm reading right

[Something like that]

the size of your host's mysql server's table cache is far too small for the load its under.  This causes writes to be painfully slow.

They need to increase the table cache size, if i'm reading right

Yes. I'd set table_cache=512.

I'd also increase your query cache size by setting query_cache_size=64M (it's set to 32M now).


Also, MyISAM doesn't like concurrent writes. I experienced severe leg when posting using MyISAM. I highly recommend converting your tables to InnoDB.

If you DON'T change to InnoDB, I'd increase your key buffer size for MyISAM. It's currently set to 64M. I'd set it to 256M (you have the RAM to spare). key_buffer=256M

If you DO change to InnoDB, add/change innodb_buffer_pool_size=256M.

These settings can be found in you my.cnf file. Change them and restart MySQL or reboot.

[Sarge]

Hello samvirtual, have you tried the above suggestions?

[samvirtual]

I appreciate all the inputs you have put in, but i still have not understood, how to implement these suggestions.

[Sarge]

I appreciate all the inputs you have put in, but i still have not understood, how to implement these suggestions.

OK, post the contents of /etc/my.cnf (the file my.cnf in the /etc directory -- from the root directory of your server, not your account's) and we'll show you what exactly to do.

[samvirtual]

hi , thanks for your input once again,

here is /etc/my.cnf file

[mysqld]
set-variable = max_connections=500
safe-show-database


i was surprised there were only 3 lines in it.


i have moved to a new server so my status.php output currently is this. even though the load on this server is much less, ram is more, but the forums hardly show any improvement in performance. speed wise.

Code Select Expand

Basic Information
February 01, 2008, 06:07:42 PM
Operating System: CentOS release 5 (Final)
Processor: Intel® Xeon® CPU E5335 @ 2.00GHz (2000.074MHz)
Load averages: 0.00, 0.01, 0.00
Current processes: 153 (152 sleeping, 1 running, 0 zombie)
Processes by CPU: (other) (56) 0.1%
Memory usage: 94.862% (373172k / 393384k)
Swap: 0.023% (60k / 262136k)
MySQL Statistics
MySQL 4.1.x
Connections per second: 0.0227
Kilobytes received per second: 0.5826
Kilobytes sent per second: 0.8175
Queries per second: 4.5514
Percentage of slow queries: 0
Opened vs. Open tables:
(table_cache) 6.3438 (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.0047 (should be <= 0.01)
Key buffer write hit rate:
(key_buffer_size) 0.9598 (should be <= 0.5)
Thread cache hit rate:
(thread_cache_size) 1.0076 (should be >= 30 )
Thread cache usage:
(thread_cache_size) 0 (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) 1 (should be <= 0.5)
Query cache prune rate:
(query_cache_size) 0 (should be <= 0.05)

MySQL Statistics
MySQL 4.1.x
Connections per second: 0.0228
Kilobytes received per second: 0.5803
Kilobytes sent per second: 0.8159
Queries per second: 4.5346
Percentage of slow queries: 0
Opened vs. Open tables:
(table_cache) 6.3438 (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.0047 (should be <= 0.01)
Key buffer write hit rate:
(key_buffer_size) 0.9598 (should be <= 0.5)
Thread cache hit rate:
(thread_cache_size) 1.0075 (should be >= 30 )
Thread cache usage:
(thread_cache_size) 0 (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) 1 (should be <= 0.5)
Query cache prune rate:
(query_cache_size) 0 (should be <= 0.05)

MySQL status
Aborted_clients: 2
Aborted_connects: 0
Binlog_cache_disk_use: 0
Binlog_cache_use: 0
Bytes_received: 3493669
Bytes_sent: 4911945
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: 10229
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: 10033
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: 5
Com_grant: 6
Com_ha_close: 0
Com_ha_open: 0
Com_ha_read: 0
Com_help: 0
Com_insert: 3695
Com_insert_select: 0
Com_kill: 0
Com_load: 0
Com_load_master_data: 0
Com_load_master_table: 0
Com_lock_tables: 34
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: 18
Com_replace_select: 0
Com_reset: 0
Com_restore_table: 0
Com_revoke: 4
Com_revoke_all: 0
Com_rollback: 0
Com_savepoint: 0
Com_select: 1524
Com_set_option: 253
Com_show_binlog_events: 0
Com_show_binlogs: 2
Com_show_charsets: 51
Com_show_collations: 51
Com_show_column_types: 0
Com_show_create_db: 0
Com_show_create_table: 18
Com_show_databases: 54
Com_show_errors: 0
Com_show_fields: 57
Com_show_grants: 27
Com_show_innodb_status: 0
Com_show_keys: 31
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: 24
Com_show_slave_hosts: 0
Com_show_slave_status: 0
Com_show_status: 5
Com_show_storage_engines: 0
Com_show_tables: 169
Com_show_variables: 110
Com_show_warnings: 6
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: 0
Com_unlock_tables: 34
Com_update: 89
Com_update_multi: 1
Connections: 134
Created_tmp_disk_tables: 0
Created_tmp_files: 4
Created_tmp_tables: 1
Delayed_errors: 0
Delayed_insert_threads: 0
Delayed_writes: 0
Flush_commands: 1
Handler_commit: 0
Handler_delete: 10078
Handler_discover: 0
Handler_read_first: 104
Handler_read_key: 3662
Handler_read_next: 12499
Handler_read_prev: 0
Handler_read_rnd: 294
Handler_read_rnd_next: 1187901
Handler_rollback: 0
Handler_update: 103
Handler_write: 4129
Key_blocks_not_flushed: 0
Key_blocks_unused: 6816
Key_blocks_used: 432
Key_read_requests: 124436
Key_reads: 586
Key_write_requests: 58304
Key_writes: 55960
Max_used_connections: 4
Not_flushed_delayed_rows: 0
Open_files: 122
Open_streams: 0
Open_tables: 64
Opened_tables: 406
Qcache_free_blocks: 0
Qcache_free_memory: 0
Qcache_hits: 0
Qcache_inserts: 0
Qcache_lowmem_prunes: 0
Qcache_not_cached: 0
Qcache_queries_in_cache: 0
Qcache_total_blocks: 0
Questions: 26659
Rpl_status: NULL
Select_full_join: 11
Select_full_range_join: 0
Select_range: 40
Select_range_check: 0
Select_scan: 1077
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: 7
Sort_rows: 735
Sort_scan: 40
Table_locks_immediate: 16097
Table_locks_waited: 0
Threads_cached: 0
Threads_connected: 3
Threads_created: 133
Threads_running: 1
Uptime: 5879

MySQL variables
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_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
concurrent_insert: ON
connect_timeout: 5
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
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: NO
have_bdb: NO
have_blackhole_engine: NO
have_compress: YES
have_crypt: YES
have_csv: NO
have_example_engine: NO
have_geometry: YES
have_innodb: YES
have_isam: NO
have_merge_engine: YES
have_ndbcluster: NO
have_openssl: NO
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_data_file_path: ibdata1:10M:autoextend
innodb_data_home_dir:
innodb_fast_shutdown: ON
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_table_locks: ON
innodb_thread_concurrency: 8
interactive_timeout: 28800
join_buffer_size: 131072
key_buffer_size: 8388600
key_cache_age_threshold: 300
key_cache_block_size: 1024
key_cache_division_limit: 100
language: /usr/share/mysql/english/
large_files_support: ON
lc_time_names: en_US
license: GPL
local_infile: ON
locked_in_memory: OFF
log: OFF
log_bin: OFF
log_error:
log_slave_updates: OFF
log_slow_queries: OFF
log_update: 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: 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_prepared_stmt_count: 16382
max_relay_log_size: 0
max_seeks_for_key: 4294967295
max_sort_length: 1024
max_tmp_tables: 32
max_user_connections: 0
max_write_lock_count: 4294967295
myisam_data_pointer_size: 4
myisam_max_extra_sort_file_size: 2147483648
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: 2500
pid_file: /var/lib/mysql/host.plotvalue.com.pid
port: 3306
preload_buffer_size: 32768
prepared_stmt_count: 0
protocol_version: 10
query_alloc_block_size: 8192
query_cache_limit: 1048576
query_cache_min_res_unit: 4096
query_cache_size: 0
query_cache_type: ON
query_cache_wlock_invalidate: OFF
query_prealloc_size: 8192
range_alloc_block_size: 2048
read_buffer_size: 131072
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_net_timeout: 3600
slave_transaction_retries: 0
slow_launch_time: 2
socket: /var/lib/mysql/mysql.sock
sort_buffer_size: 2097144
sql_mode:
sql_notes: ON
sql_warnings: ON
storage_engine: MyISAM
sync_binlog: 0
sync_frm: ON
sync_replication: 0
sync_replication_slave_id: 0
sync_replication_timeout: 0
system_time_zone: EST
table_cache: 64
table_type: MyISAM
thread_cache_size: 0
thread_stack: 196608
time_format: %H:%i:%s
time_zone: SYSTEM
tmp_table_size: 33554432
tmpdir:
transaction_alloc_block_size: 8192
transaction_prealloc_size: 4096
tx_isolation: REPEATABLE-READ
version: 4.1.22-standard
version_comment: MySQL Community Edition - Standard (GPL)
version_compile_machine: i686
version_compile_os: pc-linux-gnu
wait_timeout: 28800

[Sarge]

Phalloidium already gave some suggestions. Enter these lines after in the [mysqld] section of /etc/my.cnf, after safe-show-database:

Code Select Expand


table_cache=512
query_cache_size=64M
key_buffer_size=256M


If you can login directly to the server as root using SSH (you can use PuTTy for that), make a backup copy of my.cnf and edit it by running these commands from the shell:

Code Select Expand


cp /etc/my.cnf /etc/my.cnf.backup
nano /etc/my.cnf


Enter the lines I mentioned at the start of this post, press Ctrl+O and Enter on the keyboard to save the file, then Ctrl+X to close nano and return to the shell.

Then restart MySQL with the command:

Code Select Expand


service mysql restart


Let MySQL run for about 48 hours and let us know of the performance.

Note:
If MySQL doesn't start or behaves strangely, you can restore the old my.cnf with:

Code Select Expand


mv -f /etc/my.cnf.backup /etc/my.cnf


Also, I see some zombie processes running on your server. Check (select) "Disable hostname lookups" from SMF Admin > Features and Options > Layout and Options tab. This can help a lot with performance too.

[Chriss Cohn]

In addition i advice you strongly to use APC php-cache... this will lead into that your load averages will drop drastically.
I nearly started to scream as i read this:

Processes by CPU: php (14) 63.4%

Also with the changes advised by my pre-talkers, your mySQL load will drop drastically (hopefully).

Do this and everything should be fine....

[samvirtual]

thanks a lot guys, i will get back with the results, on monday, and yes i do have root access. it is on vps.

[slackerpunk]

hi here is the my.cnf file of my server

Code Select Expand

[mysqld]
set-variable=local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

skip-bdb

set-variable = innodb_buffer_pool_size=2M
set-variable = innodb_additional_mem_pool_size=500K
set-variable = innodb_log_buffer_size=500K
set-variable = innodb_thread_concurrency=2
[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
#log-error=/var/log/mysqld.log
#pid-file=/var/run/mysqld/mysqld.pid
#skip-bdb

set-variable = innodb_buffer_pool_size=2M
set-variable = innodb_additional_mem_pool_size=500K
set-variable = innodb_log_buffer_size=500K
set-variable = innodb_thread_concurrency=2

thread_cache_size = 256
table_cache = 1024
max_connections = 1000
max_user_connections = 1500
long_query_time = 5
query_cache_size = 64
max_heap_table_size - 32
tmp_table_size = 64
concurrent_insert = 2

Some Notes
Operating System:     Linux 2.6.9-023stab044.11-entnosplit
Processor:    Intel® Xeon® CPU E5310 @ 1.60GHz (400.081MHz)
Memory: 384MB Guaranteed 1024 Burst
Bandwidth: 450GB/month

Status Php File
-Usually around 100-150 users during peak hours
-I have APC installed Level 1 caching
-Mods
Ad management
Tinyportal
bookmarks
who voted what
youtube bbcode
signature settings mod
simple imageshack
smfshop

anyonce can recommend good configuration for my.cnf? thanks

[Sarge]

Examine the output of mysqlreport and MySQL Tuning Primer.

For more accurate results, be sure to let the MySQL service running for 48 hours before running these tools.

Run mysqlreport with the --all switch (make sure it's executable):

Code Select Expand

./mysqlreport --all

Also check out The Guide To Understanding mysqlreport, it's quite detailed and explains a lot of things.

Tuning Primer is a shell script, so you run it as:

Code Select Expand

sh ./tuning-primer.sh

The above commands assume that you're running the commands from the directory where the tools are saved. Also, it might be necessary to run them when logged on as the root user.

To save the output for later viewing, you can pipe it to a file by adding > file to the command, for example:

Code Select Expand

./mysqlreport --all > mysqlreport.txt
Then you can cp (copy) them to your forum directory, for example, and download them with a FTP client onto your computer. You would probably have to chown the output files to your FTP user first, otherwise the transfer will either fail or create 0-byte files.

Let us know what the reports tell you. Be sure to edit out any sensitive information.

[slackerpunk]

ok I have just restarted the httpd and mysqld I will post here probably tomorrow

[samvirtual]

Hi guys, thanks for all your input. Here are the results. everything is working fine, and fast, but i dont know which change is responsible for it, and i have a feeling i still have nt tweaked to perfection.

here is the output

Status.php

php info

APC. PHP

I have eaccelerator and apc installed. But the apc.php shows 99% misses. what does that mean?

[Chriss Cohn]

Hi guys, thanks for all your input. Here are the results. everything is working fine, and fast, but i dont know which change is responsible for it, and i have a feeling i still have nt tweaked to perfection.

here is the output

Status.php

php info

APC. PHP

I have eaccelerator and apc installed. But the apc.php shows 99% misses. what does that mean?

Ahhhmmmmm.... 125MB for the APC cache file?
To cache the WHOLE Forum scripts, not more than 25MB are necessary.
The rest seems to be pretty high configured.

So i recommend to change:
apc.shm_size to max 30(MB)
and set "apc.mmap_file_mask" to nothing (don't define it in php.ini) so it always uses RAM instead of Disk.

Quote from the documentation:

apc.mmap_file_mask     string                   If compiled with MMAP support by using --enable-mmap      this is the mktemp-style file_mask to pass to the      mmap module for determing whether your mmap'ed memory      region is going to be file-backed or shared memory      backed.  For straight file-backed mmap, set it to      something like /tmp/apc.XXXXXX      (exactly 6 Xs).      To use POSIX-style shm_open/mmap put a .shm      somewhere in your mask.  e.g. /apc.shm.XXXXXX      You can also set it to /dev/zero to use your      kernel's /dev/zero interface to anonymous mmap'ed      memory.  Leaving it undefined will force an anonymous mmap.

I always leave it therefor undefined.Regards, Christian

[samvirtual]

thanks for you response, i have made the cache 30 megs, and edited the php.ini as suggested. thanks.  but i still dont understand what the 99% misses imply.