Uploaded image for project: 'Data Management'
  1. Data Management
  2. DM-8966

Performance plots with AP prototype on SSD

    XMLWordPrintable

    Details

    • Type: Story
    • Status: Done
    • Resolution: Done
    • Fix Version/s: None
    • Component/s: L1 Database
    • Labels:
    • Story Points:
      4
    • Sprint:
      DB_S17_01
    • Team:
      Data Access and Database

      Description

      Run my AP prototype on ccqserv150 with databases storage on SSD/NVMe and add performance figures here.

        Attachments

        1. my-insert-ssd.png
          my-insert-ssd.png
          29 kB
        2. my-select-ssd.png
          my-select-ssd.png
          28 kB
        3. pg-insert-nvme.png
          pg-insert-nvme.png
          27 kB
        4. pg-insert-ssd.png
          pg-insert-ssd.png
          28 kB
        5. pg-select-nvme.png
          pg-select-nvme.png
          28 kB
        6. pg-select-ssd.png
          pg-select-ssd.png
          27 kB

          Issue Links

            Activity

            Hide
            salnikov Andy Salnikov added a comment - - edited

            Small set of pictures comparing three test cases:

            • Postgres with data stored on SSD/NVMe disk
            • Postgres with data stored on SSD/SATA disk
            • MySQL (MariaDB 5.5.50) with data stored on SSD/SATA disk
            • PK on DiaObject table is (htmId20, diaObjectId, validityStart) like in the previous tests

            Note that MariaDB used out-of box setup with a standard buffer sizes which is probably not adequate, I'll test different configuration later.

            Plots for reading performance:



            Plots for writing performance:



            Show
            salnikov Andy Salnikov added a comment - - edited Small set of pictures comparing three test cases: Postgres with data stored on SSD/NVMe disk Postgres with data stored on SSD/SATA disk MySQL (MariaDB 5.5.50) with data stored on SSD/SATA disk PK on DiaObject table is (htmId20, diaObjectId, validityStart) like in the previous tests Note that MariaDB used out-of box setup with a standard buffer sizes which is probably not adequate, I'll test different configuration later. Plots for reading performance: Plots for writing performance:
            Hide
            salnikov Andy Salnikov added a comment -

            Comments for above plots:

            • performance looks very similar for SATA vs NVMe
            • reading performance for MySQL is on par with Postgres, writing performance for MySQL is worse, not sure if this is because performance is not optimized yet
            • performance gets worse with growing amount of data stored, 15k visit is approximately 2 weeks of observation time, even with that we are already close to limit I think.
            Show
            salnikov Andy Salnikov added a comment - Comments for above plots: performance looks very similar for SATA vs NVMe reading performance for MySQL is on par with Postgres, writing performance for MySQL is worse, not sure if this is because performance is not optimized yet performance gets worse with growing amount of data stored, 15k visit is approximately 2 weeks of observation time, even with that we are already close to limit I think.
            Hide
            salnikov Andy Salnikov added a comment -

            One issue that I saw while doing these tests is that mysql sometimes returns errors for what I think is a legitimate INSERT statement, complaining about duplicate primary key. I spent a lot of time trying to understand it, I'm inclined to think this may be a bug in MySQL/MariaDB/InnoDB. Opened a ticket with MariaDB to try to figure it out: https://jira.mariadb.org/browse/MDEV-11760

            Show
            salnikov Andy Salnikov added a comment - One issue that I saw while doing these tests is that mysql sometimes returns errors for what I think is a legitimate INSERT statement, complaining about duplicate primary key. I spent a lot of time trying to understand it, I'm inclined to think this may be a bug in MySQL/MariaDB/InnoDB. Opened a ticket with MariaDB to try to figure it out: https://jira.mariadb.org/browse/MDEV-11760
            Hide
            salnikov Andy Salnikov added a comment -

            Turns out the bug is not really a bug but a "nice" feature of MySQL (like many other features). It happens because MySQL decides to auto-increment ID value when inserted ID value is 0. I need to turn off AUTO_INCREMENT flag that is added to schema by sqlalchemy.

            Show
            salnikov Andy Salnikov added a comment - Turns out the bug is not really a bug but a "nice" feature of MySQL (like many other features). It happens because MySQL decides to auto-increment ID value when inserted ID value is 0. I need to turn off AUTO_INCREMENT flag that is added to schema by sqlalchemy.
            Hide
            salnikov Andy Salnikov added a comment -

            Nothing to review here, progress will be covered in next tickets.

            Show
            salnikov Andy Salnikov added a comment - Nothing to review here, progress will be covered in next tickets.

              People

              Assignee:
              salnikov Andy Salnikov
              Reporter:
              salnikov Andy Salnikov
              Watchers:
              Andy Salnikov
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  CI Builds

                  No builds found.