Is my hosting choice to slow? - vBulletin Community Forum::
Is my hosting choice to slow? vBulletin Hosting Options VBulletin Experts, Hosting Expert I need your help please, Extremely slow website.
http://www.vbulletin.com/forum/showthread.php?t=144441HOME |
vB is running really slow when the post table is accessed, its over 4GB now! Anything that would speed this up would be great :)
PHP runs fine, but MySQL really bottlenecks. If there is a query to insert a post for example, it locks the table and prevents anything being done until its inserted (maybe 10-15 seconds). If I can stop the locking the site will drastically speed up.
Its a dedicated server, 2 x 2.8Xeons, 1GB Ram, 2 x 120GB drives (php/win on one, MySQL on the other). Running Windows 2003 fully patched.
This site is the only main site on there (3 tiny ones). Running 3.0.5 (but manually patched to 3.0.7 because of hacks). 5.5 Million posts and up to 1000 users online, normally 400-700.
PHP : 4.3.11, MySQL : 4.0.24
PHP Info : http://www.pcreview.co.uk/phpinfo2.php
My.ini:
[mysqld]
basedir=E:/mysql
datadir=E:/mysql/data
skip-locking
skip-innodb
max_connections = 650
key_buffer_size=64M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1500
thread_cache_size = 64
wait_timeout = 3600
connect_timeout = 10
tmp_table_size = 32M
max_allowed_packet = 16M
max_connect_errors = 10
query_cache_limit = 1M
query_cache_size = 32M
query_cache_type = 1
#(Unknown option:)query_prealloc_size = 16384
#(Unknown option:)query_alloc_block_size = 16384
set-variable = long_query_time=2
#(Unknown option:)log-long-format
log-slow-queries=
[mysqld_safe]
open_files_limit = 8192
[mysqldump]
quick
max_allowed_packet = 16M
[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M
Extended Status:
+--------------------------+------------+
Variable_name Value
+--------------------------+------------+
Aborted_clients 1647
Aborted_connects 25
Bytes_received 559277780
Bytes_sent 1698806249
Com_admin_commands 1
Com_alter_table 1
Com_analyze 0
Com_backup_table 0
Com_begin 0
Whis is my forum sooooooo slow? - vBulletin Community Forum::
Whis is my forum sooooooo slow? vBulletin 1.1.x Suggestions and Feedback vBulletin 2 How Do I and Troubleshooting. 2. Sat 4th Jan 03 1:18pm. My forum is
http://www.vbulletin.com/forum/showthread.php?p=37654HOME |
Com_change_db 380261
Com_change_master 0
Com_check 105
slow down a vB message? - vBulletin Community Forum::
slow down a vB message? vBulletin 2 How Do I and Troubleshooting vBulletin Community Forum > vBulletin Legacy Versions (2.x, 3.0 and 3.5)
http://www.vbulletin.com/forum/showthread.php?t=46091HOME |
Com_commit 0
Com_create_db 0
Com_create_function 0
Com_create_index 0
Com_create_table 0
Com_delete 1115
Com_delete_multi 0
Com_drop_db 0
Com_drop_function 0
Com_drop_index 0
Com_drop_table 0
Com_flush 0
Com_grant 0
Com_ha_close 0
Com_ha_open 0
Com_ha_read 0
Com_insert 173551
Com_insert_select 39
Com_kill 1581
Com_load 0
Com_load_master_data 0
Com_load_master_table 0
Com_lock_tables 0
Com_optimize 121
Com_purge 0
Com_rename_table 0
Com_repair 0
Com_replace 1161
Com_replace_select 0
Com_reset 0
Com_restore_table 0
Com_revoke 0
Com_rollback 0
Com_savepoint 0
Com_select 618060
Com_set_option 5
Com_show_binlog_events 0
Com_show_binlogs 0
Com_show_create 142
Com_show_databases 2
Com_show_fields 199
Com_show_grants 0
Com_show_keys 0
Com_show_logs 0
Com_show_master_status 0
Com_show_new_master 0
Com_show_open_tables 0
Com_show_processlist 427
Com_show_slave_hosts 0
Com_show_slave_status 0
Com_show_status 45945
Com_show_innodb_status 39836
Com_show_tables 8
Com_show_variables 9
Com_slave_start 0
Com_slave_stop 0
Com_truncate 0
Com_unlock_tables 0
Com_update 400997
Com_update_multi 0
Connections 381410
Created_tmp_disk_tables 2059
Created_tmp_tables 76563
Created_tmp_files 941
Delayed_insert_threads 0
Delayed_writes 0
Delayed_errors 0
Flush_commands 1
Handler_commit 0
Handler_delete 58719
Handler_read_first 43279
Handler_read_key 193000319
Handler_read_next 435328257
Handler_read_prev 915719
Handler_read_rnd 150489813
Handler_read_rnd_next 575692267
Handler_rollback 0
Handler_update 523128
Handler_write 3641813
Key_blocks_used 62112
Key_read_requests 472136867
Key_reads 2294875
Key_write_requests 4117442
Key_writes 427275
Max_used_connections 313
Not_flushed_key_blocks 0
Not_flushed_delayed_rows 0
Open_tables 43
Open_files 70
Open_streams 0
Opened_tables 9322
Questions 3525534
Qcache_queries_in_cache 8035
Qcache_inserts 609129
Qcache_hits 1482204
Qcache_lowmem_prunes 29089
Qcache_not_cached 7431
Qcache_free_memory 13582784
Qcache_free_blocks 4297
Qcache_total_blocks 21194
Rpl_status NULL
Select_full_join 28
Select_full_range_join 0
Select_range 40910
Select_range_check 0
Select_scan 238556
Slave_open_temp_tables 0
Slave_running OFF
Slow_launch_threads 0
Slow_queries 3909
Sort_merge_passes 470
Sort_range 96028
Sort_rows 310077933
Sort_scan 30769
Table_locks_immediate 6591961
Table_locks_waited 7380
Threads_cached 61
Threads_created 1111
Threads_connected 4
Threads_running 1
Uptime 61232
+--------------------------+------------+
There is a 400MB pagefile at the moment, but about 400MB free memory. That 110MB was only for the MySQL-nt process.
If you could get us the rest of this
http://www.vbulletin.com/forum/showthread.php?t=70117
Ian, the BIGGEST problem I see, is your old, very old, outdated mysql 3 version, which is insanely slower when compared to mysql 4 with query cacheing enabled.
Edit: Are you sure you are using MySQL 3? you have query cacheing on which means you'd need to have mysql 4.
Yes, sorry, I'm an idiot - I've updated my first post :o
PHPInfo says client API version 3.23.49, so I assumed it must be that.
I think you could do with abit more ram, or at least more MySQL memory buffers, but I think we should wait for AWS or Eva to give us their opinions :D I am still a padawon
Are you logging preformance at all?
Can you post a screenshot of the Taskmanager so we can see how your ram is holding up?
MySQL LOVES ram. And you might not have quite enough to manage with a site that size.
Seems ok to me :) (see attached). The locking of the post table seems to be causing most of the problems.
MySQL is 4.0.24 (thanks for pointing that out).
PHP : 4.3.11, MySQL : 4.2.04MySQL 4.2 don't exist AFAIK. It goes from 4.1 to 5.0
At the moment its only 10% CPU and 110MB ram (it is quiet now though).
I changed the thread/user tables to innodb and that seems to have made a massive performance improvement, but I'll only be able to tell for sure tonight when its busy.
Ian, how much of that was in swap ontop of the 110MB of ram?
If you could get us the rest of this
http://www.vbulletin.com/forum/showthread.php?t=70117
Ian, the BIGGEST problem I see, is your old, very old, outdated mysql 3 version, which is insanely slower when compared to mysql 4 with query cacheing enabled.
Edit: Are you sure you are using MySQL 3? you have query cacheing on which means you'd need to have mysql 4.
OK, its 120MB in PF
Are you logging preformance at all?
Can you post a screenshot of the Taskmanager so we can see how your ram is holding up?
MySQL LOVES ram. And you might not have quite enough to manage with a site that size.
Do you have the Virtual Memory Column showing in the taskmanager ?
Task Manager > View > Select Columns > Virtual Memory Size
Unlike linux, windows actively moves things into PF that are not being used as much as other things, esp when it is nearing its maximum ammount of physical ram.
Take a look in task manager and select process tab. Look for mysqld.exe or mysql-max-nt.exe It will tell you how much ram and cpu power mysql is using. Post that here.
You really need more ram at this point.
For now I would raise key_buffer to 256M, that is the highest you can have it set at with a gig of ram, lower max_connections to 450 and lower wait_timeout to 120.
For a site with that kind of traffic it might be time to move mysql to a dedicated box. More ram will help, but, down the road you'll experience the same thing.
 Where's The Advantage In Windows Genuine Advantage?
 Stocks Bounce After S&P Joins Bear Market
|
Slow vBulletin