1.2 million post server - do I need to upgrade?

[Spark]

http://www.bladeforums.com/forums

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

[eva2000]

please provide the following

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 ?

[Spark]

1. your server specs, such as mysql and php version

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

[Spark]

| 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 | 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

[eva2000]

1. upgrade to mysql 3.23.52

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

[Spark]

1. Ok, I'll have our consultant upgrade mysql ASAP

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

[Spark]

UPDATE - replaced the my.cnf file with that information and my server went down and would not operate. Something ain't right with that information.

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?

[eva2000]

change it to below

[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

[Spark]

So essentially, remove the following 3 lines from [mysqld]?

port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking

?

Kevin

[eva2000]

Originally posted by Spark
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