1.2 million post server - do I need to upgrade?
Filed Under mercialleasing.com |
Here's my server hardware info:
http://www.bladeforums.com/phpsysinfo
PHP Version 4.1.2
MySQL Version 3.23.36
top stats - (during slow period on server)
11:06pm up 16 days, 16:52, 1 user, load average: 1.61, 1.91, 2.20
259 processes: 257 sleeping, 2 running, 0 zombie, 0 stopped
CPU0 states: 29.1% user, 5.1% system, 0.0% nice, 64.1% idle
CPU1 states: 14.0% user, 53.1% system, 0.0% nice, 31.2% idle
Mem: 1028428K av, 1025788K used, 2640K free, 0K shrd, 2548K buff
Swap: 530104K av, 13448K used, 516656K free 487960K cached
Avg users - 150-200, peak is around 230.
VB is 99% of the traffic
Not sure what else you need - but I'll be happy to provide info.
Kevin
2. create or replace your /etc/my.cnf with below contents and restart mysql
[client]
port = 3306
socket = /var/lib/mysql/mysql.sock
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
set-variable = max_connections=650
set-variable = key_buffer=16M
set-variable = myisam_sort_buffer_size=64M
set-variable = join_buffer=1M
set-variable = record_buffer=1M
set-variable = sort_buffer=2M
set-variable = table_cache=1024
set-variable = thread_cache_size=256
set-variable = wait_timeout=9600
set-variable = connect_timeout=10
set-variable = max_allowed_packet=16M
set-variable = max_connect_errors=10
[safe_mysqld]
open_files_limit=8192
[mysqldump]
quick
set-variable = max_allowed_packet=16M
[myisamchk]
set-variable = key_buffer=64M
set-variable = sort_buffer=64M
set-variable = read_buffer=16M
set-variable = write_buffer=16M
3. last is you need to check or clean up some space on your /usr partition you seem to only have 94MB left of free space
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 10
net_write_timeout 60
open_files_limit 0
pid_file /var/run/mysqld/mysqld.pid
port 3306
protocol_version 10
record_buffer 3141632
query_buffer_size 0
safe_show_database OFF
server_id 0
skip_locking ON
skip_networking OFF
skip_show_database OFF
slow_launch_time 2
socket /var/lib/mysql/mysql.sock
sort_buffer 6291448
table_cache 1024
table_type MYISAM
thread_cache_size 256
thread_stack 65536
transaction_isolation READ-COMMITTED
timezone EDT
tmp_table_size 1048576
tmpdir /tmp/
version 3.23.36
wait_timeout 28800
5. your mysql extended-status output either still telnet as root user type
mysqladmin -u root -p extended-status
or preferred is to installed extended-status output script which is [snip]and post url to that here
http://www.bladeforums.com/forums/admin/mysqlinfo.php
6. oh and is your vB the only thing on the server? or other scripts? sites?
Pretty much the only one. Others are insignificant.
7. how many average and max concurrent users on your vB forum ?
160-80 / Max is around 230-40
8. create a file named phpinfo.php and place this code in it and post the url/link to it from your web site
http://www.bladeforums.com/phpinfo.php
9. if you run Apache and you have your own dedicated server or access to your httpd.conf (apache configuration file) can you post the values you have set for the following :
KeepAlive - ON
MaxKeepAliveRequests - 100
KeepAliveTimeout - 15
MinSpareServers - 5
MaxSpareServers - 20
StartServers - 8
MaxClients - 150
10. what version of vB are you running ? [/B][/QUOTE]
2.2.8
Let me know if you need something else
So essentially, remove the following 3 lines from [mysqld]?
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
?
Kevin yeah under [mysqld] group and the whole [client] group
[mysqld]
set-variable = max_connections=650
set-variable = key_buffer=16M
set-variable = myisam_sort_buffer_size=64M
set-variable = join_buffer=1M
set-variable = record_buffer=1M
set-variable = sort_buffer=2M
set-variable = table_cache=1024
set-variable = thread_cache_size=256
set-variable = wait_timeout=9600
set-variable = connect_timeout=10
set-variable = max_allowed_packet=16M
set-variable = max_connect_errors=10
[safe_mysqld]
open_files_limit=8192
[mysqldump]
quick
set-variable = max_allowed_packet=16M
[myisamchk]
set-variable = key_buffer=64M
set-variable = sort_buffer=64M
set-variable = read_buffer=16M
set-variable = write_buffer=16M
See above.
2. if possible how mysql was compiled/installed
Factory installed Red hat configuration
3. your top stats
See above
4. your mysql configuration variables located at /etc/my.cnf
mysqladmin -u root -p variables
Variable_name Value
ansi_mode OFF
back_log 50
basedir /usr/
bdb_cache_size 8388600
bdb_log_buffer_size 524288
bdb_home /var/lib/mysql/
bdb_max_lock 10000
bdb_logdir
bdb_shared_data OFF
bdb_tmpdir /tmp/
bdb_version Sleepycat Software: Berkeley DB 3.2.9a: (March 27, 2001)
binlog_cache_size 32768
character_set latin1
character_sets latin1 big5 cp1251 cp1257[SNIP]
concurrent_insert ON
connect_timeout 5
datadir /var/lib/mysql/
delay_key_write ON
delayed_insert_limit 100
delayed_insert_timeout 300
delayed_queue_size 1000
flush OFF
flush_time 0
have_bdb YES
have_gemini NO
have_innobase NO
have_isam YES
have_raid NO
have_ssl NO
init_file
interactive_timeout 28800
join_buffer_size 3141632
key_buffer_size 16773120
language /usr/share/mysql/english/
large_files_support ON
locked_in_memory OFF
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_binlog_size 1073741824
max_connections 350
max_connect_errors 10
max_delayed_threads 20
max_heap_table_size 16777216
max_join_size 4294967295
max_sort_length 1024
max_user_connections 0
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
?
Kevin
2. Some of those figures are massive changes - are they going to speed up server response?
3. That /usr partition has been like that since the day we got the server.
Kevin
1. your server specs, such as mysql and php version
2. if possible how mysql was compiled/installed
3. your top stats
4. your mysql configuration variables located at /etc/my.cnf or c:my.cnf if on Windows server if you don't have that file you need to log into telnet and as root user type
mysqladmin -u root -p variables
copy and paste output here
5. your mysql extended-status output either still telnet as root user type
mysqladmin -u root -p extended-status
copy and paste output here
or preferred is to installed extended-status output script which is either located
- in your vB 2.2.6 or higher vB version's zip file extra's folder, upload mysqlinfo.php script to your site or if you're on an pre vB 2.2.6 install go to
- http://vbulletin.com/forum/showthread.php?threadid=3477 and install that scrip making sure to edit $mysqllogin line with your own mysqlusername and password
and post url to that here
6. oh and is your vB the only thing on the server? or other scripts? sites?
7. how many average and max concurrent users on your vB forum ?
8. create a file named phpinfo.php and place this code in it and post the url/link to it from your web site
phpinfo();
?>
i.e. yourdomain.com/phpinfo.php
9. if you run Apache and you have your own dedicated server or access to your httpd.conf (apache configuration file) can you post the values you have set for the following :
KeepAlive
MaxKeepAliveRequests
KeepAliveTimeout
MinSpareServers
MaxSpareServers
StartServers
MaxClients
10. what version of vB are you running ?
Replaced with old version and server is back up
UPDATE 2 - serves me right for copying and pasting into the same file. Created new file and backed up original, seems to be working.
Still a massive time delay though when performing a "View new posts" search... will this be solved when MySql is upgraded?
#If you have any other info about this subject , Please add it free.# |