News:

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

Main Menu

Interesting performance

Started by H, September 21, 2005, 12:53:06 PM

Previous topic - Next topic

H

I have been reading this forum with interest and decided to use status.php to see how my own forum compared. The site is not the one is my sig though it is also hosted at 1and1.

I was amazed that they are using so much memory.

Yet the sight seems fast. (page render time of under 0.3 seconds)

Is it worth contacting the host telling them about the incorrect mysql config/memory usage before the server becomes really slow?



Basic Information
September 21, 2005, 06:48:02 PM
Operating System: Debian 3.0
Processor: Pentium (1800MHz)
Current processes: 4 (2 sleeping, 2 running, 0 zombie)
Processes by CPU: php4 (1) 16.0%, (other) (2) 0.0%
Memory usage: 99.374% (3984708k / 4009816k)
Swap: 3.165% (8388k / 265064k)
MySQL Statistics
MySQL 4.0.x
Connections per second: 11.67
Kilobytes received per second: 1.9816
Kilobytes sent per second: 1.5452
Queries per second: 258.0173
Percentage of slow queries: 0
Opened vs. Open tables:
(table_cache) 475432.2188 (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.027 (should be <= 0.01)
Key buffer write hit rate:
(key_buffer_size) 0.1595 (should be <= 0.5)
Thread cache hit rate:
(thread_cache_size) 1.0218 (should be >= 30 )
Thread cache usage:
(thread_cache_size) 0.85 (should be >= 0.7 and <= 0.9)
Temporary table disk usage:
(tmp_table_size) 0.115 (should be <= 0.5)
Sort merge pass rate:
(sort_buffer) 0.0001 (should be <= 0.001)
Query cache enabled:
(query_cache_type) 1 (should be >= 1 and <= 1)
Query cache miss rate:
(query_cache_limit) 0.0897 (should be <= 0.1)
Query cache prune rate:
(query_cache_size) 0.3352 (should be <= 0.05)

MySQL status
Aborted_clients: 19963
Aborted_connects: 1490
Bytes_received: 2447064462
Bytes_sent: 1908152168
Com_admin_commands: 3193138
Com_alter_table: 7262
Com_analyze: 4
Com_backup_table: 0
Com_begin: 40850
Com_change_db: 34281500
Com_change_master: 0
Com_check: 978170
Com_commit: 40627
Com_create_db: 35
Com_create_function: 0
Com_create_index: 9
Com_create_table: 20313
Com_delete: 5330876
Com_delete_multi: 60
Com_drop_db: 19
Com_drop_function: 0
Com_drop_index: 0
Com_drop_table: 2011
Com_flush: 41532
Com_grant: 31
Com_ha_close: 0
Com_ha_open: 0
Com_ha_read: 0
Com_insert: 8645035
Com_insert_select: 133283
Com_kill: 30
Com_load: 2082
Com_load_master_data: 0
Com_load_master_table: 0
Com_lock_tables: 186986
Com_optimize: 1017398
Com_purge: 0
Com_rename_table: 0
Com_repair: 3608
Com_replace: 461250
Com_replace_select: 770
Com_reset: 0
Com_restore_table: 0
Com_revoke: 19
Com_rollback: 0
Com_savepoint: 0
Com_select: 68042463
Com_set_option: 1013475
Com_show_binlog_events: 0
Com_show_binlogs: 348
Com_show_create: 1002833
Com_show_databases: 2133
Com_show_fields: 3011920
Com_show_grants: 2826
Com_show_keys: 18692
Com_show_logs: 0
Com_show_master_status: 0
Com_show_new_master: 0
Com_show_open_tables: 0
Com_show_processlist: 25094
Com_show_slave_hosts: 0
Com_show_slave_status: 0
Com_show_status: 50397
Com_show_innodb_status: 0
Com_show_tables: 1220892
Com_show_variables: 1184
Com_slave_start: 0
Com_slave_stop: 0
Com_truncate: 146
Com_unlock_tables: 186987
Com_update: 15884083
Com_update_multi: 220542
Connections: 14073682
Created_tmp_disk_tables: 651791
Created_tmp_tables: 5667116
Created_tmp_files: 158370
Delayed_insert_threads: 1
Delayed_writes: 8725
Delayed_errors: 0
Flush_commands: 1
Handler_commit: 38
Handler_delete: 2648853
Handler_read_first: 7018136
Handler_read_key: 1977086719
Handler_read_next: 2358476900
Handler_read_prev: 1094208364
Handler_read_rnd: 660880356
Handler_read_rnd_next: 4012965624
Handler_rollback: 21883
Handler_update: 305188541
Handler_write: 1225552615
Key_blocks_used: 15586
Key_read_requests: 4266462723
Key_reads: 115126750
Key_write_requests: 87259590
Key_writes: 13916213
Max_used_connections: 158
Not_flushed_key_blocks: 0
Not_flushed_delayed_rows: 0
Open_tables: 64
Open_files: 127
Open_streams: 0
Opened_tables: 30427662
Questions: 311161916
Qcache_queries_in_cache: 9325
Qcache_inserts: 50965227
Qcache_hits: 154801959
Qcache_lowmem_prunes: 22809212
Qcache_not_cached: 6101566
Qcache_free_memory: 12946144
Qcache_free_blocks: 4104
Qcache_total_blocks: 25441
Rpl_status: NULL
Select_full_join: 1066104
Select_full_range_join: 93265
Select_range: 1283745
Select_range_check: 1602
Select_scan: 22888413
Slave_open_temp_tables: 0
Slave_running: OFF
Slow_launch_threads: 0
Slow_queries: 2122
Sort_merge_passes: 68004
Sort_range: 2249761
Sort_rows: 785830658
Sort_scan: 8657433
Table_locks_immediate: 107013509
Table_locks_waited: 486818
Threads_cached: 17
Threads_created: 13773243
Threads_connected: 4
Threads_running: 1
Uptime: 1205973

MySQL variables
back_log: 50
basedir: /usr/
binlog_cache_size: 32768
bulk_insert_buffer_size: 8388608
character_set: german1
character_sets: latin1 big5 czech euc_kr gb2312 gbk latin1_de sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5
concurrent_insert: ON
connect_timeout: 5
convert_character_set:
datadir: /db/mysql/
default_week_format: 3
delay_key_write: ON
delayed_insert_limit: 100
delayed_insert_timeout: 300
delayed_queue_size: 1000
flush: OFF
flush_time: 300
ft_boolean_syntax: + -><()~*:""&|
ft_min_word_len: 4
ft_max_word_len: 254
ft_max_word_len_for_sort: 20
ft_stopword_file: (built-in)
have_bdb: NO
have_crypt: YES
have_innodb: YES
have_isam: YES
have_raid: NO
have_symlink: YES
have_openssl: NO
have_query_cache: YES
init_file:
innodb_additional_mem_pool_size: 20971520
innodb_autoextend_increment: 8
innodb_buffer_pool_size: 134217728
innodb_data_file_path: /db/mysql/ibdata1:10M:autoextend
innodb_data_home_dir:
innodb_file_io_threads: 4
innodb_force_recovery: 0
innodb_thread_concurrency: 8
innodb_flush_log_at_trx_commit: 1
innodb_fast_shutdown: ON
innodb_flush_method:
innodb_lock_wait_timeout: 60
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_mirrored_log_groups: 1
innodb_max_dirty_pages_pct: 90
innodb_max_purge_lag: 0
innodb_table_locks: ON
interactive_timeout: 3600
join_buffer_size: 131072
key_buffer_size: 16777216
language: /usr/share/mysql/english/
large_files_support: ON
license: GPL
local_infile: ON
locked_in_memory: OFF
log: ON
log_update: OFF
log_bin: OFF
log_slave_updates: OFF
log_slow_queries: ON
log_warnings: 3
long_query_time: 3
low_priority_updates: OFF
lower_case_file_system: OFF
lower_case_table_names: 0
max_allowed_packet: 10484736
max_binlog_cache_size: 4294967295
max_binlog_size: 1073741824
max_connections: 240
max_connect_errors: 1000
max_delayed_threads: 20
max_insert_delayed_threads: 20
max_heap_table_size: 16777216
max_join_size: 4294967295
max_relay_log_size: 0
max_seeks_for_key: 4294967295
max_sort_length: 1024
max_user_connections: 18
max_tmp_tables: 32
max_write_lock_count: 4294967295
myisam_max_extra_sort_file_size: 268435456
myisam_max_sort_file_size: 2147483647
myisam_repair_threads: 1
myisam_recover_options: BACKUP,FORCE
myisam_sort_buffer_size: 8388608
net_buffer_length: 16384
net_read_timeout: 30
net_retry_count: 10
net_write_timeout: 60
new: OFF
open_files_limit: 32768
pid_file: /var/run/mysqld/mysqld.pid
log_error: /var/log/mysql.err
port: 3306
protocol_version: 10
query_alloc_block_size: 8192
query_cache_limit: 1048576
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: 131072
read_only: OFF
read_rnd_buffer_size: 262144
rpl_recovery_rank: 0
server_id: 0
slave_net_timeout: 3600
skip_external_locking: ON
skip_networking: OFF
skip_show_database: OFF
slow_launch_time: 2
socket: /var/run/mysqld/mysqld.sock
sort_buffer_size: 524280
sql_mode: 0
table_cache: 64
table_type: MYISAM
thread_cache_size: 20
thread_stack: 126976
tx_isolation: REPEATABLE-READ
timezone: CEST
tmp_table_size: 33554432
tmpdir: /var/tmp/
transaction_alloc_block_size: 8192
transaction_prealloc_size: 4096
version: 4.0.25-standard-log
version_comment: Official MySQL RPM
version_compile_os: pc-linux-gnu
wait_timeout: 60
-H
Former Support Team Lead
                              I recommend:
Namecheap (domains)
Fastmail (e-mail)
Linode (VPS)
                             

Ben_S

*nix systems use all the ram for caching, as long as its not using loads of swap then it's perfectly fine.

As for the config, I wouldn't bother, a big faceless company like one and one will not be interested in making changes,
Liverpool FC Forum with 14 million+ posts.

H

Quote from: Ben_S on September 21, 2005, 01:57:58 PM
*nix systems use all the ram for caching, as long as its not using loads of swap then it's perfectly fine.

As for the config, I wouldn't bother, a big faceless company like one and one will not be interested in making changes,

Thanks. I didnt know about that caching as I doesnt occur on my test FC4 machine  ;)
-H
Former Support Team Lead
                              I recommend:
