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

Benchmark *execution* speed of simple DBT-3 query: MariaDB vs MySQL-it-merged-from

Details

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

    Description

      quote from the email:

      Date: Wed, 06 Feb 2013 20:03:00 -0800
      From: Igor Babaev <igor@askmonty.org>
      To: dev@lists.askmonty.org
      Subject: Re: DBT3 next round

      If we have a lower performance for MariaDB 10.0.1 for the same queries
      with the same execution plans we need all possible data to analyze the
      problem. To have the results of a profiling of the underperformed query
      would be perfect.
      Bare in mind only that MariaDB 10.0.1 is based on MySQL 5.6.5. So a
      worse performance of MariaDB 10.0.1 in comparison with MySQL 5.6.5 on a
      query indicates that we have probably a bug in our code.

      Please focus first on simple queries like Q1 and see why the numbers are
      worse there (if they are really worse) than for MySQL 5.6.5.

      Message-ID: <512AE0AD.9060406@askmonty.org>
      Date: Sun, 24 Feb 2013 19:55:25 -0800
      From: Igor Babaev <igor@askmonty.org>
      To: Sergei Petrunia <psergey@askmonty.org>, Axel Schwenke <axel@askmonty.org>
      CC: dev@lists.askmonty.org

      Axel,
      Sergey's idea to factor out InnoDB seems to be quite productive.
      Could you please to get the same profiles for the MyISAM DBT3
      as you got for InnoDB DBT3?

      Attachments

        1. dbt3-q1-profiles-mar19.ods
          26 kB
        2. tags
          4 kB
        3. test-image-1.png
          test-image-1.png
          11 kB

        Issue Links

          includes

          Task - A task that needs to be done. MDEV-4308 DBT-3 Q1 benchmark: Check/profile reduced queries

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed

          Task - A task that needs to be done. MDEV-4309 DBT-3 Q1 benchmark: Benchmark + profile a patch

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            psergei Sergei Petrunia created issue -
            axel Axel Schwenke made changes -
            Field Original Value New Value
            Status Open [ 1 ] In Progress [ 3 ]
            axel Axel Schwenke made changes -
            Comment [ The benchmark tool is available here: http://bazaar.launchpad.net/~ahel/maria/mariadb-benchmarks/files/head:/sqlite/

            First results. The comparison was Sqlite 3.7.2 (comes with Ubuntu 12) vs. MariaDB-5.5.29.

            MariaDB is running with this my.cnf:

            [mysqld]
            skip-networking
            socket = /tmp/mysqld.sock.xl
            max-connections=100
            table-open-cache=100
            thread-cache=16
            innodb-file-per-table
            innodb-log-buffer-size=8M
            innodb-log-file-size=128M
            innodb-buffer-pool-instances=8

            results from my laptop (i5, dual core + HT, manually forced to 800MHz, SSD)

            result summary for DSN='DBI:mysql:database=test;mysql_socket=/tmp/mysqld.sock.xl;mysql_server_prepare=1'
            select iterations: 10000, trx size: 1000
            ----------------------------------------
            threads 1 1 2 4 8 16
            rows insert select select select select select
            10 0.00664 2.149 2.969 4.126 8.040 15.81
            100 0.0257 2.190 2.976 4.034 7.823 15.80
            1000 0.164 2.173 2.913 4.131 7.913 15.81
            10000 1.658 2.198 2.957 4.212 7.835 15.87
            100000 15.41 2.303 2.969 4.016 7.850 15.88
            ----------------------------------------

            result summary for DSN='DBI:mysql:database=test;mysql_socket=/tmp/mysqld.sock.xl'
            select iterations: 10000, trx size: 1000
            ----------------------------------------
            threads 1 1 2 4 8 16
            rows insert select select select select select
            10 0.00749 2.434 3.165 4.495 8.576 17.18
            100 0.0218 2.417 3.207 4.551 8.625 17.39
            1000 0.195 2.426 3.184 4.653 8.703 17.32
            10000 1.895 2.468 3.200 4.448 8.611 17.32
            100000 18.00 2.593 3.217 4.580 8.614 17.28
            ----------------------------------------

            result summary for DSN='DBI:SQLite:dbname=/tmp/test.db'
            select iterations: 10000, trx size: 1000
            ----------------------------------------
            threads 1 1 2 4 8 16
            rows insert select select select select select
            10 0.0276 0.361 0.455 0.731 1.404 2.812
            100 0.0297 0.341 0.484 0.769 1.444 2.862
            1000 0.0445 0.364 0.434 0.786 1.484 3.035
            10000 0.467 0.370 0.601 0.787 1.573 3.284
            100000 4.832 0.423 0.513 0.948 1.826 3.587
            ----------------------------------------

            result summary for DSN='DBI:SQLite:dbname=/dev/shm/test.db'
            select iterations: 10000, trx size: 1000
            ----------------------------------------
            threads 1 1 2 4 8 16
            rows insert select select select select select
            10 0.00099 0.340 0.412 0.763 1.413 2.834
            100 0.00291 0.363 0.416 0.773 1.519 2.915
            1000 0.0237 0.366 0.435 0.742 1.517 3.005
            10000 0.252 0.396 0.521 0.804 1.582 3.182
            100000 2.537 0.447 0.553 0.903 1.803 3.606
            ----------------------------------------


            results for my "server" machine (Athlon64, 4 cores, manually forced to 800MHz, spinning disk)

            result summary for DSN='DBI:mysql:database=test;mysql_socket=/tmp/mysqld.sock.xl;mysql_server_prepare=1'
            select iterations: 10000, trx size: 1000
            ----------------------------------------
            threads 1 1 2 4 8 16
            rows insert select select select select select
            10 0.00866 2.005 2.050 2.081 4.240 8.909
            100 0.0159 2.015 2.070 2.081 4.244 8.811
            1000 0.152 2.009 2.054 2.084 4.296 8.799
            10000 1.719 2.040 2.064 2.079 4.433 8.795
            100000 15.03 2.165 2.074 2.310 4.275 8.870
            ----------------------------------------

            result summary for DSN='DBI:mysql:database=test;mysql_socket=/tmp/mysqld.sock.xl'
            select iterations: 10000, trx size: 1000
            ----------------------------------------
            threads 1 1 2 4 8 16
            rows insert select select select select select
            10 0.00805 2.267 2.322 2.394 5.148 10.03
            100 0.0181 2.310 2.323 2.337 4.825 10.03
            1000 0.176 2.341 2.332 2.363 4.867 10.09
            10000 1.827 2.320 2.321 2.343 4.854 10.08
            100000 17.77 2.436 2.322 2.360 4.885 10.23
            ----------------------------------------

            result summary for DSN='DBI:SQLite:dbname=/tmp/test.db'
            select iterations: 10000, trx size: 1000
            ----------------------------------------
            threads 1 1 2 4 8 16
            rows insert select select select select select
            10 0.00395 0.534 0.540 0.535 1.069 2.124
            100 0.0790 0.544 0.550 0.554 1.127 2.190
            1000 0.0536 0.561 0.560 0.563 1.162 2.252
            10000 0.556 0.582 0.584 0.587 1.179 2.354
            100000 5.982 0.637 0.641 0.653 1.313 2.595
            ----------------------------------------

            result summary for DSN='DBI:SQLite:dbname=/dev/shm/test.db'
            select iterations: 10000, trx size: 1000
            ----------------------------------------
            threads 1 1 2 4 8 16
            rows insert select select select select select
            10 0.00112 0.540 0.542 0.538 1.081 2.142
            100 0.00466 0.552 0.555 0.557 1.116 2.204
            1000 0.0445 0.564 0.563 0.571 1.161 2.281
            10000 0.467 0.591 0.590 0.592 1.191 2.367
            100000 4.917 0.648 0.653 0.657 1.320 2.652
            ----------------------------------------


            observations:

            - Sqlite is damned fast; inserts scale nearly linearly with number of rows; selects scale nearly linearly over threads; index performance is good
            - Sqlite inserts can profit a little from having the DB file on a ram disk; no impact on selects
            - for MySQL it is important to use the binary protocol
            - MySQL scales linearly over rows (insert) and threads (select); indexes are still better than with Sqlite

            It seems impossible to beat Sqlite with separate server and client process - the communication overhead is eating up all benefits. Also Sqlite can obviously do concurrent reads now.

            Things might look different with an embedded MySQL/MariaDB. Concurrent DML was not tested, it is expected that MySQL/MariaDB has benefits there.
            ]
            axel Axel Schwenke made changes -
            Status In Progress [ 3 ] Open [ 1 ]
            axel Axel Schwenke made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            axel Axel Schwenke made changes -
            Description quote from the email:

            Date: Wed, 06 Feb 2013 20:03:00 -0800
            From: Igor Babaev <igor@askmonty.org>
            To: dev@lists.askmonty.org
            Subject: Re: DBT3 next round

            If we have a lower performance for MariaDB 10.0.1 for the same queries
            with the same execution plans we need all possible data to analyze the
            problem. To have the results of a profiling of the underperformed query
            would be perfect.
            Bare in mind only that MariaDB 10.0.1 is based on MySQL 5.6.5. So a
            worse performance of MariaDB 10.0.1 in comparison with MySQL 5.6.5 on a
            query indicates that we have probably a bug in our code.

            Please focus first on simple queries like Q1 and see why the numbers are
            worse there (if they are really worse) than for MySQL 5.6.5.
            		 quote from the email:

            Date: Wed, 06 Feb 2013 20:03:00 -0800
            From: Igor Babaev <igor@askmonty.org>
            To: dev@lists.askmonty.org
            Subject: Re: DBT3 next round

            If we have a lower performance for MariaDB 10.0.1 for the same queries
            with the same execution plans we need all possible data to analyze the
            problem. To have the results of a profiling of the underperformed query
            would be perfect.
            Bare in mind only that MariaDB 10.0.1 is based on MySQL 5.6.5. So a
            worse performance of MariaDB 10.0.1 in comparison with MySQL 5.6.5 on a
            query indicates that we have probably a bug in our code.

            Please focus first on simple queries like Q1 and see why the numbers are
            worse there (if they are really worse) than for MySQL 5.6.5.

            -----

            Message-ID: <512AE0AD.9060406@askmonty.org>
            Date: Sun, 24 Feb 2013 19:55:25 -0800
            From: Igor Babaev <igor@askmonty.org>
            To: Sergei Petrunia <psergey@askmonty.org>, Axel Schwenke <axel@askmonty.org>
            CC: dev@lists.askmonty.org

            Axel,
            Sergey's idea to factor out InnoDB seems to be quite productive.
            Could you please to get the same profiles for the MyISAM DBT3
            as you got for InnoDB DBT3?
            psergei Sergei Petrunia made changes -
            Attachment Q1exec.png [ 21201 ]
            psergei Sergei Petrunia made changes -
            Attachment Q1exec.png [ 21201 ]
            psergei Sergei Petrunia made changes -
            Attachment Q1exec.png [ 21202 ]
            psergei Sergei Petrunia made changes -
            Attachment Q1exec.png [ 21202 ]
            psergei Sergei Petrunia made changes -
            Attachment Q1exec.png [ 21203 ]
            psergei Sergei Petrunia made changes -
            Attachment Q1exec.png [ 21203 ]
            psergei Sergei Petrunia made changes -
            Attachment test-image-1.png [ 21206 ]
            serg Sergei Golubchik made changes -
            Attachment bug.gif [ 21207 ]
            serg Sergei Golubchik made changes -
            Attachment bug.gif [ 21207 ]
            serg Sergei Golubchik made changes -
            Attachment tags [ 21208 ]
            serg Sergei Golubchik made changes -
            serg Sergei Golubchik made changes -
            axel Axel Schwenke made changes -
            Status In Progress [ 3 ] Open [ 1 ]
            psergei Sergei Petrunia made changes -
            Priority Major [ 3 ] Critical [ 2 ]
            psergei Sergei Petrunia made changes -
            psergei Sergei Petrunia made changes -
            psergei Sergei Petrunia made changes -
            Attachment dbt3-q1-profiles-mar19.ods [ 21402 ]
            axel Axel Schwenke made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            axel Axel Schwenke made changes -
            axel Axel Schwenke made changes -
            Status In Progress [ 3 ] Open [ 1 ]
            axel Axel Schwenke made changes -
            Priority Critical [ 2 ] Major [ 3 ]
            psergei Sergei Petrunia made changes -
            Resolution Fixed [ 1 ]
            Status Open [ 1 ] Closed [ 6 ]
            serg Sergei Golubchik made changes -
            Workflow defaullt [ 26135 ] MariaDB v2 [ 43797 ]
            ratzpo Rasmus Johansson (Inactive) made changes -
            Workflow MariaDB v2 [ 43797 ] MariaDB v3 [ 64111 ]
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 64111 ] MariaDB v4 [ 132075 ]

            People

              axel Axel Schwenke
              psergei Sergei Petrunia
              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.