Tuning MySQL, but without success ...

[Odysseus]

OK, I'm giving up. ^^

I've been trying to optimize my MySQL settings for weeks now, but I cannot find a configuration, which works properly or even SEEMS to be somewhat better then others ... maybe someone here can help me, because the performance is simply too little for my machine. :)

I'm using vBulletin 2.2.8 on this Server:

1x Pentium-4 2,4 GHz
512 MB Ram (8 or 16 MB used for onboard graphics, I think)
7200 upm HDD (IBM IC35, IDE)
SuSE Linux 8.1
Apache 1.3.26
PHP 4.2.2 (with PHPA installed and running properly)
MySQL 3.23.52
Confixx 2.0 Professional


Till the end of April, the server took >200 users without any problem, running at at load of 3.00 in really [b]bad[/i] times. After restarting my website (which also uses PHP/MySQL, but only causes ~400 MB Traffic a day, compared to the ~1.6 GB traffic caused by the forums should be not too bad), the server cannot even handle 150 users without getting into serious problems after some hours.
The SIZE of mysqld shown by the "top" shell-command is ~8M right after starting mysqld, but very soon, it becomes >60M and the server has to swap, thus boosting up the serverload to 8.00, 14.00 or sometimes even 20.00, which makes browsing the vB nearly impossible.

I also installed a cron yesterday which performs a "flush-tables" every hour ... didn't help. :(


Maybe you know how I could improve this my.cnf: :)

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
set-variable = max_connections=300
set-variable = key_buffer=64M
set-variable = myisam_sort_buffer_size=64M
set-variable = join_buffer=1M
set-variable = record_buffer=1M
set-variable = sort_buffer=4M
set-variable = thread_cache_size=128
set-variable = table_cache=256
set-variable = wait_timeout=2
set-variable = connect_timeout=10
set-variable = max_allowed_packet=16M
set-variable = max_connect_errors=10

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
open_files_limir=8192

[mysqldump]
quick
set-variable = max_allowed_packet=16M

[myisamchk]
set-variable = key_buffer=64M
set-variable = sort_buffer=32M
set-variable = read_buffer=32M
set-variable = write_buffer=16M

[isamchk]
set-variable = key_buffer=20M
set-variable = sort_buffer=20M
set-variable = read_buffer=2M
set-variable = write_buffer=2M

[Raz Meister]

Upgrade to MySQL 4 to take advantage of the new caching feature. You should be able to handle much more traffic with it.

[eva2000]

OK, I'm giving up. ^^

I've been trying to optimize my MySQL settings for weeks now, but I cannot find a configuration, which works properly or even SEEMS to be somewhat better then others ... maybe someone here can help me, because the performance is simply too little for my machine. :)

I'm using vBulletin 2.2.8 on this Server:

1x Pentium-4 2,4 GHz
512 MB Ram (8 or 16 MB used for onboard graphics, I think)
7200 upm HDD (IBM IC35, IDE)
SuSE Linux 8.1
Apache 1.3.26
PHP 4.2.2 (with PHPA installed and running properly)
MySQL 3.23.52
Confixx 2.0 Professional


Till the end of April, the server took >200 users without any problem, running at at load of 3.00 in really [b]bad[/i] times. After restarting my website (which also uses PHP/MySQL, but only causes ~400 MB Traffic a day, compared to the ~1.6 GB traffic caused by the forums should be not too bad), the server cannot even handle 150 users without getting into serious problems after some hours.
The SIZE of mysqld shown by the "top" shell-command is ~8M right after starting mysqld, but very soon, it becomes >60M and the server has to swap, thus boosting up the serverload to 8.00, 14.00 or sometimes even 20.00, which makes browsing the vB nearly impossible.

I also installed a cron yesterday which performs a "flush-tables" every hour ... didn't help. :(


