News:

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

Main Menu

Forum slows down

Started by ZeroShiki, July 24, 2005, 02:40:25 AM

Previous topic - Next topic

Fiery

#20
Hey, I thought I would jump in on all the fun:

http://www.ndbison.com/status.php?mysql_info=1

Memory usage 98%...?????????

...Did I pick a crappy host, based on my stats?

Thanks


Server Status
Basic Information
August 11, 2005, 01:26:33 AM
Operating System: CentOS release 4.1 (Final)
Processor: AMD Opteron(tm) Processor 248 (2193.210MHz)
Load averages: 3.37, 3.27, 3.17
Current processes: 328 (314 sleeping, 6 running, 8 zombie)
Processes by CPU: mysqld (48) 6.2%, [php] (8) 4.0%, rsync (3) 2.1%, perl (11) 0.6%
Memory usage: 98.743% (4008624k / 4059672k)
Swap: 0.062% (648k / 1052248k)
MySQL Statistics
MySQL 4.0.x
Connections per second: 3.2137
Kilobytes received per second: 32.5408
Kilobytes sent per second: 8.1403
Queries per second: 178.7545
Percentage of slow queries: 0
Opened vs. Open tables:
(table_cache) 38.8112 (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.0015 (should be <= 0.01)
Key buffer write hit rate:
(key_buffer_size) 0.2401 (should be <= 0.5)
Thread cache hit rate:
(thread_cache_size) 0.0001 (should be <= 0.05)
Thread cache usage:
(thread_cache_size) 0.2578 (should be >= 0.8 and <= 0.9)
Temporary table disk usage:
(tmp_table_size) 0.4467 (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.2069 (should be <= 0.1)
Query cache prune rate:
(query_cache_size) 0.2262 (should be <= 0.05)

MySQL status
Aborted_clients: 954
Aborted_connects: 88
Bytes_received: 4247997752
Bytes_sent: 1062667354
Com_admin_commands: 273294
Com_alter_table: 87
Com_analyze: 0
Com_backup_table: 0
Com_begin: 718
Com_change_db: 4239552
Com_change_master: 0
Com_check: 445
Com_commit: 718
Com_create_db: 23
Com_create_function: 0
Com_create_index: 0
Com_create_table: 4083
Com_delete: 2044293
Com_delete_multi: 0
Com_drop_db: 7
Com_drop_function: 0
Com_drop_index: 0
Com_drop_table: 132
Com_flush: 1584
Com_grant: 2620
Com_ha_close: 0
Com_ha_open: 0
Com_ha_read: 0
Com_insert: 1538059
Com_insert_select: 1891
Com_kill: 0
Com_load: 0
Com_load_master_data: 0
Com_load_master_table: 0
Com_lock_tables: 10087
Com_optimize: 1569
Com_purge: 0
Com_rename_table: 1
Com_repair: 31
Com_replace: 14333
Com_replace_select: 0
Com_reset: 0
Com_restore_table: 0
Com_revoke: 0
Com_rollback: 0
Com_savepoint: 0
Com_select: 2944107
Com_set_option: 80713
Com_show_binlog_events: 0
Com_show_binlogs: 96
Com_show_create: 23919
Com_show_databases: 2050
Com_show_fields: 21452
Com_show_grants: 2001
Com_show_keys: 680
Com_show_logs: 0
Com_show_master_status: 0
Com_show_new_master: 0
Com_show_open_tables: 0
Com_show_processlist: 6013
Com_show_slave_hosts: 0
Com_show_slave_status: 0
Com_show_status: 5596
Com_show_innodb_status: 0
Com_show_tables: 90744
Com_show_variables: 6656
Com_slave_start: 0
Com_slave_stop: 0
Com_truncate: 1
Com_unlock_tables: 10087
Com_update: 673412
Com_update_multi: 3407
Connections: 409694
Created_tmp_disk_tables: 153450
Created_tmp_tables: 343539
Created_tmp_files: 141
Delayed_insert_threads: 1
Delayed_writes: 454056
Delayed_errors: 0
Flush_commands: 1
Handler_commit: 0
Handler_delete: 1424303
Handler_read_first: 805691
Handler_read_key: 54781359
Handler_read_next: 127552881
Handler_read_prev: 837710
Handler_read_rnd: 11956886
Handler_read_rnd_next: 2737475035
Handler_rollback: 18
Handler_update: 20332243
Handler_write: 15221470
Key_blocks_used: 233650
Key_read_requests: 151748102
Key_reads: 223139
Key_write_requests: 19328111
Key_writes: 4640890
Max_used_connections: 36
Not_flushed_key_blocks: 0
Not_flushed_delayed_rows: 0
Open_tables: 1536
Open_files: 2990
Open_streams: 0
Opened_tables: 59614
Questions: 22788344
Qcache_queries_in_cache: 12659
Qcache_inserts: 2332129
Qcache_hits: 10647678
Qcache_lowmem_prunes: 666005
Qcache_not_cached: 609176
Qcache_free_memory: 12587192
Qcache_free_blocks: 4036
Qcache_total_blocks: 30474
Rpl_status: NULL
Select_full_join: 35551
Select_full_range_join: 3720
Select_range: 91512
Select_range_check: 46
Select_scan: 518737
Slave_open_temp_tables: 0
Slave_running: OFF
Slow_launch_threads: 0
Slow_queries: 73
Sort_merge_passes: 5
Sort_range: 156833
Sort_rows: 26898717
Sort_scan: 387536
Table_locks_immediate: 8435713
Table_locks_waited: 25688
Threads_cached: 33
Threads_created: 37
Threads_connected: 5
Threads_running: 1
Uptime: 127484

MySQL variables
back_log: 50
basedir: /
binlog_cache_size: 32768
bulk_insert_buffer_size: 8388608
character_set: latin1
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: 10
convert_character_set:
datadir: /var/lib/mysql/
default_week_format: 0
delay_key_write: ON
delayed_insert_limit: 100
delayed_insert_timeout: 300
delayed_queue_size: 1000
flush: OFF
flush_time: 0
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: 1048576
innodb_autoextend_increment: 8
innodb_buffer_pool_size: 8388608
innodb_data_file_path: 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: 50
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: 100
join_buffer_size: 1044480
key_buffer_size: 262144000
language: /usr/share/mysql/english/
large_files_support: ON
license: GPL
local_infile: ON
locked_in_memory: OFF
log: OFF
log_update: OFF
log_bin: OFF
log_slave_updates: OFF
log_slow_queries: ON
log_warnings: 1
long_query_time: 2
low_priority_updates: OFF
lower_case_file_system: OFF
lower_case_table_names: 0
max_allowed_packet: 33553408
max_binlog_cache_size: 4294967295
max_binlog_size: 1073741824
max_connections: 600
max_connect_errors: 10
max_delayed_threads: 20
max_insert_delayed_threads: 20
max_heap_table_size: 16777216
max_join_size: 18446744073709551615
max_relay_log_size: 0
max_seeks_for_key: 4294967295
max_sort_length: 1024
max_user_connections: 30
max_tmp_tables: 32
max_write_lock_count: 4294967295
myisam_max_extra_sort_file_size: 268435456
myisam_max_sort_file_size: 9223372036854775807
myisam_repair_threads: 1
myisam_recover_options: OFF
myisam_sort_buffer_size: 67108864
net_buffer_length: 16384
net_read_timeout: 30
net_retry_count: 10
net_write_timeout: 60
new: OFF
open_files_limit: 10000
pid_file: /var/lib/mysql/box28.bluehost.com.pid
log_error:
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: 1044480
read_only: OFF
read_rnd_buffer_size: 1044480
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/lib/mysql/mysql.sock
sort_buffer_size: 1048568
sql_mode: 0
table_cache: 1536
table_type: MYISAM
thread_cache_size: 128
thread_stack: 196608
tx_isolation: REPEATABLE-READ
timezone: MDT
tmp_table_size: 33554432
tmpdir: /tmp/
transaction_alloc_block_size: 8192
transaction_prealloc_size: 4096
version: 4.0.24-standard-log
version_comment: Official MySQL RPM
version_compile_os: unknown-linux-gnu
wait_timeout: 100



Ben_S

*nix fills the memory for fun, the swap value is the one to look at.

Looks fairly decent to me, query_cache could maybe do with a bit of a tweak, but on the whole, it looks good, and is certainly a nice server.
Liverpool FC Forum with 14 million+ posts.

FTF

Hi there

Im in the process of moving server and domain but while that goes through I am interested in knowing if there is a reason why at times my forum at the below address is really unreliable in terms of posts timing out when you submit and trying to view posts but timing out and sometimes very slow loading times.

The forum is a sub directory of my main website so would this affect performance? My new location will make the forum the principle aspect of the server so in the main directory,

Could someone take a look please

http://www.britains-tractors.co.uk/forum/status.php

Many thanks

Andy

Ben_S

No info about the server itself available, but the MySQL config doesn't look healthy.

The directory you put the board in will make no difference to performance.
Liverpool FC Forum with 14 million+ posts.

FTF

Lycos is my hosting company - 20gb/month bandwidth with php/mysql up to date etc

How do I get info regarding the server?

and what treatment would there be to make it look healthier?

Ben_S

In all honesty, I doubt a large company like lycos will do anything to tweak things.
Liverpool FC Forum with 14 million+ posts.

[Unknown]

Quote from: Ben S on August 11, 2005, 05:21:43 AM
*nix fills the memory for fun, the swap value is the one to look at.

Looks fairly decent to me, query_cache could maybe do with a bit of a tweak, but on the whole, it looks good, and is certainly a nice server.

Looks like they're running php as a CGI, though, without an accelerator.  It's drinking more ram because of that.

Quote from: Ben S on August 11, 2005, 07:49:26 AM
In all honesty, I doubt a large company like lycos will do anything to tweak things.

Sadly enough, yes.  Btcouk, you can show them the script and tell them it would be helpful to you if:

1. /proc were added to open_basedir; nothing in there will be writable, but it makes it possible to freeze things based on the load average much more efficiently than piping uptime (this is of course assuming the server is Linux.)
2. MySQL were configured to use ram more effectively.  Lycos should know how much ram is available, but increasing the table_cache just to 512 would probably make a very large difference - especially with 100 queries per second.
3. They upgraded MySQL.  The most recent release is 4.0.25, not 4.0.18 which is what they are running.  I worry other software is out of date too.

Without more information (from /proc, actually) there's not much else I can suggest.

-[Unknown]

Fiery

Quote from: Ben_S on August 11, 2005, 05:21:43 AM
*nix fills the memory for fun, the swap value is the one to look at.

Looks fairly decent to me, query_cache could maybe do with a bit of a tweak, but on the whole, it looks good, and is certainly a nice server.

cool, thanks

blunt

Hi, all. :)

My forum has been running slow for a while, and I've noticed that when I try to optimize my databases in the admin panel of SMF, it only seems to do one table out of the 35.  Am I reading that right? 

QuoteYour database contains 35 tables.
Attempting to optimize your database...
Optimizing smf_log_online... 0.148438 kb optimized.

1 table(s) optimized.

Could this be something to do with how my permissions are set?  Most files seem to be set at 644 by default.  I've changed a few to 666, but I'm not really sure what I'm doing.  The forum has 69 members and has been running for about a year now, without many problems, so it can't be too drastically wrong.

BTW, I've uploaded the status.php file, and I'd be grateful if somebody would take a look and tell me if my hosts need another kick up the arse, please.  I'm coming up to renewal time, and if they aren't performing well, I think I might change.  Thanks.

http://www.cafedoom.com/forum/status.php

[Unknown]

Quote from: blunt on September 08, 2005, 03:59:52 PM
Hi, all. :)

My forum has been running slow for a while, and I've noticed that when I try to optimize my databases in the admin panel of SMF, it only seems to do one table out of the 35.  Am I reading that right?

That's nothing to worry about.  Actually, more tables are being optimized than that, but it's listing the ones which change in size.

You'd probably see a good benefit from upgrading to SMF 1.0.5.  But, from the looks of it, you're on shared hosting... and the server isn't exactly well-configured, but it's okay.

Are you having performance problems?  With a forum that size, I wouldn't expect them.

-[Unknown]

blunt

Thanks, Unknown :)

But yes, I'm having problems still.  Tonight, I couldn't get into my forum at all, at first.  Did a tracert, and that got through fine, but the forum wouldn't show, even from inside cPanel.  I optimized the MySql database, and that didn't make any difference.  Then I used the ssi-examples to find a recent post, clicked on it, and I was in.  Went to the admin panel and optimized straight away, and it seems to be OK now, but it quite often displays slowly - it takes quite a while for all the images to load.  There's only about 6000 posts on the forum, so I can't imagine it's anything to do with size.

I'm reluctant to upgrade, because I'm worried the Helios theme won't work properly with it, or I'll get some other type of problem that I won't know how to fix :D 

If you think it'll be OK, I'll give it a try.

[Unknown]

Your forum loads okay for me.  But, I'd still have to recommend you upgrade.  I should note that the Helios theme is available for the latest stable and beta versions, as far as I know - and may even be improved since the version you are using.

Anyway, to the server, I suggest you look at the status.php when your forum is slow.  Important things to look at:

Load averages:     1.08, 1.50, 1.73

That should, if possible, always be lower than the number of processors (virtual or otherwise) on your server.  Assuming it's 2, then anything under 2.00 can be considered healthy.  Still, those are higher than I like to see.  If these numbers are very high, it's likely your forum won't load at all.

Processes by CPU:     mysqld (19) 37.2%, httpd (80) 35.7%, exim (7) 7.0%, [kscand] (1) 1.5%, spamd (6) 1.3%

It looks like MySQL is eating the most CPU, although httpd is really drinking a lot too.  It's highly unlikely this is caused by your forum, but rather probably by another site hosted on the same server.  Exim is also taking its toll, which means someone is probably sending or receiving quite a few emails.

If your host has a lot of clients using PHP, they really should install an accelerator, like eAccelerator.  Without a phpinfo I can't tell if they have, but judging from httpd's cpu usage I'd assume not.  An accelerator/bytecode cacher will typically reduce both cpu and memory usage, as long as PHP is being used on the server.

Memory usage:      98.269% (2019800k / 2055376k)
Swap: 17.449% (356000k / 2040212k)

2 gigabytes of ram are being used on your server too.  Typically, you want swap usage to be below 10%.... so it looks like this server is being over-utilized.  However, the accelerator might improve things, in which case it might not be so bad.

As for your server's MySQL configuration, it has the following problems:

  - table_cache is a little too low.  I'd suggest trying something more like 1280 or perhaps 1536.
  - tmp_table_size is way too large.  Allowing 12 megs in memory per temporary table is asking to run out of memory.  I'd suggest trying to cut back to 8 or 10 megs - the reduction in swap usage would probably make it worth it.

-[Unknown]

blunt

#32
The figures look even worse this morning (if I'm reading it right -

QuoteLoad averages: 3.01, 3.32, 3.21
Current processes: 161 (154 sleeping, 7 running, 0 zombie)
Processes by CPU: mysqld (20) 32.7%, httpd (24) 19.4%, pkgacct (2) 4.9%, ps (1) 3.0%, cppop (4) 2.1%, [kscand] (1) 1.5%, spamd (6) 1.1% 
Memory usage: 98.124% (2016812k / 2055376k)
Swap: 19.696% (401836k / 2040212k) 

http://www.cafedoom.com/phpinfo.php they use Zend optimizer - is that the same type of thing as eAccelerator, or something completely different?

Many thanks for your detailed reply, Unknown.  I've had intermittent problems with slowness for months now, and with the site not loading at all sometimes.  Every time I contact the hosts (asmallorange.com) they're nice enough, and seem to try and help, but the problem still persists.  It could be that they've over sold the server space then?

I'll take your advice and check next time things slow down.  And I'll upgrade to the newer version of SMF.

Thanks again :)

[Unknown]

QuoteProcesses by CPU: mysqld (20) 32.7%, httpd (24) 19.4%, pkgacct (2) 4.9%, ps (1) 3.0%, cppop (4) 2.1%, [kscand] (1) 1.5%, spamd (6) 1.1%

Looks like cPanel was making a backup (pkgacct.)

Quotehttp://www.cafedoom.com/phpinfo.php they use Zend optimizer - is that the same type of thing as eAccelerator, or something completely different?

It's similar, but it's not as good.  Both softwares are free, but Zend has a paid version (Zend Platform now, I think) that actually does the bytecaching and other important parts of it.  The free Zend Optimizer doesn't do hardly as much, and doesn't really compare with eAccelerator.

I have seen a server running Zend Optimizer switch to eAccelerator (more than once, actually) and see a very noticeable performance improvement.

QuoteIt could be that they've over sold the server space then?

It could be.  I'm not entirely sure, of course.

-[Unknown]

RCC_SaMiaM

I am having a slowdown that is a bit different.  It started today, the page will say done, but then about 5 seconds or so later the page will actually display.  It shows that it only took .2 seconds to create the page, but takes 5 seconds, sometimes more, to display it.

I had this problem before, but it went away.  I have several SMF installs on my dedicated server, but the forum for my main site is the only one this happens to.  All other installs work just fine.

http://ramchargercentral.com/boards/

[Unknown]

That's caused by this:

http://www.everywherechat.com/members.asp?room=RCC_Chat_Room

Which is taking quite a long time to load.

-[Unknown]


blunt

Quote from: [Unknown] on September 09, 2005, 12:30:04 PM
QuoteProcesses by CPU: mysqld (20) 32.7%, httpd (24) 19.4%, pkgacct (2) 4.9%, ps (1) 3.0%, cppop (4) 2.1%, [kscand] (1) 1.5%, spamd (6) 1.1%

Looks like cPanel was making a backup (pkgacct.)

Quotehttp://www.cafedoom.com/phpinfo.php they use Zend optimizer - is that the same type of thing as eAccelerator, or something completely different?

It's similar, but it's not as good.  Both softwares are free, but Zend has a paid version (Zend Platform now, I think) that actually does the bytecaching and other important parts of it.  The free Zend Optimizer doesn't do hardly as much, and doesn't really compare with eAccelerator.

I have seen a server running Zend Optimizer switch to eAccelerator (more than once, actually) and see a very noticeable performance improvement.

QuoteIt could be that they've over sold the server space then?

It could be.  I'm not entirely sure, of course.

-[Unknown]

Thanks again, Unknown.  I'll suggest they change over to eAccelerator.

BTW, the load averages are getting worse every time I look (up to 7/4/4 now)

QuoteServer Status
Basic Information
September 09, 2005, 03:08:54 PM
Operating System: Red Hat Enterprise Linux ES release 3 (Taroon Update 5) 
Processor: Intel® Xeon(TM) CPU 2.80GHz (2790.790MHz)
Load averages: 7.34, 4.95, 4.60
Current processes: 372 (365 sleeping, 7 running, 0 zombie)
Processes by CPU: httpd (201) 53.1%, mysqld (20) 37.1%, pkgacct (2) 9.8%, sh (2) 2.0%, [kscand] (1) 1.6%, spamd (6) 1.5% 
Memory usage: 99.092% (2036720k / 2055376k)
Swap: 36.232% (739216k / 2040212k)
::)

[Unknown]

Hmm, pkgacct is still whirring away....

Anyway, I forgot to mention... the load averages are three numbers.  The first is a 1 minute average, the second a 5 minute average, and the third a 15 minute average.  Or at least, on Linux those are the times.

So, if you see something like "1, 5, 10" that means it's getting better.  If you see "10, 5, 1" it's getting worse... and so on.

-[Unknown]

blunt

I got this reply from their tech, this morning - (I showed them your suggested changes  ;) )

Quote
Greetings,

Whe have found some high-impact torrent sites being hosted on here and suspended the account, which should free up some resources. As well we have tweaked the mysql settings as they suggested. We are however not in agreement with eaccelerator. While it may increase the speeds on a server for a while it's fundamental flaw is in the tremendous disk caching that it does to maintain faster speeds. On our shared hosting environment this would hit the point of diminishing returns in a few weeks and therefore cause more problems with server performance thay we again would have to take care of.

And holy ****** - look at the difference in the load averages today, plus swap, MySQL usage, etc.  -

QuoteOperating System: Red Hat Enterprise Linux ES release 3 (Taroon Update 5) 
Processor: Intel® Xeon(TM) CPU 2.80GHz (2790.790MHz)
Load averages: 0.66, 0.76, 0.78
Current processes: 175 (172 sleeping, 2 running, 1 zombie)
Processes by CPU: mysqld (20) 21.8%, httpd (35) 17.7%, [exim (1) 2.3%, (other) (49) 2.1%, [kscand] (1) 1.5%, exim (4) 1.1% 
Memory usage: 98.851% (2031760k / 2055376k)
Swap: 12.451% (254020k / 2040212k) 

Looks like it did the trick :D

Many thanks for your help with this, Unknown.  I wish I was hosting with you instead of them ;D

Advertisement: