Pentium IV 1.7GHZ 512MB RAM 40GB HD

[Albasoul]

I need help configuring mysql for best performance. I just bought this new server from RS. I haven't added any domains yet, but I am planning on hosting my forum on this box.

512 MB of RAM will be shared by httpd and mysqld, and I am using PHP 4.2.2, mysql 3.23.52. my.cnf is the default.

Eva, would you give me your "prescription" once again pls.

Thanks in advance

[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 zip file's 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 ?

[ccd1]

I see you're still copying and pasting the same instructions eva2000, you have references to vb 2.2.6

[eva2000]

Originally posted by baragon0
I see you're still copying and pasting the same instructions eva2000, you have references to vb 2.2.6 so ? :)

[ccd1]

Originally posted by eva2000
so ? :)

Nothing, it's just that I went crazy looking for an old 2.2.6 extras folder. :p

[Albasoul]

PHP 4.2.2
Mysql 3.23.52 (rpm upgrade)

(nothing running in this box as of now)
11:56am up 12:02, 1 user, load average: 0.00, 0.00, 0.00
68 processes: 67 sleeping, 1 running, 0 zombie, 0 stopped
CPU states: 0.1% user, 0.1% system, 0.0% nice, 99.6% idle
Mem: 512920K av, 329180K used, 183740K free, 1344K shrd, 71940K buff
Swap: 1028152K av, 0K used, 1028152K free 141576K cached


[root@ensim admin]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[root@ensim admin]#


+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| Aborted_clients | 0 |
| Aborted_connects | 3 |
| Bytes_received | 182 |
| Bytes_sent | 2920 |
| Com_admin_commands | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 0 |
| 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 | 0 |
| Com_delete | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_insert | 0 |
| Com_insert_select | 0 |
| Com_kill | 0 |
| Com_load | 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 | 0 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 0 |
| Com_select | 0 |
| Com_set_option | 0 |
| Com_show_binlogs | 0 |
| Com_show_create | 0 |
| Com_show_databases | 0 |
| Com_show_fields | 0 |
| Com_show_grants | 0 |
| Com_show_keys | 0 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_open_tables | 0 |
| Com_show_processlist | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 2 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 0 |
| Com_show_variables | 1 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 0 |
| Connections | 8 |
| 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 | 1 |
| Handler_read_key | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 11 |
| 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 | 0 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 0 |
| Open_files | 0 |
| Open_streams | 0 |
| Opened_tables | 6 |
| Questions | 6 |
| 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 | 5 |
| Table_locks_waited | 0 |
| Threads_cached | 0 |
| Threads_created | 7 |
| Threads_connected | 1 |
| Threads_running | 1 |
| Uptime | 33078 |
+--------------------------+-------+


VB will not be the only domain in that box. There will be 5 domains in total. A postnuke site, a vb site and three other sites with less traffic but all dynamic php/mysql.

Php specs you can look them up here (http://207.44.140.60/info.php)

Timeout 300
KeepAlive On
MaxKeepAliveRequests 100
KeepAliveTimeout 15
MinSpareServers 5
MaxSpareServers 10
StartServers 5
MaxClients 150


I am running VBulletin 2.27 with a lot of hacks installed.

[eva2000]

change /etc/my.cnf to the below and restart mysql


[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
set-variable = max_connections=550
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

[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
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

[Albasoul]

Eva, I have been using the same config you gave me above, but my load is going as high as 125 during peak hours. Mysqld stops responding and I have to start it manually. When I run ps -aux I can see a great number of mysqld instances running. Your help would be appreciated. Here are some variables:


Pentium IV 1.7GHZ 512RAM 40GB HD
PHP 4.2.2 mysql3.23.52 phpinfo() (http://www.albasoul.net/info.php)

+--------------------------+-----------+
| Variable_name | Value |
+--------------------------+-----------+
| Aborted_clients | 0 |
| Aborted_connects | 5 |
| Bytes_received | 21383603 |
| Bytes_sent | 397591315 |
| Com_admin_commands | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 10735 |
| 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 | 0 |
| Com_delete | 1092 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_insert | 2507 |
| Com_insert_select | 20 |
| Com_kill | 0 |
| Com_load | 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 | 39 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 0 |
| Com_select | 91690 |
| Com_set_option | 0 |
| Com_show_binlogs | 0 |
| Com_show_create | 0 |
| Com_show_databases | 0 |
| Com_show_fields | 0 |
| Com_show_grants | 0 |
| Com_show_keys | 0 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_open_tables | 0 |
| Com_show_processlist | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 1 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 0 |
| Com_show_variables | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 17324 |
| Connections | 9073 |
| Created_tmp_disk_tables | 1734 |
| Created_tmp_tables | 3129 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_delete | 755 |
| Handler_read_first | 1330 |
| Handler_read_key | 2968159 |
| Handler_read_next | 2355786 |
| Handler_read_prev | 36 |
| Handler_read_rnd | 2280971 |
| Handler_read_rnd_next | 56652386 |
| Handler_update | 1458542 |
| Handler_write | 915499 |
| Key_blocks_used | 4233 |
| Key_read_requests | 2712653 |
| Key_reads | 4184 |
| Key_write_requests | 8013 |
| Key_writes | 7602 |
| Max_used_connections | 15 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 127 |
| Open_files | 209 |
| Open_streams | 0 |
| Opened_tables | 133 |
| Questions | 132474 |
| Select_full_join | 3571 |
| Select_full_range_join | 0 |
| Select_range | 8909 |
| Select_range_check | 0 |
| Select_scan | 51880 |
| Slave_running | OFF |
| Slave_open_temp_tables | 0 |
| Slow_launch_threads | 0 |
| Slow_queries | 0 |
| Sort_merge_passes | 0 |
| Sort_range | 9291 |
| Sort_rows | 2337478 |
| Sort_scan | 38122 |
| Table_locks_immediate | 126200 |
| Table_locks_waited | 72 |
| Threads_cached | 15 |
| Threads_created | 16 |
| Threads_connected | 1 |
| Threads_running | 1 |
| Uptime | 4837 |
+--------------------------+-----------+


2:47pm up 1 day, 18:38, 1 user, load average: 2.50, 2.46, 2.31
117 processes: 114 sleeping, 3 running, 0 zombie, 0 stopped
CPU states: 45.5% user, 26.1% system, 0.0% nice, 28.3% idle
Mem: 512920K av, 491368K used, 21552K free, 2060K shrd, 123224K buff
Swap: 1028152K av, 46756K used, 981396K free 160492K cached


Timeout 180
KeepAlive Off
MaxKeepAliveRequests 50
KeepAliveTimeout 30
MinSpareServers 5
MaxSpareServers 20
StartServers 5
MaxClients 150
MaxRequestsPerChild 1000

[Albasoul]

Eva, can you give me some new advise, I am having a hell of a time getting mysql not skyrocket the load of my server.

Thanks in advance, all the details are in the message above

[eva2000]

Originally posted by Albasoul
Eva, can you give me some new advise, I am having a hell of a time getting mysql not skyrocket the load of my server.

Thanks in advance, all the details are in the message above well P4 1.7 williamette cpus weren't meant for web serving and are poorer performers than P3 1.13, 1.26 and 1.4ghz tualatins with 512k L2 caches

but since you started a new thread at http://www.vbulletin.com/forum/showthread.php?s=&threadid=56607 i'll continue from there