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

Mariadb drop in sysbench Tps for higher duration run in tmpfs filesystem

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Minor
    • Resolution: Won't Fix
    • Affects Version/s: 10.3.2
    • Fix Version/s: N/A
    • Component/s: Server
    • Labels:
    • Environment:
      Ubuntu 16.0.2

      Description

      I want to confirm if this is the expected behaviour on tmpfs/ramdisk filesystem

      While benchmarking sysbench oltp_update_index lua for a 150 sec run I see drop in tps consistently after 80 sec . This is happening both in intel x86 as well as ARM platform but hurting ARM a little more as good as 10%,

      Setup : In memory filesystem( datadir is kept in tmpfs filesystem) and bufferpool is 50gb, innodb_flush_log_at_trx_commit =0,and logfile size is 2 gb ,bufferpool instance is 30

      Test : 20 tables with 100000 records in each table. Run for 64 threads with sysbench oltp_update_index.lua (update tests)

      Time : 150 sec

      Few observation :
      1) I don’t see the issue in pure I/O operations ie The bufferpool size is kept default and datadir is in ssd’s instead of tmpfs.
      2) The tps as expected is heavily dropped in pure I/O so the issues seems to have masked.
      3) Flushing seems to be slow when we do in memory test and not able to cope up with read speed in tmpfs.
      4) In pure I/O read/write test I see innodb_flush_sync =0 is not recommended as the TPS is almost 40% compared to async operations.

      017-11-24 9:40:34 76 [Note] Detected table cache mutex contention at instance 1: 20% waits. Additional table cache instance activated. Number of instances after activation: 2.
      [ 10s ] thds: 64 tps: 138157.88 qps: 138157.88 (r/w/o: 0.00/138157.88/0.00) lat (ms,95%): 1.14 err/s: 0.00 reconn/s: 0.00
      [ 20s ] thds: 64 tps: 164025.68 qps: 164025.68 (r/w/o: 0.00/164025.68/0.00) lat (ms,95%): 0.94 err/s: 0.00 reconn/s: 0.00
      [ 30s ] thds: 64 tps: 164611.23 qps: 164611.23 (r/w/o: 0.00/164611.23/0.00) lat (ms,95%): 0.94 err/s: 0.00 reconn/s: 0.00
      [ 40s ] thds: 64 tps: 164317.42 qps: 164317.42 (r/w/o: 0.00/164317.42/0.00) lat (ms,95%): 0.94 err/s: 0.00 reconn/s: 0.00
      [ 50s ] thds: 64 tps: 164128.75 qps: 164128.85 (r/w/o: 0.00/164128.85/0.00) lat (ms,95%): 0.94 err/s: 0.00 reconn/s: 0.00
      [ 60s ] thds: 64 tps: 164028.19 qps: 164028.19 (r/w/o: 0.00/164028.19/0.00) lat (ms,95%): 0.94 err/s: 0.00 reconn/s: 0.00
      [ 70s ] thds: 64 tps: 148203.75 qps: 148203.65 (r/w/o: 0.00/148203.65/0.00) lat (ms,95%): 1.23 err/s: 0.00 reconn/s: 0.00
      [ 80s ] thds: 64 tps: 113156.11 qps: 113156.11 (r/w/o: 0.00/113156.11/0.00) lat (ms,95%): 1.58 err/s: 0.00 reconn/s: 0.00
      [ 90s ] thds: 64 tps: 118685.99 qps: 118685.99 (r/w/o: 0.00/118685.99/0.00) lat (ms,95%): 1.52 err/s: 0.00 reconn/s: 0.00
      [ 100s ] thds: 64 tps: 118142.32 qps: 118142.32 (r/w/o: 0.00/118142.32/0.00) lat (ms,95%): 1.55 err/s: 0.00 reconn/s: 0.00
      [ 110s ] thds: 64 tps: 122747.22 qps: 122747.22 (r/w/o: 0.00/122747.22/0.00) lat (ms,95%): 1.50 err/s: 0.00 reconn/s: 0.00
      [ 120s ] thds: 64 tps: 112475.31 qps: 112475.31 (r/w/o: 0.00/112475.31/0.00) lat (ms,95%): 1.58 err/s: 0.00 reconn/s: 0.00
      [ 130s ] thds: 64 tps: 112336.17 qps: 112336.17 (r/w/o: 0.00/112336.17/0.00) lat (ms,95%): 1.58 err/s: 0.00 reconn/s: 0.00
      [ 140s ] thds: 64 tps: 121541.22 qps: 121541.22 (r/w/o: 0.00/121541.22/0.00) lat (ms,95%): 1.50 err/s: 0.00 reconn/s: 0.00
      [ 150s ] thds: 64 tps: 116951.59 qps: 116951.59 (r/w/o: 0.00/116951.59/0.00) lat (ms,95%): 1.55 err/s: 0.00 reconn/s: 0.00

      I modified the cnf files to use the below two parameter

      innodb_flush_sync=0
      innodb_io_capacity =8000

      [ 10s ] thds: 64 tps: 139392.95 qps: 139392.95 (r/w/o: 0.00/139392.95/0.00) lat (ms,95%): 1.08 err/s: 0.00 reconn/s: 0.00
      [ 20s ] thds: 64 tps: 163050.96 qps: 163050.96 (r/w/o: 0.00/163050.96/0.00) lat (ms,95%): 0.90 err/s: 0.00 reconn/s: 0.00
      [ 30s ] thds: 64 tps: 162052.53 qps: 162052.53 (r/w/o: 0.00/162052.53/0.00) lat (ms,95%): 0.90 err/s: 0.00 reconn/s: 0.00
      [ 40s ] thds: 64 tps: 162532.91 qps: 162532.91 (r/w/o: 0.00/162532.91/0.00) lat (ms,95%): 0.92 err/s: 0.00 reconn/s: 0.00
      [ 50s ] thds: 64 tps: 163324.90 qps: 163324.90 (r/w/o: 0.00/163324.90/0.00) lat (ms,95%): 0.90 err/s: 0.00 reconn/s: 0.00
      [ 60s ] thds: 64 tps: 164528.45 qps: 164528.65 (r/w/o: 0.00/164528.65/0.00) lat (ms,95%): 0.89 err/s: 0.00 reconn/s: 0.00
      [ 70s ] thds: 64 tps: 164178.11 qps: 164177.91 (r/w/o: 0.00/164177.91/0.00) lat (ms,95%): 0.89 err/s: 0.00 reconn/s: 0.00
      [ 80s ] thds: 64 tps: 162853.44 qps: 162853.44 (r/w/o: 0.00/162853.44/0.00) lat (ms,95%): 0.90 err/s: 0.00 reconn/s: 0.00
      [ 90s ] thds: 64 tps: 163859.76 qps: 163859.76 (r/w/o: 0.00/163859.76/0.00) lat (ms,95%): 0.89 err/s: 0.00 reconn/s: 0.00
      [ 100s ] thds: 64 tps: 163382.50 qps: 163382.50 (r/w/o: 0.00/163382.50/0.00) lat (ms,95%): 0.89 err/s: 0.00 reconn/s: 0.00
      [ 110s ] thds: 64 tps: 164321.72 qps: 164321.82 (r/w/o: 0.00/164321.82/0.00) lat (ms,95%): 0.87 err/s: 0.00 reconn/s: 0.00
      [ 120s ] thds: 64 tps: 163324.30 qps: 163324.20 (r/w/o: 0.00/163324.20/0.00) lat (ms,95%): 0.90 err/s: 0.00 reconn/s: 0.00
      [ 130s ] thds: 64 tps: 164130.22 qps: 164130.22 (r/w/o: 0.00/164130.22/0.00) lat (ms,95%): 0.89 err/s: 0.00 reconn/s: 0.00
      [ 140s ] thds: 64 tps: 163013.68 qps: 163013.68 (r/w/o: 0.00/163013.68/0.00) lat (ms,95%): 0.90 err/s: 0.00 reconn/s: 0.00
      [ 150s ] thds: 64 tps: 155699.33 qps: 155699.33 (r/w/o: 0.00/155699.33/0.00) lat (ms,95%): 1.08 err/s: 0.00 reconn/s: 0.00

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              axel Axel Schwenke
              Reporter:
              ssethia Sandeep sethia
              Votes:
              0 Vote for this issue
              Watchers:
              4 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.