Hal9000
Wed 2nd Mar '05, 6:36pm
Hello
I haven't made many changes since I moved to a dedicated server. The server load is usually above 0.70 - 1.00 with 120-200 users online. The big problem is that the mysql processes in specific times (00:00 to 00:30) keeps getting out of control and reaches 350 and more. Maybe it's a task programmed for that time which I haven't found or maybe it's a matter of server settings. In any case I would be grateful if George can have a look at it and make any suqqestions.
http://67.19.56.125/stat.php
(http://67.19.56.125/stat.php)
1. Dedicated
2. cpu speed/type single or dual cpus): Pentium 4 2.8Ghz
how much memory installed: 1 Gb Ram
hard drive type/configuration: 80GB EIDE non raid
linux distributor or windows version: RedHat Enteprise 3 i686
apache/IIS version: apache 1.3.33
PHP version: php 4.3.10
MySQL version: mysql MySQL 4.0.22
3. Nope
4. Made by host -The Planet-
5. Top stats
274 processes: 272 sleeping, 1 running, 1 zombie, 0 stopped
total 1.4% 0.0% 1.9% 0.0% 0.0% 0.0% 96.6%
cpu00 0.9% 0.0% 3.8% 0.0% 0.0% 0.0% 95.1%
cpu01 1.9% 0.0% 0.0% 0.0% 0.0% 0.0% 98.0%
Mem: 1028020k av, 1009836k used, 18184k free, 0k shrd, 35244k buff
765076k actv, 144020k in_d, 13232k in_c
Swap: 2040212k av, 175396k used, 1864816k free 579148k cached
PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND
11883 nobody 20 0 1176 1176 772 R 1.4 0.1 0:00 0 top
Http processes currently running = 154
Mysql processes currently running = 44
Netstat information summary
6. my.cnf
# Example MySQL config file for large systems.
#
# This is for a large system with memory = 512M where the system runs mainly
# MySQL.
#
# You can copy this file to
# /etc/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is /var/lib/mysql) or
# ~/.my.cnf to set user-specific options.
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.
# The following options will be passed to all MySQL clients
[client]
#password = your_password
port = 3306
socket = /var/lib/mysql/mysql.sock
# Here follows entries for some specific programs
# The MySQL server
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 256M
max_allowed_packet = 1M
table_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
myisam_sort_buffer_size = 64M
thread_cache = 8
query_cache_size= 16M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
#
#skip-networking
# Replication Master Server (default)
# binary logging is required for replication
# log-bin
# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
# server-id = 1
# Replication Slave (comment out master section to use this)
#
# To configure this host as a replication slave, you can choose between
# two methods :
#
# 1) Use the CHANGE MASTER TO command (fully described in our manual) -
# the syntax is:
#
#
# where you replace <host>, <user>, <password> by quoted strings and
# <port> by the master's port number (3306 by default).
#
# Example:
#
# CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
# MASTER_USER='joe', MASTER_PASSWORD='secret';
#
# OR
#
# 2) Set the variables below. However, in case you choose this method, then
# start replication for the first time (even unsuccessfully, for example
# if you mistyped the password in master-password and the slave fails to
# connect), the slave will create a master.info file, and any later
# change in this file to the variables' values below will be ignored and
# overridden by the content of the master.info file, unless you shutdown
# the slave server, delete master.info and restart the slaver server.
# For that reason, you may want to leave the lines below untouched
# (commented) and instead use CHANGE MASTER TO (see above)
#
# required unique id between 2 and 2^32 - 1
# (and different from the master)
# defaults to 2 if master-host is set
# but will not function as a slave if omitted
#server-id = 2
#
# The replication master for this slave - required
#master-host = <hostname>
#
# The username the slave will use for authentication when connecting
# to the master - required
#master-user = <username>
#
# The password the slave will authenticate with when connecting to
# the master - required
#master-password = <password>
#
# The port the master is listening on.
# optional - defaults to 3306
#master-port = <port>
#
# binary logging - not required for slaves, but recommended
#log-bin
# Point the following paths to different dedicated disks
#tmpdir = /tmp/
#log-update = /path-to-dedicated-directory/hostname
# Uncomment the following if you are using BDB tables
#bdb_cache_size = 64M
#bdb_max_lock = 100000
# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /var/lib/mysql/
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /var/lib/mysql/
#innodb_log_arch_dir = /var/lib/mysql/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 256M
#innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 64M ++
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[isamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
7. Extended Status
+--------------------------+----------+
| Variable_name | Value |
+--------------------------+----------+
| Aborted_clients | 9 |
| Aborted_connects | 2 |
| Bytes_received | 742182 |
| Bytes_sent | 30766611 |
| Com_admin_commands | 8 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 410 |
| 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 | 7 |
| 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 | 102 |
| Com_insert_select | 2 |
| 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 | 46 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 1152 |
| 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 | 1 |
| 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 | 1 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 354 |
| Connections | 412 |
| Created_tmp_disk_tables | 2 |
| Created_tmp_tables | 44 |
| Created_tmp_files | 3 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 1 |
| Handler_read_first | 188 |
| Handler_read_key | 63733 |
| Handler_read_next | 37561 |
| Handler_read_prev | 542 |
| Handler_read_rnd | 16604 |
| Handler_read_rnd_next | 377403 |
| Handler_rollback | 0 |
| Handler_update | 21416 |
| Handler_write | 2991 |
| Key_blocks_used | 2661 |
| Key_read_requests | 107520 |
| Key_reads | 2660 |
| Key_write_requests | 242 |
| Key_writes | 198 |
| Max_used_connections | 35 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 146 |
| Open_files | 240 |
| Open_streams | 0 |
| Opened_tables | 152 |
| Questions | 3670 |
| Qcache_queries_in_cache | 238 |
| Qcache_inserts | 1143 |
| Qcache_hits | 1220 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 9 |
| Qcache_free_memory | 14660208 |
| Qcache_free_blocks | 123 |
| Qcache_total_blocks | 685 |
| Rpl_status | NULL |
| Select_full_join | 5 |
| Select_full_range_join | 0 |
| Select_range | 256 |
| Select_range_check | 0 |
| Select_scan | 204 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 0 |
| Sort_merge_passes | 0 |
| Sort_range | 210 |
| Sort_rows | 31422 |
| Sort_scan | 77 |
| Table_locks_immediate | 3013 |
| Table_locks_waited | 13 |
| Threads_cached | 5 |
| Threads_created | 53 |
| Threads_connected | 31 |
| Threads_running | 3 |
| Uptime | 170 |
+--------------------------+----------+
8. Other Scripts : phpadsnew and virtuanews
9. how many average and max concurrent users?
Average 100-120
Max 263
Cookie Timeout : 20 mins
10. http://67.19.56.125/phpinfo.php
11. httpd.conf
KeepAlive: On
MaxKeepAliveRequests: 100
KeepAliveTimeout: 15
MinSpareServers: 5
MaxSpareServers: 10
StartServers: 5
MaxClients: 200
12. vB Version : 3.07
13. Everything ok
I haven't made many changes since I moved to a dedicated server. The server load is usually above 0.70 - 1.00 with 120-200 users online. The big problem is that the mysql processes in specific times (00:00 to 00:30) keeps getting out of control and reaches 350 and more. Maybe it's a task programmed for that time which I haven't found or maybe it's a matter of server settings. In any case I would be grateful if George can have a look at it and make any suqqestions.
http://67.19.56.125/stat.php
(http://67.19.56.125/stat.php)
1. Dedicated
2. cpu speed/type single or dual cpus): Pentium 4 2.8Ghz
how much memory installed: 1 Gb Ram
hard drive type/configuration: 80GB EIDE non raid
linux distributor or windows version: RedHat Enteprise 3 i686
apache/IIS version: apache 1.3.33
PHP version: php 4.3.10
MySQL version: mysql MySQL 4.0.22
3. Nope
4. Made by host -The Planet-
5. Top stats
274 processes: 272 sleeping, 1 running, 1 zombie, 0 stopped
total 1.4% 0.0% 1.9% 0.0% 0.0% 0.0% 96.6%
cpu00 0.9% 0.0% 3.8% 0.0% 0.0% 0.0% 95.1%
cpu01 1.9% 0.0% 0.0% 0.0% 0.0% 0.0% 98.0%
Mem: 1028020k av, 1009836k used, 18184k free, 0k shrd, 35244k buff
765076k actv, 144020k in_d, 13232k in_c
Swap: 2040212k av, 175396k used, 1864816k free 579148k cached
PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND
11883 nobody 20 0 1176 1176 772 R 1.4 0.1 0:00 0 top
Http processes currently running = 154
Mysql processes currently running = 44
Netstat information summary
6. my.cnf
# Example MySQL config file for large systems.
#
# This is for a large system with memory = 512M where the system runs mainly
# MySQL.
#
# You can copy this file to
# /etc/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is /var/lib/mysql) or
# ~/.my.cnf to set user-specific options.
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.
# The following options will be passed to all MySQL clients
[client]
#password = your_password
port = 3306
socket = /var/lib/mysql/mysql.sock
# Here follows entries for some specific programs
# The MySQL server
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 256M
max_allowed_packet = 1M
table_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
myisam_sort_buffer_size = 64M
thread_cache = 8
query_cache_size= 16M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
#
#skip-networking
# Replication Master Server (default)
# binary logging is required for replication
# log-bin
# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
# server-id = 1
# Replication Slave (comment out master section to use this)
#
# To configure this host as a replication slave, you can choose between
# two methods :
#
# 1) Use the CHANGE MASTER TO command (fully described in our manual) -
# the syntax is:
#
#
# where you replace <host>, <user>, <password> by quoted strings and
# <port> by the master's port number (3306 by default).
#
# Example:
#
# CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
# MASTER_USER='joe', MASTER_PASSWORD='secret';
#
# OR
#
# 2) Set the variables below. However, in case you choose this method, then
# start replication for the first time (even unsuccessfully, for example
# if you mistyped the password in master-password and the slave fails to
# connect), the slave will create a master.info file, and any later
# change in this file to the variables' values below will be ignored and
# overridden by the content of the master.info file, unless you shutdown
# the slave server, delete master.info and restart the slaver server.
# For that reason, you may want to leave the lines below untouched
# (commented) and instead use CHANGE MASTER TO (see above)
#
# required unique id between 2 and 2^32 - 1
# (and different from the master)
# defaults to 2 if master-host is set
# but will not function as a slave if omitted
#server-id = 2
#
# The replication master for this slave - required
#master-host = <hostname>
#
# The username the slave will use for authentication when connecting
# to the master - required
#master-user = <username>
#
# The password the slave will authenticate with when connecting to
# the master - required
#master-password = <password>
#
# The port the master is listening on.
# optional - defaults to 3306
#master-port = <port>
#
# binary logging - not required for slaves, but recommended
#log-bin
# Point the following paths to different dedicated disks
#tmpdir = /tmp/
#log-update = /path-to-dedicated-directory/hostname
# Uncomment the following if you are using BDB tables
#bdb_cache_size = 64M
#bdb_max_lock = 100000
# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /var/lib/mysql/
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /var/lib/mysql/
#innodb_log_arch_dir = /var/lib/mysql/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 256M
#innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 64M ++
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[isamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
7. Extended Status
+--------------------------+----------+
| Variable_name | Value |
+--------------------------+----------+
| Aborted_clients | 9 |
| Aborted_connects | 2 |
| Bytes_received | 742182 |
| Bytes_sent | 30766611 |
| Com_admin_commands | 8 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 410 |
| 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 | 7 |
| 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 | 102 |
| Com_insert_select | 2 |
| 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 | 46 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 1152 |
| 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 | 1 |
| 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 | 1 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 354 |
| Connections | 412 |
| Created_tmp_disk_tables | 2 |
| Created_tmp_tables | 44 |
| Created_tmp_files | 3 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 1 |
| Handler_read_first | 188 |
| Handler_read_key | 63733 |
| Handler_read_next | 37561 |
| Handler_read_prev | 542 |
| Handler_read_rnd | 16604 |
| Handler_read_rnd_next | 377403 |
| Handler_rollback | 0 |
| Handler_update | 21416 |
| Handler_write | 2991 |
| Key_blocks_used | 2661 |
| Key_read_requests | 107520 |
| Key_reads | 2660 |
| Key_write_requests | 242 |
| Key_writes | 198 |
| Max_used_connections | 35 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 146 |
| Open_files | 240 |
| Open_streams | 0 |
| Opened_tables | 152 |
| Questions | 3670 |
| Qcache_queries_in_cache | 238 |
| Qcache_inserts | 1143 |
| Qcache_hits | 1220 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 9 |
| Qcache_free_memory | 14660208 |
| Qcache_free_blocks | 123 |
| Qcache_total_blocks | 685 |
| Rpl_status | NULL |
| Select_full_join | 5 |
| Select_full_range_join | 0 |
| Select_range | 256 |
| Select_range_check | 0 |
| Select_scan | 204 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 0 |
| Sort_merge_passes | 0 |
| Sort_range | 210 |
| Sort_rows | 31422 |
| Sort_scan | 77 |
| Table_locks_immediate | 3013 |
| Table_locks_waited | 13 |
| Threads_cached | 5 |
| Threads_created | 53 |
| Threads_connected | 31 |
| Threads_running | 3 |
| Uptime | 170 |
+--------------------------+----------+
8. Other Scripts : phpadsnew and virtuanews
9. how many average and max concurrent users?
Average 100-120
Max 263
Cookie Timeout : 20 mins
10. http://67.19.56.125/phpinfo.php
11. httpd.conf
KeepAlive: On
MaxKeepAliveRequests: 100
KeepAliveTimeout: 15
MinSpareServers: 5
MaxSpareServers: 10
StartServers: 5
MaxClients: 200
12. vB Version : 3.07
13. Everything ok