MySQL Forums
Forum List  »  Knowledge Base

Threads_running always 1
Posted by: Anh Nguyen
Date: September 25, 2009 10:05AM

Hi,

I applied a high load to mysql, both 'show status' and ps always showed 1 thread running. I am wondering if the issue has been discussed somewhere and a corresponding bug has been filed, and/or also if I have misconfigured mysql in any ways.
'show innodb status' showed transactions "not started" with different thread ids.

Suggestions/pointers are appreciated.

Here are my testing environment, and the results of the aforementioned commands:

[root@perf14 pt]# uname -a
Linux xxx.com 2.6.18-164.el5 #1 SMP Tue Aug 18 15:51:48 EDT 2009 x86_64 x86_64 x86_64 GNU/Linux

[root@perf14 pt]# /usr/libexec/mysqld --version --help
/usr/libexec/mysqld Ver 5.0.84-log for redhat-linux-gnu on x86_64 (Source distribution)

[root@perf14 pt]# rpm -qa | grep mysql
mysql-5.0.84-2.el5s2
mysql-libs-5.0.84-2.el5s2
php-mysql-5.2.9-2.el5s2
mysql-jdbc-5.0.8-1jpp.1.el5s2
mysql-libs-5.0.84-2.el5s2
mysql-connector-odbc-3.51.27r695-1.el5s2
mysqlclient14-4.1.22-1.el5s2
mysql-5.0.84-2.el5s2
mod_auth_mysql-3.0.0-3.2.el5_3
mysql-server-5.0.84-2.el5s2

[root@perf14 calMode_200i_2min_withstatus.1]# cat my.cnf
[mysqld]
datadir=/home/mysql/data
user=mysql
old_passwords=1
socket=/var/lib/mysql/mysql.sock
innodb_data_home_dir=/home/mysql/innodb_data
innodb_data_file_path = ibdata1:2000M:autoextend
innodb_log_group_home_dir=/home/mysql/logs
innodb_additional_mem_pool_size=32M
#innodb_flush_log_at_trx_commit=120000
innodb_flush_log_at_trx_commit=0
max_connections=256
table_cache=4096
thread_cache=16
thread_cache_size=16
innodb_file_per_table = true

log-slow-queries=/tmp/mysql-slow.log
log-long-format
long_query_time = 6

# Set the Innodb buffer pool size to n GB
# = this value is in bytes (80% of total memory)
innodb-buffer-pool-size = 16G
# Turn off the check-sum of pages read from disk
innodb-checksums = false
# Let any number of threads commit concurrently
#innodb-commit-concurrency = 0
innodb-commit-concurrency = 8
# Turn off the double write function
innodb-doublewrite = false
# Use direct io
innodb-flush-method = O_DIRECT
#innodb-flush-method = O_DSYNC - TBT
# Use a large buffer for the transaction log (you may want to go over 8M, and go even larger)
innodb-log-buffer-size = 16M
#innodb-log-buffer-size = 1G
#innodb-log-buffer-size = 64M
# Set the log file size to one half the buffer pool above
innodb-log-file-size = 1G
#innodb_log_buffer_size = 8M -TBT alap
#innodb_log_file_size = 128M - TBT alap

#key_buffer = 1000M

# Turn off XA support as its not needed in this test
innodb-support-xa = false
# Set the number of concurrent threads to unlimited
innodb-thread-concurrency = 8
# Turn on large page memory support
large-pages = true
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

|
=====================================
090925 9:12:14 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 53 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 3273, signal count 2226
Mutex spin waits 0, rounds 152068, OS waits 1940
RW-shared spins 2419, OS waits 1003; RW-excl spins 495, OS waits 166
------------
TRANSACTIONS
------------
Trx id counter 0 28368021
Purge done for trx's n:o < 0 28141830 undo n:o < 0 0
History list length 7
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 28367857, not started, process no 10262, OS thread id 118960364
8
MySQL thread id 55, query id 2862347 perf12.lab.bos.redhat.com 192.168.1.12 ejb3
---TRANSACTION 0 28367855, not started, process no 10262, OS thread id 118933740
MySQL thread id 54, query id 2862405 perf12.lab.bos.redhat.com 192.168.1.12 ejb3
---TRANSACTION 0 28367821, not started, process no 10262, OS thread id 118800620
8
MySQL thread id 49, query id 2862425 perf12.lab.bos.redhat.com 192.168.1.12 ejb3
---TRANSACTION 0 28367819, not started, process no 10262, OS thread id 118507756
8
MySQL thread id 38, query id 2862179 perf12.lab.bos.redhat.com 192.168.1.12 ejb3
---TRANSACTION 0 28366479, not started, process no 10262, OS thread id 118534380
8
MySQL thread id 39, query id 2846595 perf12.lab.bos.redhat.com 192.168.1.12 ejb3
---TRANSACTION 0 28367838, not started, process no 10262, OS thread id 118827244
8

There are more "not started" threads, but I cut them out

--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
200 OS file reads, 4278 OS file writes, 2917 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 2.89 writes/s, 2.26 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2,
0 inserts, 0 merged recs, 0 merges
Hash table size 35401603, used cells 1948522, node heap has 2957 buffer(s)
3758.02 hash searches/s, 4329.20 non-hash searches/s
---
LOG
---
Log sequence number 7 1985380255
Log flushed up to 7 1985344379
Last checkpoint at 7 1983716162
0 pending log writes, 0 pending chkp writes
1824 log i/o's done, 2.06 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 1886459797; in additional pool allocated 12493312
Buffer pool size 1048576
Free buffers 994542
Database pages 51077
Modified db pages 299
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 204, created 50983, written 53735
0.00 reads/s, 3.06 creates/s, 11.23 writes/s
Buffer pool hit rate 1000 / 1000
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 10262, id 1177622848, state: sleeping
Number of rows inserted 8906466, updated 1, deleted 0, read 1364493
341.14 inserts/s, 0.00 updates/s, 0.00 deletes/s, 4139.60 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
|
1 row in set, 1 warning (0.10 sec)

mysql>
mysql> show status;
+-----------------------------------+------------+
| Variable_name | Value |
--More--(45%)
+-----------------------------------+------------+
| Aborted_clients | 0 |
| Aborted_connects | 0 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 246 |
| Bytes_sent | 35887 |
| Com_admin_commands | 0 |
| 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 | 0 |
| 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 | 0 |
--More--(50%)
| Com_lock_tables | 0 |
| 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 | 0 |
| 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 | 1 |
| Com_set_option | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_charsets | 0 |
| Com_show_collations | 0 |
| Com_show_column_types | 0 |
--More--(60%)
| Com_show_create_db | 0 |
| Com_show_create_table | 0 |
| Com_show_databases | 0 |
| Com_show_errors | 0 |
| Com_show_fields | 0 |
| Com_show_grants | 0 |
| Com_show_innodb_status | 3 |
| 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 | 0 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 3 |
| Com_show_storage_engines | 2 |
| Com_show_tables | 0 |
| Com_show_triggers | 0 |
| Com_show_variables | 0 |
| Com_show_warnings | 0 |
--More--(65%)
| 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 | 0 |
| Com_unlock_tables | 0 |
| Com_update | 0 |
| Com_update_multi | 0 |
| 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 | 56 |
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 5 |
| Created_tmp_tables | 3 |
--More--(69%)
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 0 |
| Handler_read_key | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 506 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 636 |
| Innodb_buffer_pool_pages_data | 51246 |
| Innodb_buffer_pool_pages_dirty | 341 |
| Innodb_buffer_pool_pages_flushed | 54657 |
| Innodb_buffer_pool_pages_free | 994352 |
| Innodb_buffer_pool_pages_misc | 2978 |
| Innodb_buffer_pool_pages_total | 1048576 |
| Innodb_buffer_pool_read_ahead_rnd | 1 |
| Innodb_buffer_pool_read_ahead_seq | 0 |
| Innodb_buffer_pool_read_requests | 84251496 |
| Innodb_buffer_pool_reads | 164 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 33545721 |
| Innodb_data_fsyncs | 3089 |
| Innodb_data_pending_fsyncs | 0 |
| Innodb_data_pending_reads | 0 |
| Innodb_data_pending_writes | 0 |
| Innodb_data_read | 5525504 |
| Innodb_data_reads | 200 |
| Innodb_data_writes | 4496 |
| Innodb_data_written | 2001267712 |
| Innodb_dblwr_pages_written | 0 |
| Innodb_dblwr_writes | 0 |
| Innodb_log_waits | 0 |
| Innodb_log_write_requests | 2165426 |
| Innodb_log_writes | 1562 |
| Innodb_os_log_fsyncs | 1879 |
| Innodb_os_log_pending_fsyncs | 0 |
| Innodb_os_log_pending_writes | 0 |
| Innodb_os_log_written | 1105554432 |
| Innodb_page_size | 16384 |
| Innodb_pages_created | 51152 |
| Innodb_pages_read | 204 |
| Innodb_pages_written | 54657 |
| 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 | 8934138 |
| Innodb_rows_read | 1699845 |
| Innodb_rows_updated | 1 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 6694 |
| Key_blocks_used | 0 |
| Key_read_requests | 0 |
| Key_reads | 0 |
| Key_write_requests | 0 |
| Key_writes | 0 |
| Last_query_cost | 0.000000 |
--More--(84%)
| Max_used_connections | 51 |
| 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 | 15 |
| Open_streams | 0 |
| Open_tables | 106 |
| Opened_tables | 0 |
| Prepared_stmt_count | 0 |
| Qcache_free_blocks | 0 |
| Qcache_free_memory | 0 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 0 |
| Queries | 3500545 |
| Questions | 10 |
| Rpl_status | NULL |
| Select_full_join | 0 |
| Ssl_cipher_list | |
| Ssl_client_connects | 0 |
| Ssl_connect_renegotiates | 0 |
| Ssl_ctx_verify_depth | 0 |
| Ssl_ctx_verify_mode | 0 |
| Ssl_default_timeout | 0 |
| Ssl_finished_accepts | 0 |
| Ssl_finished_connects | 0 |
| Ssl_session_cache_hits | 0 |
| Ssl_session_cache_misses | 0 |
| Ssl_session_cache_mode | NONE |
| Ssl_session_cache_overflows | 0 |
| Ssl_session_cache_size | 0 |
| Ssl_session_cache_timeouts | 0 |
| Ssl_sessions_reused | 0 |
| Ssl_used_session_cache_entries | 0 |
| Ssl_verify_depth | 0 |
| Ssl_verify_mode | 0 |
| Ssl_version | |
| Table_locks_immediate | 3781054 |
| Table_locks_waited | 0 |
| Tc_log_max_pages_used | 0 |
| Tc_log_page_size | 0 |
| Tc_log_page_waits | 0 |
| Threads_cached | 0 |
| Threads_connected | 51 |
| Threads_created | 51 |
| Threads_running | 1 |
| Uptime | 1690 |

Options: ReplyQuote


Subject
Views
Written By
Posted
Threads_running always 1
5360
September 25, 2009 10:05AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.