Profiling/Live 20040715 1110

This run was with the rebuild search index script running. On Ariel as master. Ran for about 10 minutes. This was to obtain data after turning off the query cache on Ariel MySQL. A run after that change is at Profiling/Live 20040715 1038. Notable changes:

  • drop from 1670 each (17.4%) to 357 each (8.4%) for SELECT cur_id,cur_namespace,cur_title,cur_text FROM cur,searchindex
  • drop from 5.97 each (6.81%) to 3.52 each (6.66%) for SELECT cur_id FROM cur WHERE cur_namespace=N AND cur_title='X'
  • rise from 170 each (3.55%) to 415 each (4.85%) for less common SELECT cur_id,cur_namespace,cur_title,cur_text FROM cur,searchindex

Too few samples for other queries. Some did seem to take longer. Using the query cache should be considered again if we get a search server because it will offload two of the three queries above from Ariel.

Based on this, the query cache was left turned off. Subsequent site performance seemed to confirm that off is currently the best setting.

 sql enwiki -vvv <~jamesday/profiling.sql
--------------
select @total:=pf_time from profiling where pf_name='-total'
--------------

+-----------------+
| @total:=pf_time |
+-----------------+
|  102610.2421875 |
+-----------------+
1 row in set (0.00 sec)

--------------
select left(pf_name,70) as name, pf_time as total, pf_count as count,
truncate(pf_time/pf_count,8) as each, truncate(pf_time/@total*100,8) as percent
from profiling order by pf_time desc
--------------

+------------------------------------------------------------------------+------------+-------+---------------+--------------+
| name                                                                   | total      | count | each          | percent      |
+------------------------------------------------------------------------+------------+-------+---------------+--------------+
| -total                                                                 |     102610 |   229 |  448.07966020 | 100.00000000 |
| Article::view                                                          |    32578.1 |   156 |  208.83409705 |  31.74938334 |
| Parser::parse                                                          |    29159.8 |   104 |  280.38228665 |  28.41797971 |
| Parser::internalParse                                                  |      22803 |   159 |  143.41540143 |  22.22297535 |
| Setup.php                                                              |    19947.3 |   229 |   87.10621929 |  19.43989585 |
| Parser::replaceInternalLinks                                           |    15346.6 |   318 |   48.25962743 |  14.95616928 |
| OutputPage::output                                                     |    14917.5 |   229 |   65.14196830 |  14.53803287 |
| Setup.php-MessageCache                                                 |    12653.9 |   226 |   55.99048931 |  12.33195665 |
| query: SELECT cur_id,cur_namespace,cur_title,cur_text FROM cur,searchi |     8572.3 |    24 |  357.17923990 |   8.35423596 |
| LinkCache::addLinkObj                                                  |    7560.69 |  1940 |    3.89726361 |   7.36835938 |
| query: SELECT cur_id FROM cur WHERE cur_namespace=N AND cur_title='X'  |    6832.78 |  1940 |    3.52205294 |   6.65896753 |
| DifferenceEngine::showDiffPage                                         |     6638.4 |     1 | 6638.39941406 |   6.46952903 |
| wfMsg                                                                  |    6540.34 | 11339 |    0.57680058 |   6.37396585 |
| Title::secureAndSplit                                                  |     5093.8 | 15554 |    0.32749133 |   4.96422207 |
| query: SELECT cur_id,cur_namespace,cur_title,cur_text FROM cur,searchi |    4974.12 |    12 |  414.51013183 |   4.84758780 |
| Parser::replaceVariables                                               |    4970.15 |   961 |    5.17185162 |   4.84371667 |
| -overhead-total                                                        |    4476.84 | 67007 |    0.06681147 |   4.36295279 |
| Parser::tidy                                                           |    4237.48 |   104 |   40.74495755 |   4.12968091 |
| query: SELECT old_namespace,old_title,old_timestamp,old_comment,old_mi |    3552.73 |     1 | 3552.72656250 |   3.46235082 |
| query: SELECT cur_namespace,cur_title,cur_comment, cur_id, cur_user,cu |    3051.81 |     1 | 3051.80590820 |   2.97417279 |
| Setup.php-misc                                                         |    2886.98 |   228 |   12.66217683 |   2.81353620 |
| ParserCache::get                                                       |    2620.02 |   154 |   17.01312969 |   2.55337275 |
| Parser::replaceExternalLinks                                           |    2306.73 |   159 |   14.50771576 |   2.24804732 |
| Title::newFromText                                                     |    2102.18 | 13080 |    0.16071708 |   2.04870332 |
| query: COMMIT                                                          |    2033.08 |   327 |    6.21736989 |   1.98136161 |
| Setup.php-includes                                                     |    1974.78 |   229 |    8.62347225 |   1.92453999 |
| -overhead-internal                                                     |    1968.41 | 67007 |    0.02937614 |   1.91833418 |
| LinkCache::preFill                                                     |    1829.57 |    93 |   19.67274261 |   1.78302382 |
| Skin::makeKnownLinkObj                                                 |    1741.73 |  6846 |    0.25441585 |   1.69742407 |
| query: SELECT lcc_cacheobj FROM linkscc WHERE lcc_pageid = N           |    1720.17 |    90 |   19.11295301 |   1.67640747 |
| Skin::makeLink                                                         |    1535.43 |   184 |    8.34471263 |   1.49636828 |
| Parser::doBlockLevels                                                  |    1293.13 |   104 |   12.43392826 |   1.26023339 |
| query: BEGIN                                                           |    1262.27 |   327 |    3.86015035 |   1.23015903 |
| query: SELECT cur_text FROM cur WHERE cur_namespace=N AND cur_title='X |    1108.28 |   240 |    4.61783498 |   1.08008749 |
| query: SELECT COUNT(*) AS n FROM watchlist WHERE wl_user=N             |    997.925 |     1 |  997.92510986 |   0.97253947 |
| main-misc-setup                                                        |    880.608 |   229 |    3.84544805 |   0.85820634 |
| query: SELECT cur_namespace,cur_title,cur_timestamp,cur_comment,cur_mi |    760.661 |     1 |  760.66131591 |   0.74131129 |
| query: SELECT ss_total_views, ss_total_edits, ss_good_articles FROM si |    657.797 |   193 |    3.40827269 |   0.64106332 |
| Skin::outputPage                                                       |    608.176 |     7 |   86.88235037 |   0.59270540 |
| query: SELECT LENGTH(cur_text) AS x, cur_namespace, cur_is_redirect FR |    590.819 |   179 |    3.30066634 |   0.57578976 |
| Setup.php-language                                                     |    553.545 |   228 |    2.42783101 |   0.53946415 |
| Article::getContent                                                    |    523.164 |   110 |    4.75603804 |   0.50985571 |
| Setup.php-User                                                         |    504.921 |   226 |    2.23416286 |   0.49207642 |
| query: SHOW COLUMNS FROM cur LIKE 'X';                                 |    499.718 |   216 |    2.31350863 |   0.48700583 |
| query: SELECT cur_id,cur_title FROM cur USE INDEX (cur_random) WHERE c |     473.69 |    10 |   47.36903381 |   0.46164040 |
| query: SELECT N FROM user_newtalk WHERE user_ip='X'                    |    471.362 |   185 |    2.54790401 |   0.45937153 |
| Setup.php-SetupSession                                                 |    401.913 |   229 |    1.75507831 |   0.39168890 |
| query: SELECT cur_text,cur_timestamp,cur_user,cur_user_text,cur_commen |    344.335 |   119 |    2.89356827 |   0.33557529 |
| Skin::doAfterContent                                                   |    326.022 |     5 |   65.20432128 |   0.31772813 |
| query: SELECT user_id FROM user WHERE user_name='X'                    |    314.862 |     9 |   34.98470730 |   0.30685276 |
| query: SELECT COUNT(*) AS n FROM cur WHERE cur_timestamp>'X'           |    280.969 |     1 |  280.96893310 |   0.27382152 |
| Parser::removeHTMLtags                                                 |    279.153 |   159 |    1.75567722 |   0.27205147 |
| Parser::doAllQuotes                                                    |    270.437 |   159 |    1.70086382 |   0.26355784 |
| Skin::doAfterContent-3                                                 |    232.854 |     5 |   46.57081604 |   0.22693064 |
| query: SELECT cur_user,cur_user_text,cur_timestamp,cur_comment,cur_min |    223.173 |    83 |    2.68882622 |   0.21749541 |
| query: SELECT cur_touched,cur_is_redirect FROM cur WHERE cur_id=N      |    201.474 |    72 |    2.79825443 |   0.19634913 |
| query: SELECT * FROM ipblocks WHERE ipb_user=N                         |     191.28 |    40 |    4.78200340 |   0.18641427 |
| Setup.php-OutputPage                                                   |    173.477 |   225 |    0.77100836 |   0.16906390 |
| query: SELECT 'X' as type, rc_namespace AS namespace, rc_title AS titl |    142.965 |     1 |  142.96452331 |   0.13932773 |
| Setup.php-memcached                                                    |    141.727 |   228 |    0.62161074 |   0.13812193 |
| query: SELECT cur_restrictions FROM cur WHERE (cur_id=N)               |    136.118 |    78 |    1.74509938 |   0.13265513 |
| Skin::doBeforeContent                                                  |    135.196 |     5 |   27.03918457 |   0.13175675 |
| PageHistory::history                                                   |    104.162 |     1 |  104.16249847 |   0.10151276 |
| Setup.php-extensions                                                   |    102.088 |   229 |    0.44579881 |   0.09949097 |
| Setup.php-DateFormatter                                                |     100.18 |   225 |    0.44524597 |   0.09763191 |
| Skin::makeBrokenLinkObj                                                |    94.8799 |   621 |    0.15278573 |   0.09246634 |
| Skin::doAfterContent-4                                                 |     91.716 |     5 |   18.34319915 |   0.08938288 |
| Skin::quickBar                                                         |    91.3773 |     4 |   22.84432029 |   0.08905278 |
| query: SELECT cur_text,cur_timestamp,cur_user,cur_counter,cur_restrict |    72.7771 |    39 |    1.86607947 |   0.07092576 |
| Setup.php-database                                                     |      68.69 |   229 |    0.29995640 |   0.06694265 |
| Parser::doHeadings                                                     |    63.9423 |   159 |    0.40215277 |   0.06231570 |
| query: SELECT N FROM watchlist WHERE wl_user=N AND wl_namespace=N AND  |    44.7421 |    37 |    1.20924521 |   0.04360390 |
| query: SELECT DISTINCT cur_title,cur_namespace,cl_sortkey FROM cur,cat |     36.515 |     2 |   18.25749015 |   0.03558609 |
| Parser::replaceInternalLinks-setup                                     |    33.4079 |   318 |    0.10505633 |   0.03255806 |
| Skin::initPage                                                         |    28.4197 |   189 |    0.15036876 |   0.02769674 |
| query: SELECT old_namespace,old_title,old_timestamp,old_text,old_flags |    25.5753 |     1 |   25.57532691 |   0.02492473 |
| query: SELECT iw_url,iw_local FROM interwiki WHERE iw_prefix='X'       |    18.0613 |     8 |    2.25766110 |   0.01760183 |
| query: UPDATE recentchanges SET rc_cur_time='X' WHERE rc_cur_id=N      |    16.4393 |     3 |    5.47977002 |   0.01602112 |
| query: UPDATE cur SET cur_text='X'                                     |    15.2024 |     3 |    5.06746451 |   0.01481566 |
| query: SELECT bl_to FROM brokenlinks WHERE bl_from='X'                 |    14.7766 |     3 |    4.92552598 |   0.01440068 |
| query: SELECT cur_text, cur_user_text, cur_comment FROM cur WHERE cur_ |    14.3286 |     1 |   14.32864570 |   0.01396414 |
| query: SELECT cur_namespace,cur_title FROM cur WHERE cur_id=N LIMIT N  |    12.2403 |     1 |   12.24033737 |   0.01192896 |
| query: UPDATE recentchanges SET rc_this_oldid=N WHERE rc_namespace=N A |    12.0314 |     3 |    4.01047547 |   0.01172536 |
| query: SELECT img_size,img_description,img_user,img_user_text,img_time |    9.57555 |     1 |    9.57555294 |   0.00933196 |
| query: SELECT * FROM ipblocks WHERE (ipb_address='X' OR ipb_user=N)    |    8.83302 |     8 |    1.10412812 |   0.00860832 |
| query: REPLACE INTO linkscc(lcc_pageid,lcc_cacheobj) VALUES(N, 'X')    |    8.49425 |     3 |    2.83141613 |   0.00827816 |
| query: SELECT cur_id,cur_namespace,cur_title FROM cur,links WHERE cur_ |    8.03505 |     3 |    2.67835140 |   0.00783065 |
| query: DELETE FROM linkscc WHERE lcc_pageid='X'                        |    5.87641 |     3 |    1.95880413 |   0.00572692 |
| query: INSERT INTO old (old_namespace,old_title,old_text,old_comment,o |    3.47864 |     3 |    1.15954566 |   0.00339014 |
| query: SELECT DISTINCT 'X' as type, N as namespace, cl_to as title, N  |     3.2852 |     2 |    1.64260101 |   0.00320163 |
| Setup.php-BlockCache                                                   |    3.24759 |   227 |    0.01430654 |   0.00316497 |
| query: SELECT oi_size AS img_size, oi_description AS img_description,o |    1.77283 |     1 |    1.77282810 |   0.00172773 |
| query: INSERT INTO recentchanges (rc_timestamp,rc_cur_time,rc_namespac |    1.51335 |     3 |    0.50445083 |   0.00147485 |
| query: SELECT old_id,old_user,old_comment,old_user_text,old_timestamp, |    1.30753 |     1 |    1.30753135 |   0.00127426 |
| query: SELECT cur_namespace,cur_title FROM imagelinks,cur WHERE il_to= |    1.16963 |     1 |    1.16962909 |   0.00113987 |
| query: SELECT user_name,user_password,user_newpassword,user_email,user |    1.14885 |     2 |    0.57442569 |   0.00111962 |
| query: SELECT N FROM user_newtalk WHERE user_id=N                      |    1.04061 |     2 |    0.52030467 |   0.00101413 |
| query: SELECT COUNT(*) AS n FROM archive WHERE ar_namespace=N AND ar_t |   0.430859 |     1 |    0.43085917 |   0.00041989 |
| Skin::doAfterContent-2                                                 |   0.220721 |     5 |    0.04414427 |   0.00021510 |
| Skin::doAfterContent-1                                                 | 0.00972095 |     5 |    0.00194419 |   0.00000947 |
| Parser::replaceInternalLinks-prefixhandling                            |   -13.0918 |  5833 |   -0.00224443 |  -0.01275875 |
101 rows in set (0.00 sec)

MySQL configuration

edit
MySQL on localhost (4.0.20-log)                          up 3+21:35:18 [11:37:42]
 Queries: 307.3M  qps:  956 Slow:   25.0k         Se/In/Up/De(%):    73/00/00/00
             qps now:  958 Slow qps: 0.0  Threads:   66 (  28/  59) 72/00/00/00
 Cache Hits: 42.0M Hits/s: 130.8 Hits now:   0.0  Ratio: 18.7% Ratio now:  0.0%
 Key Efficiency: 99.7%  Bps in/out: 10.0k/ 4.9k   Now in/out: 97.0k/395.9k

show variables
    -> ;
+---------------------------------+-------------------------------------------------------
| Variable_name                   | Value
+---------------------------------+--------------------------------------------------------
| back_log                        | 50
| basedir                         | /usr/local/mysql/
| binlog_cache_size               | 32768
| bulk_insert_buffer_size         | 8388608
| character_set                   | latin1
| character_sets                  | latin1 dec8 dos german1 hp8
koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia
 hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5
|
| concurrent_insert               | ON
| connect_timeout                 | 5
| convert_character_set           |
| datadir                         | /usr/local/mysql/var/
| default_week_format             | 0
| delay_key_write                 | ON
| delayed_insert_limit            | 100
| delayed_insert_timeout          | 300
| delayed_queue_size              | 1000
| flush                           | OFF
| flush_time                      | 0
| ft_boolean_syntax               | + -><()~*:""&|
| ft_min_word_len                 | 2
| ft_max_word_len                 | 254
| ft_max_word_len_for_sort        | 20
| ft_stopword_file                | (built-in)
| have_bdb                        | NO
| have_crypt                      | YES
| have_innodb                     | YES
| have_isam                       | YES
| have_raid                       | NO
| have_symlink                    | YES
| have_openssl                    | NO
| have_query_cache                | YES
| init_file                       |
| innodb_additional_mem_pool_size | 1048576
| innodb_buffer_pool_size         | 1157627904
| innodb_data_file_path           | /mnt/raid1/ibdata1:68000M;/usr/local/mysql/data/ibdata2:50M:autoextend
| innodb_data_home_dir            |
| innodb_file_io_threads          | 8
| innodb_force_recovery           | 0
| innodb_thread_concurrency       | 8
| innodb_flush_log_at_trx_commit  | 1
| innodb_fast_shutdown            | ON
| innodb_flush_method             |
| innodb_lock_wait_timeout        | 50
| innodb_log_arch_dir             | ./
| innodb_log_archive              | OFF
| innodb_log_buffer_size          | 8388608
| innodb_log_file_size            | 83886080
| innodb_log_files_in_group       | 2
| innodb_log_group_home_dir       | ./
| innodb_mirrored_log_groups      | 1
| innodb_max_dirty_pages_pct      | 90
| interactive_timeout             | 28800
| join_buffer_size                | 256000000
| key_buffer_size                 | 402653184
| language                        | /usr/local/mysql/share/mysql/english/
| large_files_support             | ON
| license                         | GPL
| local_infile                    | ON
| locked_in_memory                | OFF
| log                             | OFF
| log_update                      | OFF
| log_bin                         | ON
| log_slave_updates               | OFF
| log_slow_queries                | OFF
| log_warnings                    | ON
| long_query_time                 | 10
| low_priority_updates            | OFF
| lower_case_file_system          | OFF
| lower_case_table_names          | 0
| max_allowed_packet              | 16776192
| max_binlog_cache_size           | 536870912
| max_binlog_size                 | 1073741824
| max_connections                 | 600
| max_connect_errors              | 40000
| max_delayed_threads             | 20
| max_insert_delayed_threads      | 20
| max_heap_table_size             | 16777216
| max_join_size                   | 40048576000
| max_relay_log_size              | 0
| max_seeks_for_key               | 4294967295
| max_sort_length                 | 1024
| max_user_connections            | 0
| max_tmp_tables                  | 32
| max_write_lock_count            | 4294967295
| myisam_max_extra_sort_file_size | 268435456
| myisam_max_sort_file_size       | 9223372036854775807
| myisam_repair_threads           | 1
| myisam_recover_options          | OFF
| myisam_sort_buffer_size         | 33554432
| net_buffer_length               | 16384
| net_read_timeout                | 30
| net_retry_count                 | 10
| net_write_timeout               | 60
| new                             | OFF
| open_files_limit                | 8610
| pid_file                        | /usr/local/mysql/var/ariel.wikimedia.org.pid
| log_error                       |
| port                            | 3306
| protocol_version                | 10
| query_alloc_block_size          | 32768
| query_cache_limit               | 4190208
| query_cache_size                | 0
| query_cache_type                | ON
| query_prealloc_size             | 32768
| range_alloc_block_size          | 2048
| read_buffer_size                | 4190208
| read_only                       | OFF
| read_rnd_buffer_size            | 4190208
| rpl_recovery_rank               | 0
| server_id                       | 2
| slave_net_timeout               | 3600
| skip_external_locking           | ON
| skip_networking                 | OFF
| skip_show_database              | OFF
| slow_launch_time                | 2
| socket                          | /tmp/mysql.sock
| sort_buffer_size                | 134217720
| sql_mode                        | 0
| table_cache                     | 4000
| table_type                      | MYISAM
| thread_cache_size               | 90
| thread_stack                    | 196608
| tx_isolation                    | REPEATABLE-READ
| timezone                        | UTC
| tmp_table_size                  | 268435456
| tmpdir                          | /tmp/
| transaction_alloc_block_size    | 8192
| transaction_prealloc_size       | 4096
| version                         | 4.0.20-log
| version_comment                 | Source distribution
| version_compile_os              | unknown-linux
| wait_timeout                    | 28800
+---------------------------------+--------------------------------------------------
131 rows in set (0.00 sec)

mysql> show status;
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| Aborted_clients          | 4079       |
| Aborted_connects         | 1474       |
| Bytes_received           | 3442743072 |
| Bytes_sent               | 1738731947 |
| Com_admin_commands       | 221        |
| Com_alter_table          | 0          |
| Com_analyze              | 0          |
| Com_backup_table         | 0          |
| Com_begin                | 20232992   |
| Com_change_db            | 14824253   |
| Com_change_master        | 0          |
| Com_check                | 0          |
| Com_commit               | 18381627   |
| Com_create_db            | 0          |
| Com_create_function      | 0          |
| Com_create_index         | 0          |
| Com_create_table         | 0          |
| Com_delete               | 898212     |
| Com_delete_multi         | 49905      |
| Com_drop_db              | 0          |
| Com_drop_function        | 0          |
| Com_drop_index           | 0          |
| Com_drop_table           | 0          |
| Com_flush                | 0          |
| Com_grant                | 1          |
| Com_ha_close             | 0          |
| Com_ha_open              | 0          |
| Com_ha_read              | 0          |
| Com_insert               | 855530     |
| Com_insert_select        | 13753      |
| Com_kill                 | 15         |
| Com_load                 | 0          |
| Com_load_master_data     | 0          |
| Com_load_master_table    | 0          |
| Com_lock_tables          | 1754       |
| Com_optimize             | 0          |
| Com_purge                | 0          |
| Com_rename_table         | 0          |
| Com_repair               | 0          |
| Com_replace              | 423915     |
| Com_replace_select       | 0          |
| Com_reset                | 0          |
| Com_restore_table        | 0          |
| Com_revoke               | 0          |
| Com_rollback             | 0          |
| Com_savepoint            | 0          |
| Com_select               | 191697088  |
| Com_set_option           | 17         |
| Com_show_binlog_events   | 0          |
| Com_show_binlogs         | 0          |
| Com_show_create          | 0          |
| Com_show_databases       | 0          |
| Com_show_fields          | 14411312   |
| Com_show_grants          | 7          |
| Com_show_keys            | 0          |
| Com_show_logs            | 0          |
| Com_show_master_status   | 3          |
| Com_show_new_master      | 0          |
| Com_show_open_tables     | 0          |
| Com_show_processlist     | 67774      |
| Com_show_slave_hosts     | 4          |
| Com_show_slave_status    | 2808       |
| Com_show_status          | 67668      |
| Com_show_innodb_status   | 15         |
| Com_show_tables          | 0          |
| Com_show_variables       | 715        |
| Com_slave_start          | 0          |
| Com_slave_stop           | 1          |
| Com_truncate             | 6          |
| Com_unlock_tables        | 819        |
| Com_update               | 1426326    |
| Connections              | 14829076   |
| Created_tmp_disk_tables  | 169512     |
| Created_tmp_tables       | 291376     |
| Created_tmp_files        | 29745      |
| Delayed_insert_threads   | 0          |
| Delayed_writes           | 0          |
| Delayed_errors           | 0          |
| Flush_commands           | 1          |
| Handler_commit           | 18381079   |
| Handler_delete           | 10003      |
| Handler_read_first       | 567481     |
| Handler_read_key         | 789753538  |
| Handler_read_next        | 1665404716 |
| Handler_read_prev        | 3917       |
| Handler_read_rnd         | 53724572   |
| Handler_read_rnd_next    | 774330026  |
| Handler_rollback         | 14709375   |
| Handler_update           | 819532     |
| Handler_write            | 462660276  |
| Key_blocks_used          | 360922     |
| Key_read_requests        | 2542029915 |
| Key_reads                | 7931157    |
| Key_write_requests       | 23901644   |
| Key_writes               | 16698208   |
| Max_used_connections     | 600        |
| Not_flushed_key_blocks   | 0          |
| Not_flushed_delayed_rows | 0          |
| Open_tables              | 4000       |
| Open_files               | 509        |
| Open_streams             | 0          |
| Opened_tables            | 17875      |
| Questions                | 322258084  |
| Qcache_queries_in_cache  | 0          |
| Qcache_inserts           | 46158204   |
| Qcache_hits              | 44075976   |
| Qcache_lowmem_prunes     | 386781     |
| Qcache_not_cached        | 144151100  |
| Qcache_free_memory       | 0          |
| Qcache_free_blocks       | 1          |
| Qcache_total_blocks      | 0          |
| Rpl_status               | NULL       |
| Select_full_join         | 454        |
| Select_full_range_join   | 1          |
| Select_range             | 593064     |
| Select_range_check       | 74069      |
| Select_scan              | 570851     |
| Slave_open_temp_tables   | 0          |
| Slave_running            | OFF        |
| Slow_launch_threads      | 4          |
| Slow_queries             | 25620      |
| Sort_merge_passes        | 0          |
| Sort_range               | 227566     |
| Sort_rows                | 345894975  |
| Sort_scan                | 287277     |
| Table_locks_immediate    | 197520166  |
| Table_locks_waited       | 18368      |
| Threads_cached           | 57         |
| Threads_created          | 59963      |
| Threads_connected        | 68         |
| Threads_running          | 29         |
| Uptime                   | 337001     |
+--------------------------+------------+
132 rows in set (0.00 sec)

mysql> show innodb status;
|
=====================================
040715 11:39:10 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 8 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 38496733, signal count 26896141
Mutex spin waits 5033762897, rounds 6881844959, OS waits 22092367
RW-shared spins 32315261, OS waits 8836777; RW-excl spins 105329274, OS waits 4698042
------------------------
LATEST DETECTED DEADLOCK
------------------------
040715 11:16:40
censored
------------
TRANSACTIONS
------------
Trx id counter 0 59935406
Purge done for trx's n:o < 0 14694830 undo n:o < 0 0
Total number of lock structs in row lock hash table 388
censored
--------
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: 1; buffer pool: 1
22466687 OS file reads, 3512737 OS file writes, 1373222 OS fsyncs
2 pending preads, 0 pending pwrites
48.62 reads/s, 20890 avg bytes/read, 10.75 writes/s, 0.50 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf for space 0: size 414, free list len 216, seg size 631,
1457197 inserts, 1367915 merged recs, 574801 merges
Hash table size 10791019, used cells 2576317, node heap has 4342 buffer(s)
7871.39 hash searches/s, 3834.02 non-hash searches/s
---
LOG
---
Log sequence number 18 3731296549
Log flushed up to   18 3731285123
Last checkpoint at  18 3613815352
1 pending log writes, 0 pending chkp writes
1212672 log i/o's done, 0.25 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 6030808896; in additional pool allocated 1048576
Buffer pool size   332800
Free buffers       16
Database pages     328440
Modified db pages  25612
Pending reads 2
Pending writes: LRU 0, flush list 0, single page 0
Pages read 30860124, created 484387, written 3337918
63.24 reads/s, 0.87 creates/s, 15.37 writes/s
Buffer pool hit rate 999 / 1000
--------------
ROW OPERATIONS
--------------
3 queries inside InnoDB, 0 queries in queue
Main thread process no. 13834, id 1132460384, state: flushing log
Number of rows inserted 2616038, updated 3630814, deleted 1736760, read 32154683994
2.87 inserts/s, 4.75 updates/s, 1.12 deletes/s, 6174.10 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
 |