Forum Moderators: bakedjake
I've tried doing some tweaking to my.cnf and disabling weblogs/webalizer/etc but nothing helps.
I would greatly appreciate any help of any kind offered.
A few stats.....
Apache, PHP 4.4.2 and MySQL 4.1.12
dual xeon 3ghz, 2gb ram
top - 10:47:18 up 295 days, 6:25, 1 user, load average: 4.62, 4.88, 5.60
Tasks: 229 total, 5 running, 223 sleeping, 0 stopped, 1 zombie
Cpu(s): 23.3% us, 4.8% sy, 59.7% ni, 9.9% id, 1.2% wa, 0.1% hi, 1.0% si
Mem: 2074812k total, 2055844k used, 18968k free, 39204k buffers
Swap: 2048248k total, 4648k used, 2043600k free, 1355268k cached PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
22917 root 25 0 7488 1852 1416 R 93.1 0.1 368:10.44 http
10425 apache 19 4 32592 16m 8404 S 13.3 0.8 0:01.79 httpd
10515 apache 19 4 32584 16m 8396 S 12.3 0.8 0:00.81 httpd
10363 apache 19 4 32584 16m 8408 S 11.6 0.8 0:02.39 httpd
10404 apache 19 4 32592 16m 8228 S 10.3 0.8 0:01.21 httpd
10314 apache 19 4 32544 16m 8384 S 9.9 0.8 0:01.95 httpd
11753 root 19 4 2404 900 568 R 9.6 0.0 23:26.22 exclog
my.cnf
[mysqld]
socket=/home/virtual/FILESYSTEMTEMPLATE/.mysqlsock/mysql.sock
skip-innodb
query_cache_limit = 1M
query_cache_size = 64M
query_cache_type = 1
max_connections=500
interactive_timeout=20
wait_timeout=10
connect_timeout=10
thread_cache_size=128
key_buffer=128M
join_buffer=1M
max_allowed_packet=16M
table_cache=1024
record_buffer=1M
sort_buffer_size=2M
read_buffer_size=2M
max_connect_errors=99999999999999999999999
datadir=/var/lib/mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1[mysql.server]
user=mysql
basedir=/var/lib
[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[client]
socket=/home/virtual/FILESYSTEMTEMPLATE/.mysqlsock/mysql.sock
mysqladmin -p status
Uptime: 86946 Threads: 4 Questions: 1527793 Slow queries: 1 Opens: 84 Flush tables: 1 Open tables: 68 Queries per second avg: 17.572
mysqladmin -p extended-status
+--------------------------------+-----------+
¦ Variable_name ¦ Value ¦
+--------------------------------+-----------+
¦ Aborted_clients ¦ 4980 ¦
¦ Aborted_connects ¦ 2 ¦
¦ Binlog_cache_disk_use ¦ 0 ¦
¦ Binlog_cache_use ¦ 0 ¦
¦ Bytes_received ¦ 97176227 ¦
¦ Bytes_sent ¦ 321939460 ¦
¦ Com_admin_commands ¦ 0 ¦
¦ Com_alter_db ¦ 0 ¦
¦ Com_alter_table ¦ 0 ¦
¦ Com_analyze ¦ 1 ¦
¦ Com_backup_table ¦ 0 ¦
¦ Com_begin ¦ 0 ¦
¦ Com_change_db ¦ 350160 ¦
¦ Com_change_master ¦ 0 ¦
¦ Com_check ¦ 1 ¦
¦ Com_checksum ¦ 0 ¦
¦ Com_commit ¦ 0 ¦
¦ Com_create_db ¦ 0 ¦
¦ Com_create_function ¦ 0 ¦
¦ Com_create_index ¦ 0 ¦
¦ Com_create_table ¦ 0 ¦
¦ Com_dealloc_sql ¦ 0 ¦
¦ Com_delete ¦ 74 ¦
¦ Com_delete_multi ¦ 0 ¦
¦ Com_do ¦ 0 ¦
¦ Com_drop_db ¦ 0 ¦
¦ Com_drop_function ¦ 0 ¦
¦ Com_drop_index ¦ 0 ¦
¦ Com_drop_table ¦ 0 ¦
¦ Com_drop_user ¦ 0 ¦
¦ Com_execute_sql ¦ 0 ¦
¦ Com_flush ¦ 0 ¦
¦ Com_grant ¦ 0 ¦
¦ Com_ha_close ¦ 0 ¦
¦ Com_ha_open ¦ 0 ¦
¦ Com_ha_read ¦ 0 ¦
¦ Com_help ¦ 0 ¦
¦ Com_insert ¦ 6112 ¦
¦ Com_insert_select ¦ 42 ¦
¦ Com_kill ¦ 0 ¦
¦ Com_load ¦ 0 ¦
¦ Com_load_master_data ¦ 0 ¦
¦ Com_load_master_table ¦ 0 ¦
¦ Com_lock_tables ¦ 0 ¦
¦ Com_optimize ¦ 2 ¦
¦ Com_preload_keys ¦ 0 ¦
¦ Com_prepare_sql ¦ 0 ¦
¦ Com_purge ¦ 0 ¦
¦ Com_purge_before_date ¦ 0 ¦
¦ Com_rename_table ¦ 0 ¦
¦ Com_repair ¦ 1 ¦
¦ Com_replace ¦ 68 ¦
¦ Com_replace_select ¦ 1 ¦
¦ Com_reset ¦ 0 ¦
¦ Com_restore_table ¦ 0 ¦
¦ Com_revoke ¦ 0 ¦
¦ Com_revoke_all ¦ 0 ¦
¦ Com_rollback ¦ 0 ¦
¦ Com_savepoint ¦ 0 ¦
¦ Com_select ¦ 40270 ¦
¦ Com_set_option ¦ 349678 ¦
¦ Com_show_binlog_events ¦ 0 ¦
¦ Com_show_binlogs ¦ 16 ¦
¦ Com_show_charsets ¦ 53 ¦
¦ Com_show_collations ¦ 53 ¦
¦ Com_show_column_types ¦ 0 ¦
¦ Com_show_create_db ¦ 0 ¦
¦ Com_show_create_table ¦ 6 ¦
¦ Com_show_databases ¦ 5 ¦
¦ Com_show_errors ¦ 0 ¦
¦ Com_show_fields ¦ 100 ¦
¦ Com_show_grants ¦ 28 ¦
¦ Com_show_innodb_status ¦ 0 ¦
¦ Com_show_keys ¦ 42 ¦
¦ Com_show_logs ¦ 0 ¦
¦ Com_show_master_status ¦ 0 ¦
¦ Com_show_new_master ¦ 0 ¦
¦ Com_show_open_tables ¦ 0 ¦
¦ Com_show_privileges ¦ 0 ¦
¦ Com_show_processlist ¦ 5 ¦
¦ Com_show_slave_hosts ¦ 0 ¦
¦ Com_show_slave_status ¦ 0 ¦
¦ Com_show_status ¦ 10 ¦
¦ Com_show_storage_engines ¦ 1 ¦
¦ Com_show_tables ¦ 207 ¦
¦ Com_show_variables ¦ 135 ¦
¦ Com_show_warnings ¦ 0 ¦
¦ Com_slave_start ¦ 0 ¦
¦ Com_slave_stop ¦ 0 ¦
¦ Com_truncate ¦ 0 ¦
¦ Com_unlock_tables ¦ 0 ¦
¦ Com_update ¦ 88856 ¦
¦ Com_update_multi ¦ 0 ¦
¦ Connections ¦ 347443 ¦
¦ Created_tmp_disk_tables ¦ 0 ¦
¦ Created_tmp_files ¦ 0 ¦
¦ Created_tmp_tables ¦ 483 ¦
¦ Delayed_errors ¦ 0 ¦
¦ Delayed_insert_threads ¦ 0 ¦
¦ Delayed_writes ¦ 0 ¦
¦ Flush_commands ¦ 1 ¦
¦ Handler_commit ¦ 0 ¦
¦ Handler_delete ¦ 87 ¦
¦ Handler_discover ¦ 0 ¦
¦ Handler_read_first ¦ 431 ¦
¦ Handler_read_key ¦ 2609306 ¦
¦ Handler_read_next ¦ 5199385 ¦
¦ Handler_read_prev ¦ 1349756 ¦
¦ Handler_read_rnd ¦ 48667 ¦
¦ Handler_read_rnd_next ¦ 280167199 ¦
¦ Handler_rollback ¦ 0 ¦
¦ Handler_update ¦ 2224515 ¦
¦ Handler_write ¦ 2127410 ¦
¦ Key_blocks_not_flushed ¦ 0 ¦
¦ Key_blocks_unused ¦ 13117 ¦
¦ Key_blocks_used ¦ 102863 ¦
¦ Key_read_requests ¦ 1959704 ¦
¦ Key_reads ¦ 102962 ¦
¦ Key_write_requests ¦ 26566 ¦
¦ Key_writes ¦ 20282 ¦
¦ Max_used_connections ¦ 16 ¦
¦ Not_flushed_delayed_rows ¦ 0 ¦
¦ Open_files ¦ 123 ¦
¦ Open_streams ¦ 0 ¦
¦ Open_tables ¦ 68 ¦
¦ Opened_tables ¦ 84 ¦
¦ Qcache_free_blocks ¦ 114 ¦
¦ Qcache_free_memory ¦ 66252360 ¦
¦ Qcache_hits ¦ 347946 ¦
¦ Qcache_inserts ¦ 39737 ¦
¦ Qcache_lowmem_prunes ¦ 0 ¦
¦ Qcache_not_cached ¦ 533 ¦
¦ Qcache_queries_in_cache ¦ 424 ¦
¦ Qcache_total_blocks ¦ 1002 ¦
¦ Questions ¦ 1528846 ¦
¦ Rpl_status ¦ NULL ¦
¦ Select_full_join ¦ 15 ¦
¦ Select_full_range_join ¦ 0 ¦
¦ Select_range ¦ 758 ¦
¦ Select_range_check ¦ 0 ¦
¦ Select_scan ¦ 3946 ¦
¦ Slave_open_temp_tables ¦ 0 ¦
¦ Slave_running ¦ OFF ¦
¦ Slave_retried_transactions ¦ 0 ¦
¦ Slow_launch_threads ¦ 0 ¦
¦ Slow_queries ¦ 1 ¦
¦ Sort_merge_passes ¦ 0 ¦
¦ Sort_range ¦ 0 ¦
¦ Sort_rows ¦ 99701 ¦
¦ Sort_scan ¦ 646 ¦
¦ Ssl_accept_renegotiates ¦ 0 ¦
¦ Ssl_accepts ¦ 0 ¦
¦ Ssl_callback_cache_hits ¦ 0 ¦
¦ Ssl_cipher ¦ ¦
¦ Ssl_cipher_list ¦ ¦
¦ Ssl_client_connects ¦ 0 ¦
¦ Ssl_connect_renegotiates ¦ 0 ¦
¦ Ssl_ctx_verify_depth ¦ 0 ¦
¦ Ssl_ctx_verify_mode ¦ 0 ¦
¦ Ssl_default_timeout ¦ 0 ¦
¦ Ssl_finished_accepts ¦ 0 ¦
¦ Ssl_finished_connects ¦ 0 ¦
¦ Ssl_session_cache_hits ¦ 0 ¦
¦ Ssl_session_cache_misses ¦ 0 ¦
¦ Ssl_session_cache_mode ¦ NONE ¦
¦ Ssl_session_cache_overflows ¦ 0 ¦
¦ Ssl_session_cache_size ¦ 0 ¦
¦ Ssl_session_cache_timeouts ¦ 0 ¦
¦ Ssl_sessions_reused ¦ 0 ¦
¦ Ssl_used_session_cache_entries ¦ 0 ¦
¦ Ssl_verify_depth ¦ 0 ¦
¦ Ssl_verify_mode ¦ 0 ¦
¦ Ssl_version ¦ ¦
¦ Table_locks_immediate ¦ 136299 ¦
¦ Table_locks_waited ¦ 37 ¦
¦ Threads_cached ¦ 13 ¦
¦ Threads_connected ¦ 3 ¦
¦ Threads_created ¦ 16 ¦
¦ Threads_running ¦ 2 ¦
¦ Uptime ¦ 86994 ¦
+--------------------------------+-----------+
I can post any other server stats needed to help figure this thing out.
Possible fixes.
1) Restart apache (not a graceful restart - a full stop+start). It may be possible (unlikely, but this is an easy thing to try) that an instance of a script is stuck in a loop. Stopping and restarting will kill off existing runs of the scripts.
2) Run php with a code cache - something like mmcache or zend optimizer. If you have large scripts, these will compile the code once and cache it rather than compile it every time the script is called.
3) Do some optimization of the code. It could just be poorly written or highly intensive code that needs to be optimized.
If it is mysql instead of your script
1) Check out your slow query log. If you don't have it turned on, just try "show full processlist". Look for any queries that aren't executing quickly and either rewrite the queries or add indexes.
Zend is installed and running, for some reason when looking at phpinfo the zend logo is not there but says it's installed.
The script I'm using pulls a text link from the database and there are 75 text links. With every pageview there are 75 links being pulled from the db and 75 entries (processes) in the access log for each site visitor. Multiply 75 by about 500,000 and there's my problem.
The same script is used on at least 10k other sites but I think I've pushed it near it's limits. Maybe optimizing won't help becuase of the script.
[root@srv1 ~]# php -v
PHP 4.4.2 (cgi) (built: Mar 15 2006 00:27:22)
Copyright (c) 1997-2006 The PHP Group
Zend Engine v1.3.0, Copyright (c) 1998-2004 Zend Technologies
Are you saying 75 entries in your http access log for each page load? You said text links and unless you are doing popups, frames or images I don't see how you would have 75 entries in your log file.
Each time a page is loaded then there are 75 accesses from that one visitor...per page view. I does cache the info to a file but I guess has to process new info for each visitor too. I've been using this script for about 3 years and it has been perfect up until now.
I just started a program similar to google ads and if a webmaster allows 50 links per page view on their site and they receive 1 million pageviews that day, this mean my server has to deal with 50 million entries in the server access log.
