SMF and server CPU

Started by Saku, December 10, 2005, 12:25:41 PM

Previous topic - Next topic

Saku

hi  i have a problem with
SMF 1.1RC1, PHP 4.4.1, MySQL 4.1.12
i dont have a very big forum ( 60-70 users online, 10000 members, 156913 messages, 40000 pages views per day)
using a dedicated server Intel(R) Celeron(R) CPU 3.06GHz, 1Go RAM.

my status.php:
Operating System:  Fedora Core release 4 (Stentz)
Processor: Intel® Celeron® CPU 3.06GHz (3064.690MHz)
Load averages: 1.42, 1.30, 1.19
Current processes: 96 (92 sleeping, 4 running, 0 zombie)
Processes by CPU: httpd (28) 32.6%, (other) (24) 0.0%
Memory usage: 83.557% (852068k / 1019744k)
Swap: 0% (0k / 0k)

MySQL processes
Total processes: 3 (2 sleeping, 1 running, 0 locked)

Connections per second:  1.6298
Kilobytes received per second: 0.1456
Kilobytes sent per second: 2.8283
Queries per second: 26.4388
Percentage of slow queries: 0
Opened vs. Open tables:
(table_cache) 2192.7188 (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.0007 (should be <= 0.01)
Key buffer write hit rate:
(key_buffer_size) 0.1317 (should be <= 0.5)
Thread cache hit rate:
(thread_cache_size) 1 (should be <= 0.05)
Thread cache usage:
(thread_cache_size) 0 (should be >= 0.8 and <= 0.9)
Temporary table disk usage:
(tmp_table_size) 0.8654 (should be <= 0.5)
Sort merge pass rate:
(sort_buffer) 0.0056 (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

Liverpool FC Forum with 14 million+ posts.

Saku

i'll try to convert  my tables to InnoDB tonight
but i don't know how to change MySQL's settings

H

Use phpmyadmin or Mysql administrator to change the table type ;)
-H
Former Support Team Lead
                              I recommend:
Namecheap (domains)
Fastmail (e-mail)
Linode (VPS)
                             

Saku

thanks but i know how to change table type,
I'm talking about this :

Quote from: [Unknown] on September 19, 2005, 02:58:57 AM
Have MySQL's settings been tweaked to optimize memory usage and general efficiency?

H

Quote from: Sakuragi on December 10, 2005, 01:16:05 PM
thanks but i know how to change table type,
I'm talking about this :

Quote from: [Unknown] on September 19, 2005, 02:58:57 AM
Have MySQL's settings been tweaked to optimize memory usage and general efficiency?

Look at the mysql configuration. Search for my.cnf
-H
Former Support Team Lead
                              I recommend:
Namecheap (domains)
Fastmail (e-mail)
Linode (VPS)
                             

Joshua Dickerson

You might want to enable the query cache. Increase the size of your table cache.
Come work with me at Promenade Group



Need help? See the wiki. Want to help SMF? See the wiki!

Did you know you can help develop SMF? See us on Github.

How have you bettered the world today?

Saku


Joshua Dickerson

Quote from: Sakuragi on December 12, 2005, 02:02:24 PM
how to do that?
is this your own server or is someone else running it?
Come work with me at Promenade Group



Need help? See the wiki. Want to help SMF? See the wiki!

Did you know you can help develop SMF? See us on Github.

How have you bettered the world today?

Saku

I am the only user who runs this server

Joshua Dickerson

Come work with me at Promenade Group



Need help? See the wiki. Want to help SMF? See the wiki!

Did you know you can help develop SMF? See us on Github.

How have you bettered the world today?

Ben_S

Post the link to status.php
Liverpool FC Forum with 14 million+ posts.

Saku

December 23, 2005, 11:25:26 PM
Operating System: Fedora Core release 4 (Stentz)
Processor: Intel® Celeron® CPU 3.06GHz (3063.435MHz)
Load averages: 1.67, 2.01, 1.89
Current processes: 138 (135 sleeping, 2 running, 1 zombie)
Processes by CPU: httpd (38) 30.4%, [httpd] (1) 0.6%, (other) (29) 0.1%
Memory usage: 94.756% (965484k / 1018920k)
Swap: 0% (0k / 0k)
MySQL processes
Total processes: 14 (5 sleeping, 9 running, 0 locked)
MySQL Statistics
MySQL 4.1.x
Connections per second: 3.3821
Kilobytes received per second: 7.0474
Kilobytes sent per second: 4.6797
Queries per second: 49.9096
Percentage of slow queries: 0
Opened vs. Open tables:
(table_cache) 958.5156 (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.0003 (should be <= 0.01)
Key buffer write hit rate:
(key_buffer_size) 0.1174 (should be <= 0.5)
Thread cache hit rate:
(thread_cache_size) 1 (should be <= 0.05)
Thread cache usage:
(thread_cache_size) 0 (should be >= 0.8 and <= 0.9)
Temporary table disk usage:
(tmp_table_size) 0.8876 (should be <= 0.5)
Sort merge pass rate:
(sort_buffer) 0.001 (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)

MySQL status
Aborted_clients: 55
Aborted_connects: 74
Binlog_cache_disk_use: 0
Binlog_cache_use: 0
Bytes_received: 1430805831
Bytes_sent: 950091512
Com_admin_commands: 172
Com_alter_db: 0
Com_alter_table: 4
Com_analyze: 0
Com_backup_table: 0
Com_begin: 0
Com_change_db: 1109780
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: 7988
Com_dealloc_sql: 0
Com_delete: 338636
Com_delete_multi: 0
Com_do: 0
Com_drop_db: 0
Com_drop_function: 0
Com_drop_index: 0
Com_drop_table: 7988
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: 220241
Com_insert_select: 624
Com_kill: 0
Com_load: 0
Com_load_master_data: 0
Com_load_master_table: 0
Com_lock_tables: 0
Com_optimize: 233
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: 46649
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: 6489638
Com_set_option: 152
Com_show_binlog_events: 0
Com_show_binlogs: 3
Com_show_charsets: 38
Com_show_collations: 38
Com_show_column_types: 0
Com_show_create_db: 3
Com_show_create_table: 0
Com_show_databases: 38
Com_show_errors: 0
Com_show_fields: 1
Com_show_grants: 3
Com_show_innodb_status: 0
Com_show_keys: 19
Com_show_logs: 0
Com_show_master_status: 0
Com_show_new_master: 0
Com_show_open_tables: 0
Com_show_privileges: 0
Com_show_processlist: 34
Com_show_slave_hosts: 0
Com_show_slave_status: 0
Com_show_status: 38
Com_show_storage_engines: 0
Com_show_tables: 200
Com_show_variables: 114
Com_show_warnings: 0
Com_slave_start: 0
Com_slave_stop: 0
Com_truncate: 0
Com_unlock_tables: 0
Com_update: 1001604
Com_update_multi: 0
Connections: 670568
Created_tmp_disk_tables: 170218
Created_tmp_files: 50537
Created_tmp_tables: 191767
Delayed_errors: 0
Delayed_insert_threads: 0
Delayed_writes: 0
Flush_commands: 1
Handler_commit: 34
Handler_delete: 160214
Handler_discover: 0
Handler_read_first: 1224966
Handler_read_key: 303788850
Handler_read_next: 403396417
Handler_read_prev: 579278
Handler_read_rnd: 8566416
Handler_read_rnd_next: 3562965245
Handler_rollback: 566353
Handler_update: 5377973
Handler_write: 158039696
Key_blocks_not_flushed: 0
Key_blocks_unused: 4570
Key_blocks_used: 7248
Key_read_requests: 600172248
Key_reads: 206096
Key_write_requests: 4327103
Key_writes: 508203
Max_used_connections: 95
Not_flushed_delayed_rows: 0
Open_files: 89
Open_streams: 0
Open_tables: 64
Opened_tables: 61345
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: 9895418
Rpl_status: NULL
Select_full_join: 723
Select_full_range_join: 35643
Select_range: 680666
Select_range_check: 8
Select_scan: 2853752
Slave_open_temp_tables: 0
Slave_running: OFF
Slave_retried_transactions: 0
Slow_launch_threads: 6072
Slow_queries: 108
Sort_merge_passes: 25199
Sort_range: 22453
Sort_rows: 25620909
Sort_scan: 1057241
Table_locks_immediate: 10667127
Table_locks_waited: 1037
Threads_cached: 0
Threads_connected: 26
Threads_created: 670567
Threads_running: 1
Uptime: 198267

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_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
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: 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_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
net_buffer_length: 16384
net_read_timeout: 30
net_retry_count: 10
net_write_timeout: 60
new: OFF
old_passwords: OFF
open_files_limit: 1024
pid_file: /var/lib/mysql/serverge11.edelweisshosting.net.pid
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: 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:
storage_engine: MyISAM
sql_notes: OFF
sql_warnings: OFF
sync_binlog: 0
sync_replication: 0
sync_replication_slave_id: 0
sync_replication_timeout: 0
sync_frm: ON
system_time_zone: CET
table_cache: 64
table_type: MyISAM
thread_cache_size: 0
thread_stack: 126976
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.12-standard
version_comment: MySQL Community Edition - Standard (GPL)
version_compile_machine: i686
version_compile_os: pc-linux-gnu
wait_timeout: 28800


Mod installed :
-TinyPortal
-Personal Icons
-SMF shop mod
-Paid subscriptions
-Magic llama
-Ignore Boards
-Static Page Mod
-mMood
-Global Announcements & SuperSticky
-FlashChat
-Coppermine Photo Gallery

Ben_S

I have great difficulty in reading that, can't you just post the link?
Liverpool FC Forum with 14 million+ posts.

Ben_S

Whats the current contents of your /etc.my.cnf file?

Is this server just powering your SMF?
Liverpool FC Forum with 14 million+ posts.

Saku

Quote from: Ben_S on December 23, 2005, 08:17:08 PM
Whats the current contents of your /etc.my.cnf file?
sorry for my ignorance but  where can i find this file "etc.my.cnf" ??  In which repertory?
Quote from: Ben_S on December 23, 2005, 08:17:08 PM
Is this server just powering your SMF?
yes just my board

Ben_S

Whoops sorry that should have read /etc/my.cnf
Liverpool FC Forum with 14 million+ posts.

Saku

there is no my.cnf on my server :o



when i type : whereis my.cnf  ==>
[root@serverge11 /]# whereis my.cnf
my:

but i found "mtools.conf"

# Example mtools.conf files.  Uncomment the lines which correspond to
# your architecture and comment out the "SAMPLE FILE" line below

# Linux floppy drives
drive a: file="/dev/fd0" exclusive mformat_only
drive b: file="/dev/fd1" exclusive mformat_only

# First SCSI hard disk partition
#drive c: file="/dev/sda1"

# First IDE hard disk partition
#drive c: file="/dev/hda1"

# # dosemu floppy image
# drive m: file="/var/lib/dosemu/diskimage"

# dosemu hdimage
drive n: file="/var/lib/dosemu/hdimage" offset=8832

# # HPOJ (ptal-photod)
mtools_skip_check=1
drive p: file=":0" remote

# # Atari ramdisk image
# drive o: file="/tmp/atari_rd" offset=136

# # ZIP disk for Solaris:
# Drive X is ZIP-100 at target 5
# drive X: file="/dev/rdsk/c0t5d0s2" partition=4 scsi=1 nodelay

# # ZIP disk for SunOS:
# # Zip drive is at target 5, which default kernel calls tape st1 !!
# drive Y: file="/dev/rsd5c" partition=4 scsi=1 nodelay

# # autoselect zip drive/floppy on HP-UX 9/10
#    drive a: file="/dev/rdsk/c201d5"      exclusive partition=4
#    drive a: file="/dev/rdsk/c201d5s0"    exclusive partition=4
#    drive a: file="/dev/rfloppy/c201d0s0" exclusive

#          A/UX target 5 on 1st scsi bus   jaz or zip
# drive X: file="/dev/rdsk/c105d0s31"      partition=4


# Some examples for BeOS.
# floppy drive. hardcoded in devices.c, so no real need to define it here
#drive a: file="/dev/floppy_disk" exclusive
# ZIP drive on SCSI ID 6
#drive z: file="/dev/scsi_disk_060" offset=16384 fat_bits=16                       

# SCO Unix 3.2v4
# # Floppy disk drives
#
# drive a: file="/dev/install" exclusive
# drive b: file="/dev/install1" exclusive

# # SCSI hard disk partitions

# drive c: file="/dev/dsk/0sC"
# drive d: file="/dev/dsk/0sD"
# drive e: file="/dev/dsk/0sE"
# drive f: file="/dev/dsk/0sF"
# drive g: file="/dev/dsk/0sG"
# drive h: file="/dev/dsk/0sH"

# # uncomment the following line to display all file names in lower
# # case by default
# mtools_lower_case=1

Ben_S

Try this

[mysqld]
thread_cache_size=150
key_buffer=100M

sort_buffer_size=1M
read_buffer_size=1M
read_rnd_buffer_size=768K

skip-locking
;skip-innodb

query_cache_limit=4M
query_cache_size=64M
query_cache_type=1

max_connections=450
interactive_timeout=100
wait_timeout=100
connect_timeout=100

join_buffer=2M
max_allowed_packet=16M
table_cache=900
record_buffer=1M

max_connect_errors=10

myisam_sort_buffer_size=32M

[safe_mysqld]
open_files_limit=6192

[mysqldump]
quick
max_allowed_packet=16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=2M
write_buffer=2M

[myisamchk]
key_buffer=64M
sort_buffer=128M
read_buffer=2M
write_buffer=2M

[mysqlhotcopy]
interactive-timeout
Liverpool FC Forum with 14 million+ posts.

Saku

you mean i should add this to mtools.conf ??
sory but this is the first time i use a dedicated server :(

Ben_S

No create my.cnf and add that to it.

I'd also suggest you buy a decent *nix administration book and start reading as sooner or later you will have problems and will need to have a clue about what you are doing.
Liverpool FC Forum with 14 million+ posts.

Saku

Quote from: Ben_S on December 24, 2005, 09:30:39 AM
No create my.cnf and add that to it.

I did it and i restart mysql service

Quote from: Ben_S on December 24, 2005, 09:30:39 AM
I'd also suggest you buy a decent *nix administration book and start reading as sooner or later you will have problems and will need to have a clue about what you are doing.
thanks i'll do it, but now i use WinSCP3 it's very useful for newbie like me :D


Saku

Load averages still high  :  1.74, 1.66, 2.31
you can help me to install eaccelerator please?

http://www.simplemachines.org/community/index.php?topic=49995.msg428942#msg428942

Ben_S

Hows it performing now?

As for eAccelerator, check out the eA website - theres RPM's for fedora I beleive.
Liverpool FC Forum with 14 million+ posts.

Saku

Load average: 1.94, 1.57, 1.40 :(

Ben_S

Ignore load numbers, they mean relatively little, if your board is serving pages in a reasonable time thats all that matters.

What are your page creation times?
Liverpool FC Forum with 14 million+ posts.

Saku

Page created in 0.593 seconds with 22 queries.
but my host told me if the load still higher than 100% i have to change my server

Ben_S

If your host mentioned a %age with respect to load, I'd find a new host. *nix load is number not a percentage.

If your board is performing ok, page creation time not excessivly high then there isn't a problem. Your on a celeron, it's being asked to do a lot of work but it's doing it ok.

eAccelerator will help though.
Liverpool FC Forum with 14 million+ posts.

Saku

yeah i'll retry to install eaccelerator tomorrow, i'll ask my host to help me with this

xhale

If you're on a dedicate box and not sharing it with other users, why would it matter to your hoster what your load is? I would think that's mainly an issue only if you're taking resources away from other users on the box.

qtime

I have serverload up to 30 and running fast enough... so that figure is very relatively.
I am using eaccelarator also, this increase speed 10 times.

Advertisement: