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

SQL statements for database initialisation should be run in a transaction


    • Icon: Incident report Incident report
    • Resolution: Fixed
    • Icon: Blocker Blocker
    • 1.9.9 (beta)
    • 1.8.6
    • Proxy (P), Server (S)
    • 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.

      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:

      ...create schema...

      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:

      INSERT INTO...
      INSERT INTO...
      INSERT INTO...
      ... (>12k lines of INSERTs)

      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.

            Unassigned Unassigned
            signum Christoph Haas
            0 Vote for this issue
            1 Start watching this issue