News:

SMF 2.1.4 has been released! Take it for a spin! Read more.

Main Menu

Still a Slow load

Started by Trevor Hale, December 22, 2009, 08:34:51 PM

Previous topic - Next topic

Trevor Hale

Hi Guys,

I am running SMF 2.0 Rc2 with PortaMx 0.971 and got a drastic increase migrating from RC1.2.  However, I am still experiencing major slow downs when I get 5 users and 20 guests running around the forum.  can take upwards of 20+ seconds to generate pages.

I ran status.php, and the numbers seem to be way out of wack.  Can anyone please offer any suggestions.

I have a VPS And we are currently in the process of upgrading to a dual core 3.0Ghz machine.  We will assign about 8 GB or ram to my SMF VPS Box, and I am told I will be allocated a complete single one of the CPU's.

Regardless I am looking for some suggestions to these numbers if anyone can help.



Basic Information
December 22, 2009, 09:19:30 AM
Operating System: CentOS release 5.3 (Final)
Processor: Intel® Xeon(TM) CPU 2.40GHz (2389.186MHz)
Load averages: 0.09, 0.36, 0.44
Current processes: 142 (141 sleeping, 1 running, 0 zombie)
Processes by CPU: httpd (22) 2.8%, mysqld (1) 1.4%, (other) (72) 1.2%, python (8) 0.7%
Memory usage: 84.526% (3293324k / 3896208k)
Swap: 0% (8k / 4128760k)
MySQL Statistics
MySQL 5.0.x
Connections per second: 0.0723
Kilobytes received per second: 0.3772
Kilobytes sent per second: 0.507
Queries per second: 1.3026
Percentage of slow queries: 0
Opened vs. Open tables:
(table_cache) 2264.3281 (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.0006 (should be <= 0.01)
Key buffer write hit rate:
(key_buffer_size) 0.4332 (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.4442 (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: 20
Aborted_connects: 94
Binlog_cache_disk_use: 0
Binlog_cache_use: 0
Bytes_received: 2185762344
Bytes_sent: 2938280133
Com_admin_commands: 17568
Com_alter_db: 0
Com_alter_table: 548
Com_analyze: 0
Com_backup_table: 0
Com_begin: 0
Com_call_procedure: 0
Com_change_db: 384102
Com_change_master: 0
Com_check: 0
Com_checksum: 0
Com_commit: 0
Com_create_db: 4
Com_create_function: 0
Com_create_index: 0
Com_create_table: 692
Com_create_user: 0
Com_dealloc_sql: 0
Com_delete: 170900
Com_delete_multi: 0
Com_do: 0
Com_drop_db: 4
Com_drop_function: 0
Com_drop_index: 0
Com_drop_table: 590
Com_drop_user: 0
Com_execute_sql: 0
Com_flush: 15
Com_grant: 0
Com_ha_close: 0
Com_ha_open: 0
Com_ha_read: 0
Com_help: 0
Com_insert: 278011
Com_insert_select: 501
Com_kill: 0
Com_load: 0
Com_load_master_data: 0
Com_load_master_table: 0
Com_lock_tables: 885
Com_optimize: 943
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: 75174
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: 4419595
Com_set_option: 440602
Com_show_binlog_events: 0
Com_show_binlogs: 0
Com_show_charsets: 9
Com_show_collations: 473
Com_show_column_types: 0
Com_show_create_db: 0
Com_show_create_table: 23437
Com_show_databases: 737
Com_show_errors: 0
Com_show_fields: 25516
Com_show_grants: 0
Com_show_innodb_status: 0
Com_show_keys: 735
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: 78
Com_show_slave_hosts: 0
Com_show_slave_status: 0
Com_show_status: 6
Com_show_storage_engines: 0
Com_show_tables: 4035
Com_show_triggers: 23437
Com_show_variables: 5
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: 26
Com_unlock_tables: 970
Com_update: 1084118
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: 409297
Created_tmp_disk_tables: 58635
Created_tmp_files: 5
Created_tmp_tables: 131988
Delayed_errors: 0
Delayed_insert_threads: 0
Delayed_writes: 0
Flush_commands: 1
Handler_commit: 0
Handler_delete: 201744
Handler_discover: 0
Handler_prepare: 0
Handler_read_first: 543544
Handler_read_key: 63163849
Handler_read_next: 29625231
Handler_read_prev: 5851160
Handler_read_rnd: 4855544
Handler_read_rnd_next: 178121858
Handler_rollback: 0
Handler_savepoint: 0
Handler_savepoint_rollback: 0
Handler_update: 1355423
Handler_write: 1611788
Innodb_buffer_pool_pages_data: 19
Innodb_buffer_pool_pages_dirty: 0
Innodb_buffer_pool_pages_flushed: 1
Innodb_buffer_pool_pages_free: 493
Innodb_buffer_pool_pages_misc: 0
Innodb_buffer_pool_pages_total: 512
Innodb_buffer_pool_read_ahead_rnd: 1
Innodb_buffer_pool_read_ahead_seq: 0
Innodb_buffer_pool_read_requests: 92
Innodb_buffer_pool_reads: 12
Innodb_buffer_pool_wait_free: 0
Innodb_buffer_pool_write_requests: 1
Innodb_data_fsyncs: 7
Innodb_data_pending_fsyncs: 0
Innodb_data_pending_reads: 0
Innodb_data_pending_writes: 0
Innodb_data_read: 2756608
Innodb_data_reads: 41
Innodb_data_writes: 7
Innodb_data_written: 35328
Innodb_dblwr_pages_written: 1
Innodb_dblwr_writes: 1
Innodb_log_waits: 0
Innodb_log_write_requests: 0
Innodb_log_writes: 2
Innodb_os_log_fsyncs: 5
Innodb_os_log_pending_fsyncs: 0
Innodb_os_log_pending_writes: 0
Innodb_os_log_written: 1024
Innodb_page_size: 16384
Innodb_pages_created: 0
Innodb_pages_read: 19
Innodb_pages_written: 1
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: 7053
Key_blocks_used: 1233
Key_read_requests: 278018477
Key_reads: 158488
Key_write_requests: 2522364
Key_writes: 1092592
Last_query_cost: 0.000000
Max_used_connections: 70
Ndb_cluster_node_id: 0
Ndb_config_from_host:
Ndb_config_from_port: 0
Ndb_number_of_data_nodes: 0
Not_flushed_delayed_rows: 0
Open_files: 120
Open_streams: 0
Open_tables: 64
Opened_tables: 144917
Prepared_stmt_count: 0
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
Queries: 7372149
Questions: 7372149
Rpl_status: NULL
Select_full_join: 244197
Select_full_range_join: 463
Select_range: 1129805
Select_range_check: 0
Select_scan: 1442147
Slave_open_temp_tables: 0
Slave_retried_transactions: 0
Slave_running: OFF
Slow_launch_threads: 787
Slow_queries: 277
Sort_merge_passes: 0
Sort_range: 106687
Sort_rows: 6783977
Sort_scan: 407426
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: 10544537
Table_locks_waited: 12929
Tc_log_max_pages_used: 0
Tc_log_page_size: 0
Tc_log_page_waits: 0
Threads_cached: 0
Threads_connected: 1
Threads_created: 409296
Threads_running: 1
Uptime: 5659624

MySQL variables
auto_increment_increment: 1
auto_increment_offset: 1
automatic_sp_privileges: ON
back_log: 50
basedir: /usr/
bdb_cache_size: 8384512
bdb_home: /var/lib/mysql/
bdb_log_buffer_size: 262144
bdb_logdir:
bdb_max_lock: 10000
bdb_shared_data: OFF
bdb_tmpdir: /tmp/
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: 10
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
keep_files_on_create: OFF
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: NO
have_bdb: YES
have_blackhole_engine: NO
have_compress: YES
have_community_features: NO
have_profiling: NO
have_crypt: YES
have_csv: NO
have_dynamic_loading: YES
have_example_engine: NO
have_federated_engine: NO
have_geometry: YES
have_innodb: YES
have_isam: NO
have_merge_engine: YES
have_ndbcluster: DISABLED
have_openssl: DISABLED
have_ssl: DISABLED
have_query_cache: YES
have_raid: NO
have_rtree_keys: YES
have_symlink: YES
hostname: cockpit.hosting.randrdevelopment.net
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_adaptive_hash_index: ON
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_rollback_on_timeout: OFF
innodb_support_xa: ON
innodb_sync_spin_loops: 20
innodb_table_locks: ON
innodb_thread_concurrency: 8
innodb_thread_sleep_delay: 10000
innodb_use_legacy_cardinality_algorithm: ON
interactive_timeout: 28800
join_buffer_size: 131072
key_buffer_size: 8384512
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: 1048576
max_binlog_cache_size: 4294963200
max_binlog_size: 1073741824
max_connect_errors: 10
max_connections: 100
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_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: 2146435072
myisam_recover_options: OFF
myisam_repair_threads: 1
myisam_sort_buffer_size: 8388608
myisam_stats_method: nulls_unequal
ndb_autoincrement_prefetch_sz: 1
ndb_force_send: ON
ndb_use_exact_count: ON
ndb_use_transactions: ON
ndb_cache_check_time: 0
ndb_connectstring:
net_buffer_length: 16384
net_read_timeout: 30
net_retry_count: 10
net_write_timeout: 60
new: OFF
old_passwords: ON
open_files_limit: 1024
optimizer_prune_level: 1
optimizer_search_depth: 62
pid_file: /var/run/mysqld/mysqld.pid
plugin_dir:
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: 0
query_cache_type: ON
query_cache_wlock_invalidate: OFF
query_prealloc_size: 8192
range_alloc_block_size: 4096
read_buffer_size: 131072
read_only: OFF
read_rnd_buffer_size: 262144
relay_log:
relay_log_index:
relay_log_info_file: relay-log.info
relay_log_purge: ON
relay_log_space_limit: 0
rpl_recovery_rank: 0
secure_auth: OFF
secure_file_priv:
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: 2097144
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: EDT
table_cache: 64
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.82sp1
version_bdb: Sleepycat Software: Berkeley DB 4.1.24: (July 21, 2009)
version_comment: Source distribution
version_compile_machine: i686
version_compile_os: redhat-linux-gnu
wait_timeout: 28800



Any help you can provide would be appreciated.  I would love to be able to optomize the way SMF performs for my members.

Vekseid

Your mysql configuration is seriously out of whack. Post your my.cnf?
Adult Role Playing Forums - - Over five million posts - - Elliquiy's LAMP configuration (maybe NSFW)

Blog about Forums and Servers - - Twenty things to make Simple Machines Forum go faster

Private/Instant Message requests for free support will be ignored.

Trevor Hale

#2
Here you go..  Thanks for looking at this.

I would have to say, based on all the reading I have been doing.. I hardly have any settings in this file.  Perhaps that is my problem, but the real question is.. What settings do I add, and what do I not..

Trev

Vekseid

#3
Well, lessee. Would want more details on your needs and specifics, but I've made some additions in bold that should help you enough for now.

Quote
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql


# You'll want to raise this depending on the size of your forum's MyISAM indexes. If you convert to InnoDB, you'll want to configure the buffers for that - but start here for now.
key_buffer                      = 16M
# A table cache of 1024 will suit until your forum is pushing a million posts and hundreds of online users, but you do need a minimum of 512 or so.
table_cache                     = 1024
# No reason to set these otherwise, unless someone does some benchmarks with optimizer_search_depth
concurrent_insert               = 2
optimizer_search_depth          = 4
# Always set the following two numbers equal. There's no reason to raise them over 32M for community sites - don't trust programs that tell you to.
tmp_table_size                  = 32M
max_heap_table_size             = 32M
# Again, the following two should be equal. You could probably get by with 16 or 32, but you used 70 at one point, so here's a buffer.
thread_cache_size               = 96
max_connections                 = 96
# Set this to 16M because that's what the client's is. Just annoying to have it out of sync.
max_allowed_packet              = 16M
# Make sure sure you're using the query cache at low loads - but eventually you will want to disable it.
query_cache_limit        = 256K
query_cache_size         = 16M
query_cache_min_res_unit = 4K


# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

# To allow mysqld to connect to a MySQL Cluster management daemon, uncomment
# these lines and adjust the connectstring as needed.
#ndbcluster
#ndb-connectstring="nodeid=4;host=localhost:1186"

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

[ndbd]
# If you are running a MySQL Cluster storage daemon (ndbd) on this machine,
# adjust its connection to the management daemon here.
# Note: ndbd init script requires this to include nodeid!
connect-string="nodeid=2;host=localhost:1186"

[ndb_mgm]
# connection string for MySQL Cluster management tool
connect-string="host=localhost:1186"
Adult Role Playing Forums - - Over five million posts - - Elliquiy's LAMP configuration (maybe NSFW)

Blog about Forums and Servers - - Twenty things to make Simple Machines Forum go faster

Private/Instant Message requests for free support will be ignored.

Trevor Hale

Awesome,

I will keep a backup of my old file, and try making these modifications..  Thanks for the help.

I will report back.

Trev

Vekseid

Derp. Edited out the ridiculous code tags >_>
Adult Role Playing Forums - - Over five million posts - - Elliquiy's LAMP configuration (maybe NSFW)

Blog about Forums and Servers - - Twenty things to make Simple Machines Forum go faster

Private/Instant Message requests for free support will be ignored.

Trevor Hale

LOL.  No worries, I figured out what you meant..  I replaced the file after editing it.  Stopped MySQL, and Restarted it, now here is my result.

I have seen a lot of reference to converting to INODB or however it is pronounced, and I am afraid that might be a bit out of my capability.  I have a growing community here, and expect to increase membership quickly.  It probably would be quicker to convert to that format sooner then later if it is preferred, just not sure about how to going about that.  Anyway, I appreciate your efforts, and hopefully this has made a difference, I doubt I will know for a few days if things really are smoother.

Thanks again,

Trev


Basic Information
December 23, 2009, 03:57:57 AM
Operating System: CentOS release 5.3 (Final)
Processor: Intel® Xeon(TM) CPU 2.40GHz (2389.186MHz)
Load averages: 3.92, 1.97, 1.13
Current processes: 150 (145 sleeping, 5 running, 0 zombie)
Processes by CPU: webalizer (1) 44.3%, collectinfo.pl (1) 23.3%, ps (1) 23.0%, sh (2) 13.5%, mysqld (1) 9.5%, httpd (22) 7.1%, webalizer.pl (1) 1.8%, su (1) 1.1%, (other) (68) 1.6%, mysqld_safe (1) 0.8%, python (8) 0.7%
Memory usage: 85.531% (3332472k / 3896208k)
Swap: 0.001% (60k / 4128760k)
MySQL Statistics
MySQL 5.0.x
Connections per second: 0.3607
Kilobytes received per second: 0.1839
Kilobytes sent per second: 1.5333
Queries per second: 1.1639
Percentage of slow queries: 0
Opened vs. Open tables:
(table_cache) 1.0206 (should be <= 80)
Table cache usage:
(table_cache) 0.2842 (should be >= 0.5 and <= 0.9)
Key buffer read hit rate:
(key_buffer_size) 0.1897 (should be <= 0.01)
Key buffer write hit rate:
(key_buffer_size) 1 (should be <= 0.5)
Thread cache hit rate:
(thread_cache_size) 11 (should be >= 30 )
Thread cache usage:
(thread_cache_size) 0.0104 (should be >= 0.7 and <= 0.9)
Temporary table disk usage:
(tmp_table_size) 0.2647 (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: 0
Aborted_connects: 1
Binlog_cache_disk_use: 0
Binlog_cache_use: 0
Bytes_received: 11485
Bytes_sent: 95778
Com_admin_commands: 0
Com_alter_db: 0
Com_alter_table: 0
Com_analyze: 0
Com_backup_table: 0
Com_begin: 0
Com_call_procedure: 0
Com_change_db: 4
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_create_user: 0
Com_dealloc_sql: 0
Com_delete: 1
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: 1
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: 28
Com_set_option: 2
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: 2
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: 1
Com_show_slave_hosts: 0
Com_show_slave_status: 0
Com_show_status: 2
Com_show_storage_engines: 0
Com_show_tables: 6
Com_show_triggers: 0
Com_show_variables: 2
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: 3
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: 22
Created_tmp_disk_tables: 9
Created_tmp_files: 5
Created_tmp_tables: 34
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: 4
Handler_read_key: 91
Handler_read_next: 108
Handler_read_prev: 4
Handler_read_rnd: 10
Handler_read_rnd_next: 1487
Handler_rollback: 0
Handler_savepoint: 0
Handler_savepoint_rollback: 0
Handler_update: 3
Handler_write: 1077
Innodb_buffer_pool_pages_data: 19
Innodb_buffer_pool_pages_dirty: 0
Innodb_buffer_pool_pages_flushed: 0
Innodb_buffer_pool_pages_free: 493
Innodb_buffer_pool_pages_misc: 0
Innodb_buffer_pool_pages_total: 512
Innodb_buffer_pool_read_ahead_rnd: 1
Innodb_buffer_pool_read_ahead_seq: 0
Innodb_buffer_pool_read_requests: 77
Innodb_buffer_pool_reads: 12
Innodb_buffer_pool_wait_free: 0
Innodb_buffer_pool_write_requests: 0
Innodb_data_fsyncs: 3
Innodb_data_pending_fsyncs: 0
Innodb_data_pending_reads: 0
Innodb_data_pending_writes: 0
Innodb_data_read: 2494464
Innodb_data_reads: 25
Innodb_data_writes: 3
Innodb_data_written: 1536
Innodb_dblwr_pages_written: 0
Innodb_dblwr_writes: 0
Innodb_log_waits: 0
Innodb_log_write_requests: 0
Innodb_log_writes: 1
Innodb_os_log_fsyncs: 3
Innodb_os_log_pending_fsyncs: 0
Innodb_os_log_pending_writes: 0
Innodb_os_log_written: 512
Innodb_page_size: 16384
Innodb_pages_created: 0
Innodb_pages_read: 19
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: 14449
Key_blocks_used: 48
Key_read_requests: 253
Key_reads: 48
Key_write_requests: 3
Key_writes: 3
Last_query_cost: 0.000000
Max_used_connections: 2
Ndb_cluster_node_id: 0
Ndb_config_from_host:
Ndb_config_from_port: 0
Ndb_number_of_data_nodes: 0
Not_flushed_delayed_rows: 0
Open_files: 579
Open_streams: 0
Open_tables: 291
Opened_tables: 297
Prepared_stmt_count: 0
Qcache_free_blocks: 3
Qcache_free_memory: 16685688
Qcache_hits: 0
Qcache_inserts: 17
Qcache_lowmem_prunes: 0
Qcache_not_cached: 23
Qcache_queries_in_cache: 15
Qcache_total_blocks: 48
Queries: 71
Questions: 71
Rpl_status: NULL
Select_full_join: 0
Select_full_range_join: 0
Select_range: 6
Select_range_check: 0
Select_scan: 21
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: 10
Sort_scan: 1
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: 52
Table_locks_waited: 0
Tc_log_max_pages_used: 0
Tc_log_page_size: 0
Tc_log_page_waits: 0
Threads_cached: 1
Threads_connected: 1
Threads_created: 2
Threads_running: 1
Uptime: 61

MySQL variables
auto_increment_increment: 1
auto_increment_offset: 1
automatic_sp_privileges: ON
back_log: 50
basedir: /usr/
bdb_cache_size: 8384512
bdb_home: /var/lib/mysql/
bdb_log_buffer_size: 262144
bdb_logdir:
bdb_max_lock: 10000
bdb_shared_data: OFF
bdb_tmpdir: /tmp/
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: 2
connect_timeout: 10
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
keep_files_on_create: OFF
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: NO
have_bdb: YES
have_blackhole_engine: NO
have_compress: YES
have_community_features: NO
have_profiling: NO
have_crypt: YES
have_csv: NO
have_dynamic_loading: YES
have_example_engine: NO
have_federated_engine: NO
have_geometry: YES
have_innodb: YES
have_isam: NO
have_merge_engine: YES
have_ndbcluster: DISABLED
have_openssl: DISABLED
have_ssl: DISABLED
have_query_cache: YES
have_raid: NO
have_rtree_keys: YES
have_symlink: YES
hostname: cockpit.hosting.randrdevelopment.net
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_adaptive_hash_index: ON
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_rollback_on_timeout: OFF
innodb_support_xa: ON
innodb_sync_spin_loops: 20
innodb_table_locks: ON
innodb_thread_concurrency: 8
innodb_thread_sleep_delay: 10000
innodb_use_legacy_cardinality_algorithm: ON
interactive_timeout: 28800
join_buffer_size: 131072
key_buffer_size: 16777216
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: 16777216
max_binlog_cache_size: 4294963200
max_binlog_size: 1073741824
max_connect_errors: 10
max_connections: 96
max_delayed_threads: 20
max_error_count: 64
max_heap_table_size: 33554432
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_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: 2146435072
myisam_recover_options: OFF
myisam_repair_threads: 1
myisam_sort_buffer_size: 8388608
myisam_stats_method: nulls_unequal
ndb_autoincrement_prefetch_sz: 1
ndb_force_send: ON
ndb_use_exact_count: ON
ndb_use_transactions: ON
ndb_cache_check_time: 0
ndb_connectstring:
net_buffer_length: 16384
net_read_timeout: 30
net_retry_count: 10
net_write_timeout: 60
new: OFF
old_passwords: ON
open_files_limit: 2154
optimizer_prune_level: 1
optimizer_search_depth: 4
pid_file: /var/run/mysqld/mysqld.pid
plugin_dir:
port: 3306
preload_buffer_size: 32768
protocol_version: 10
query_alloc_block_size: 8192
query_cache_limit: 262144
query_cache_min_res_unit: 4096
query_cache_size: 16777216
query_cache_type: ON
query_cache_wlock_invalidate: OFF
query_prealloc_size: 8192
range_alloc_block_size: 4096
read_buffer_size: 131072
read_only: OFF
read_rnd_buffer_size: 262144
relay_log:
relay_log_index:
relay_log_info_file: relay-log.info
relay_log_purge: ON
relay_log_space_limit: 0
rpl_recovery_rank: 0
secure_auth: OFF
secure_file_priv:
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: 2097144
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: EST
table_cache: 1024
table_lock_wait_timeout: 50
table_type: MyISAM
thread_cache_size: 96
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.82sp1
version_bdb: Sleepycat Software: Berkeley DB 4.1.24: (July 21, 2009)
version_comment: Source distribution
version_compile_machine: i686
version_compile_os: redhat-linux-gnu
wait_timeout: 2880

Something like that

Converting to InnoDB is advisable once you get more than 100 people online at once.

InnoDB is more sensitive to bad tuning, but it's no more difficult than setting a few variables in your my.cnf file.

Trevor Hale

This makes sense, and I am probably about 4 months away from that kind of traffic.  Currently getting just over a million hits a month (about 126,000) page views in that time frame. 

The above changes yo my.cnf have improved performance a little, but I think it could be better still.

Just need to know where to go with this next.

Best regards, and Happy Holidays to all.

Trev

Vekseid

Err, how many posts, topics, users and boards do you have? You may want to set the key_buffer to 64MB or 128MB.

Even that's suspicious, though - can you tell what else is running?
Adult Role Playing Forums - - Over five million posts - - Elliquiy's LAMP configuration (maybe NSFW)

Blog about Forums and Servers - - Twenty things to make Simple Machines Forum go faster

Private/Instant Message requests for free support will be ignored.

Trevor Hale

Right Now I have only 4,000+ Topics and 39 Boards.

Trev

Vekseid

But how many posts and users?
Adult Role Playing Forums - - Over five million posts - - Elliquiy's LAMP configuration (maybe NSFW)

Blog about Forums and Servers - - Twenty things to make Simple Machines Forum go faster

Private/Instant Message requests for free support will be ignored.

Trevor Hale

Sorry, I meant 4000 Posts..  and 300 users..  I upgraded my server hardware, and things are way better now..  Still think I can tweek the My.cnf a little better then what I have now, I read your LAMP tutorial, which is incredible by the way, but a little out of reach for me..

Thanks for all your help.  My site is only 3 months old.  But the posts, topics, and memberbase is growing at an amazing rate.

Trev

Advertisement: