vB crawling... please help

[Cold Steel]

After upgrading to a newer, more powerful server, vB (1.1.3b, heavily hacked) has been loading slowly. There's no more than 20 people on it at the busiest times, and lately it's only been around 3 because of the lag.

Here are the specs:

256 MB RAM, Pentium III 800mhz Processor, 18 gig SCSI, 30gig IDE (backup)

PHP info: http://aforums.com/phpinfo.php

TOP:
last pid: 22402; load averages: 0.02, 0.04, 0.05 up 41+19:26:06 13:00:21
204 processes: 1 running, 202 sleeping, 1 zombie
CPU states: 1.6% user, 0.0% nice, 0.8% system, 0.8% interrupt, 96.9% idle
Mem: 157M Active, 26M Inact, 56M Wired, 10M Cache, 35M Buf, 1044K Free
Swap: 600M Total, 137M Used, 463M Free, 22% Inuse

MySQL:
Ver 8.0 Distrib 3.22.32, for -freebsd4.1 on i386

MySQL Extended-status:
+--------------------------+---------+
| Variable_name | Value |
+--------------------------+---------+
| Aborted_clients | 0 |
| Aborted_connects | 215 |
| Created_tmp_tables | 619 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_delete | 740 |
| Handler_read_first | 112 |
| Handler_read_key | 335316 |
| Handler_read_next | 3205171 |
| Handler_read_rnd | 6752906 |
| Handler_update | 9466 |
| Handler_write | 132804 |
| Key_blocks_used | 2581 |
| Key_read_requests | 1503772 |
| Key_reads | 2572 |
| Key_write_requests | 2733 |
| Key_writes | 2404 |
| Max_used_connections | 120 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 36 |
| Open_files | 51 |
| Open_streams | 0 |
| Opened_tables | 40 |
| Questions | 296276 |
| Running_threads | 112 |
| Slow_queries | 25 |
| Uptime | 97307 |
+--------------------------+---------+

MySQL variables:
+----------------------------+---------------------------------+
| Variable_name | Value |
+----------------------------+---------------------------------+
| back_log | 5 |
| connect_timeout | 5 |
| basedir | /usr/local/ |
| datadir | /var/db/mysql/ |
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |
| join_buffer | 131072 |
| flush_time | 0 |
| key_buffer | 8388600 |
| language | /usr/local/share/mysql/english/ |
| log | OFF |
| log_update | OFF |
| long_query_time | 10 |
| low_priority_updates | OFF |
| max_allowed_packet | 1048576 |
| max_connections | 256 |
| max_connect_errors | 10 |
| max_delayed_insert_threads | 20 |
| max_join_size | 4294967295 |
| max_sort_length | 1024 |
| max_write_lock_count | 4294967295 |
| net_buffer_length | 16384 |
| pid_file | /var/db/mysql/asiansinc.pid |
| port | 3306 |
| protocol_version | 10 |
| record_buffer | 131072 |
| skip_locking | OFF |
| skip_networking | OFF |
| socket | /tmp/mysql.sock |
| sort_buffer | 2097144 |
| table_cache | 64 |
| thread_stack | 65536 |
| tmp_table_size | 1048576 |
| tmpdir | /var/tmp/ |
| version | 3.22.32 |
| wait_timeout | 28800 |
+----------------------------+---------------------------------+

Any help would be greatly appreciated.

Thanks,

Rich

[eva2000]

runs fine for me... is that top saying you you are using 22% or 137MB of swap :eek: ?

mysql's been running for 27 hrs only and cpu ain't loaded and you're already running mysql with default settings which aren't adding to memory usage already - i'd say 256mb ram is not enough
?

not sure maybe someone can confirm that ?

[eva2000]

also upgrading to mysql 3.23.32 would help :)

[Cold Steel]

OK, I've put in a request to get the latest version of MySQL installed.

I'm not sure about the TOP numbers, but 22% of 600 is 132, so the MB of swap would be about the same either way.

I don't have the money to add more RAM at this time, so is there any way I can squeeze more performance out of my machine by tweaking the variables?

[chrispadfield]

upgrading 1.3 to 1.5 is going to help a lot as well.

[Cold Steel]

Yep, I figured that. Too many hacks though.

You think I should just wait until v2.0 comes out before optimizing?

[chrispadfield]

if upgrading straight away yes probably. it is going to be well optimized i think with indexed added properly, may well be the best bet.

[eva2000]

i think your slowness is due to large swap usage to your drive - that's due to lack of memory unfortunately

and your mysql settings are the default which won't be good for running heavily active vB - but you can't raise the mysql settings considering your lack of memory anyway

[Cold Steel]

For some reason, things are REALLY crawling now. There's only one user on it (me). The index.php loads really slowly, and other pages time out.

TOP:
last pid: 917; load averages: 0.11, 0.08, 0.08 up 0+00:52:53 13:18:51
206 processes: 1 running, 204 sleeping, 1 zombie
CPU states: 0.8% user, 0.0% nice, 0.4% system, 0.8% interrupt, 98.1% idle
Mem: 131M Active, 62M Inact, 42M Wired, 14M Cache, 35M Buf, 1200K Free
Swap: 600M Total, 600M Free

Why is there no swap?

I've upgraded MySQL, version is now Ver 8.14 Distrib 3.23.32, for unknown-freebsdelf4.1 on i386.

Extended-status:
+--------------------------+----------+
| Aborted_clients | 0 |
| Aborted_connects | 7 |
| Bytes_received | 462132 |
| Bytes_sent | 10160101 |
| Connections | 65 |
| Created_tmp_disk_tables | 0 |
| Created_tmp_tables | 20 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_delete | 15 |
| Handler_read_first | 15 |
| Handler_read_key | 4523 |
| Handler_read_next | 14285 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 3944 |
| Handler_read_rnd_next | 63408 |
| Handler_update | 45 |
| Handler_write | 34 |
| Key_blocks_used | 270 |
| Key_read_requests | 17019 |
| Key_reads | 270 |
| Key_write_requests | 34 |
| Key_writes | 29 |
| Max_used_connections | 35 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 15 |
| Open_files | 30 |
| Open_streams | 0 |
| Opened_tables | 21 |
| Questions | 3543 |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 18 |
| Select_range_check | 0 |
| Select_scan | 507 |
| Slave_running | OFF |
| Slave_open_temp_tables | 0 |
| Slow_launch_threads | 0 |
| Slow_queries | 0 |
| Sort_merge_passes | 0 |
| Sort_range | 32 |
| Sort_rows | 5055 |
| Sort_scan | 285 |
| Threads_cached | 0 |
| Threads_created | 64 |
| Threads_connected | 36 |
| Threads_running | 1 |
| Uptime | 3295 |
+--------------------------+----------+

The MySQL variables came out looking all funny...

+-------------------------+-----------------------------------------------------
--------------------------------------------------------------------------------
----------------------+
| Variable_name | Value

|
+-------------------------+-----------------------------------------------------
--------------------------------------------------------------------------------
----------------------+
| ansi_mode | OFF

|
| back_log | 50

|
| basedir | /usr/local/

|
| binlog_cache_size | 32768

|
| character_set | latin1

|
| character_sets | latin1 dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7
cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win125
0 croat cp1257 latin5 |
| concurrent_insert | ON

|
| connect_timeout | 5

|
| datadir | /var/db/mysql/

|
| delay_key_write | ON

|
| delayed_insert_limit | 100

|
| delayed_insert_timeout | 300

|
| delayed_queue_size | 1000

|
| flush | OFF

|
| flush_time | 0

|
| have_bdb | NO

|
| have_gemini | NO

|
| have_innobase | NO

|
| have_isam | YES

|
| have_raid | NO

|
| have_ssl | NO

|
| init_file |

|
| interactive_timeout | 28800

|
| join_buffer_size | 131072

|
| key_buffer_size | 8388600

|
| language | /usr/local/share/mysql/english/

|
| large_files_support | ON

|
| log | OFF

