Question

mysql high load , big problem I hope some one help

Asked by: xserverx

mysql cause high load can any one help to optimaze mysql server please
this information about my server :

1- dedicated server only 1 site hosted on it

2- server specs :

CPU : Dual Intel Xeon CPU  E5430  @ 2.66GHz 12MB
RAM: 24Gb  
HDD: 300 GB SA-SCSI 15K none raid
Linux CentOS 64 5bit
Apache: Apache/2.2.13 (Unix)
Mysql: 5.0.81-community
PHP :5.2.9

3- script installed vbulletin 3.8.2
4- there is no innodb type on my server
5- mysql compiled with cpanel
6- top stats


[CODE]
top - 16:46:38 up 18:06,  3 users,  load average: 238.38, 206.04, 131.13
Tasks: 1250 total,  38 running, 1211 sleeping,   0 stopped,   1 zombie
Cpu(s): 92.4%us,  5.7%sy,  0.0%ni,  0.9%id,  0.0%wa,  0.1%hi,  0.9%si,  0.0%st
Mem:  24689152k total, 23738664k used,   950488k free,   232164k buffers
Swap: 32456344k total,      172k used, 32456172k free, 10289108k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
20180 mysql      5 -10 1356m 409m 4072 S 247.1  1.7  82:27.61 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=my
19609 nobody    15   0  324m  30m  14m S 16.3  0.1   0:13.47 /usr/local/apache/bin/httpd -k start -DSSL
22238 nobody    15   0  325m  30m  13m S 11.8  0.1   0:16.16 /usr/local/apache/bin/httpd -k start -DSSL
25704 nobody    15   0  321m  26m  13m S 11.8  0.1   0:11.10 /usr/local/apache/bin/httpd -k start -DSSL
  630 nobody    15   0  317m  21m  12m S 11.4  0.1   0:04.40 /usr/local/apache/bin/httpd -k start -DSSL
 1650 nobody    15   0  323m  26m  12m S 11.4  0.1   0:03.85 /usr/local/apache/bin/httpd -k start -DSSL
15117 nobody    15   0  326m  30m  13m S 11.4  0.1   0:23.36 /usr/local/apache/bin/httpd -k start -DSSL
22257 nobody    15   0  320m  25m  13m S 11.4  0.1   0:15.91 /usr/local/apache/bin/httpd -k start -DSSL
23193 nobody    15   0  326m  33m  16m S 11.4  0.1   0:12.37 /usr/local/apache/bin/httpd -k start -DSSL
23219 nobody    15   0  323m  27m  13m S 11.4  0.1   0:11.86 /usr/local/apache/bin/httpd -k start -DSSL
 1631 nobody    15   0  323m  27m  12m S 11.1  0.1   0:04.40 /usr/local/apache/bin/httpd -k start -DSSL
 1651 nobody    15   0  324m  28m  12m S 11.1  0.1   0:04.11 /usr/local/apache/bin/httpd -k start -DSSL
11756 nobody    15   0  323m  27m  13m S 11.1  0.1   0:21.62 /usr/local/apache/bin/httpd -k start -DSSL
12173 nobody    15   0  321m  25m  12m S 11.1  0.1   0:20.20 /usr/local/apache/bin/httpd -k start -DSSL
16214 nobody    15   0  325m  30m  13m S 11.1  0.1   0:20.16 /usr/local/apache/bin/httpd -k start -DSSL
19854 nobody    15   0  323m  28m  13m S 11.1  0.1   0:16.44 /usr/local/apache/bin/httpd -k start -DSSL
19883 nobody    15   0  325m  29m  13m S 11.1  0.1   0:16.05 /usr/local/apache/bin/httpd -k start -DSSL
22300 nobody    15   0  321m  27m  13m S 11.1  0.1   0:14.58 /usr/local/apache/bin/httpd -k start -DSSL
 1117 nobody    16   0  323m  27m  12m S 10.8  0.1   0:04.43 /usr/local/apache/bin/httpd -k start -DSSL
32090 nobody    15   0  323m  27m  12m S 10.8  0.1   0:04.82 /usr/local/apache/bin/httpd -k start -DSSL
11915 nobody    15   0  321m  26m  13m S 10.1  0.1   0:24.88 /usr/local/apache/bin/httpd -k start -DSSL
13438 nobody    16   0  325m  30m  13m R 10.1  0.1   0:20.84 /usr/local/apache/bin/httpd -k start -DSSL
21976 nobody    15   0  324m  29m  13m S 10.1  0.1   0:15.99 /usr/local/apache/bin/httpd -k start -DSSL
 2176 nobody    15   0  325m  28m  12m S  9.8  0.1   0:03.95 /usr/local/apache/bin/httpd -k start -DSSL
11922 nobody    15   0  322m  27m  13m S  9.5  0.1   0:25.08 /usr/local/apache/bin/httpd -k start -DSSL
17507 nobody    15   0  325m  32m  16m S  9.5  0.1   0:18.08 /usr/local/apache/bin/httpd -k start -DSSL
11759 nobody    15   0  323m  27m  13m S  9.1  0.1   0:22.74 /usr/local/apache/bin/httpd -k start -DSSL
12190 nobody    15   0  325m  30m  13m S  9.1  0.1   0:23.61 /usr/local/apache/bin/httpd -k start -DSSL
  728 nobody    15   0  323m  27m  12m S  8.2  0.1   0:05.16 /usr/local/apache/bin/httpd -k start -DSSL
 1125 nobody    15   0  323m  27m  12m S  7.5  0.1   0:04.38 /usr/local/apache/bin/httpd -k start -DSSL
32445 nobody    16   0  323m  27m  12m S  7.5  0.1   0:04.44 /usr/local/apache/bin/httpd -k start -DSSL

[/CODE]

[CODE]
top - 16:48:05 up 18:08,  3 users,  load average: 165.38, 190.85, 132.31
Tasks: 1265 total,  33 running, 1232 sleeping,   0 stopped,   0 zombie
Cpu0  : 37.6%us,  3.7%sy,  0.3%ni, 57.7%id,  0.6%wa,  0.0%hi,  0.1%si,  0.0%st
Cpu1  : 37.4%us,  3.7%sy,  0.4%ni, 54.6%id,  3.6%wa,  0.0%hi,  0.2%si,  0.0%st
Cpu2  : 39.1%us,  3.2%sy,  0.3%ni, 56.8%id,  0.4%wa,  0.0%hi,  0.2%si,  0.0%st
Cpu3  : 39.3%us,  3.2%sy,  0.3%ni, 56.1%id,  1.0%wa,  0.0%hi,  0.2%si,  0.0%st
Cpu4  : 37.6%us,  3.2%sy,  0.3%ni, 57.8%id,  1.0%wa,  0.0%hi,  0.1%si,  0.0%st
Cpu5  : 40.6%us,  3.0%sy,  0.2%ni, 55.5%id,  0.5%wa,  0.0%hi,  0.1%si,  0.0%st
Cpu6  : 38.1%us,  3.1%sy,  0.3%ni, 58.0%id,  0.4%wa,  0.0%hi,  0.2%si,  0.0%st
Cpu7  : 42.2%us,  5.6%sy,  0.1%ni, 47.8%id,  0.6%wa,  0.2%hi,  3.4%si,  0.0%st
Mem:  24689152k total, 23766372k used,   922780k free,   234424k buffers
Swap: 32456344k total,      172k used, 32456172k free, 10290164k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
20180 mysql      5 -10 1356m 421m 4072 S 747.3  1.7  85:47.72 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=my
11542 nobody    16   0  326m  31m  13m S 27.1  0.1   0:23.82 /usr/local/apache/bin/httpd -k start -DSSL
15131 nobody    16   0  326m  30m  13m S 27.1  0.1   0:19.53 /usr/local/apache/bin/httpd -k start -DSSL
20286 nobody    16   0  325m  32m  16m S 26.3  0.1   0:12.03 /usr/local/apache/bin/httpd -k start -DSSL
23164 nobody    16   0  325m  29m  12m S 24.0  0.1   0:12.65 /usr/local/apache/bin/httpd -k start -DSSL
25704 nobody    16   0  323m  28m  13m S 23.2  0.1   0:11.71 /usr/local/apache/bin/httpd -k start -DSSL
19293 nobody    16   0  325m  29m  13m R 22.4  0.1   0:20.55 /usr/local/apache/bin/httpd -k start -DSSL
32079 nobody    16   0  324m  28m  12m S 22.4  0.1   0:06.46 /usr/local/apache/bin/httpd -k start -DSSL
11589 nobody    15   0  323m  28m  12m S 21.7  0.1   0:20.83 /usr/local/apache/bin/httpd -k start -DSSL
20315 nobody    15   0  324m  28m  13m S 21.7  0.1   0:17.18 /usr/local/apache/bin/httpd -k start -DSSL
22242 nobody    15   0  325m  29m  13m S 20.9  0.1   0:14.33 /usr/local/apache/bin/httpd -k start -DSSL
11659 nobody    16   0  324m  29m  13m S 18.6  0.1   0:21.41 /usr/local/apache/bin/httpd -k start -DSSL
17307 nobody    15   0  324m  28m  13m R 18.6  0.1   0:18.57 /usr/local/apache/bin/httpd -k start -DSSL
12341 nobody    15   0  324m  29m  13m R 15.5  0.1   0:17.46 /usr/local/apache/bin/httpd -k start -DSSL
19853 nobody    16   0  326m  33m  16m R 15.5  0.1   0:15.04 /usr/local/apache/bin/httpd -k start -DSSL
22306 nobody    16   0  324m  29m  13m S 15.5  0.1   0:17.73 /usr/local/apache/bin/httpd -k start -DSSL
25710 nobody    16   0  325m  29m  13m S 15.5  0.1   0:15.38 /usr/local/apache/bin/httpd -k start -DSSL
 5752 root      25   0 10580 1020  644 R 14.7  0.0   0:00.19 netstat -ntu
