added more ram

[trip008]

a while back i was having a lot of issues w/ vbulletin getting too overloaded. i asked for help and was given some advice as to how i should configure mysql for optimal performance:

http://www.vbulletin.com/forum/showthread.php?t=96055

after i made the changes, my messageboards worked much better!! one of the final suggestions in that thread, was to upgrade my RAM to 2GB. we finally made this upgrade. now, how would i need to modify my mysql settings to take advantage of this upgrade?

*v

[eva2000]

could you repost updated answers to the following http://www.vbulletin.com/forum/showthread.php?t=70117 particular the mysql server usage stats url

[trip008]

ok, here goes:

1. is this on dedicated or shared virual server?

we are running 9 different message boards and they are load balanced on 3 separate front end web servers. the DB is on a separate server.

2. your server specs:

each of the 4 servers are:

cpu speed/type single or dual cpus): dual p3 733 Mhz
how much memory installed: 2GB (DB), 1.2GB (front end)
hard drive type/configuration: 30-80GB RAID
linux distributor or windows version: redhat linux 7.3
apache/IIS version: Apache/1.3.27 (Unix)
PHP version: php 4.3.2
MySQL version: mysql 4.0.16


3. if you use mysql 4.x instead of mysql 3.23.x, do you have any innodb type databases/tables on your server ?

not sure.

4. if possible how mysql was compiled/installed

installed from: MySQL-server-4.0.16-0.i386.rpm

5. your top stats

for ther highest traffic board: 2,882 users online
for the second highest board:1,334 users online

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

| 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 german
1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 cr
oat 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 DISABLED
| 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_buffer_pool_size | 8388608
| innodb_data_file_path |
| 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
| interactive_timeout | 28800
| join_buffer_size | 1044480
| key_buffer_size | 67108864
| language | /usr/share/mysql/english/
| large_files_support | ON
| local_infile | ON
| locked_in_memory | OFF
| log | OFF
| log_update | OFF
| log_bin | ON
| log_slave_updates | OFF
| log_slow_queries | OFF
| log_warnings | OFF
| long_query_time | 10
| low_priority_updates | OFF
| lower_case_table_names | OFF
| max_allowed_packet | 16776192
| max_binlog_cache_size | 2097152000
| max_binlog_size | 1073741824
| max_connections | 600
| max_connect_errors | 10
| max_delayed_threads | 20
| max_heap_table_size | 16777216
| max_join_size | 4294967295
| max_relay_log_size | 0
| max_seeks_for_key | 4294967295
| max_sort_length | 1024
| max_user_connections | 0
| max_tmp_tables | 32
| max_write_lock_count | 4294967295
| myisam_max_extra_sort_file_size | 268435456
| myisam_max_sort_file_size | 2147483647
| myisam_repair_threads | 1
| myisam_recover_options | OFF
| myisam_sort_buffer_size | 67108864
| net_buffer_length | 8192
| net_read_timeout | 30
| net_retry_count | 10
| net_write_timeout | 60
| new | OFF
| open_files_limit | 3010
| pid_file | /var/lib/mysql/crdotnet.pid
| log_error |
| port | 3306
| protocol_version | 10
| query_alloc_block_size | 8192
| query_cache_limit | 1048576
| query_cache_size | 16777216
| query_cache_type | ON
| query_prealloc_size | 8192
| range_alloc_block_size | 2048
| read_buffer_size | 1044480
| read_only | OFF
| read_rnd_buffer_size | 262144
| rpl_recovery_rank | 0
| server_id | 1
| 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 | 2097144
| sql_mode | 0
| table_cache | 1024
| table_type | MYISAM
| thread_cache_size | 64
| thread_stack | 126976
| tx_isolation | REPEATABLE-READ
| timezone | PDT
| tmp_table_size | 33554432
| tmpdir | /tmp/
| transaction_alloc_block_size | 8192
| transaction_prealloc_size | 4096
| version | 4.0.16-standard-log
| wait_timeout | 900



7. your mysql extended-status output

+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 19758 |
| Aborted_connects | 1 |
| Bytes_received | 2699135167 |
| Bytes_sent | 1321742024 |
| Com_admin_commands | 15 |
| Com_alter_table | 10 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 6707425 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 9 |
| Com_delete | 103104 |
| Com_delete_multi | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_flush | 13 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_insert | 2760260 |
| Com_insert_select | 33234 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 0 |
| Com_optimize | 0 |
| Com_purge | 20 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 73768 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 24239459 |
| Com_set_option | 8 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 3 |
| Com_show_create | 8 |
| Com_show_databases | 3 |
| Com_show_fields | 24 |
| Com_show_grants | 0 |
| Com_show_keys | 60 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_processlist | 0 |
| Com_show_slave_hosts | 15 |
| Com_show_slave_status | 0 |
| Com_show_status | 3644 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 206 |
| Com_show_variables | 135 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 6825712 |
| Connections | 6757918 |
| Created_tmp_disk_tables | 236216 |
| Created_tmp_tables | 1521148 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 737969 |
| Handler_read_first | 5320175 |
| Handler_read_key | 142907870 |
| Handler_read_next | 2599097414 |
| Handler_read_prev | 45729541 |
| Handler_read_rnd | 1528376661 |
| Handler_read_rnd_next | 1113063253 |
| Handler_rollback | 0 |
| Handler_update | 9005412 |
| Handler_write | 1463770764 |
| Key_blocks_used | 62345 |
| Key_read_requests | 1001904031 |
| Key_reads | 634953 |
| Key_write_requests | 5645930 |
| Key_writes | 4750961 |
| Max_used_connections | 279 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 1024 |
| Open_files | 1877 |
| Open_streams | 0 |
| Opened_tables | 10736 |
| Questions | 74541358 |
| Qcache_queries_in_cache | 1056 |
| Qcache_inserts | 24193114 |
| Qcache_hits | 27049868 |
| Qcache_lowmem_prunes | 6859025 |
| Qcache_not_cached | 60390 |
| Qcache_free_memory | 3371232 |
| Qcache_free_blocks | 952 |
| Qcache_total_blocks | 4682 |
| Rpl_status | NULL |
| Select_full_join | 5736 |
| Select_full_range_join | 0 |
| Select_range | 4383473 |
| Select_range_check | 0 |
| Select_scan | 4473585 |
| Slave_open_temp_tables | 0 |
| Slave_running | ON |
| Slow_launch_threads | 0 |
| Slow_queries | 7 |
| Sort_merge_passes | 0 |
| Sort_range | 3405522 |
| Sort_rows | 1545862313 |
| Sort_scan | 2062431 |
| Table_locks_immediate | 66793426 |
| Table_locks_waited | 484412 |
| Threads_cached | 55 |
| Threads_created | 1633 |
| Threads_connected | 24 |
| Threads_running | 2 |
| Uptime | 1098628 |
+--------------------------+------------+
8. oh and is your vB the only thing on the server? or other scripts? sites?

yes, VB is the only thing running on those 4 servers.

9. how many average and max concurrent users on your vB forum ?

for ther highest traffic board: 2,882 users online
for the second highest board:1,334 users online

10. create a file named phpinfo.php and place this code in it and post the url/link to it from your web site

http://forums.mtbr.com/temp/phpinfo.php

11. 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 =>10
StartServers =>5
MaxClients =>150

12. what version of vB are you running ?

3RC2

[eva2000]

hmm you're showing up as unlicensed vB user

Could you please enter your vBulletin.com forum registered email address on the form on this page to receive support for this issue: http://www.vbulletin.com/members/membersupport_priority.php
- you will need your vB license customer number and password to access this form.

[trip008]

hmm you're showing up as unlicensed vB user

Could you please enter your vBulletin.com forum registered email address on the form on this page to receive support for this issue: http://www.vbulletin.com/members/membersupport_priority.php
- you will need your vB license customer number and password to access this form.

ah, i had changed my email addy where vbulletin sends my email. i just changed it back. should be all good now.

*v

[eva2000]

okie dokie

a few things

1. could you also tally up the other 7 forums average and max users online and the typical cookie timeout value set for these forums.

for ther highest traffic board: 2,882 users online
for the second highest board:1,334 users online

2. for MySQL upgrade to 4.0.18
3. for PHP upgrade to 4.3.6 for web servers
4. for apache upgrade to 1.3.29 for web servers
5. for smp linux 2.4.x kernel update to latest in the series 2.4.26 smp kernels on each of the 4 servers

6. upgrade to vB3.01 to plug up the bugs/security issues in vB3RC2

7. what's maxclients set to on each web server in their respective httpd.conf files ?
8. lastly my.cnf tweaks for 2GB of ram on db server try this


[mysqld]
max_connections = 800
key_buffer = 32M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1024
thread_cache_size = 64
wait_timeout = 1800
connect_timeout = 10
max_allowed_packet = 16M
max_connect_errors = 10
query_cache_limit = 1M
query_cache_size = 32M
query_cache_type = 1
skip-innodb

[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

[trip008]

eva, thanks for the reply!!

details below-

1. could you also tally up the other 7 forums average and max users online and the typical cookie timeout value set for these forums.

TOTAL:

max:5747
avg:4940
average timeout:7488


INDIVIDUAL:

mtbr:

max: 2882
avg: 2723
timeout: 11000

road:

max:1334
avg:1219
timeout:11000

audio:

max: 464
avg:353
timeout: 7200

vgr:

max: 396
avg: 255
timeout: 7200

golf:

max:205
avg:132
timeout: 11000

car:

max:180
avg:116
timeout:11000

computing:

max:129
avg:64
timeout:7200

photo:

max:117
avg:69
timeout:900

outdoor:

max: 40
avg:9
timeout:900


7. what's maxclients set to on each web server in their respective httpd.conf files ?
each front end server is set to 150


8. lastly my.cnf tweaks for 2GB of ram on db server try this

so it looks like your recommendations for the 2GB ram are pretty similar to what you recommended for the 1.2GB.

the only difference are:

max_connections: raise from 600 to 800
key buffer: lower from 64M to 32M
wait_timeout: raise from 900 to 1800
query_cache_size: raise from 16 M to 32M

so you think i need to *lower* the key buffer?

*vlad

[eva2000]

interesting max_connections to accept more mysql concurrent connections will use up more ram if the key buffer is to high.. and possibly swap to disk

this is old related to 3.23.x mysql versions but

MySQL memory used = key_buffer + max_connections * (join_buffer + record_buffer + sort_buffer + thread_stack + tmp_table_size)

actually better yet http://dev.mysql.com/doc/mysql/en/Memory_use.html