Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-5388

Reduce usage of LOCK_open: unused_tables

    XMLWordPrintable

Details

    • Task
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.0.7
    • None
    • None

    Description

      tc_acquire_table and tc_release_table are critical sections of table cache. Among other things, they update unused_tables:
      tc_acquire_table - pop TABLE object from unused_tables
      tc_release_table - push TABLE object to unused_tables

      unused_tables is LRU list, which is quite expensive to maintain. This task suggests to get rid of unused_tables. Instead find LRU object by timestamp.

      The cost is as following:

      • 2 pointers will be removed from TABLE object (-16 bytes)
      • tc_time (ulonglong) will be added to TABLE object (+8 bytes)
      • tc_add_table() will have to iterate all TABLE objects to find LRU one

      According to initial benchmarks this patch show ~5% higher tps and LOCK_open becomes 2-nd busiest lock:

      MDEV-4956 + MDEV-5388, PFS off
      		 
      64 threads, time spent: 60s, queries executed: 11549452, qps: 192490, 1 thread qps: 3007
      		 
       
      		 
      MDEV-4956, PFS off
      		 
      64 threads, time spent: 60s, queries executed: 10981057, qps: 183017, 1 thread qps: 2859
      		 
       
      		 
      MDEV-4956 + MDEV-5388, PFS on
      		 
      64 threads, time spent: 60s, queries executed: 7536996, qps: 125616, 1 thread qps: 1962
      		 
      +--------------------------------------------------+------------+---------+------------------+----------------+----------------+----------------+
      		 
      | EVENT_NAME                                       | COUNT_STAR | SECONDS | SUM_TIMER_WAIT   | MIN_TIMER_WAIT | AVG_TIMER_WAIT | MAX_TIMER_WAIT |
      		 
      +--------------------------------------------------+------------+---------+------------------+----------------+----------------+----------------+
      		 
      | wait/synch/mutex/mysys/THR_LOCK::mutex           |   15074396 | 1146.79 | 1146786681865536 |          21204 |       76075116 |     6311445372 |
      		 
      | wait/synch/mutex/sql/LOCK_open                   |   15074458 |  571.08 |  571075729912224 |          21204 |       37883364 |     3643067052 |
      		 
      | wait/synch/mutex/sql/THD::LOCK_thd_data          |   45894002 |   12.34 |   12341814751872 |          21204 |         268584 |     1607180616 |
      		 
      | wait/synch/rwlock/sql/LOCK_tdc                   |    7493815 |    4.46 |    4464829506060 |          30132 |         595572 |     1120068936 |
      		 
      | wait/synch/rwlock/sql/MDL_lock::rwlock           |    1077689 |    1.11 |    1112654114820 |          23436 |        1032300 |      937146492 |
      		 
      | wait/synch/mutex/sql/MDL_map::mutex              |     539046 |    0.39 |     393706158336 |          21204 |         730236 |     1647120024 |
      		 
      | wait/synch/rwlock/innodb/checkpoint_lock         |          4 |    0.04 |      42785956836 |         235476 |    10696489116 |    26197761852 |
      		 
      | wait/synch/mutex/sql/LOCK_plugin                 |        641 |    0.03 |      26178158196 |          22320 |       40839276 |     1132935300 |
      		 
      | wait/synch/rwlock/sql/CRYPTO_dynlock_value::lock |      27973 |    0.00 |       1463038056 |          29016 |          52080 |      364755672 |
      		 
      | wait/synch/mutex/mysys/THR_LOCK_heap             |        447 |    0.00 |        594349236 |          22320 |        1329528 |      107303400 |
      		 
      | wait/synch/mutex/sql/LOCK_prepared_stmt_count    |        152 |    0.00 |        376797312 |          32364 |        2478636 |       75084480 |
      		 
      | wait/synch/mutex/sql/LOCK_connection_count       |        131 |    0.00 |        313792416 |          41292 |        2395308 |      189914184 |
      		 
      | wait/synch/mutex/sql/LOCK_xid_cache              |         76 |    0.00 |        284776416 |          32364 |        3746784 |      211744260 |
      		 
      | wait/synch/mutex/innodb/os_mutex                 |       4143 |    0.00 |        191892108 |          21204 |          46128 |       32445468 |
      		 
      | wait/synch/mutex/sql/LOCK_thread_cache           |         65 |    0.00 |        180919224 |          37944 |        2783304 |      155651868 |
      		 
      | wait/synch/mutex/sql/hash_filo::lock             |         70 |    0.00 |        164830968 |          56916 |        2354388 |       63728064 |
      		 
      | wait/synch/mutex/sql/LOCK_thread_count           |        138 |    0.00 |        116970192 |          35712 |         847416 |       21228552 |
      		 
      | wait/synch/rwlock/sql/LOCK_system_variables_hash |        219 |    0.00 |        104554692 |          34596 |         477276 |       26174664 |
      		 
      | wait/synch/mutex/innodb/log_sys_mutex            |        525 |    0.00 |        100368576 |          22320 |         190836 |        8923536 |
      		 
      | wait/synch/mutex/innodb/buf_pool_mutex           |        635 |    0.00 |         82544940 |          21204 |         129828 |        8780688 |
      		 
      +--------------------------------------------------+------------+---------+------------------+----------------+----------------+----------------+
      		 
       
      		 
      MDEV-4956, PFS on
      		 
      64 threads, time spent: 60s, queries executed: 7324047, qps: 122067, 1 thread qps: 1907
      		 
      +--------------------------------------------------------+------------+---------+-----------------+----------------+----------------+----------------+
      		 
      | EVENT_NAME                                             | COUNT_STAR | SECONDS | SUM_TIMER_WAIT  | MIN_TIMER_WAIT | AVG_TIMER_WAIT | MAX_TIMER_WAIT |
      		 
      +--------------------------------------------------------+------------+---------+-----------------+----------------+----------------+----------------+
      		 
      | wait/synch/mutex/sql/LOCK_open                         |   14648304 |  912.68 | 912679187791575 |          21261 |       62305920 |     3902741895 |
      		 
      | wait/synch/mutex/mysys/THR_LOCK::mutex                 |   14648242 |  833.21 | 833211125250591 |          22380 |       56881008 |     4925503419 |
      		 
      | wait/synch/mutex/sql/THD::LOCK_thd_data                |   46037758 |   12.41 |  12406031833572 |          21261 |         269306 |      242050890 |
      		 
      | wait/synch/rwlock/sql/LOCK_tdc                         |    7279564 |    3.82 |   3819054537030 |          30213 |         524438 |     1065281286 |
      		 
      | wait/synch/rwlock/sql/MDL_lock::rwlock                 |    1047271 |    0.99 |    993892141989 |          22380 |         948912 |     1013477181 |
      		 
      | wait/synch/mutex/sql/MDL_map::mutex                    |     523837 |    0.36 |    364923934923 |          22380 |         696391 |      976317429 |
      		 
      | wait/synch/rwlock/innodb/checkpoint_lock               |          4 |    0.13 |    134763148392 |         249537 |    33690787098 |   106414390083 |
      		 
      | wait/synch/mutex/sql/LOCK_plugin                       |        898 |    0.02 |     23101894875 |          21261 |       25725810 |     1177274163 |
      		 
      | wait/synch/mutex/sql/LOCK_prepared_stmt_count          |        152 |    0.00 |      2685624618 |          26856 |       17668264 |     1344866793 |
      		 
      | wait/synch/mutex/mysys/THR_LOCK_heap                   |        192 |    0.00 |      1997128536 |          43641 |       10401478 |      368843661 |
      		 
      | wait/synch/rwlock/sql/CRYPTO_dynlock_value::lock       |      27973 |    0.00 |      1039603593 |          30213 |          36927 |        5417079 |
      		 
      | wait/synch/mutex/sql/LOCK_xid_cache                    |         76 |    0.00 |       729651783 |          27975 |        9600647 |      505937946 |
      		 
      | wait/synch/mutex/sql/LOCK_thread_count                 |        138 |    0.00 |       240211254 |          40284 |        1740418 |      137173734 |
      		 
      | wait/synch/mutex/sql/LOCK_status                       |         65 |    0.00 |       236282445 |          34689 |        3634885 |      115986588 |
      		 
      | wait/synch/mutex/innodb/os_mutex                       |       4156 |    0.00 |       229240205 |          21261 |          54831 |       13149369 |
      		 
      | wait/synch/mutex/sql/LOCK_connection_count             |        131 |    0.00 |       229013421 |          40284 |        1747878 |      174671424 |
      		 
      | wait/synch/mutex/sql/TABLE_SHARE::tdc.LOCK_table_share |        148 |    0.00 |       137936892 |          21261 |         931754 |       30593460 |
      		 
      | wait/synch/mutex/innodb/log_sys_mutex                  |        608 |    0.00 |       126692434 |          22380 |         208134 |       18035296 |
      		 
      | wait/synch/mutex/sql/hash_filo::lock                   |         70 |    0.00 |       125181411 |          42522 |        1788162 |       83745960 |
      		 
      | wait/synch/mutex/sql/LOCK_thread_cache                 |         65 |    0.00 |       114220806 |          34689 |        1757203 |       31404735 |
      		 
      +--------------------------------------------------------+------------+---------+-----------------+----------------+----------------+----------------+

      Attachments

        Issue Links

          duplicates

          Bug - A problem which impairs or prevents the functions of the product. MDEV-5448 Performance regression between 10.0.4 and 10.0.5 (~8%)

          • Major - Major loss of function.
          • Closed
          relates to

          Task - A task that needs to be done. MDEV-5492 Reduce usage of LOCK_open: TABLE::in_use

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-5864 Reduce usage of LOCK_open: TABLE_SHARE::tdc.free_tables

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-4702 Reduce usage of LOCK_open

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-4956 Reduce usage of LOCK_open: TABLE_SHARE::tdc.used_tables

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-5403 Reduce usage of LOCK_open: tc_count

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-5597 Reduce usage of LOCK_open: LOCK_flush

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-7292 Table_open_cache scalability issues

          • Major - Major loss of function.
          • Closed

          Activity

            People

              svoj Sergey Vojtovich
              svoj Sergey Vojtovich
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.