New DB server Need advice on Tuning

[paulsjv]

I've been reading the posts on here about tuning a DB server and well I need HELP! :D so here's all my stats below. I was thinking about using the my-large.cnf file and bumping up the max_connections to like 800 or 1000 b/c on the server it's on now i have the max_connections set to 500 and MySQL craps out with too many connections when the server gets busy.. so anyway i need help.. my info is below...

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

MySQL version 3.23.36
PHP 4.0.6

DB Server (it's only the DB Server and nothing else)
933 PIII
36 Gig HD
1 Gig of Ram

2. if possible how mysql was compiled/installed

OS is Engarde and it is compiled from binaries (I believe) when you install the OS.


3. your top stats

Since this is a new server and I'm just setting it up I really don't have any yet.

[paulsjv]

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


==================================
| ansi_mode | OFF |
| back_log | 50 |
| basedir | / |
| binlog_cache_size | 32768 |
| character_set | latin1 |
| character_sets | latin1 big5 czech euc_kr gb2312 gbk sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5 |
| 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 | 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/share/mysql/english/ |
| large_files_support | ON |
| locked_in_memory | OFF |
| log | OFF |
| log_update | OFF |
| log_bin | OFF |
| log_slave_updates | OFF

[paulsjv]

|
| 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 | 100 |
| 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 |
| 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/lib/mysql/mysql.pid |
| port | 0 |
| protocol_version | 10 |
| record_buffer | 131072 |
| query_buffer_size | 0 |
| safe_show_database | OFF |
| server_id | 0 |
| skip_locking | ON |
| skip_networking | ON |
| skip_show_database | OFF |
| slow_launch_time | 2 |
| socket | /var/lib/mysql/mysql.sock |
| sort_buffer | 2097144 |
| table_cache | 64 |
| table_type | MYISAM |
| thread_cache_size | 0 |
| thread_stack | 65536 |
| transaction_isolation | READ-COMMITTED |
| timezone | CDT |
| tmp_table_size | 1048576 |
| tmpdir | /tmp/ |
| version | 3.23.36 |
| wait_timeout | 28800 |
=================================

[paulsjv]

5. your mysql extended-status output either still telnet as root user type

mysqladmin -u root -p extended-status

=================================

| Aborted_clients | 0 |
| Aborted_connects | 82 |
| Bytes_received | 1698 |
| Bytes_sent | 11987 |
| Connections | 93 |
| Created_tmp_disk_tables | 0 |
| Created_tmp_tables | 0 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_delete | 0 |
| Handler_read_first | 5 |
| Handler_read_key | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 10 |
| Handler_update | 0 |
| Handler_write | 0 |
| Key_blocks_used | 0 |
| Key_read_requests | 0 |
| Key_reads | 0 |
| Key_write_requests | 0 |
| Key_writes | 0 |
| Max_used_connections | 2 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 1 |
| Open_files | 2 |
| Open_streams | 0 |
| Opened_tables | 7 |
| Questions | 15 |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 0 |
| Select_range_check | 0 |
| Select_scan | 0 |
| Slave_running | OFF |
| Slave_open_temp_tables | 0 |
| Slow_launch_threads | 0 |
| Slow_queries | 0 |
| Sort_merge_passes | 0 |
| Sort_range | 0 |
| Sort_rows | 0 |
| Sort_scan | 0 |
| Table_locks_immediate | 9 |
| Table_locks_waited | 0 |
| Threads_cached | 0 |
| Threads_created | 92 |
| Threads_connected | 1 |
| Threads_running | 1 |
| Uptime | 176711 |
+--------------------------+--------+

[paulsjv]

6. oh and is your vB the only thing on the server? or other scripts? sites?

There are 4 sites on the server that use vB and a total of 6 sites that make connections to the database.

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

average connections for all the sites at once is about 150 and can max out ~700 concurrent users

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

Click here for phpinfo.php (http://206.242.133.162/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 5
MaxClients 150

10. what version of vB are you running ?

2.2.6

[eva2000]

Originally posted by paulsjv
I was thinking about using the my-large.cnf file not recommended.. you should use something called eva2000 :D

[eva2000]

1. upgrade to mysql 3.23.52
2. upgrade to PHP 4.2.3
3. upgrade to vB 2.2.8
4. create or replace the current /etc/my.cnf file with the following settings 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=800
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=1280
set-variable = thread_cache_size=512
set-variable = wait_timeout=7200
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

[paulsjv]

Originally posted by eva2000
not recommended.. you should use something called eva2000 :D
hehe.. well i've got the eva2000 then!!! haha...

but i do have a question... why do you have the wait_timeout=7200? isn't 2 hours a little long to keep a connection to the database? what about making that like 60 or something of that nature?

[eva2000]

Originally posted by paulsjv
hehe.. well i've got the eva2000 then!!! haha...

but i do have a question... why do you have the wait_timeout=7200? isn't 2 hours a little long to keep a connection to the database? what about making that like 60 or something of that nature? making it too short will give you errors as well as i have found while working on another vb client's server..