|
| log_update | OFF

|
| log_bin | OFF

|
| log_slave_updates | OFF

|
| long_query_time | 10

|
| low_priority_updates | OFF

|
| lower_case_table_names | 0

|
| max_allowed_packet | 1048576

|
| max_binlog_cache_size | 4294967295

|
| max_connections | 256

|
| max_connect_errors | 10

|
| max_delayed_threads | 20

|
| max_heap_table_size | 16777216

|
| max_join_size | 4294967295

|
| max_sort_length | 1024

|
| max_tmp_tables | 32

|
| max_write_lock_count | 4294967295

|
| myisam_recover_options | OFF

|
| myisam_sort_buffer_size | 8388608

|
| net_buffer_length | 16384

|
| net_read_timeout | 30

|
| net_retry_count | 1000000

|
| net_write_timeout | 60

|
| open_files_limit | 0

|
| pid_file | /var/db/mysql/asiansinc.com.pid

|
| port | 3306

|
| protocol_version | 10

|
| record_buffer | 131072

|
| query_buffer_size | 0

|
| safe_show_database | OFF

|
| server_id | 0

|
| skip_locking | OFF

|
| skip_networking | OFF

|
| skip_show_database | OFF

|
| slow_launch_time | 2

|
| socket | /tmp/mysql.sock

|
| sort_buffer | 2097144

|
| table_cache | 64

|
| table_type | MYISAM

|
| thread_cache_size | 0

|
| thread_stack | 65536

|
| timezone | PST

|
| tmp_table_size | 1048576

|
| tmpdir | /var/tmp/

|
| version | 3.23.32

|
| wait_timeout | 28800

|
+-------------------------+-----------------------------------------------------

And plus I sometimes get the following error:

Database error in vBulletin: Invalid SQL: SELECT thread.title,thread.threadid,thread.forumid,postid ,post.dateline FROM post,thread WHERE thread.threadid=post.threadid AND post.userid=212 ORDER BY post.dateline DESC
mysql error: Got error 127 from table handler
mysql error number: 1030
Date: Thursday 08th of February 2001 01:22:36 PM
Script: /member.php?action=getinfo&userid=212
Referer: http://aforums.com/forumdisplay.php?forumid=15

If you can, please test out my forums, and see if they load slowly for you too.

http://aforums.com

Thanks,

Rich

[werehere]

I cannot access your site because it is soo slow! So your saying it has been this slow with only a couple of people online? If there are only a few people online then how can your 256MB of ram not be enough?

Was your other server a Celeron? In my experience I have found that Celeron boxes for database applications just suck! ;)

[Cold Steel]

I have confirmed with the techs that the CPU is a Pentium, not a Celeron.

I don't know why it's going so slow... was helping all you wonderful people could help me out. :)

[Cold Steel]

Sometimes vB moves along rather fast. Other times, it just crawls.

It's crawling right now, and there's almost no one on it.

There was 5+ people on it yesterday, and it moved fast enough.

Any ideas?

[etones]

what other sites do you have running on the server?

Seems like at some points something seems to be hogging all server resources. I had a p3 733 with on 256mb ram and it easily coped with Vb 1.1.3 and 75 users.

I think that either all the hosts bandwidth is being used and you dont have any at all to burst on, or u have something on your server thats takes up all cpu time.. maybe an old cronjob?

[werehere]

Originally posted by Cold Steel
I don't know why it's going so slow... was helping all you wonderful people could help me out. :)

You seem like such a nice person, and I wish I could help you out more, but it seems like there is something else there that is taking up all of the cpu/ram that you have. I truly think you should be looking at what else could be affecting your server outside of vbulletin! ;)

Just an idea. :)

[Cold Steel]

You're right, it was other things on the server. I also run a free hosting service, and some wacko had loaded up a couple porn files onto the server.

The forums would slow down when millions of people started downloading the .asf files.

After deleting those files, my forums are flying again!

Thanks :)

[Joe]

Thats great news Cold Steel! I hope you dont recive a huge bandwidth bill for the .asf files downloaded!