20285 nobody    16   0  325m  29m  13m S 14.7  0.1   0:17.64 /usr/local/apache/bin/httpd -k start -DSSL
23179 nobody    16   0  325m  29m  12m S 14.7  0.1   0:15.22 /usr/local/apache/bin/httpd -k start -DSSL
23180 nobody    16   0  323m  27m  12m R 14.7  0.1   0:15.14 /usr/local/apache/bin/httpd -k start -DSSL
11376 nobody    15   0  325m  30m  13m S 13.9  0.1   0:26.78 /usr/local/apache/bin/httpd -k start -DSSL
29522 nobody    15   0  318m  22m  12m S 13.9  0.1   0:08.03 /usr/local/apache/bin/httpd -k start -DSSL
15122 nobody    15   0  325m  29m  13m S 13.2  0.1   0:22.09 /usr/local/apache/bin/httpd -k start -DSSL
24560 nobody    15   0  324m  29m  13m S 12.4  0.1   0:13.96 /usr/local/apache/bin/httpd -k start -DSSL
 2423 nobody    15   0  325m  29m  12m S 11.6  0.1   0:02.98 /usr/local/apache/bin/httpd -k start -DSSL
11537 nobody    16   0  324m  30m  14m R 11.6  0.1   0:20.24 /usr/local/apache/bin/httpd -k start -DSSL
12171 nobody    16   0  324m  29m  13m R 10.8  0.1   0:27.00 /usr/local/apache/bin/httpd -k start -DSSL

[/CODE]
7-/etc/my.cnf
[CODE]
[mysqld]
tmpdir=/backup/tmp
local-infile=0
skip-networking
safe-show-database
#back_log = 50

max_connections = 1000
max_user_connections= 100
key_buffer_size = 768M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 4000
thread_cache_size = 384
wait_timeout = 10
connect_timeout = 10
tmp_table_size = 64M
max_heap_table_size = 64M
max_allowed_packet = 128M
net_buffer_length = 16384
max_connect_errors = 10000
thread_concurrency = 16
concurrent_insert = 2
table_lock_wait_timeout = 30
read_rnd_buffer_size = 786432
bulk_insert_buffer_size = 8M
query_cache_limit = 1M
query_cache_size = 128M
query_cache_type = 1
query_prealloc_size = 262144
query_alloc_block_size = 65536
transaction_alloc_block_size = 8192
transaction_prealloc_size = 4096
default-storage-engine = MyISAM
max_write_lock_count = 8
[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/lib/mysql/mysql.pid
nice = -10
open_files_limit = 8192
[mysqldump]
quick
max_allowed_packet = 16M

[myisamchk]
key_buffer = 786M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M

[/CODE]
8- mysqladmin -u root -p var ext stat ver proc  
[CODE]
+---------------------------------+----------------------------------------+
| Variable_name                   | Value                                  |
+---------------------------------+----------------------------------------+
| auto_increment_increment        | 1                                      |
| auto_increment_offset           | 1                                      |
| automatic_sp_privileges         | ON                                     |
| back_log                        | 50                                     |
| basedir                         | /                                      |
| binlog_cache_size               | 32768                                  |
| bulk_insert_buffer_size         | 8388608                                |
| character_set_client            | latin1                                 |
| character_set_connection        | latin1                                 |
| character_set_database          | latin1                                 |
| character_set_filesystem        | binary                                 |
| character_set_results           | latin1                                 |
| character_set_server            | latin1                                 |
| character_set_system            | utf8                                   |
| character_sets_dir              | /usr/share/mysql/charsets/             |
| collation_connection            | latin1_swedish_ci                      |
| collation_database              | latin1_swedish_ci                      |
| collation_server                | latin1_swedish_ci                      |
| completion_type                 | 0                                      |
| concurrent_insert               | 2                                      |
| connect_timeout                 | 10                                     |
| datadir                         | /var/lib/mysql/                        |
| date_format                     | %Y-%m-%d                               |
| datetime_format                 | %Y-%m-%d %H:%i:%s                      |
| default_week_format             | 0                                      |
| delay_key_write                 | ON                                     |
| delayed_insert_limit            | 100                                    |
| delayed_insert_timeout          | 300                                    |
| delayed_queue_size              | 1000                                   |
| div_precision_increment         | 4                                      |
| keep_files_on_create            | OFF                                    |
| engine_condition_pushdown       | OFF                                    |
| expire_logs_days                | 0                                      |
| flush                           | OFF                                    |
| flush_time                      | 0                                      |
| ft_boolean_syntax               | + -><()~*:""&|                         |
| ft_max_word_len                 | 84                                     |
| ft_min_word_len                 | 4                                      |
| ft_query_expansion_limit        | 20                                     |
| ft_stopword_file                | (built-in)                             |
| group_concat_max_len            | 1024                                   |
| have_archive                    | YES                                    |
| have_bdb                        | NO                                     |
| have_blackhole_engine           | YES                                    |
| have_compress                   | YES                                    |
| have_crypt                      | YES                                    |
| have_csv                        | YES                                    |
| have_dynamic_loading            | YES                                    |
| have_example_engine             | YES                                    |
| have_federated_engine           | YES                                    |
| have_geometry                   | YES                                    |
| have_innodb                     | YES                                    |
| have_isam                       | NO                                     |
| have_merge_engine               | YES                                    |
| have_ndbcluster                 | DISABLED                               |
| have_openssl                    | NO                                     |
| have_ssl                        | NO                                     |
| have_query_cache                | YES                                    |
| have_raid                       | NO                                     |
| have_rtree_keys                 | YES                                    |
| have_symlink                    | YES                                    |
| hostname                        | al-wlid.al-wlid.com                    |
| init_connect                    |                                        |
| init_file                       |                                        |
| init_slave                      |                                        |
| innodb_additional_mem_pool_size | 1048576                                |
| innodb_autoextend_increment     | 8                                      |
| innodb_buffer_pool_awe_mem_mb   | 0                                      |
| innodb_buffer_pool_size         | 8388608                                |
| innodb_checksums                | ON                                     |
| innodb_commit_concurrency       | 0                                      |
| innodb_concurrency_tickets      | 500                                    |
| innodb_data_file_path           | ibdata1:10M:autoextend                 |
| innodb_data_home_dir            |                                        |
| innodb_adaptive_hash_index      | ON                                     |
| innodb_doublewrite              | ON                                     |
| innodb_fast_shutdown            | 1                                      |
| innodb_file_io_threads          | 4                                      |
| innodb_file_per_table           | OFF                                    |
| innodb_flush_log_at_trx_commit  | 1                                      |
| innodb_flush_method             |                                        |
| innodb_force_recovery           | 0                                      |
| innodb_lock_wait_timeout        | 50                                     |
| innodb_locks_unsafe_for_binlog  | OFF                                    |
| innodb_log_arch_dir             |                                        |
| innodb_log_archive              | OFF                                    |
| innodb_log_buffer_size          | 1048576                                |
| innodb_log_file_size            | 5242880                                |
| innodb_log_files_in_group       | 2                                      |
| innodb_log_group_home_dir       | ./                                     |
| innodb_max_dirty_pages_pct      | 90                                     |
| innodb_max_purge_lag            | 0                                      |
| innodb_mirrored_log_groups      | 1                                      |
| innodb_open_files               | 300                                    |
| innodb_rollback_on_timeout      | OFF                                    |
| innodb_support_xa               | ON                                     |
| innodb_sync_spin_loops          | 20                                     |
| innodb_table_locks              | ON                                     |
| innodb_thread_concurrency       | 8                                      |
| innodb_thread_sleep_delay       | 10000                                  |
| interactive_timeout             | 28800                                  |
| join_buffer_size                | 1048576                                |
| key_buffer_size                 | 805306368                              |
| key_cache_age_threshold         | 300                                    |
| key_cache_block_size            | 1024                                   |
| key_cache_division_limit        | 100                                    |
| language                        | /usr/share/mysql/english/              |
| large_files_support             | ON                                     |
| large_page_size                 | 0                                      |
| large_pages                     | OFF                                    |
| lc_time_names                   | en_US                                  |
| license                         | GPL                                    |
| local_infile                    | OFF                                    |
| locked_in_memory                | OFF                                    |
| log                             | OFF                                    |
| log_bin                         | OFF                                    |
| log_bin_trust_function_creators | OFF                                    |
| log_error                       |                                        |
| log_queries_not_using_indexes   | OFF                                    |
| log_slave_updates               | OFF                                    |
| log_slow_queries                | OFF                                    |
| log_warnings                    | 1                                      |
| long_query_time                 | 10                                     |
| low_priority_updates            | OFF                                    |
| lower_case_file_system          | OFF                                    |
| lower_case_table_names          | 0                                      |
| max_allowed_packet              | 134217728                              |
| max_binlog_cache_size           | 18446744073709547520                   |
| max_binlog_size                 | 1073741824                             |
| max_connect_errors              | 10000                                  |
| max_connections                 | 1000                                   |
| max_delayed_threads             | 20                                     |
| max_error_count                 | 64                                     |
| max_heap_table_size             | 67108864                               |
| max_insert_delayed_threads      | 20                                     |
| max_join_size                   | 18446744073709551615                   |
| max_length_for_sort_data        | 1024                                   |
| max_prepared_stmt_count         | 16382                                  |
| max_relay_log_size              | 0                                      |
| max_seeks_for_key               | 18446744073709551615                   |
| max_sort_length                 | 1024                                   |
| max_sp_recursion_depth          | 0                                      |
| max_tmp_tables                  | 32                                     |
| max_user_connections            | 100                                    |
| max_write_lock_count            | 8                                      |
| multi_range_count               | 256                                    |
| myisam_data_pointer_size        | 6                                      |
| myisam_max_sort_file_size       | 9223372036853727232                    |
| myisam_recover_options          | OFF                                    |
| myisam_repair_threads           | 1                                      |
| myisam_sort_buffer_size         | 67108864                               |
| myisam_stats_method             | nulls_unequal                          |
| ndb_autoincrement_prefetch_sz   | 1                                      |
| ndb_force_send                  | ON                                     |
| ndb_use_exact_count             | ON                                     |
| ndb_use_transactions            | ON                                     |
| ndb_cache_check_time            | 0                                      |
| ndb_connectstring               |                                        |
| net_buffer_length               | 16384                                  |
| net_read_timeout                | 30                                     |
| net_retry_count                 | 10                                     |
| net_write_timeout               | 60                                     |
| new                             | OFF                                    |
| old_passwords                   | OFF                                    |
| open_files_limit                | 9010                                   |
| optimizer_prune_level           | 1                                      |
| optimizer_search_depth          | 62                                     |
| pid_file                        | /var/lib/mysql/al-wlid.al-wlid.com.pid |
| plugin_dir                      |                                        |
| port                            | 0                                      |
| preload_buffer_size             | 32768                                  |
| profiling                       | OFF                                    |
| profiling_history_size          | 15                                     |
| protocol_version                | 10                                     |
| query_alloc_block_size          | 65536                                  |
| query_cache_limit               | 1048576                                |
| query_cache_min_res_unit        | 4096                                   |
| query_cache_size                | 134217728                              |
| query_cache_type                | ON                                     |
| query_cache_wlock_invalidate    | OFF                                    |
| query_prealloc_size             | 262144                                 |
| range_alloc_block_size          | 4096                                   |
| read_buffer_size                | 1048576                                |
| read_only                       | OFF                                    |
| read_rnd_buffer_size            | 786432                                 |
| relay_log                       |                                        |
| relay_log_index                 |                                        |
| relay_log_info_file             | relay-log.info                         |
| relay_log_purge                 | ON                                     |
| relay_log_space_limit           | 0                                      |
| rpl_recovery_rank               | 0                                      |
| secure_auth                     | OFF                                    |
| secure_file_priv                |                                        |
| server_id                       | 0                                      |
| skip_external_locking           | ON                                     |
| skip_networking                 | ON                                     |
| skip_show_database              | OFF                                    |
| slave_compressed_protocol       | OFF                                    |
| slave_load_tmpdir               | /backup/tmp/                           |
| slave_net_timeout               | 3600                                   |
| slave_skip_errors               | OFF                                    |
| slave_transaction_retries       | 10                                     |
| slow_launch_time                | 2                                      |
| socket                          | /var/lib/mysql/mysql.sock              |
| sort_buffer_size                | 2097152                                |
| sql_big_selects                 | ON                                     |
| sql_mode                        |                                        |
| sql_notes                       | ON                                     |
| sql_warnings                    | OFF                                    |
| ssl_ca                          |                                        |
| ssl_capath                      |                                        |
| ssl_cert                        |                                        |
| ssl_cipher                      |                                        |
| ssl_key                         |                                        |
| storage_engine                  | MyISAM                                 |
| sync_binlog                     | 0                                      |
| sync_frm                        | ON                                     |
| system_time_zone                | WET                                    |
| table_cache                     | 4000                                   |
| table_lock_wait_timeout         | 30                                     |
| table_type                      | MyISAM                                 |
| thread_cache_size               | 384                                    |
| thread_stack                    | 262144                                 |
| time_format                     | %H:%i:%s                               |
| time_zone                       | SYSTEM                                 |
| timed_mutexes                   | OFF                                    |
| tmp_table_size                  | 67108864                               |
| tmpdir                          | /backup/tmp                            |
| transaction_alloc_block_size    | 8192                                   |
| transaction_prealloc_size       | 4096                                   |
| tx_isolation                    | REPEATABLE-READ                        |
| updatable_views_with_limit      | YES                                    |
| version                         | 5.0.81-community                       |
| version_comment                 | MySQL Community Edition (GPL)          |
| version_compile_machine         | x86_64                                 |
| version_compile_os              | unknown-linux-gnu                      |
| wait_timeout                    | 10                                     |
+---------------------------------+----------------------------------------+
+-----------------------------------+-------------+
| Variable_name                     | Value       |
+-----------------------------------+-------------+
| Aborted_clients                   | 417         |
| Aborted_connects                  | 857         |
| Binlog_cache_disk_use             | 0           |
| Binlog_cache_use                  | 0           |
| Bytes_received                    | 754507455   |
| Bytes_sent                        | 53648413489 |
| Com_admin_commands                | 121         |
| Com_alter_db                      | 0           |
| Com_alter_table                   | 0           |
| Com_analyze                       | 0           |
| Com_backup_table                  | 0           |
| Com_begin                         | 0           |
| Com_call_procedure                | 0           |
| Com_change_db                     | 370119      |
| Com_change_master                 | 0           |
| Com_check                         | 0           |
| Com_checksum                      | 0           |
| Com_commit                        | 0           |
| Com_create_db                     | 0           |
| Com_create_function               | 0           |
| Com_create_index                  | 0           |
| Com_create_table                  | 2           |
| Com_create_user                   | 0           |
| Com_dealloc_sql                   | 0           |
| Com_delete                        | 636         |
| Com_delete_multi                  | 0           |
| Com_do                            | 0           |
| Com_drop_db                       | 0           |
| Com_drop_function                 | 0           |
| Com_drop_index                    | 0           |
| Com_drop_table                    | 2           |
| 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                        | 64656       |
| Com_insert_select                 | 2           |
| Com_kill                          | 0           |
| Com_load                          | 0           |
| Com_load_master_data              | 0           |
| Com_load_master_table             | 0           |
| Com_lock_tables                   | 105         |
| Com_optimize                      | 0           |
| Com_preload_keys                  | 0           |
| Com_prepare_sql                   | 0           |
| Com_purge                         | 0           |
| Com_purge_before_date             | 0           |
| Com_rename_table                  | 0           |
| Com_repair                        | 0           |
| Com_replace                       | 1082        |
| Com_replace_select                | 0           |
| Com_reset                         | 0           |
| Com_restore_table                 | 0           |
| Com_revoke                        | 0           |
| Com_revoke_all                    | 0           |
| Com_rollback                      | 0           |
| Com_savepoint                     | 0           |
| Com_select                        | 979114      |
| Com_set_option                    | 116         |
| Com_show_binlog_events            | 0           |
| Com_show_binlogs                  | 0           |
| Com_show_charsets                 | 0           |
| Com_show_collations               | 0           |
| Com_show_column_types             | 0           |
| Com_show_create_db                | 0           |
| Com_show_create_table             | 0           |
| Com_show_databases                | 0           |
| Com_show_errors                   | 0           |
| Com_show_fields                   | 1           |
| Com_show_grants                   | 0           |
| Com_show_innodb_status            | 0           |
| Com_show_keys                     | 0           |
| Com_show_logs                     | 0           |
| Com_show_master_status            | 0           |
| Com_show_ndb_status               | 0           |
| Com_show_new_master               | 0           |
| Com_show_open_tables              | 0           |
| Com_show_privileges               | 0           |
| Com_show_processlist              | 34          |
| Com_show_slave_hosts              | 0           |
| Com_show_slave_status             | 0           |
| Com_show_status                   | 19          |
| Com_show_storage_engines          | 0           |
| Com_show_tables                   | 0           |
| Com_show_triggers                 | 0           |
| Com_show_variables                | 1           |
| Com_show_warnings                 | 0           |
| Com_slave_start                   | 0           |
| Com_slave_stop                    | 0           |
| Com_stmt_close                    | 0           |
| Com_stmt_execute                  | 0           |
| Com_stmt_fetch                    | 0           |
| Com_stmt_prepare                  | 0           |
| Com_stmt_reset                    | 0           |
| Com_stmt_send_long_data           | 0           |
| Com_truncate                      | 2           |
| Com_unlock_tables                 | 105         |
| Com_update                        | 215955      |
| Com_update_multi                  | 2           |
| Com_xa_commit                     | 0           |
| Com_xa_end                        | 0           |
| Com_xa_prepare                    | 0           |
| Com_xa_recover                    | 0           |
| Com_xa_rollback                   | 0           |
| Com_xa_start                      | 0           |
| Compression                       | OFF         |
| Connections                       | 261731      |
| Created_tmp_disk_tables           | 195         |
| Created_tmp_files                 | 15805       |
| Created_tmp_tables                | 471588      |
| Delayed_errors                    | 0           |
| Delayed_insert_threads            | 0           |
| Delayed_writes                    | 0           |
| Flush_commands                    | 1           |
| Handler_commit                    | 0           |
| Handler_delete                    | 1219        |
| Handler_discover                  | 0           |
| Handler_prepare                   | 0           |
| Handler_read_first                | 62192       |
| Handler_read_key                  | 369984098   |
| Handler_read_next                 | 206328090   |
| Handler_read_prev                 | 147978      |
| Handler_read_rnd                  | 360641788   |
| Handler_read_rnd_next             | 1246051665  |
| Handler_rollback                  | 0           |
| Handler_savepoint                 | 0           |
| Handler_savepoint_rollback        | 0           |
| Handler_update                    | 210768      |
| Handler_write                     | 457752956   |
| Innodb_buffer_pool_pages_data     | 20          |
| Innodb_buffer_pool_pages_dirty    | 0           |
| Innodb_buffer_pool_pages_flushed  | 0           |
| Innodb_buffer_pool_pages_free     | 492         |
| Innodb_buffer_pool_pages_misc     | 0           |
| Innodb_buffer_pool_pages_total    | 512         |
| Innodb_buffer_pool_read_ahead_rnd | 1           |
| Innodb_buffer_pool_read_ahead_seq | 0           |
| Innodb_buffer_pool_read_requests  | 240         |
| Innodb_buffer_pool_reads          | 13          |
| Innodb_buffer_pool_wait_free      | 0           |
| Innodb_buffer_pool_write_requests | 0           |
| Innodb_data_fsyncs                | 3           |
| Innodb_data_pending_fsyncs        | 0           |
| Innodb_data_pending_reads         | 0           |
| Innodb_data_pending_writes        | 0           |
| Innodb_data_read                  | 2510848     |
| Innodb_data_reads                 | 26          |
| Innodb_data_writes                | 3           |
| Innodb_data_written               | 1536        |
| Innodb_dblwr_pages_written        | 0           |
| Innodb_dblwr_writes               | 0           |
| Innodb_log_waits                  | 0           |
| Innodb_log_write_requests         | 0           |
| Innodb_log_writes                 | 1           |
| Innodb_os_log_fsyncs              | 3           |
| Innodb_os_log_pending_fsyncs      | 0           |
| Innodb_os_log_pending_writes      | 0           |
| Innodb_os_log_written             | 512         |
| Innodb_page_size                  | 16384       |
| Innodb_pages_created              | 0           |
| Innodb_pages_read                 | 20          |
| Innodb_pages_written              | 0           |
| Innodb_row_lock_current_waits     | 0           |
| Innodb_row_lock_time              | 0           |
| Innodb_row_lock_time_avg          | 0           |
| Innodb_row_lock_time_max          | 0           |
| Innodb_row_lock_waits             | 0           |
| Innodb_rows_deleted               | 0           |
| Innodb_rows_inserted              | 0           |
| Innodb_rows_read                  | 0           |
| Innodb_rows_updated               | 0           |
| Key_blocks_not_flushed            | 0           |
| Key_blocks_unused                 | 586088      |
| Key_blocks_used                   | 60630       |
| Key_read_requests                 | 1497402980  |
| Key_reads                         | 60631       |
| Key_write_requests                | 67819       |
| Key_writes                        | 61185       |
| Last_query_cost                   | 0.000000    |
| Max_used_connections              | 150         |
| Ndb_cluster_node_id               | 0           |
| Ndb_config_from_host              |             |
| Ndb_config_from_port              | 0           |
| Ndb_number_of_data_nodes          | 0           |
| Not_flushed_delayed_rows          | 0           |
| Open_files                        | 610         |
| Open_streams                      | 0           |
| Open_tables                       | 541         |
| Opened_tables                     | 555         |
| Prepared_stmt_count               | 0           |
| Qcache_free_blocks                | 3207        |
| Qcache_free_memory                | 89421984    |
| Qcache_hits                       | 1699759     |
| Qcache_inserts                    | 496964      |
| Qcache_lowmem_prunes              | 0           |
| Qcache_not_cached                 | 482029      |
| Qcache_queries_in_cache           | 5104        |
| Qcache_total_blocks               | 14435       |
| Queries                           | 3599589     |
| Questions                         | 3599589     |
| Rpl_status                        | NULL        |
| Select_full_join                  | 5           |
| Select_full_range_join            | 0           |
| Select_range                      | 209756      |
| Select_range_check                | 0           |
| Select_scan                       | 99865       |
| Slave_open_temp_tables            | 0           |
| Slave_retried_transactions        | 0           |
| Slave_running                     | OFF         |
| Slow_launch_threads               | 0           |
| Slow_queries                      | 0           |
| Sort_merge_passes                 | 7900        |
| Sort_range                        | 169255      |
| Sort_rows                         | 420213794   |
| Sort_scan                         | 441396      |
| Table_locks_immediate             | 1949936     |
| Table_locks_waited                | 76867       |
| Tc_log_max_pages_used             | 0           |
| Tc_log_page_size                  | 0           |
| Tc_log_page_waits                 | 0           |
| Threads_cached                    | 110         |
| Threads_connected                 | 40          |
| Threads_created                   | 150         |
| Threads_running                   | 33          |
| Uptime                            | 4895        |
| Uptime_since_flush_status         | 3599589     |
+-----------------------------------+-------------+
Uptime: 4895  Threads: 40  Questions: 3599591  Slow queries: 0  Opens: 555  Flush tables: 1  Open tables: 541  Queries per second avg: 735.361
mysqladmin  Ver 8.41 Distrib 5.0.81, for unknown-linux-gnu on x86_64
Copyright (C) 2000-2006 MySQL AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Server version 5.0.81-community
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 1 hour 21 min 35 sec

Threads: 40  Questions: 3599591  Slow queries: 0  Opens: 555  Flush tables: 1  Open tables: 541  Queries per second avg: 735.361
+--------+---------------+-----------+---------------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
| Id     | User          | Host      | db            | Command | Time | State                | Info                                                                                                 |
+--------+---------------+-----------+---------------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
| 236320 | root          | localhost |               | Sleep   | 497  |                      |                                                                                                      |
| 261340 | forum_games | localhost | forum_games | Sleep   | 7    |                      |                                                                                                      |
| 261508 | eximstats     | localhost | eximstats     | Sleep   | 3    |                      |                                                                                                      |
| 261512 | forum_games | localhost | forum_games | Sleep   | 3    |                      |                                                                                                      |
| 261591 | forum_vb      | localhost | forum_vb      | Query   | 0    | Locked               | UPDATE session
SET lastactivity = 1250873530, inforum = 0, inthread = 0, incalendar = 0, badloc |
| 261636 | forum_vb      | localhost | forum_vb      | Query   | 0    | Locked               | UPDATE session
SET lastactivity = 1250873531, inforum = 0, inthread = 0, incalendar = 0, badloc |
| 261647 | forum_vb      | localhost | forum_vb      | Query   | 0    | Locked               | UPDATE session
SET lastactivity = 1250873531, inforum = 0, inthread = 0, incalendar = 0, badloc |
| 261650 | forum_vb      | localhost | forum_vb      | Query   | 0    | Locked               | UPDATE session
SET lastactivity = 1250873531, incalendar = 0, badlocation = 0, useragent = 'Moz |
| 261654 | forum_vb      | localhost | forum_vb      | Query   | 0    | Locked               | UPDATE session
SET lastactivity = 1250873531, location = '/index.php', inforum = 0, inthread =  |
| 261655 | forum_vb      | localhost | forum_vb      | Query   | 0    | Locked               | UPDATE session
SET lastactivity = 1250873531, location = '/index.php', inforum = 0, inthread =  |
| 261657 | forum_vb      | localhost | forum_vb      | Query   | 0    | Locked               | INSERT IGNORE INTO session
(sessionhash, userid, host, idhash, lastactivity, location, styleid, |
| 261658 | forum_vb      | localhost | forum_vb      | Query   | 0    | Locked               | UPDATE session
SET lastactivity = 1250873531, incalendar = 0, badlocation = 0
WHERE sessio |
| 261662 | forum_vb      | localhost | forum_vb      | Sleep   | 0    |                      |                                                                                                      |
| 261669 | forum_vb      | localhost | forum_vb      | Query   | 0    | Locked               | INSERT IGNORE INTO session
(sessionhash, userid, host, idhash, lastactivity, location, styleid, |
| 261670 | forum_vb      | localhost | forum_vb      | Query   | 0    | Copying to tmp table | SELECT
user.username, (user.options & 512) AS invisible, user.usergroupid,
session.userid, ses |
| 261674 | forum_vb      | localhost | forum_vb      | Query   | 0    | Sorting result       | SELECT
user.username, (user.options & 512) AS invisible, user.usergroupid,
session.userid, ses |
| 261678 | forum_vb      | localhost | forum_vb      | Query   | 0    | Locked               | SELECT *
FROM session
WHERE sessionhash = '4df0bc8bd7cc93044a69fdccd988a31b'
AND lastac |
| 261679 | forum_vb      | localhost | forum_vb      | Query   | 0    | Copying to tmp table | SELECT
user.username, (user.options & 512) AS invisible, user.usergroupid,
session.userid, ses |
| 261680 | forum_vb      | localhost | forum_vb      | Query   | 0    | Locked               | SELECT *
FROM session
WHERE sessionhash = '4775661818b9c79e230a8d23f9f7f15b'
AND lastac |
| 261683 | forum_vb      | localhost | forum_vb      | Query   | 0    | Copying to tmp table | SELECT
user.username, (user.options & 512) AS invisible, user.usergroupid,
session.userid, ses |
| 261684 | forum_vb      | localhost | forum_vb      | Query   | 0    | Locked               | SELECT *
FROM session
WHERE userid = 0
AND host = '196.202.94.42'
AND idhash = '35 |
| 261685 | forum_vb      | localhost | forum_vb      | Query   | 0    | Locked               | SELECT *
FROM session
WHERE userid = 0
AND host = '195.229.235.37'
AND idhash = 'b |
| 261686 | forum_vb      | localhost | forum_vb      | Query   | 0    | Locked               | SELECT *
FROM session
WHERE userid = 0
AND host = '212.118.126.253'
AND idhash = ' |
| 261687 | forum_vb      | localhost | forum_vb      | Query   | 0    | Locked               | SELECT *
FROM session
WHERE sessionhash = '9556d1a20320e7b9ba9e37daa86a798f'
AND lastac |
| 261689 | forum_vb      | localhost | forum_vb      | Query   | 0    | Locked               | SELECT *
FROM session
WHERE userid = 0
AND host = '41.238.235.115'
AND idhash = 'a |
| 261691 | forum_vb      | localhost | forum_vb      | Query   | 0    | Locked               | SELECT *
FROM session
WHERE userid = 0
AND host = '188.161.238.68'
AND idhash = '7 |
| 261693 | forum_vb      | localhost | forum_vb      | Query   | 0    | Locked               | SELECT *
FROM session
WHERE sessionhash = '65ed78267b2d8695b6a7634bbfc7bd1e'
AND lastac |
| 261696 | forum_vb      | localhost | forum_vb      | Query   | 0    | Locked               | SELECT *
FROM session
WHERE sessionhash = 'fc611b0f316fa037c1e22358a46ac240'
AND lastac |
| 261702 | forum_vb      | localhost | forum_vb      | Query   | 0    | Locked               | SELECT *
FROM session
WHERE sessionhash = 'd293cd5ea27884b8c65b323fb26d5652'
AND lastac |
| 261709 | forum_vb      | localhost | forum_vb      | Query   | 0    | Locked               | SELECT *
FROM session
WHERE userid = 0
AND host = '188.49.108.246'
AND idhash = '8 |
| 261710 | forum_vb      | localhost | forum_vb      | Query   | 0    | Locked               | SELECT *
FROM session
WHERE sessionhash = '950f102a42d660c6e178844870dbcd8a'
AND lastac |
| 261715 | forum_vb      | localhost | forum_vb      | Query   | 0    | Locked               | SELECT *
FROM session
WHERE sessionhash = '97078d2d2385f4c3fb8e13bc8a5bbbfb'
AND lastac |
| 261716 | forum_vb      | localhost | forum_vb      | Query   | 0    | Locked               | SELECT *
FROM session
WHERE sessionhash = '98381e7a71ec379ccf1cfd1ff00676ab'
AND lastac |
| 261719 | forum_vb      | localhost | forum_vb      | Query   | 0    | Locked               | SELECT *
FROM session
WHERE sessionhash = '8cb7f044b93894b08b1a0b3f44f7d973'
AND lastac |
| 261720 | forum_vb      | localhost | forum_vb      | Query   | 0    | Locked               | SELECT *
FROM session
WHERE sessionhash = '70b82bc073290a5518fb36354eeb427c'
AND lastac |
| 261722 | forum_vb      | localhost | forum_vb      | Query   | 0    | Locked               | SELECT *
FROM session
WHERE sessionhash = 'bd3f2491020bc934968407857fe84ee4'
AND lastac |
| 261725 | forum_vb      | localhost | forum_vb      | Sleep   | 0    |                      |                                                                                                      |
| 261727 | forum_vb      | localhost | forum_vb      | Query   | 0    | Locked               | SELECT *
FROM session
WHERE userid = 0
AND host = '94.96.193.37'
AND idhash = '9f6 |
| 261728 | forum_games | localhost | forum_games | Sleep   | 0    |                      |                                                                                                      |
| 261730 | root          | localhost |               | Query   | 0    |                      | show processlist                                                                                     |
+--------+---------------+-----------+---------------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
[/CODE]
9- vb and other 3 small scripts
10- on timeout 900 I have 2500 to 3800 users online in real time
11-phpinfo attached
12-apache prefork values :
[CODE]
DeflateCompressionLevel 6
KeepAlive On
MaxKeepAliveRequests 80
KeepAliveTimeout 6
HostnameLookups Off
Timeout 7
StartServers 15
MinSpareServers 5
MaxSpareServers 20
ServerLimit 1024
MaxClients 1024
MaxRequestsPerChild 10000
[/CODE]
13- find / -size +2000000k :

/home/forum/tmp/analog/cache
14-
uname -a :
[CODE]
Linux server.server.com 2.6.18-128.1.10.el5 #1 SMP Thu May 7 10:35:59 EDT 2009 x86_64 x86_64 x86_64 GNU/Linux
[/CODE]
ulimit -aH :
[CODE]
core file size          (blocks, -c) 1000000
data seg size           (kbytes, -d) unlimited
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 202752
max locked memory       (kbytes, -l) 32
max memory size         (kbytes, -m) unlimited
open files                      (-n) 4096
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 8192
cpu time               (seconds, -t) unlimited
max user processes              (-u) 14335
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited
[/CODE]
cat /proc/cpuinfo :
[CODE]

processor       : 0
vendor_id       : GenuineIntel
cpu family      : 6
model           : 23
model name      : Intel(R) Xeon(R) CPU           E5430  @ 2.66GHz
stepping        : 6
cpu MHz         : 2666.816
cache size      : 6144 KB
physical id     : 0
siblings        : 4
core id         : 0
cpu cores       : 4
apicid          : 0
fpu             : yes
fpu_exception   : yes
cpuid level     : 10
wp              : yes
flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx lm constant_tsc pni monitor ds_cpl vmx est tm2 cx16 xtpr lahf_lm
bogomips        : 5335.66
clflush size    : 64
cache_alignment : 64
address sizes   : 38 bits physical, 48 bits virtual
power management:

processor       : 1
vendor_id       : GenuineIntel
cpu family      : 6
model           : 23
model name      : Intel(R) Xeon(R) CPU           E5430  @ 2.66GHz
stepping        : 6
cpu MHz         : 2666.816
cache size      : 6144 KB
physical id     : 1
siblings        : 4
core id         : 0
cpu cores       : 4
apicid          : 4
fpu             : yes
fpu_exception   : yes
cpuid level     : 10
wp              : yes
flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx lm constant_tsc pni monitor ds_cpl vmx est tm2 cx16 xtpr lahf_lm
bogomips        : 5332.78
clflush size    : 64
cache_alignment : 64
address sizes   : 38 bits physical, 48 bits virtual
power management:

processor       : 2
vendor_id       : GenuineIntel
cpu family      : 6
model           : 23
model name      : Intel(R) Xeon(R) CPU           E5430  @ 2.66GHz
stepping        : 6
cpu MHz         : 2666.816
cache size      : 6144 KB
physical id     : 0
siblings        : 4
core id         : 1
cpu cores       : 4
apicid          : 1
fpu             : yes
fpu_exception   : yes
cpuid level     : 10
wp              : yes
flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx lm constant_tsc pni monitor ds_cpl vmx est tm2 cx16 xtpr lahf_lm
bogomips        : 5332.85
clflush size    : 64
cache_alignment : 64
address sizes   : 38 bits physical, 48 bits virtual
power management:

processor       : 3
vendor_id       : GenuineIntel
cpu family      : 6
model           : 23
model name      : Intel(R) Xeon(R) CPU           E5430  @ 2.66GHz
stepping        : 6
cpu MHz         : 2666.816
cache size      : 6144 KB
physical id     : 1
siblings        : 4
core id         : 1
cpu cores       : 4
apicid          : 5
fpu             : yes
fpu_exception   : yes
cpuid level     : 10
wp              : yes
flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx lm constant_tsc pni monitor ds_cpl vmx est tm2 cx16 xtpr lahf_lm
bogomips        : 5332.79
clflush size    : 64
cache_alignment : 64
address sizes   : 38 bits physical, 48 bits virtual
power management:

processor       : 4
vendor_id       : GenuineIntel
cpu family      : 6
model           : 23
model name      : Intel(R) Xeon(R) CPU           E5430  @ 2.66GHz
stepping        : 6
cpu MHz         : 2666.816
cache size      : 6144 KB
physical id     : 0
siblings        : 4
core id         : 2
cpu cores       : 4
apicid          : 2
fpu             : yes
fpu_exception   : yes
cpuid level     : 10
wp              : yes
flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx lm constant_tsc pni monitor ds_cpl vmx est tm2 cx16 xtpr lahf_lm
bogomips        : 5332.82
clflush size    : 64
cache_alignment : 64
address sizes   : 38 bits physical, 48 bits virtual
power management:

processor       : 5
vendor_id       : GenuineIntel
cpu family      : 6
model           : 23
model name      : Intel(R) Xeon(R) CPU           E5430  @ 2.66GHz
stepping        : 6
cpu MHz         : 2666.816
cache size      : 6144 KB
physical id     : 1
siblings        : 4
core id         : 2
cpu cores       : 4
apicid          : 6
fpu             : yes
fpu_exception   : yes
cpuid level     : 10
wp              : yes
flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx lm constant_tsc pni monitor ds_cpl vmx est tm2 cx16 xtpr lahf_lm
bogomips        : 5332.78
clflush size    : 64
cache_alignment : 64
address sizes   : 38 bits physical, 48 bits virtual
power management:

processor       : 6
vendor_id       : GenuineIntel
cpu family      : 6
model           : 23
model name      : Intel(R) Xeon(R) CPU           E5430  @ 2.66GHz
stepping        : 6
cpu MHz         : 2666.816
cache size      : 6144 KB
physical id     : 0
siblings        : 4
core id         : 3
cpu cores       : 4
apicid          : 3
fpu             : yes
fpu_exception   : yes
cpuid level     : 10
wp              : yes
flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx lm constant_tsc pni monitor ds_cpl vmx est tm2 cx16 xtpr lahf_lm
bogomips        : 5332.75
clflush size    : 64
cache_alignment : 64
address sizes   : 38 bits physical, 48 bits virtual
power management:

processor       : 7
vendor_id       : GenuineIntel
cpu family      : 6
model           : 23
model name      : Intel(R) Xeon(R) CPU           E5430  @ 2.66GHz
stepping        : 6
cpu MHz         : 2666.816
cache size      : 6144 KB
physical id     : 1
siblings        : 4
core id         : 3
cpu cores       : 4
apicid          : 7
fpu             : yes
fpu_exception   : yes
cpuid level     : 10
wp              : yes
flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx lm constant_tsc pni monitor ds_cpl vmx est tm2 cx16 xtpr lahf_lm
bogomips        : 5332.77
clflush size    : 64
cache_alignment : 64
address sizes   : 38 bits physical, 48 bits virtual
power management:
[/CODE]

This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.

Subscribe now for full access to Experts Exchange and get

Instant Access to this Solution

  • Plus...
  • 30 Day FREE access, no risk, no obligation
  • Collaborate with the world's top tech experts
  • Unlimited access to our exclusive solution database
  • Never be left without tech help again

Subscribe Now

Asked On
2009-08-21 at 11:04:56ID24672191
Tags

mysql

,

high load

,

optimization

,

performance

,

vBulletin

Topics

MySQL Server

,

Web Hosting

,

Apache Web Server

Participating Experts
2
Points
500
Comments
18

Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.

  • "The time we save is the biggest benefit of Experts Exchange to Warner Bros. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange." Mike Kapnisakis, Warner Bros.
  • "Our team likes having a resource that is more secure than just using Google and most experts using this service really know their stuff. It's nice to look here first versus using Google." Dayna Sellner, Lockheed Martin
  • "Anytime that I've been stumped with a problem, 9 out of 10 times Experts Exchange has either the accepted solution or an open discussion of the potential solution to the problem." Kenny Red, eBay Inc.

See what Experts Exchange can do for you.

Got a question?

We've got the answer.

Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.

Screenshot of Experts Exchange Knowledgebase

Need individual assistance?

Our experts are ready to help.

If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.

Screenshot of Experts Exchange Knowledgebase

Want to learn from the best?

Read articles from industry experts.

Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.

Screenshot of an Article

Working on a long term project?

Store your work and research.

Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.

Screenshot of Experts Exchange Knowledgebase

Access the answers to your technology questions today.

Subscribe Now

30-day free trial. Register in 60 seconds.

What Makes Experts Exchange Unique?

Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.

Trusted by the world's most respected brands.

image of each brand's logo

Faithfully serving IT professionals since 1996.

Experts Exchange Logo

Try it out and discover for yourself.

Subscribe Now

30-day free trial. Register in 60 seconds.

Related Solutions

  1. Database Ranking
    Does any body know which one has high performance? Access,MySQL,Oracle,MSSQL Can you rank from the highest performance to lowest performance? I'm not sure which database is the best and the worst
  2. Ranking?
    I have an array of numbers that I want to rank in ascending sequence. I'm new to Delphi and can't find the rank command or equivalent?
  3. How to create a MySQL view for wp_users and wp_user…
    Does anyone have an idea on how I can create a view in MySQL off a WordPress database so I can query wp_users and get a recordset back with all the wp_usermeta data as well? If you are not familiar, wp_users contains the main record and wp_usermeta contains extra information ...
  4. how to delete all posts in wp Mysql database
    I want to delete all posts and related objects in a Wordpress database(mysql) programmatically. There are plugins that accomplish this with user intervention I would like this to be done automatically. It is easy to create a post using wp_insert_posts but not for deleting th...

Free Tech Articles

  1. WARNING: 5 Reasons why you should NEVER fix a computer for free.
    It is in our nature to love the puzzle. We are obsessed. The lot of us. We love puzzles. We love the challenge. We thrive on finding the answer. We hate disarray. It bothers us deep in our soul. W...
  2. SCCM OSD Basic troubleshooting
    SCCM 2007 OSD is a fantastic way to deploy operating systems, however, like most things SCCM issues can sometimes be difficult to resolve due to the sheer volume of logs to sift through and the dispe...
  3. Migrate Small Business Server 2003 to Exchange 2010 and Windows 2008 R2
    This guide is intended to provide step by step instructions on how to migrate from Small Business Server 2003 to Windows 2008 R2 with Exchange 2010. For this migration to work you will need the fo...
  4. Create a Win7 Gadget
    This article shows you how to create a simple "Gadget" -- a sort of mini-application supported by Windows 7 and Vista. Gadgets can be dropped anywhere on the desktop to provide instant information, ...
  5. Outlook continually prompting for username and password
    There have been a lot of questions recently regarding Outlook prompting for a username and password whilst using Exchange 2007. There are a few reasons why this would happen and I will try to cover t...
  6. Backup Exchange 2010 Information Store using Windows Backup
    There seems to be quite a lot of confusion around the ability to backup Exchange 2010 using the built in Windows Backup feature. This stems from the omission of this feature prior to Exchange 2007 s...

Cloud Class Webinars

  1. Avoiding Bugs in Microsoft Access
    Alison Balter takes and in-depth look at avoiding bugs in Access. In this webinar you will learn about using the immediate window to debug your applications, invoking the debugger, using breakpoints to troubleshoot, stepping through code, setting the next statement to execute, ...
  2. Top 10 Best New Features in Visio 2010
    Scott Helmers gives live demonstrations of the top 10 new features in Visio 2010. This webinar will teach you how to create compelling diagrams by adding shapes to the page with a single click, linking the shapes in a diagram to data in Excel (or SQL Server, or SharePoint), ...
  3. IT Consultant Business Secrets Revealed
    Michael Munger, Experts Exchange tech pro and IT consultant, pulls back the curtain on his very successful businesses and answers question on every IT consultant and business owner should know about. He shares secrets on what he did to solve the 5 most common problems in IT, ...
  4. Disaster Recovery and Business Continuity
    Quest CTO, Mike Billon, gives an overview of the steps involved in building a dunamic disaster recovery plan. Through case studies and an examination of software/hardware tooles for monitoring and testing, you'll gain a better understandin of where you are, where you want ...
  5. Organize Your Visio Diagrams with Containers and Lists
    Scott Helmers uses cross functional flowcharts, wireframe diagrams, data graphic legends and seating charts to teach you: how to ustilize all three new structured diagram components in Visio 2010, the best practices for organizeing shapes in previous version of Visio, how to organize ...
  6. How to Us Objects, Properties, Events and Methods in Microsoft Access
    Alison Dalter gives an in-depbth look at objects, properties, events and methods in Microsoft Access. In this webinar you will learn about using the object browser, referring to objects, working with properties and methods, working with object variables, understanding the ...

Join the Community

Give a Little. Get a Lot.

Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.

Join the Community

Answers

 

by: rstaveleyPosted on 2009-08-21 at 12:13:41ID: 25154877

There are a lot of locked queries.

 

by: rstaveleyPosted on 2009-08-21 at 12:21:39ID: 25154943

Looks to me like the smoking gun is the query...

SELECT user.username, (user.options & 512) AS invisible, user.usergroupid, session.userid, ses...

...which is "Copying to tmp table", and therefore presumably applying a lock to the session table.

I guess that's also the cause of the CPU guzzling. Could be worth looking for that in your app.

 

by: fosiul01Posted on 2009-08-21 at 12:26:44ID: 25154999

download this tunner from this website
( i guess you already know about this but still )

http://www.day32.com/MySQL/

it is a bash script, run this script in your server, see if it can detect any settings as problematic

 

by: fosiul01Posted on 2009-08-21 at 12:30:11ID: 25155032

also use this command

ps -eo pcpu,pid,user,args | sort -r -k1 | less

to see which process is eating more cpu power ....

 

by: xserverxPosted on 2009-08-21 at 12:32:27ID: 25155053

THANK YOU

sh tuning-primer.sh give me this results :
I hope that you help to resolve this problem what I should to do to performance mysql :


        -- MYSQL PERFORMANCE TUNING PRIMER --
             - By: Matthew Montgomery -
 
MySQL Version 5.0.81-community x86_64
 
Uptime = 0 days 3 hrs 59 min 0 sec
Avg. qps = 460
Total Questions = 6599780
Threads Connected = 104
 
Warning: Server has not been running for at least 48hrs.
It may not be safe to use these recommendations
 
To find out more information on how each of these
runtime variables effects performance visit:
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html
Visit http://www.mysql.com/products/enterprise/advisors.html
for info about MySQL's Enterprise Monitoring and Advisory Service
 
SLOW QUERIES
The slow query log is NOT enabled.
Current long_query_time = 10 sec.
You have 0 out of 6600058 that take longer than 10 sec. to complete
Your long_query_time may be too high, I typically set this under 5 sec.
 
BINARY UPDATE LOG
The binary update log is NOT enabled.
You will not be able to do point in time recovery
See http://dev.mysql.com/doc/refman/5.0/en/point-in-time-recovery.html
 
WORKER THREADS
Current thread_cache_size = 384
Current threads_cached = 44
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine
 
MAX CONNECTIONS
Current max_connections = 1000
Current threads_connected = 106
Historic max_used_connections = 150
The number of used connections is 15% of the configured maximum.
Your max_connections variable seems to be fine.
 
MEMORY USAGE
Max Memory Ever Allocated : 1.61 G
Configured Max Per-thread Buffers : 4.88 G
Configured Max Global Buffers : 906 M
Configured Max Memory Limit : 5.76 G
Physical Memory : 23.54 G
Max memory limit seem to be within acceptable norms
 
KEY BUFFER
Current MyISAM index space = 1.03 G
Current key_buffer_size = 768 M
Key cache miss rate is 1 : 82480
Key buffer free ratio = 72 %
Your key_buffer_size seems to be too high.
Perhaps you can use these resources elsewhere
 
QUERY CACHE
Query cache is enabled
Current query_cache_size = 128 M
Current query_cache_used = 73 M
Current query_cache_limit = 1 M
Current Query cache Memory fill ratio = 57.14 %
Current query_cache_min_res_unit = 4 K
MySQL won't cache query results that are larger than query_cache_limit in size
 
SORT OPERATIONS
Current sort_buffer_size = 2 M
Current read_rnd_buffer_size = 768 K
Sort buffer seems to be fine
 
JOINS
Current join_buffer_size = 1.00 M
You have had 8 queries where a join could not use an index properly
You should enable "log-queries-not-using-indexes"
Then look for non indexed joins in the slow query log.
If you are unable to optimize your queries you may want to increase your
join_buffer_size to accommodate larger joins in one pass.
 
Note! This script will still suggest raising the join_buffer_size when
ANY joins not using indexes are found.
 
OPEN FILES LIMIT
Current open_files_limit = 9010 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
Your open_files_limit value seems to be fine
 
TABLE CACHE
Current table_cache value = 4000 tables
You have a total of 2244 tables
You have 2773 open tables.
The table_cache value seems to be fine
 
TEMP TABLES
Current max_heap_table_size = 64 M
Current tmp_table_size = 64 M
Of 513685 temp tables, 0% were created on disk
Created disk tmp tables ratio seems fine
 
TABLE SCANS
Current read_buffer_size = 1 M
Current table scan ratio = 4137 : 1
You have a high ratio of sequential access requests to SELECTs
You may benefit from raising read_buffer_size and/or improving your use of indexes.
 
TABLE LOCKING
Current Lock Wait ratio = 1 : 8
You may benefit from selective use of InnoDB.
If you have long running SELECT's against MyISAM tables and perform
frequent updates consider setting 'low_priority_updates=1'
                                              
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:

Select allOpen in new window

 

by: fosiul01Posted on 2009-08-21 at 12:49:02ID: 25155197

ommm i really like that tunning script, its gives  a good idea if you need to tunning anything,
according to this you just have this problem

JOINS
Current join_buffer_size = 1.00 M
You have had 8 queries where a join could not use an index properly
You should enable "log-queries-not-using-indexes"
Then look for non indexed joins in the slow query log.
If you are unable to optimize your queries you may want to increase your
join_buffer_size to accommodate larger joins in one pass.


try to enabled  "log-queries-not-using-indexes" , see how many queries is not using index and if there is any issue, its  busy server ...


2. YOur server is running out of Ram, so its starts to use Swap. and if a server starts to use swap partitions , its put pressure on Cpu and I/O


3. can you just if all your cpu sharing the loads or its just one cpu is taking all the load by

mpstat -P ALL

also use Sar command to check cpu activities


I am still in the process of learning mysql ... so i am trying to say theoritically .

but you should really think of Ram issue ..

 

by: xserverxPosted on 2009-08-21 at 13:03:15ID: 25155308

I am not work really out of memory

can you please give me the exact /etc/my.cnf ?
I don't understand what I can do 

Mem:  24689152k total, 21128292k used,  3560860k free,   867256k buffers
Swap: 32456344k total,      172k used, 32456172k free,  9724996k cached
                                              
1:
2:

Select allOpen in new window

 

by: fosiul01Posted on 2009-08-21 at 13:11:38ID: 25155381

the my.cnf you got according to that script its allright, i dont see any problem

except its saying to enabled  log-queries-not-using-indexes

enabled this one in my.cnf , give it some times, see if you see too much log for due to index
more info
http://dev.mysql.com/doc/refman/5.1/en/server-options.html#option_mysqld_log-queries-not-using-indexes

also, check

sar and mpstat report to see if cpu load distribution is correct

did this problem started recently or it was from before ??

 

by: fosiul01Posted on 2009-08-21 at 13:32:27ID: 25155549

one more thing

your  slow query log is NOT enabled. enabled slow log quaries along with  log-queries-not-using-indexes


those might give you an idea if there is any problem of queries or not

 

by: fosiul01Posted on 2009-08-21 at 13:33:43ID: 25155563

i will be off for 3 hours.
check all those enabled all those logs and check with then tunning script again read the result with that script , its self explainned
also check those sar and mpstat

in the mean time other EE might come out with other solution

 

by: xserverxPosted on 2009-08-21 at 14:04:50ID: 25155828

do you advice to switch session table to InnoDB engine  ?
what about innodb_buffer_pool_size setting ?

 

by: fosiul01Posted on 2009-08-21 at 15:48:38ID: 25156552

sorry just came back
You are not using innodb, so you dont have to think about innodb_buffer_pool_size
you accepted the solution
but what is the outcome ?? did you find the solution ??

 

by: xserverxPosted on 2009-08-21 at 17:06:17ID: 25156844

not yet I don't find solution yet :(

 

by: rstaveleyPosted on 2009-08-22 at 01:06:58ID: 25157865

I put that query with the copy to the temporary table into Google, and found something in Arabic, which had the attached query below in it.

This was a Database error in vBulletin 3.6.0 Beta 1. Assuming your application is a vBulletin version that's not much further on from this and your license is still good for support, I recommend you post your findings at http://www.vbulletin.com/contact.php or follow through at http://www.vbulletin.com/forum/.

However, if you are unsupported you might try running the attached query through explain (just prefix the query with "explain ") and show us the explain plan. We wouldn't expect to see the need for a temporary table for that query unless the user table didn't have userid as its 1y key(unlikely error) and/or the session table didn't have userid indexed (a more likely oversight) or that the session table doesn't have lastactivity indexed (another likely possibility).

If you get "MySQL Error : Unknown column 'infractiongroupid' in 'field list'" like the error in that Arabic site just try omitting that field from the SELECT - it might be something that has been removed or added to the schema with time.

If you find that it is going via a temporary table because session.userid isn't indexed, you can try:

 CREATE INDEX session_userid_ndx ON session (userid);

Similarly, if you find that it is going via a temporary table because session.lastactivity isn't indexed, you can try:

 CREATE INDEX session_lastactivity_ndx ON session (lastactivity);

Better if you can do this through the official vBulletin support channels though.

/* Taken from http://www.traidnt.net/vb/showthread.php?t=113735&page=199 */
 
SELECT
user.username, (user.options & 512) AS invisible, user.usergroupid,
session.userid, session.inforum, session.lastactivity,
IF(displaygroupid=0, user.usergroupid, displaygroupid) AS displaygroupid, infractiongroupid
FROM session AS session
LEFT JOIN user AS user ON(user.userid = session.userid)
WHERE session.lastactivity > 1170908118 /* Or some other timestamp in your case */
ORDER BY username ASC;

                                              
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:

Select allOpen in new window

 

by: rstaveleyPosted on 2009-08-22 at 01:15:11ID: 25157890

I notice you had a similar question at http:Q_24371966.html, which you abandoned. Has this been an ongoing problem since May?

 

by: rstaveleyPosted on 2009-08-22 at 01:33:56ID: 25157923

The advice at http:Q_24371966.html#24361388 and http:#25155197 to set log-queries-not-using-indexes (and repeatedly through this thread - follow fosuil01's link in http:#25155381) is wise and it should highlight your un-indexed queries. If you've got a messed up vBulletin schema, this should show you the issues.

Consider also installing a fresh copy vBulletin onto a local server as indicated at http://www.vbulletin.com/docs/html/ and do a schema comparison to see if anything has been messed up in your production database.

 

by: xserverxPosted on 2009-08-22 at 06:04:55ID: 25158559

my probelm in th session table only when I disable display logged in user in forum home on my board admin
Admin CP -> vBulletin Options -> Forums Home Page Options -> Display Logged in Users?

I have the load reduced to 2 or 3
is there some thing to do for optimize this session table ?

mysql> show processlist;
+--------+----------+-----------+----------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| Id     | User     | Host      | db       | Command | Time | State        | Info                                                                                                 |
+--------+----------+-----------+----------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| 821408 | forum_vb | localhost | forum_vb | Query   |    4 | Locked       | UPDATE session
                                        SET lastactivity = 1250945391, location = '/index.php', inforum = 0, inthread =  |
| 821414 | forum_vb | localhost | forum_vb | Query   |    4 | Locked       | UPDATE session
                                        SET lastactivity = 1250945391, location = '/index.php', inforum = 0, inthread =  |
| 821460 | forum_vb | localhost | forum_vb | Query   |    4 | Locked       | UPDATE session
                                        SET lastactivity = 1250945392, location = '/index.php', inforum = 0, inthread =  |
| 821474 | forum_vb | localhost | forum_vb | Query   |    4 | Locked       | UPDATE session
                                        SET lastactivity = 1250945392, location = '/showthread.php?t=18974', inthread =  |
| 821476 | forum_vb | localhost | forum_vb | Query   |    4 | Locked       | UPDATE session
                                        SET lastactivity = 1250945392, location = '/index.php', inforum = 0, inthread =  |
| 821492 | forum_vb | localhost | forum_vb | Query   |    4 | Locked       | UPDATE session
                                        SET lastactivity = 1250945393, location = '/index.php', inforum = 0, inthread =  |
| 821496 | forum_vb | localhost | forum_vb | Query   |    4 | Locked       | UPDATE session
                                        SET lastactivity = 1250945393, location = '/index.php', inforum = 0, inthread =  |
| 821526 | forum_vb | localhost | forum_vb | Query   |    4 | Locked       | UPDATE session
                                        SET lastactivity = 1250945393, location = '/forumdisplay.php?f=15', inforum = 15 |
| 821532 | forum_vb | localhost | forum_vb | Query   |    4 | Locked       | UPDATE session
                                        SET lastactivity = 1250945394, location = '/showthread.php?t=56880', inforum = 7 |
| 821538 | forum_vb | localhost | forum_vb | Query   |    4 | Locked       | UPDATE session
                                        SET lastactivity = 1250945394, location = '/index.php', inforum = 0, inthread =  |
| 821543 | forum_vb | localhost | forum_vb | Query   |    3 | Locked       | UPDATE session
                                        SET lastactivity = 1250945394, location = '/index.php', inforum = 0, inthread =  |
| 821545 | forum_vb | localhost | forum_vb | Query   |    4 | Locked       | UPDATE session
                                        SET lastactivity = 1250945394, location = '/index.php', inforum = 0, inthread =  |
| 821549 | forum_vb | localhost | forum_vb | Query   |    4 | Locked       | UPDATE session
                                        SET lastactivity = 1250945394, inforum = 0, inthread = 0, incalendar = 0, badloc |
| 821551 | forum_vb | localhost | forum_vb | Query   |    4 | Locked       | UPDATE session
                                        SET lastactivity = 1250945394, incalendar = 0, badlocation = 0, useragent = 'Moz |
| 821553 | forum_vb | localhost | forum_vb | Query   |    4 | Locked       | UPDATE session
                                        SET lastactivity = 1250945394, incalendar = 0, badlocation = 0, useragent = 'Moz |
| 821558 | forum_vb | localhost | forum_vb | Query   |    4 | Locked       | INSERT IGNORE INTO session
                                        (sessionhash, userid, host, idhash, lastactivity, location, styleid, |
| 821562 | forum_vb | localhost | forum_vb | Query   |    4 | Locked       | UPDATE session
                                        SET lastactivity = 1250945395, location = '/showthread.php?t=21520', inthread =  |
| 821563 | forum_vb | localhost | forum_vb | Query   |    4 | Locked       | UPDATE session
                                        SET lastactivity = 1250945395, inforum = 0, inthread = 0, incalendar = 0, badloc |
| 821568 | forum_vb | localhost | forum_vb | Query   |    4 | Locked       | UPDATE session
                                        SET lastactivity = 1250945395, location = '/editpost.php?do=editpost&amp;p=11584 |
| 821573 | forum_vb | localhost | forum_vb | Query   |    4 | Locked       | UPDATE session
                                        SET lastactivity = 1250945395, incalendar = 0, badlocation = 0, useragent = 'Moz |
| 821578 | forum_vb | localhost | forum_vb | Query   |    4 | Locked       | UPDATE session
                                        SET lastactivity = 1250945395, location = '/showthread.php?t=8795', inthread = 8 |
| 821588 | forum_vb | localhost | forum_vb | Query   |    4 | Locked       | INSERT IGNORE INTO session
                                        (sessionhash, userid, host, idhash, lastactivity, location, styleid, |
| 821591 | forum_vb | localhost | forum_vb | Query   |    3 | Locked       | UPDATE session
                                        SET lastactivity = 1250945396, location = '/index.php', inforum = 0, inthread =  |
| 821593 | forum_vb | localhost | forum_vb | Query   |    3 | Locked       | UPDATE session
                                        SET lastactivity = 1250945396, location = '/index.php', inforum = 0, inthread =  |
| 821597 | forum_vb | localhost | forum_vb | Query   |    4 | Locked       | INSERT IGNORE INTO session
                                        (sessionhash, userid, host, idhash, lastactivity, location, styleid, |
| 821598 | forum_vb | localhost | forum_vb | Query   |    3 | Locked       | UPDATE session
                                        SET lastactivity = 1250945396, location = '/index.php', inforum = 0, inthread =  |
| 821601 | forum_vb | localhost | forum_vb | Query   |    3 | Locked       | UPDATE session
                                        SET lastactivity = 1250945396, location = '/index.php', inforum = 0, inthread =  |
| 821603 | forum_vb | localhost | forum_vb | Query   |    3 | Locked       | UPDATE session
                                        SET lastactivity = 1250945396, location = '/showthread.php?t=3017', inforum = 59 |
| 821604 | forum_vb | localhost | forum_vb | Query   |    4 | Locked       | INSERT IGNORE INTO session
                                        (sessionhash, userid, host, idhash, lastactivity, location, styleid, |
| 821607 | forum_vb | localhost | forum_vb | Query   |    3 | Locked       | INSERT IGNORE INTO session
                                        (sessionhash, userid, host, idhash, lastactivity, location, styleid, |
| 821618 | forum_vb | localhost | forum_vb | Query   |    3 | Locked       | UPDATE session
                                        SET lastactivity = 1250945397, location = '/showthread.php?t=34615', inforum = 8 |
| 821623 | forum_vb | localhost | forum_vb | Query   |    2 | Locked       | UPDATE session
                                        SET lastactivity = 1250945397, location = '/index.php', inforum = 0, inthread =  |
| 821624 | forum_vb | localhost | forum_vb | Query   |    3 | Locked       | INSERT IGNORE INTO session
                                        (sessionhash, userid, host, idhash, lastactivity, location, styleid, |
| 821626 | forum_vb | localhost | forum_vb | Query   |    3 | Locked       | INSERT IGNORE INTO session
                                        (sessionhash, userid, host, idhash, lastactivity, location, styleid, |
| 821628 | forum_vb | localhost | forum_vb | Query   |    2 | Locked       | UPDATE session
                                        SET lastactivity = 1250945397, location = '/index.php', inforum = 0, inthread =  |
| 821634 | forum_vb | localhost | forum_vb | Query   |    3 | Locked       | UPDATE session
                                        SET lastactivity = 1250945397, incalendar = 0, badlocation = 0
                                        WHERE sessio |
| 821635 | forum_vb | localhost | forum_vb | Query   |    2 | Locked       | UPDATE session
                                        SET lastactivity = 1250945397, location = '/index.php', inforum = 0, inthread =  |
| 821637 | forum_vb | localhost | forum_vb | Query   |    3 | Locked       | INSERT IGNORE INTO session
                                        (sessionhash, userid, host, idhash, lastactivity, location, styleid, |
| 821638 | forum_vb | localhost | forum_vb | Query   |    2 | Locked       | UPDATE session
                                        SET lastactivity = 1250945397, location = '/index.php', inforum = 0, inthread =  |
| 821639 | forum_vb | localhost | forum_vb | Query   |    2 | Locked       | UPDATE session
                                        SET lastactivity = 1250945397, location = '/index.php', inforum = 0, inthread =  |
| 821648 | forum_vb | localhost | forum_vb | Query   |    1 | Locked       | UPDATE session
                                        SET lastactivity = 1250945397, location = '/index.php', inforum = 0, inthread =  |
| 821655 | forum_vb | localhost | forum_vb | Query   |    2 | Locked       | INSERT IGNORE INTO session
                                        (sessionhash, userid, host, idhash, lastactivity, location, styleid, |
| 821657 | forum_vb | localhost | forum_vb | Query   |    2 | Locked       | UPDATE session
                                        SET lastactivity = 1250945397, location = '/tags.php?tag=%CA%D1%CF%CF+%D8%ED%E6% |
| 821666 | forum_vb | localhost | forum_vb | Query   |    2 | Locked       | UPDATE session
                                        SET lastactivity = 1250945398, location = '/tags.php?tag=%D8%AD%D9%84%D9%88%D9%8 |
| 821668 | forum_vb | localhost | forum_vb | Query   |    2 | Locked       | UPDATE session
                                        SET lastactivity = 1250945398, incalendar = 0, badlocation = 0, useragent = 'Moz |
| 821670 | forum_vb | localhost | forum_vb | Query   |    1 | Locked       | UPDATE session
                                        SET lastactivity = 1250945398, inforum = 0, inthread = 0, incalendar = 0, badloc |
| 821671 | forum_vb | localhost | forum_vb | Query   |    1 | Locked       | UPDATE session
                                        SET lastactivity = 1250945398, inforum = 0, inthread = 0, incalendar = 0, badloc |
| 821676 | forum_vb | localhost | forum_vb | Query   |    2 | Locked       | INSERT IGNORE INTO session
                                        (sessionhash, userid, host, idhash, lastactivity, location, styleid, |
| 821677 | forum_vb | localhost | forum_vb | Query   |    2 | Locked       | UPDATE session
                                        SET lastactivity = 1250945398, incalendar = 0, badlocation = 0, useragent = 'Moz |
| 821678 | forum_vb | localhost | forum_vb | Query   |    1 | Locked       | UPDATE session
                                        SET lastactivity = 1250945398, location = '/index.php', inforum = 0, inthread =  |
| 821680 | forum_vb | localhost | forum_vb | Query   |    2 | Locked       | INSERT IGNORE INTO session
                                        (sessionhash, userid, host, idhash, lastactivity, location, styleid, |
| 821684 | forum_vb | localhost | forum_vb | Query   |    2 | Locked       | UPDATE session
                                        SET lastactivity = 1250945398, location = '/tags.php?tag=%D8%B7%D8%B1%D9%8A%D9%8 |
| 821690 | root     | localhost | NULL     | Query   |    0 | NULL         | show processlist                                                                                     |
| 821691 | forum_vb | localhost | forum_vb | Query   |    2 | Locked       | UPDATE session
                                        SET lastactivity = 1250945398, incalendar = 0, badlocation = 0
                                        WHERE sessio |
| 821692 | forum_vb | localhost | forum_vb | Query   |    2 | Locked       | UPDATE session
                                        SET lastactivity = 1250945398, location = '/tags.php?tag=%CA%D1%CF%CF+%D8%ED%E6% |
| 821695 | forum_vb | localhost | forum_vb | Query   |    2 | Locked       | UPDATE session
                                        SET lastactivity = 1250945398, location = '/showthread.php?t=3484', inforum = 59 |
| 821698 | forum_vb | localhost | forum_vb | Query   |    2 | Locked       | UPDATE session
                                        SET lastactivity = 1250945398, location = '/showthread.php?t=57969', inforum = 1 |
| 821700 | forum_vb | localhost | forum_vb | Query   |    1 | Locked       | UPDATE session
                                        SET lastactivity = 1250945398, location = '/index.php', inforum = 0, inthread =  |
| 821701 | forum_vb | localhost | forum_vb | Query   |    1 | Locked       | UPDATE session
                                        SET lastactivity = 1250945398, incalendar = 0, badlocation = 0, useragent = 'Moz |
| 821702 | forum_vb | localhost | forum_vb | Query   |    1 | Locked       | UPDATE session
                                        SET lastactivity = 1250945398, location = '/showthread.php?t=4983', inthread = 4 |
| 821704 | forum_vb | localhost | forum_vb | Query   |    1 | Locked       | UPDATE session
                                        SET lastactivity = 1250945398, location = '/index.php', inforum = 0, inthread =  |
| 821708 | forum_vb | localhost | forum_vb | Query   |    1 | Locked       | UPDATE session
                                        SET lastactivity = 1250945399, location = '/index.php', inforum = 0, inthread =  |
| 821709 | forum_vb | localhost | forum_vb | Query   |    1 | Locked       | INSERT IGNORE INTO session
                                        (sessionhash, userid, host, idhash, lastactivity, location, styleid, |
| 821714 | forum_vb | localhost | forum_vb | Query   |    1 | Locked       | INSERT IGNORE INTO session
                                        (sessionhash, userid, host, idhash, lastactivity, location, styleid, |
| 821715 | forum_vb | localhost | forum_vb | Query   |    1 | Sending data | SELECT
                        user.username, (user.options & 512) AS invisible, user.usergroupid,
                        session.userid, ses |
| 821717 | forum_vb | localhost | forum_vb | Query   |    1 | Locked       | UPDATE session
                                        SET lastactivity = 1250945399, location = '/tags.php?tag=%D8%B7%D8%B1%D9%8A%D9%8 |
| 821719 | forum_vb | localhost | forum_vb | Query   |    1 | Sending data | SELECT
                        user.username, (user.options & 512) AS invisible, user.usergroupid,
                        session.userid, ses |
| 821720 | forum_vb | localhost | forum_vb | Query   |    1 | Locked       | INSERT IGNORE INTO session
                                        (sessionhash, userid, host, idhash, lastactivity, location, styleid, |
| 821722 | forum_vb | localhost | forum_vb | Query   |    1 | Locked       | INSERT IGNORE INTO session
                                        (sessionhash, userid, host, idhash, lastactivity, location, styleid, |
| 821724 | forum_vb | localhost | forum_vb | Query   |    1 | Locked       | SELECT
                        user.username, (user.options & 512) AS invisible, user.usergroupid,
                        session.userid, ses |
| 821725 | forum_vb | localhost | forum_vb | Query   |    1 | Locked       | SELECT
                        user.username, (user.options & 512) AS invisible, user.usergroupid,
                        session.userid, ses |
| 821726 | forum_vb | localhost | forum_vb | Query   |    1 | Locked       | UPDATE session
                                        SET lastactivity = 1250945399, location = '/tags.php?tag=%D8%AD%D9%84%D9%88%D9%8 |
| 821727 | forum_vb | localhost | forum_vb | Query   |    1 | Locked       | INSERT IGNORE INTO session
                                        (sessionhash, userid, host, idhash, lastactivity, location, styleid, |
| 821728 | forum_vb | localhost | forum_vb | Query   |    1 | Locked       | SELECT
                        user.username, (user.options & 512) AS invisible, user.usergroupid,
                        session.userid, ses |
| 821732 | forum_vb | localhost | forum_vb | Query   |    1 | Locked       | SELECT *
                                FROM session
                                WHERE userid = 0
                                        AND host = '41.238.157.141'
                                        AND idhash = '3 |
| 821733 | forum_vb | localhost | forum_vb | Query   |    1 | Locked       | SELECT *
                                FROM session
                                WHERE sessionhash = 'f942a1593fb289af3de328788f4995d0'
                                        AND lastac |
| 821735 | forum_vb | localhost | forum_vb | Query   |    1 | Locked       | SELECT *
                                FROM session
                                WHERE sessionhash = 'ef4d6c7c29ae6a0f6af050d5c736b056'
                                        AND lastac |
| 821736 | forum_vb | localhost | forum_vb | Query   |    1 | Locked       | SELECT *
                                FROM session
                                WHERE userid = 0
                                        AND host = '41.196.210.53'
                                        AND idhash = 'd9 |
+--------+----------+-----------+----------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
78 rows in set (0.00 sec)

                                              
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:
125:
126:
127:
128:
129:
130:
131:
132:
133:
134:
135:
136:
137:
138:
139:
140:
141:
142:
143:
144:
145:
146:
147:
148:
149:
150:
151:
152:
153:
154:
155:
156:
157:
158:
159:
160:
161:
162:
163:
164:
165:
166:
167:
168:
169:
170:
171:
172:
173:
174:
175:
176:
177:
178:

Select allOpen in new window

 

by: rstaveleyPosted on 2009-08-23 at 04:01:12ID: 25162142

The only process that's not locked is:

| 821715 | forum_vb | localhost | forum_vb | Query   |    1 | Sending data | SELECT
                        user.username, (user.options & 512) AS invisible, user.usergroupid,
                        session.userid, ses |

So again, it looks like that's responsible for locking the table.

Have you found out of the columns I mentioned in http:#25157865 are indexed?

20120131-EE-VQP-002

3 Ways to Join

30-Day Free Trial

The Experts

98% positive feedback on 31,087 answers since March 2000. angeliii is a Microsoft Most Valuable Professional for his work with MS SQL Server & Develoment.

He has also proven his knowledge of Visual Basic Programming, PHP Scripting and Oracle Databases.

The Experts

97% positive feedback on 10,752 answers since July 2000. lrmoore has more than 18 years experience in the networking industry.

The six-time Mircosoft MVPs specialties include firewalls, virtual private networking, and network management.

Testimonials

"...and excellent source for support... Kind of like having your very own IT dept." Electriciansnet

Testimonials

"I was apprehensive at signing up at first. However... it has already made my life as an IT administrator much easier." JaCrews

Testimonials

"WOW! You guys have great, active, and knowledgeable people on here." moore50

Business Clients

Business Clients

In the Press

"If you’ve got a question... Experts Exchange can supply an answer.”

In the Press

"...an invaluable aid for both IT professionals and those who require tech support."

In the Press

"where IT professionals provide quick answers on just about any topic"

Business Account Plans

Loading Advertisement...