Maybe you know how I could improve this my.cnf: :)

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
set-variable = max_connections=300
set-variable = key_buffer=64M
set-variable = myisam_sort_buffer_size=64M
set-variable = join_buffer=1M
set-variable = record_buffer=1M
set-variable = sort_buffer=4M
set-variable = thread_cache_size=128
set-variable = table_cache=256
set-variable = wait_timeout=2
set-variable = connect_timeout=10
set-variable = max_allowed_packet=16M
set-variable = max_connect_errors=10

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
open_files_limir=8192

[mysqldump]
quick
set-variable = max_allowed_packet=16M

[myisamchk]
set-variable = key_buffer=64M
set-variable = sort_buffer=32M
set-variable = read_buffer=32M
set-variable = write_buffer=16M

[isamchk]
set-variable = key_buffer=20M
set-variable = sort_buffer=20M
set-variable = read_buffer=2M
set-variable = write_buffer=2M
need more info to help ya :)

please provide the following

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 ?

[Odysseus]

@ Raz Meister: The problem ist, that Confixx 2.0 Pro won't function properly on MySQL 4 - as far as I know - and I'm not experienced enought yet to remove it and replace it with e.g. webmin ...

@ eva2000: Thanks for replying.
It took me quitre a while to collect all the information you requested. :)
Some of the questions were already answered by my introduction post, but I will repeat this in order to make things more clear.

1. is this on dedicated or shared virual server
A dedicated P4 server located at 1&1 Karlsruhe, Germany. (I'm from Germany, too, btw.)


2. your server specs.
CPU: P4 2400 MHz
RAM: 512 MB (8 or 16 MB gone away for onboard graphics, I think)
HDD: IDE 7200 upm 40 GB IBM IC35
OS: SuSE Linux 8.1, current YaST Updates installed
Apache 1.3.26
MySQL 3.23.52
PHP 4.2.2 with PHPA


3. if you use mysql 4.x instead of mysql 3.23.x, do you have any innodb type databases/tables on your server ?
I utilize MySQL 3.23.52 (reason mentioned above in when addressing Ras Meister) and use MyISAM only.


4. if possible how mysql was compiled/installed
Sorry about that ... the server came "as it is" from 1&1. It's not my own hardware, it's rented.


5. your top stats
This happens almost every day, sometimes even more than just one time, and sometimes it lasts several minutes. Not very amusing ...
2:43pm up 11 days, 20:35, 1 user, load average: 17.97, 9.22, 4.07
233 processes: 232 sleeping, 1 running, 0 zombie, 0 stopped
CPU states: 3.8% user, 1.0% system, 0.0% nice, 2.8% idle
Mem: 507560K av, 503100K used, 4460K free, 0K shrd, 3344K buff
Swap: 787176K av, 31968K used, 755208K free 306180K cached

snipped the unneeded stuff :)

[Odysseus]

And now part 2, because the posting had too many letters :)

6. your mysql configuration variables located at /etc/my.cnf or
------------------------------------------
| back_log | 50
| basedir | /usr/
| binlog_cache_size | 32768
| character_set | latin1
| character_sets | latin1 big5 (.....)
| concurrent_insert | ON
| connect_timeout | 10
| 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_innodb | NO
| have_isam | YES
| have_raid | NO
| have_openssl | NO
| init_file |
| interactive_timeout | 28800
| join_buffer_size | 1044480
| key_buffer_size | 67104768
| 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
| log_long_queries | OFF
| long_query_time | 10
| low_priority_updates | OFF
| lower_case_table_names | 0
| max_allowed_packet | 16776192
| max_binlog_cache_size | 4294967295
| max_binlog_size | 1073741824
| max_connections | 300
| 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_max_extra_sort_file_size | 256
| myisam_max_sort_file_size | 2047
| myisam_recover_options | 0
| myisam_sort_buffer_size | 67108864
| 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/mysqld.pid
| port | 3306
| protocol_version | 10
| record_buffer | 1044480
| record_rnd_buffer | 1044480
| query_buffer_size | 0
| safe_show_database | OFF
| server_id | 0
| slave_net_timeout | 3600
| skip_locking | ON
| skip_networking | OFF
| skip_show_database | OFF
| slow_launch_time | 2
| socket | /var/lib/mysql/mysql.sock
| sort_buffer | 4194296
| sql_mode | 0
| table_cache | 256
| table_type | MYISAM
| thread_cache_size | 128
| thread_stack | 65536
| transaction_isolation | READ-COMMITTED
| timezone | CEST
| tmp_table_size | 33554432
| tmpdir | /tmp/
| version | 3.23.52
| wait_timeout | 2
+---------------------------------+----------


