Uploaded image for project: 'ZABBIX BUGS AND ISSUES'
  1. ZABBIX BUGS AND ISSUES
  2. ZBX-4024

SQL statements for database initialisation should be run in a transaction

    XMLWordPrintable

    Details

    • Type: Incident report
    • Status: Closed
    • Priority: Blocker
    • Resolution: Fixed
    • Affects Version/s: 1.8.6
    • Fix Version/s: 1.9.9 (beta)
    • Component/s: Proxy (P), Server (S)
    • Environment:
      Debian GNU/Linux "testing". Linux kernel 2.6.38. 64 bit (aka "amd64"). SQLite version 3.7.7. Laptop with Core i7 system with 4 GB of RAM and 128 GB SSD.

      Description

      The SQL statements used to initialise the database should be encapsulated in a transaction. Current situation:

      In create/schema/sqlite.sql you use a transaction:

      BEGIN TRANSACTION;
      ...create schema...
      COMMIT;

      In create/data/data.sql you don't use a transaction:
      INSERT INTO...
      INSERT INTO...
      INSERT INTO...
      ... (>12k lines of INSERTs)

      When using SQLite3 this leads to an fsync() call after each INSERT statement. On a decent server with a non-decent file system (ext3 does not handle fsync() correctly) and a decent write-cache this may work. But on my development system I use ext4 with an SSD and populating the SQLite3 database takes over an hour! With ext3 it takes half an hour. When using strace I see that the system is stuck time and again on fsync() calls. Besides this has surely worn down my SSD a lot.

      I investigated a little and it appears like SQLite is doing the right thing using fsync after each write action. In fact they recommend to use transactions so that only one fsync is happening after the transaction is committed. I changed the create/data/data.sql as follows:

      BEGIN;
      INSERT INTO...
      INSERT INTO...
      INSERT INTO...
      ... (>12k lines of INSERTs)
      COMMIT;

      This initialises the database within just one second.

      I researched on MySQL and PostgreSQL and both understand BEGIN and COMMIT (but not "BEGIN TRANSACTION") so it should be safe to use. I cannot test for DB2 and Oracle though.

      I flagged this issue an "improvement" but in fact it's a serious problem that wastes a lot of time during installation and damages disks. As you just have to add BEGIN and COMMIT this should be simple to fix.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              Unassigned Unassigned
              Reporter:
              signum Christoph Haas
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: