Форум пользователей MySQL

Здравствуйте. Вот собственоо куски с SHOW INNODB STATUS:
Type    Name    Status
InnoDB
=====================================
110103 23:14:28 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 60 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 32014047, signal count 26798197
Mutex spin waits 0, rounds 1875342656, OS waits 9641087
RW-shared spins 27165773, OS waits 11677043; RW-excl spins 13671638, OS waits 8103225

------------
TRANSACTIONS
------------
Trx id counter 0 85431134
Purge done for trx's n < 0 85425772 undo n < 0 0
History list length 908
... truncated...
OM
                `tree`
            LEFT JOIN
                `user_tree`
            ON
                `id` = `tree_id`
            AND
                `user_id` = 75741289
Trx read view will not see trx with id >= 0 85430847, sees < 0 85426138
---TRANSACTION 0 85430845, ACTIVE 4 sec, process no 1212, OS thread id 139737527199504 waiting in InnoDB queue
mysql tables in use 2, locked 0
MySQL thread id 6136326, query id 70656030 10.0.2.3 user_db Sending data
SELECT
                `user_id`, `id` AS `tree_id`, `name`, `toll`, `type`, `modifiers`, `stage_0`, `stage_1`, `stage_2`, `state`, `plant_time`
            FROM
                `tree`
            LEFT JOIN
                `user_tree`
            ON
                `id` = `tree_id`
            AND
                `user_id` = 9788738
Trx read view will not see trx with id >= 0 85430846, sees < 0 85426138
---TRANSACTION 0 85430829, ACTIVE 4 sec, process no 1212, OS thread id 139737511745296 waiting in InnoDB queue
mysql tables in use 2, locked 0
MySQL thread id 6136202, query id 70656012 10.0.2.3 user_db Sending data
SELECT
                `user_land`.*,
                `garden`.`harvest_time`,
                `garden`.`size_x`,
                `garden`.`size_y`
            FROM
                 `user_land`
            LEFT JOIN `garden` ON `garden`.`id`=`user_land`.`garden_id`
            WHERE
                `user_land`.`user_id`=47846322
Trx read view will not see trx with id >= 0 85430830, sees < 0 85426138


/*Кусок вырезан там все повторяется */


--------
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: 1
362302503 OS file reads, 57107386 OS file writes, 15732100 OS fsyncs
1176.01 reads/s, 16765 avg bytes/read, 98.58 writes/s, 11.03 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 13, free list len 329, seg size 343,
44490273 inserts, 44488090 merged recs, 3284902 merges
Hash table size 17393, node heap has 2 buffer(s)
3550.62 hash searches/s, 2858.34 non-hash searches/s
---
LOG
---
Log sequence number 15 3231417934
Log flushed up to   15 3231417934
Last checkpoint at  15 3230311971
0 pending log writes, 0 pending chkp writes
13942902 log i/o's done, 6.83 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 35517282; in additional pool allocated 1045760
Dictionary memory allocated 267784
Buffer pool size   512
Free buffers       0
Database pages     510
Modified db pages  62
Pending reads 1
Pending writes: LRU 62, flush list 0, single page 0
Pages read 379482509, created 2557189, written 47531423
1203.36 reads/s, 5.10 creates/s, 101.43 writes/s
Buffer pool hit rate 951 / 1000
--------------
ROW OPERATIONS
--------------
8 queries inside InnoDB, 135 queries in queue
139 read views open inside InnoDB
Main thread process no. 1212, id 139737540290320, state: sleeping
Number of rows inserted 85914150, updated 12460127, deleted 15151099, read 20938072597
168.38 inserts/s, 8.35 updates/s, 49.55 deletes/s, 31858.57 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

Собственно вопрос что не так с этими запросами, и почему они висят долгое время(show processlist 150 - row)? Заранее благодарен за вашу помощь!
Вот еще SHOW GLOBAL STATUS Меня в нем настараживает Created_tmp_disk_tables = 5739749. И что означает Innodb_data_read?
Variable_name   Value
Aborted_clients 527
Aborted_connects        1
Binlog_cache_disk_use   0
Binlog_cache_use        0
Bytes_received  46945952183
Bytes_sent      1642787260269
Com_admin_commands      98
Com_assign_to_keycache  0
Com_alter_db    0
Com_alter_db_upgrade    0
Com_alter_event 0
Com_alter_function      0
Com_alter_procedure     0
Com_alter_server        0
Com_alter_table 245
Com_alter_tablespace    0
Com_analyze     0
Com_backup_table        0
Com_begin       0
Com_binlog      0
Com_call_procedure      0
Com_change_db   984
Com_change_master       0
Com_check       0
Com_checksum    0
Com_commit      0
Com_create_db   4
Com_create_event        0
Com_create_function     0
Com_create_index        0
Com_create_procedure    0
Com_create_server       0
Com_create_table        125
Com_create_trigger      0
Com_create_udf  0
Com_create_user 0
Com_create_view 0
Com_dealloc_sql 0
Com_delete      2878489
Com_delete_multi        0
Com_do  0
Com_drop_db     5
Com_drop_event  0
Com_drop_function       0
Com_drop_index  0
Com_drop_procedure      0
Com_drop_server 0
Com_drop_table  170
Com_drop_trigger        0
Com_drop_user   0
Com_drop_view   0
Com_empty_query 0
Com_execute_sql 0
Com_flush       29
Com_grant       0
Com_ha_close    0
Com_ha_open     0
Com_ha_read     0
Com_help        0
Com_insert      7875251
Com_insert_select       4
Com_install_plugin      0
Com_kill        28
Com_load        0
Com_load_master_data    0
Com_load_master_table   0
Com_lock_tables 152
Com_optimize    0
Com_preload_keys        0
Com_prepare_sql 0
Com_purge       0
Com_purge_before_date   0
Com_release_savepoint   0
Com_rename_table        0
Com_rename_user 0
Com_repair      0
Com_replace     1063962
Com_replace_select      0
Com_reset       0
Com_restore_table       0
Com_revoke      0
Com_revoke_all  0
Com_rollback    0
Com_rollback_to_savepoint       0
Com_savepoint   0
Com_select      32245241
Com_set_option  6216499
Com_show_authors        0
Com_show_binlog_events  0
Com_show_binlogs        0
Com_show_charsets       8
Com_show_collations     14
Com_show_column_types   0
Com_show_contributors   0
Com_show_create_db      26
Com_show_create_event   0
Com_show_create_func    0
Com_show_create_proc    0
Com_show_create_table   3099
Com_show_create_trigger 0
Com_show_databases      122
Com_show_engine_logs    0
Com_show_engine_mutex   0
Com_show_engine_status  13
Com_show_events 0
Com_show_errors 0
Com_show_fields 2612
Com_show_function_status        0
Com_show_grants 0
Com_show_keys   441
Com_show_master_status  0
Com_show_new_master     0
Com_show_open_tables    0
Com_show_plugins        0
Com_show_privileges     0
Com_show_procedure_status       0
Com_show_processlist    1051
Com_show_profile        0
Com_show_profiles       252
Com_show_slave_hosts    0
Com_show_slave_status   0
Com_show_status 669
Com_show_storage_engines        0
Com_show_table_status   112241
Com_show_tables 658
Com_show_triggers       868
Com_show_variables      91
Com_show_warnings       0
Com_slave_start 0
Com_slave_stop  0
Com_stmt_close  12
Com_stmt_execute        15
Com_stmt_fetch  0
Com_stmt_prepare        15
Com_stmt_reprepare      0
Com_stmt_reset  0
Com_stmt_send_long_data 0
Com_truncate    34
Com_uninstall_plugin    0
Com_unlock_tables       149
Com_update      10075486
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     6211028
Created_tmp_disk_tables 5739749
Created_tmp_files       29
Created_tmp_tables      12496689
Delayed_errors  0
Delayed_insert_threads  0
Delayed_writes  0
Flush_commands  1
Handler_commit  54134105
Handler_delete  2460261
Handler_discover        0
Handler_prepare 0
Handler_read_first      6478539
Handler_read_key        4198036791
Handler_read_next       10047343188
Handler_read_prev       99
Handler_read_rnd        17700616
Handler_read_rnd_next   7924348480
Handler_rollback        3183
Handler_savepoint       0
Handler_savepoint_rollback      0
Handler_update  27732400
Handler_write   272004110
Innodb_buffer_pool_pages_data   510
Innodb_buffer_pool_pages_dirty  60
Innodb_buffer_pool_pages_flushed        48315382
Innodb_buffer_pool_pages_free   0
Innodb_buffer_pool_pages_misc   2
Innodb_buffer_pool_pages_total  512
Innodb_buffer_pool_read_ahead_rnd       1544625
Innodb_buffer_pool_read_ahead_seq       945196
Innodb_buffer_pool_read_requests        33418595876
Innodb_buffer_pool_reads        360457421
Innodb_buffer_pool_wait_free    0
Innodb_buffer_pool_write_requests       683549708
Innodb_data_fsyncs      15855074
Innodb_data_pending_fsyncs      1
Innodb_data_pending_reads       0
Innodb_data_pending_writes      0
Innodb_data_read        6320341422080
Innodb_data_reads       368418386
Innodb_data_writes      57923166
Innodb_data_written     1645646516224
Innodb_dblwr_pages_written      48315440
Innodb_dblwr_writes     928703
Innodb_log_waits        206
Innodb_log_write_requests       144783578
Innodb_log_writes       13789757
Innodb_os_log_fsyncs    14086499
Innodb_os_log_pending_fsyncs    0
Innodb_os_log_pending_writes    0
Innodb_os_log_written   62318428672
Innodb_page_size        16384
Innodb_pages_created    2589536
Innodb_pages_read       385763096
Innodb_pages_written    48315382
Innodb_row_lock_current_waits   0
Innodb_row_lock_time    144784
Innodb_row_lock_time_avg        346
Innodb_row_lock_time_max        4018
Innodb_row_lock_waits   418
Innodb_rows_deleted     15451707
Innodb_rows_inserted    86887332
Innodb_rows_read        21258453303
Innodb_rows_updated     12616850
Key_blocks_not_flushed  0
Key_blocks_unused       13396
Key_blocks_used 5
Key_read_requests       15115579
Key_reads       13
Key_write_requests      2434137
Key_writes      37
Last_query_cost 0.000000
Max_used_connections    152
Not_flushed_delayed_rows        0
Open_files      0
Open_streams    0
Open_table_definitions  52
Open_tables     64
Opened_files    22974159
Opened_table_definitions        438
Opened_tables   2851086
Prepared_stmt_count     0
Qcache_free_blocks      2845
Qcache_free_memory      11373080
Qcache_hits     4911738
Qcache_inserts  29795259
Qcache_lowmem_prunes    218759
Qcache_not_cached       2717885
Qcache_queries_in_cache 4867
Qcache_total_blocks     12596
Queries 71608103
Questions       71608076
Rpl_status      NULL
Select_full_join        0
Select_full_range_join  0
Select_range    637783
Select_range_check      0
Select_scan     12874577
Slave_open_temp_tables  0
Slave_retried_transactions      0
Slave_running   OFF
Slow_launch_threads     0
Slow_queries    43860
Sort_merge_passes       12
Sort_range      43113
Sort_rows       2451912
Sort_scan       6077597
Ssl_accept_renegotiates 0
Ssl_accepts     0
Ssl_callback_cache_hits 0
Ssl_cipher
Ssl_cipher_list
Ssl_client_connects     0
Ssl_connect_renegotiates        0
Ssl_ctx_verify_depth    0
Ssl_ctx_verify_mode     0
Ssl_default_timeout     0
Ssl_finished_accepts    0
Ssl_finished_connects   0
Ssl_session_cache_hits  0
Ssl_session_cache_misses        0
Ssl_session_cache_mode  NONE
Ssl_session_cache_overflows     0
Ssl_session_cache_size  0
Ssl_session_cache_timeouts      0
Ssl_sessions_reused     0
Ssl_used_session_cache_entries  0
Ssl_verify_depth        0
Ssl_verify_mode 0
Ssl_version
Table_locks_immediate   112254650
Table_locks_waited      387
Tc_log_max_pages_used   0
Tc_log_page_size        0
Tc_log_page_waits       0
Threads_cached  4
Threads_connected       6
Threads_created 38510
Threads_running 6
Uptime  2490752
Uptime_since_flush_status       2490752

Отредактированно cooler (04.01.2011 02:06:38)