7. your mysql extended-status output
+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 3707 |
| Aborted_connects | 0 |
| Bytes_received | 538191974 |
| Bytes_sent | 2453094085 |
| Com_admin_commands | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 345301 |
| 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 | 1 |
| Com_delete | 11204 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_flush | 42 |
| Com_grant | 0 |
| Com_insert | 13908 |
| Com_insert_select | 779 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 4 |
| Com_optimize | 0 |
| Com_purge | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 3881 |
| Com_replace_select | 5 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 0 |
| Com_select | 3360700 |
| Com_set_option | 141 |
| Com_show_binlogs | 0 |
| Com_show_create | 141 |
| Com_show_databases | 4 |
| Com_show_fields | 151 |
| Com_show_grants | 2 |
| Com_show_keys | 2 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_open_tables | 0 |
| Com_show_processlist | 577 |
| Com_show_slave_status | 0 |
| Com_show_status | 578 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 36 |
| Com_show_variables | 24 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 345925 |
| Connections | 348384 |
| Created_tmp_disk_tables | 5605 |
| Created_tmp_tables | 80511 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 43 |
| Handler_delete | 26666 |
| Handler_read_first | 171336 |
| Handler_read_key | 25769170 |
| Handler_read_next | 439172499 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 32753314 |
| Handler_read_rnd_next | 2425340420 |
| Handler_update | 513070 |
| Handler_write | 6894967 |
| Key_blocks_used | 62341 |
| Key_read_requests | 127269845 |
| Key_reads | 188502 |
| Key_write_requests | 77917 |
| Key_writes | 25051 |
| Max_used_connections | 293 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 256 |
| Open_files | 302 |
| Open_streams | 0 |
| Opened_tables | 25078 |
| Questions | 4428054 |
| Select_full_join | 3085 |
| Select_full_range_join | 0 |
| Select_range | 367550 |
| Select_range_check | 0 |
| Select_scan | 379468 |
| Slave_running | OFF |
| Slave_open_temp_tables | 0 |
| Slow_launch_threads | 0 |
| Slow_queries | 1422 |
| Sort_merge_passes | 0 |
| Sort_range | 358811 |
| Sort_rows | 34165600 |
| Sort_scan | 255421 |
| Table_locks_immediate | 4259109 |
| Table_locks_waited | 21333 |
| Threads_cached | 96 |
| Threads_created | 400 |
| Threads_connected | 32 |
| Threads_running | 31 |
| Uptime | 151059 |
+--------------------------+------------+


8. oh and is your vB the only thing on the server? or other scripts? sites?
The frontsite of my webproject. It also uses PHP / MySQL, but 3 Querys are the maximum on any page there. I User norman mysql_connect(), no pconnect, I always use mysql_free_result(), unset() onto virtually every variable which may include Text-Data


9. how many average and max concurrent users on your vB forum ?
Usually, 150-200 users are online in high traffic times. But in the last few days the wheather has been very nice in Germany, so only 70-130 people are online. The serverload becomes critical with 170 users online, sometimes even with only 130, but only when the mysql-Server has not been resetted for a longer time (1-2 days).
Some weeks ago, 220 Users were absolutely no problem for the server ...

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://www.warhammer-forum.de/phpinfo.php
As you can see, Apache has been compiled with really many options ... as already mentioned, this is a rented server and this is the standard configuration of the system.


11. httpd.conf variables:
KeepAlive = Off
MaxKeepAliveRequests = 100
KeepAliveTimeout = 15
MinSpareServers = 5
MaxSpareServers = 10
StartServers = 5
MaxClients = 250


12. vBulletin
Version 2.2.8, I don't want to upgrade till vB3. :)

[Odysseus]

Any suggestions? :)

[eva2000]

okay a few suggestions

1. upgrade mysql to 4.0.12 then
2. upgrade PHP to 4.3.1
3. reduce httpd.conf's maxclients from 250 to 150
4. report back here with your mysqlinfo.php url after the first 3 are done for a recommened optimised /etc/my.cnf file :)

[Odysseus]

okay a few suggestions

1. upgrade mysql to 4.0.12 then
2. upgrade PHP to 4.3.1

Hm ... I'm new to linux and webservers. At the moment, compiling PHP exceeds my abilities. ;)
Also, I'm using Confixx 2.0 Pro, which doesn't support MySQL 4.
(This Confixx-trash costs 1.100 Euro per license ... you'd expect such an expensive piece of software to work properly, but it doesn't :mad:)



After all, I found out why the server was so slow:
The "update thread-view-count" function caused really many deadlocks. (I guess this is one disadvantage if you use a single IDE hdd...)
I installed a hack wich "caches" the threadviews in a HEAP-table. Now my load doesn't exceed 2.50 even in high traffic times anymore. *happy*


But still I'd like to say thanks for your help. :)

[Sabrina]

Hm ... I'm new to linux and webservers. At the moment, compiling PHP exceeds my abilities. ;)
Also, I'm using Confixx 2.0 Pro, which doesn't support MySQL 4.
(This Confixx-trash costs 1.100 Euro per license ... you'd expect such an expensive piece of software to work properly, but it doesn't :mad:)



After all, I found out why the server was so slow:
The "update thread-view-count" function caused really many deadlocks. (I guess this is one disadvantage if you use a single IDE hdd...)
I installed a hack wich "caches" the threadviews in a HEAP-table. Now my load doesn't exceed 2.50 even in high traffic times anymore. *happy*



Im not sure if i should be posting under this section because my vbulletin is 2.3, but im having the same problem. you said you installed a hack that lowers the server load? Can you please tell me how to do this or where to get this hack?

Thank You

[Odysseus]

Im not sure if i should be posting under this section because my vbulletin is 2.3, but im having the same problem. you said you installed a hack that lowers the server load? Can you please tell me how to do this or where to get this hack?

Thank You

Sabrina,
the hack can be found here:
http://www.vbulletin.org/forum/showthread.php?s=&threadid=40137

It is quite simple to install, but you might have to modify the update interval setting, which ist set to 200 by default.
(I even chose 1000 because of the size of my forums).

I also found out, that my server load can be reduced dramatically by setting the cookie timeout up to 1800 seconds (default is 900 seconds). This can be done in the optione menu in the admin panel, without hacking.

I don't know why this works so well, but it simply does. :)

[Sabrina]

Sabrina,
the hack can be found here:
http://www.vbulletin.org/forum/showthread.php?s=&threadid=40137

It is quite simple to install, but you might have to modify the update interval setting, which ist set to 200 by default.
(I even chose 1000 because of the size of my forums).

I also found out, that my server load can be reduced dramatically by setting the cookie timeout up to 1800 seconds (default is 900 seconds). This can be done in the optione menu in the admin panel, without hacking.

I don't know why this works so well, but it simply does. :)

you put 1000 for your NIX?

thanks by the way :)

[Odysseus]

you put 1000 for your NIX?

No. The hack uses a random number to determine wheter the cached views are to be written down to the thread table or not.
This number is 200 by default, but i changed it to 1000.
It's not a vB option, it's hard coded in the hack.

thanks by the way :)

You're welcome. :)