Nifty dedicated server

[fury]

I got one at ServerMatrix.com to run a Kings of Chaos alliance recruiter (and maybe a general KoC site) on. Might grab a vBulletin license for it, if I get enough donations to buy one.

Here are the specs:
Dual Xeon 2.4ghz (HT-enabled)
1gb of RAM
80gb IDE hard drive
Redhat Enterprise Linux 3
MySQL 4.0.18
Apache 2.0.46
PHP 4.3.2

my.cnf
[mysqld]
key_buffer = 96M
myisam_sort_buffer_size = 64M
join_buffer_size = 2M
read_buffer_size = 2M
sort_buffer_size = 4M
table_cache = 1024
thread_cache_size = 64
wait_timeout = 900
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

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
skip-networking

mysqladmin extended-status+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 1 |
| Aborted_connects | 1 |
| Bytes_received | 15096454 |
| Bytes_sent | 1372957071 |
| Com_admin_commands | 144603 |
| Com_alter_table | 3 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 11957 |
| 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_delete_multi | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_insert | 9469 |
| Com_insert_select | 0 |
| 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 | 0 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 66670 |
| Com_set_option | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_create | 0 |
| Com_show_databases | 4 |
| Com_show_fields | 21 |
| 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 | 0 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 3 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 4 |
| Com_show_variables | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 31106 |
| Connections | 11965 |
| Created_tmp_disk_tables | 3117 |
| Created_tmp_tables | 10502 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 0 |
| Handler_read_first | 132 |
| Handler_read_key | 10885964 |
| Handler_read_next | 20465915 |
| Handler_read_prev | 1188 |
| Handler_read_rnd | 12462102 |
| Handler_read_rnd_next | 299881269 |
| Handler_rollback | 0 |
| Handler_update | 1279681 |
| Handler_write | 7559090 |
| Key_blocks_used | 37351 |
| Key_read_requests | 28283728 |
| Key_reads | 2384 |
| Key_write_requests | 2826105 |
| Key_writes | 83201 |
| Max_used_connections | 5 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 13 |
| Open_files | 18 |
| Open_streams | 0 |
| Opened_tables | 36 |
| Questions | 168571 |
| Qcache_queries_in_cache | 65 |
| Qcache_inserts | 66501 |
| Qcache_hits | 37372 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 169 |
| Qcache_free_memory | 33390400 |
| Qcache_free_blocks | 32 |
| Qcache_total_blocks | 169 |
| Rpl_status | NULL |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 125 |
| Select_range_check | 0 |
| Select_scan | 10919 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 0 |
| Sort_merge_passes | 0 |
| Sort_range | 24272 |
| Sort_rows | 9530627 |
| Sort_scan | 11596 |
| Table_locks_immediate | 117161 |
| Table_locks_waited | 614 |
| Threads_cached | 5 |
| Threads_created | 6 |
| Threads_connected | 1 |
| Threads_running | 1 |
| Uptime | 29717 |
+--------------------------+------------+


Load average on the server is fine (0.00) but I wanted to make sure I was setting this server up right so I don't have to do damage control when word about the site starts getting out.

Also, it doesn't have Cpanel on it, so anyone have any resources that are helpful for what I need in general to set this up? I have vsftpd, ssh, apache, php and mysql, and that's about it.

Thanks in advance.

[eva2000]

only problem i see is you're missing max_connections setting in my.cnf

use


[mysqld]
max_connections = 500
key_buffer = 96M
myisam_sort_buffer_size = 64M
join_buffer_size = 2M
read_buffer_size = 2M
sort_buffer_size = 4M
table_cache = 1024
thread_cache_size = 64
wait_timeout = 900
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

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
skip-networking

[fury]

Thanks for the tips eva... I had to take out the skip-innodb because I had to convert one of my tables to it, table locking became a problem.

However, I have another problem. MySQL just seems to suddenly lock up, it will either respond slowly or not at all to queries/local logins from the SSH. Server load shoots up to 375, etc...

It's completely random, no telling when it's going to do it. When it happens I have to killall -9 mysqld because /etc/init.d/mysql stop won't work. This usually results in table corruption :(

+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 140 |
| Aborted_connects | 0 |
| Bytes_received | 73559215 |
| Bytes_sent | 4077859135 |
| Com_admin_commands | 606961 |
| Com_alter_table | 1 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 43268 |
| 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 | 2 |
| Com_delete_multi | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_insert | 45190 |
| Com_insert_select | 0 |
| 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 | 0 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 282762 |
| Com_set_option | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_create | 0 |
| Com_show_databases | 3 |
| Com_show_fields | 25 |
| 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 | 0 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 1 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 3 |
| Com_show_variables | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 149109 |
| Connections | 43272 |
| Created_tmp_disk_tables | 597 |
| Created_tmp_tables | 12446 |
| Created_tmp_files | 459 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 12 |
| Handler_read_first | 34363 |
| Handler_read_key | 239262240 |
| Handler_read_next | 710003397 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 199054922 |
| Handler_read_rnd_next | 178305673 |
| Handler_rollback | 42607 |
| Handler_update | 1976867 |
| Handler_write | 26993164 |
| Key_blocks_used | 73834 |
| Key_read_requests | 267176949 |
| Key_reads | 69378 |
| Key_write_requests | 435134 |
| Key_writes | 300222 |
| Max_used_connections | 69 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 119 |
| Open_files | 110 |
| Open_streams | 0 |
| Opened_tables | 137 |
| Questions | 693426 |
| Qcache_queries_in_cache | 125 |
| Qcache_inserts | 247935 |
| Qcache_hits | 129908 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 34827 |
| Qcache_free_memory | 32491776 |
| Qcache_free_blocks | 84 |
| Qcache_total_blocks | 363 |
| Rpl_status | NULL |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 216 |
| Select_range_check | 0 |
| Select_scan | 35513 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 348 |
| Sort_merge_passes | 0 |
| Sort_range | 83304 |
| Sort_rows | 84988476 |
| Sort_scan | 39253 |
| Table_locks_immediate | 497604 |
| Table_locks_waited | 24489 |
| Threads_cached | 63 |
| Threads_created | 70 |
| Threads_connected | 2 |
| Threads_running | 1 |
| Uptime | 18083 |
+--------------------------+------------+
[mysqld]
max_connections = 500
key_buffer = 96M
myisam_sort_buffer_size = 64M
join_buffer_size = 2M
read_buffer_size = 2M
sort_buffer_size = 4M
table_cache = 1024
thread_cache_size = 64
wait_timeout = 900
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
skip-networking
[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
[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Any ideas? Thanks in advance

[eva2000]

innodb tables use up alot of disk space.. make sure ya have enough space to hold it all usually in mysql data partition

if out of free disk space or having innodb tables hitting up to the linux OS max file size limits like 2 - 4GB you could exhibit slow downs in all server functions/crashes, mail not being sent etc etc

the IDE disk could also be a bottleneck due to the disk activity as well

[fury]

Apparently the disk is actually SCSI when I'm only paying for IDE...

[root@dfield01 kocplanet]# hdparm /dev/hda
/dev/hda: No such device or address

[root@dfield01 kocplanet]# hdparm /dev/sda
/dev/sda:
readonly = 0 (off)
geometry = 9729/255/63, sectors = 156301488, start = 0

http://smilies.xibase.com/shrug.gif

The tables aren't that big, the whole database is 450 megs in an SQL dump.

Is there any easy way to find out if the I/O activity to and from the disk is what is causing the lockups?

[RichM]

I have a server with exactly the same setup. Though are you sure it is SCSI? Mine has one of these:
http://www.seagate.com/cda/products/discsales/marketing/detail/0,1081,587,00.html
And it is mounted as a SCSI drive.

I also have another server that has their newer Super Xeon offering, but it has 2 of these http://www.seagate.com/cda/products/discsales/marketing/detail/0,1081,581,00.html

I am not sure if the top one is that much better, but it is defiantly not a SCSI drive.

Sorry for going off topic a little lol.

[fury]

Hmm, maybe it is a serial ata and that's why it's mounted on sda. I dunno...


+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 88 |
| Aborted_connects | 0 |
| Bytes_received | 200428207 |
| Bytes_sent | 1328533999 |
| Com_admin_commands | 1656618 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 117681 |
| 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 | 3 |
| Com_delete_multi | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_insert | 129260 |
| Com_insert_select | 0 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 0 |
| Com_optimize | 7 |
| Com_purge | 0 |
| Com_rename_table | 0 |
| Com_repair | 10 |
| Com_replace | 0 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 763693 |
| Com_set_option | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_create | 0 |
| Com_show_databases | 3 |
| Com_show_fields | 24 |
| 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 | 0 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 2 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 4 |
| Com_show_variables | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 406946 |
| Connections | 117687 |
| Created_tmp_disk_tables | 361 |
| Created_tmp_tables | 104582 |
| Created_tmp_files | 1282 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 3 |
| Handler_read_first | 328 |
| Handler_read_key | 586341540 |
| Handler_read_next | 1251005620 |
| Handler_read_prev | 2943 |
| Handler_read_rnd | 564438899 |
| Handler_read_rnd_next | 1368319315 |
| Handler_rollback | 0 |
| Handler_update | 102159551 |
| Handler_write | 226389322 |
| Key_blocks_used | 93763 |
| Key_read_requests | 921584583 |
| Key_reads | 361103 |
| Key_write_requests | 1509219 |
| Key_writes | 1251682 |
| Max_used_connections | 79 |
| Not_flushed_key_blocks | 307 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 133 |
| Open_files | 141 |
| Open_streams | 0 |
| Opened_tables | 185 |
| Questions | 1891933 |
| Qcache_queries_in_cache | 125 |
| Qcache_inserts | 666635 |
| Qcache_hits | 356737 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 97034 |
| Qcache_free_memory | 33007400 |
| Qcache_free_blocks | 28 |
| Qcache_total_blocks | 286 |
| Rpl_status | NULL |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 981 |
| Select_range_check | 0 |
| Select_scan | 89591 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 623 |
| Sort_merge_passes | 0 |
| Sort_range | 223115 |
| Sort_rows | 245939211 |
| Sort_scan | 104254 |
| Table_locks_immediate | 1293111 |
| Table_locks_waited | 126369 |
| Threads_cached | 18 |
| Threads_created | 87 |
| Threads_connected | 62 |
| Threads_running | 58 |
| Uptime | 47162 |
+--------------------------+------------+


caught this RIGHT as it started locking up

edit:


+--------------------------+-----------+
| Variable_name | Value |
+--------------------------+-----------+
| Aborted_clients | 0 |
| Aborted_connects | 0 |
| Bytes_received | 8184159 |
| Bytes_sent | 877742458 |
| Com_admin_commands | 68212 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 5029 |
| 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_delete_multi | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_insert | 5550 |
| Com_insert_select | 0 |
| 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 | 0 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 31674 |
| Com_set_option | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_create | 0 |
| Com_show_databases | 1 |
| Com_show_fields | 8 |
| 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 | 0 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 1 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 1 |
| Com_show_variables | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 16126 |
| Connections | 5032 |
| Created_tmp_disk_tables | 26 |
| Created_tmp_tables | 4263 |
| Created_tmp_files | 85 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 0 |
| Handler_read_first | 6 |
| Handler_read_key | 22917530 |
| Handler_read_next | 49300079 |
| Handler_read_prev | 45 |
| Handler_read_rnd | 23139069 |
| Handler_read_rnd_next | 53230292 |
| Handler_rollback | 0 |
| Handler_update | 4101915 |
| Handler_write | 8764370 |
| Key_blocks_used | 37963 |
| Key_read_requests | 35656583 |
| Key_reads | 37492 |
| Key_write_requests | 60513 |
| Key_writes | 50307 |
| Max_used_connections | 74 |
| Not_flushed_key_blocks | 17 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 129 |
| Open_files | 137 |
| Open_streams | 0 |
| Opened_tables | 135 |
| Questions | 78213 |
| Qcache_queries_in_cache | 31 |
| Qcache_inserts | 27660 |
| Qcache_hits | 14867 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 3987 |
| Qcache_free_memory | 33405048 |
| Qcache_free_blocks | 37 |
| Qcache_total_blocks | 106 |
| Rpl_status | NULL |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 15 |
| Select_range_check | 0 |
| Select_scan | 3464 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 49 |
| Sort_merge_passes | 0 |
| Sort_range | 8970 |
| Sort_rows | 9458385 |
| Sort_scan | 4258 |
| Table_locks_immediate | 51867 |
| Table_locks_waited | 6493 |
| Threads_cached | 0 |
| Threads_created | 75 |
| Threads_connected | 75 |
| Threads_running | 69 |
| Uptime | 1577 |
+--------------------------+-----------+


and this one as it locked up again shortly after I had restarted it after the one above.

[RichM]

This may be an I/O wait problem as they are known to cause problems on Dual Xeons that have older 2.4.x kernels.
You may want to open as ticket with SM, i had this same sort of problem and they upgraded the kernel which fixed it.

[Kier]

Hate to be the bearer of bad news, but I recently got a P4 server from ServerMatrix with a pair of IDE drives and I was absolutely crippled with iowait.

The server was completely unusable so I ditched that machine and went with an identical server from ServerMatrix but this time with a SCSI disk and haven't looked back. The new machine has no problem running some of the very large sites I host.

[RichM]

Well i used to have a Super Server 2.8Ghz with them that had a single IDE drive, i did not have any problems with that machine apart from me overloading the CPU, hence the swap for the Dual Xeons.
May be OS dependant?

[decostop]

there is a known hyperthreading (at least on a dual xeon 2.8) / kernel issue that causes iowait to go crazy. There's a few threads at servermatrix forums about it. I needed both an updated kernel and to run some vm commands...I'll dig them up.

[decostop]

bug report: http://bugzilla.redhat.com/bugzilla/show_bug.cgi?id=114553

[fury]

hmm... I updated that inactive_clean_percent file to 100 and it ran OK for a while, but now it's doing it again... Guess I need a kernel upgrade then.

+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 229 |
| Aborted_connects | 0 |
| Bytes_received | 448272793 |
| Bytes_sent | 4019616133 |
| Com_admin_commands | 2846011 |
| Com_alter_table | 2 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 217451 |
| 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 | 3124 |
| Com_delete_multi | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 1 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_insert | 237411 |
| Com_insert_select | 957 |
| 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 | 2399 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 1390509 |
| Com_set_option | 0 |
| Com_show_binlog_events | 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_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_processlist | 0 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 1 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 2 |
| Com_show_variables | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 821087 |
| Connections | 217462 |
| Created_tmp_disk_tables | 342 |
| Created_tmp_tables | 181246 |
| Created_tmp_files | 152 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 5222 |
| Handler_read_first | 57792 |
| Handler_read_key | 1050119833 |
| Handler_read_next | 1002420378 |
| Handler_read_prev | 41404 |
| Handler_read_rnd | 561388356 |
| Handler_read_rnd_next | 2418946463 |
| Handler_rollback | 0 |
| Handler_update | 197349142 |
| Handler_write | 386823952 |
| Key_blocks_used | 204996 |
| Key_read_requests | 1655821012 |
| Key_reads | 144682 |
| Key_write_requests | 7858380 |
| Key_writes | 2227135 |
| Max_used_connections | 73 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 375 |
| Open_files | 451 |
| Open_streams | 0 |
| Opened_tables | 438 |
| Questions | 3611858 |
| Qcache_queries_in_cache | 3612 |
| Qcache_inserts | 1225904 |
| Qcache_hits | 721611 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 164605 |
| Qcache_free_memory | 25076312 |
| Qcache_free_blocks | 1580 |
| Qcache_total_blocks | 9026 |
| Rpl_status | NULL |
| Select_full_join | 63 |
| Select_full_range_join | 0 |
| Select_range | 15985 |
| Select_range_check | 0 |
| Select_scan | 160746 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 380 |
| Sort_merge_passes | 0 |
| Sort_range | 381825 |
| Sort_rows | 414052794 |
| Sort_scan | 183965 |
| Table_locks_immediate | 2633257 |
| Table_locks_waited | 214064 |
| Threads_cached | 7 |
| Threads_created | 1522 |
| Threads_connected | 2 |
| Threads_running | 1 |
| Uptime | 66262 |
+--------------------------+------------+

[fury]

Well, I got denied the kernel upgrade the day after that post.

So I rewrote the script that seemed to be doing the most damage and things were OK until today. It started happening again. I'm pretty sure it's not PHP, I check the memory usage at the end of every script and it's always 450-600kb. So I think MySQL needs a re-tweaking of some sort

When I first installed vBulletin onto it with the my.cnf changes I got in this thread, the load times were crap - 15-20 seconds before anything would start showing up.

The server admin told me to use my-huge.cnf to solve the extremely slow load times I was having in vBulletin and so I did... But it's using gobs of memory...This is the LOWEST i've seen it in a while. Usually it's at 20% memory.

PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND
3859 mysql 16 0 115M 115M 1292 S 2.9 11.4 1:51 1 mysqld
3867 mysql 16 0 115M 115M 1292 S 2.5 11.4 1:33 2 mysqld
3850 mysql 16 0 115M 115M 1292 S 2.1 11.4 2:06 2 mysqld
3861 mysql 16 0 115M 115M 1292 S 2.1 11.4 1:52 1 mysqld
3839 mysql 16 0 115M 115M 1292 S 1.9 11.4 2:57 1 mysqld
3750 mysql 16 0 115M 115M 1292 S 1.7 11.4 3:05 1 mysqld

[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
skip-networking
key_buffer = 384M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
myisam_sort_buffer_size = 64M
thread_cache = 8
query_cache_size = 32M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8 Does number of CPU's mean number of physical CPU's (2) or number of virtual CPU's? (4, since they are Xeons)


Here is extended-status

+--------------------------+-----------+
| Variable_name | Value |
+--------------------------+-----------+
| Aborted_clients | 0 |
| Aborted_connects | 0 |
| Bytes_received | 72409395 |
| Bytes_sent | 786296577 |
| Com_admin_commands | 123122 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 9150 |
| 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 | 262 |
| Com_delete_multi | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_insert | 8533 |
| Com_insert_select | 34 |
| 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 | 138 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 69066 |
| Com_set_option | 0 |
| Com_show_binlog_events | 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_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_processlist | 0 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 2 |
| 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 | 41120 |
| Connections | 9153 |
| Created_tmp_disk_tables | 63 |
| Created_tmp_tables | 19137 |
| Created_tmp_files | 479 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 549 |
| Handler_read_first | 3177 |
| Handler_read_key | 104505633 |
| Handler_read_next | 102663021 |
| Handler_read_prev | 4451 |
| Handler_read_rnd | 31870561 |
| Handler_read_rnd_next | 115495885 |
| Handler_rollback | 0 |
| Handler_update | 6904491 |
| Handler_write | 12775220 |
| Key_blocks_used | 90989 |
| Key_read_requests | 201058923 |
| Key_reads | 90240 |
| Key_write_requests | 153117 |
| Key_writes | 112437 |
| Max_used_connections | 20 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 137 |
| Open_files | 206 |
| Open_streams | 0 |
| Opened_tables | 143 |
| Questions | 166768 |
| Qcache_queries_in_cache | 631 |
| Qcache_inserts | 57558 |
| Qcache_hits | 29312 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 11508 |
| Qcache_free_memory | 28688616 |
| Qcache_free_blocks | 337 |
| Qcache_total_blocks | 1721 |
| Rpl_status | NULL |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 6473 |
| Select_range_check | 0 |
| Select_scan | 11495 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 1 |
| Sort_merge_passes | 0 |
| Sort_range | 12448 |
| Sort_rows | 3867870 |
| Sort_scan | 13163 |
| Table_locks_immediate | 137935 |
| Table_locks_waited | 10484 |
| Threads_cached | 7 |
| Threads_created | 75 |
| Threads_connected | 1 |
| Threads_running | 1 |
| Uptime | 4909 |
+--------------------------+-----------+


Am I fighting a losing battle or is there some way I can find out how much memory exactly each query is using so I can start nailing that down?

[eva2000]

the huge my.cnf isn't really ideal as it allocates too much memory to mysql causing swapping to disk and slow down of the server as you have experienced

since it's been months, can you repost all info asked at http://www.vbulletin.com/forum/showthread.php?t=70117 so to see the current server trends etc

[fury]

1. dedicated
2. dual xeon 2.4ghz
how much memory installed: 1024mb ram
hard drive type/configuration: 80GB EIDE non raid
linux distributor or windows version: redhat enterprise linux 3
apache/IIS version: apache 2.0.46
PHP version: php 4.3.2
MySQL version: mysql 4.0.18


3. if you use mysql 4.x instead of mysql 3.23.x, do you have any innodb type databases/tables on your server ? no
4. if possible how mysql was compiled/installed - RPM
5. your top stats -
00:16:22 up 9 days, 10:14, 4 users, load average: 0.92, 1.09, 1.14
128 processes: 127 sleeping, 1 running, 0 zombie, 0 stopped
CPU states: cpu user nice system irq softirq iowait idle
total 15.6% 0.0% 7.3% 0.0% 0.0% 4.7% 72.1%
cpu00 10.5% 0.0% 6.3% 0.0% 0.0% 6.1% 76.8%
cpu01 10.3% 0.0% 5.5% 0.0% 0.0% 3.5% 80.4%
cpu02 19.3% 0.0% 9.3% 0.0% 0.0% 5.7% 65.4%
cpu03 22.3% 0.0% 8.1% 0.0% 0.0% 3.5% 65.8%
Mem: 1028480k av, 1010876k used, 17604k free, 0k shrd, 7844k buff
815728k actv, 94680k in_d, 20196k in_c
Swap: 1052248k av, 91836k used, 960412k free 663140k cached
PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND
6811 mysql 16 0 232M 199M 1316 S 3.9 19.8 1:46 3 mysqld
6933 mysql 16 0 232M 199M 1316 S 3.7 19.8 0:40 2 mysqld
6929 mysql 16 0 232M 199M 1316 S 3.6 19.8 0:59 1 mysqld
6936 mysql 16 0 232M 199M 1316 S 3.2 19.8 0:37 0 mysqld
6941 mysql 16 0 232M 199M 1316 S 2.2 19.8 0:17 3 mysqld
6945 mysql 16 0 232M 199M 1316 S 2.1 19.8 0:17 2 mysqld
6773 mysql 16 0 232M 199M 1316 S 1.6 19.8 5:47 0 mysqld
6934 mysql 16 0 232M 199M 1316 S 0.6 19.8 0:42 1 mysqld
6922 apache 15 0 10048 9.8M 4860 S 0.2 0.9 0:01 1 httpd
3502 mysql 15 0 232M 199M 1316 S 0.1 19.8 0:11 1 mysqld
6917 apache 15 0 10708 10M 4868 S 0.1 1.0 0:03 2 httpd
6927 apache 15 0 10556 10M 4864 S 0.1 1.0 0:02 0 httpd
6947 apache 15 0 10012 9.8M 4984 S 0.1 0.9 0:00 1 httpd
6. seen in previous post
7. seen in previous post
8. is your vB the only thing on the server? or other scripts & sites which utilise php and mysql? there are a few other scripts that use php and mysql but the main problem only started appearing after I installed vB.. and as I said in the previous post I rewrote the other scripts and it still happens

9. how many average and max concurrent users on your vB forum ? and what your cookie timeout is ? average 30 max 101 - cookie timeout 900 seconds

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.kocplanet.com/fury/phpinfo.php

11. KeepAlive Off
MaxKeepAliveRequests 100
KeepAliveTimeout 15
MinSpareServers 5
MaxSpareServers 20
StartServers 8
MaxClients 150

12. 3.0.1

[eva2000]

seems the huge my.cnf is the culprit

upgrade PHP to 4.3.6 and try this new my.cnf


[mysqld]
max_connections = 500
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 = 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