Namecheap (domains)
Fastmail (e-mail)
Linode (VPS)
                             

[Unknown]

What's sad is with so many queries per second (258.0173) minor optimizations would go a long way.

Are you sure about FC4?  It should happen on basically anything that uses the Linux kernel.  Basically, with memory usage, we have a few things going on:

  - regular memory usage.  The memory usage we all know and love.
  - shared memory usage.  Basically the same, but shared between processes (e.g. between MySQL processes.)
  - cache memory usage.  Cache is used by the Linux kernel, and generally sits around until ram is needed, at which point it goes away.
  - buffer memory usage.  Buffers are used by the filesystem/IO when reading files and etc.  IIRC, less volatile than cache, but goes away pretty quick.
  - swap memory usage.  The overflow area.  If the system runs out of working space, it creeps into the swap, which is slow (not in RAM, but on the harddisk.)

Normally, as long as you only get a little bit of swap usage at peak times (or better yet, none at all!) you're doing fine.  In that respect, their server seems to be purring along.  It's just that they're using the CGI version of PHP, probably no accelerator at all, poor default MySQL settings, etc.

-[Unknown]

H

Quote from: [Unknown] on September 21, 2005, 03:05:23 PM
What's sad is with so many queries per second (258.0173) minor optimizations would go a long way.

Are you sure about FC4?  It should happen on basically anything that uses the Linux kernel.  Basically, with memory usage, we have a few things going on:

  - regular memory usage.  The memory usage we all know and love.
  - shared memory usage.  Basically the same, but shared between processes (e.g. between MySQL processes.)
  - cache memory usage.  Cache is used by the Linux kernel, and generally sits around until ram is needed, at which point it goes away.
  - buffer memory usage.  Buffers are used by the filesystem/IO when reading files and etc.  IIRC, less volatile than cache, but goes away pretty quick.
  - swap memory usage.  The overflow area.  If the system runs out of working space, it creeps into the swap, which is slow (not in RAM, but on the harddisk.)

Normally, as long as you only get a little bit of swap usage at peak times (or better yet, none at all!) you're doing fine.  In that respect, their server seems to be purring along.  It's just that they're using the CGI version of PHP, probably no accelerator at all, poor default MySQL settings, etc.

-[Unknown]

Ok

The FC4 development machine runs standard php,mysql,eaccelerator but uses only about 10% of allocated memory (512mb)
-H
Former Support Team Lead
                              I recommend:
Namecheap (domains)
Fastmail (e-mail)
Linode (VPS)
                             

Advertisement: