Need help configuring my.cnf for my server..

[Scotteh]

Hi there,

I was wondering if anybody could help me with this slight problem.

I need help optimizing mysql for vbulletin. The server is also used for massive downloading so I need to find ways of reducing serverload.

Server Info:
Redhat Linux 8.0
Intel Celeron 2200 mhz
256 ram (will upgrade soon..hopefully)
60 GB HD
apache 1.3.27
PHP Version 4.3.1
mysql 4.0.12
phpa installed

Can anyone suggest a good my.cnf file setup?

Any help would be greatfully appreciated.

Thanks,
Scott

[Steve Machol]

http://www.vbulletin.com/forum/showthread.php?threadid=70117

[eva2000]

if you can please provide the following in this format

1. is this on dedicated or shared virual server
2. your server specs. For example:

cpu speed/type single or dual cpus): dual p3 1ghz
how much memory installed: 512mb ram
hard drive type/configuration: 60GB EIDE non raid
linux distributor or windows version: redhat linux 7.3
apache/IIS version: apache 1.3.27
PHP version: php 4.3.1
MySQL version: mysql 3.23.56


3. if you use mysql 4.x instead of mysql 3.23.x, do you have any innodb type databases/tables on your server ?
4. if possible how mysql was compiled/installed
5. your top stats
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

mysqladmin -u root -p variables

copy and paste output here

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

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

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

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

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
MaxKeepAliveRequests
KeepAliveTimeout
MinSpareServers
MaxSpareServers
StartServers
MaxClients

12. what version of vB are you running ?

[Scotteh]

1) Dedicated

2) Listed in previous post

3) Dont know..

4) Compiled by whm

5) 2:25pm up 16:52, 1 user, load average: 1.00, 0.78, 0.58
90 processes: 88 sleeping, 2 running, 0 zombie, 0 stopped
CPU states: 24.5% user, 2.7% system, 0.0% nice, 72.7% idle
Mem: 223068K av, 207248K used, 15820K free, 0K shrd, 23048K buff
Swap: 1052248K av, 25512K used, 1026736K free 124908K cached

PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
10030 root 15 0 4084 4084 3688 S 20.8 1.8 1:22 httpd
9214 mysql 15 0 22228 6252 2192 S 5.0 2.8 17:16 mysqld

6) [mysqld]
set-variable = max_connections=450

7) +--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 1 |
| Aborted_connects | 1 |
| Bytes_received | 105740838 |
| Bytes_sent | 1294105303 |
| Com_admin_commands | 0 |
| Com_alter_table | 107 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 31254 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_commit | 0 |
| Com_create_db | 1 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 4 |
| Com_delete | 2184 |
| Com_delete_multi | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_flush | 6 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_insert | 5982 |
| Com_insert_select | 671 |
| 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 | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 1151 |
| Com_replace_select | 26 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 0 |
| Com_select | 488782 |
| Com_set_option | 65 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_create | 65 |
| Com_show_databases | 9 |
| Com_show_fields | 65 |
| 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 | 99 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 2 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 1 |
| Com_show_variables | 3 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 66381 |
| Connections | 31394 |
| Created_tmp_disk_tables | 1044 |
| Created_tmp_tables | 12582 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 3608 |
| Handler_read_first | 138421 |
| Handler_read_key | 3317288 |
| Handler_read_next | 2726459 |
| Handler_read_prev | 59530 |
| Handler_read_rnd | 2981478 |
| Handler_read_rnd_next | 6692789 |
| Handler_rollback | 0 |
| Handler_update | 90829 |
| Handler_write | 175615 |
| Key_blocks_used | 1871 |
| Key_read_requests | 14750868 |
| Key_reads | 1468 |
| Key_write_requests | 38524 |
| Key_writes | 33028 |
| Max_used_connections | 9 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 102 |
| Open_files | 164 |
| Open_streams | 0 |
| Opened_tables | 459 |
| Questions | 628247 |
| Qcache_queries_in_cache | 0 |
| Qcache_inserts | 0 |
| Qcache_hits | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_free_memory | 0 |
| Qcache_free_blocks | 0 |
| Qcache_total_blocks | 0 |
| Rpl_status | NULL |
| Select_full_join | 2249 |
| Select_full_range_join | 645 |
| Select_range | 85712 |
| Select_range_check | 0 |
| Select_scan | 90795 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 7 |
| Sort_merge_passes | 0 |
| Sort_range | 47759 |
| Sort_rows | 3049751 |
| Sort_scan | 49978 |
| Table_locks_immediate | 745478 |
| Table_locks_waited | 90 |
| Threads_cached | 8 |
| Threads_created | 10 |
| Threads_connected | 2 |
| Threads_running | 1 |
| Uptime | 29857 |
+--------------------------+------------+

8) Only one other small php download script. Script itself is small but downloads consume massive bandwidth.

9) Average is around 50 Max is around 200

10) Removed

11) KeepAlive On
MaxKeepAliveRequests 100
KeepAliveTimeout 15
MinSpareServers 5
MaxSpareServers 10
StartServers 5
MaxClients 150

12) 2.3.0

[eva2000]

replace or create a /etc/my.cnf file with the below contents and restart mysql


[mysqld]
max_connections = 400
key_buffer = 16M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1024
thread_cache_size = 128
wait_timeout = 14400
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

[Scotteh]

replace or create a /etc/my.cnf file with the below contents and restart mysql

Thanks George you are an amazingly nice guy! The world needs more people like you.