Uploaded image for project: 'ZABBIX FEATURE REQUESTS'
  1. ZABBIX FEATURE REQUESTS
  2. ZBXNEXT-1336

PostgreSQL uses NUMERIC() but should use BIGINT

    XMLWordPrintable

    Details

    • Type: New Feature Request
    • Status: Closed
    • Priority: Minor
    • Resolution: Duplicate
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: Server (S)
    • Labels:
    • Environment:
      PostgreSQL

      Description

      PostgreSQL's schema currently uses NUMERIC(20,0) as it's datatype for the history_uint table. Instead, with my PostgreSQL DBA/developer hat on, PostgreSQL should be using BIGINT instead. This should be good for performance and reducing storage. -sc

      The below text comes from my forum post here:

      http://www.zabbix.com/forum/showthread.php?p=105335#post105335

      Hello. Is there a reason why PostgreSQL uses NUMERIC(20,0) instead of BIGINT for it's history_uint? BIGINT is always 8 bytes on disk, whereas NUMERIC is "two bytes for each group of four decimal digits, plus three to eight bytes overhead," per the documentation[1].

      I realize that BIGINT is signed, but per docs from PostgreSQL, NUMERIC is closer to a VARCHAR than a normal binary integer. Assuming the average zabbix user has has an average most of their counters over 1M, then using BIGINT would be a savings. Even in my small dev environment, there's a tiny savings. People storing large quantities of values where history_uint.value is more than 1M, the savings would be more pronounced.

      Code:
      ---------
      zabbix=# CREATE TABLE history_uint2 AS SELECT * FROM history_uint;
      SELECT 5918325
      zabbix=# ALTER TABLE history_uint2 ALTER COLUMN value SET DATA TYPE BIGINT;
      ERROR: bigint out of range
      zabbix=# DELETE FROM history_uint2 WHERE value > 9223372036854775807; – This value is (2 ^ 63 - 1)
      DELETE 17906
      zabbix=# ALTER TABLE history_uint2 ALTER COLUMN value SET DATA TYPE BIGINT;
      ALTER TABLE
      zabbix=# \dt+ history_uint*
      List of relations
      Schema | Name | Type | Owner | Size | Description
      ----------------------------------------------+------------
      zabbix20 | history_uint | table | zabbix20 | 363 MB |
      zabbix20 | history_uint2 | table | pgsql | 339 MB |
      ---------

      Incidentally, it looks like vm.memory.size[available] is populating bogus values:

      Code:
      ---------
      zabbix=# SELECT COUNT, i.key_ FROM history_uint AS h, items AS i WHERE i.itemid = h.itemid AND h.value >= pow(2,63) GROUP BY i.key_; count | key_
      ------+--------------------------
      17931 | vm.memory.size[available]
      (1 row)
      zabbix=# SELECT i.itemid, i.key_, h.value, pow(2,64) - h.value AS delta_from_2_pow_64 FROM history_uint AS h, items AS i WHERE i.itemid = h.itemid AND h.value > 9223372036854775808 LIMIT 10;
      itemid | key_ | value | delta_from_2_pow_64
      -------------------------------------------------------------+--------------------
      100100000023975 | vm.memory.size[available] | 18446744071572967424 | 2136584192
      100100000023867 | vm.memory.size[available] | 18446744072915468288 | 794083328
      100100000024119 | vm.memory.size[available] | 18446744072050368512 | 1659183104
      100100000023975 | vm.memory.size[available] | 18446744071603822592 | 2105729024
      100100000023867 | vm.memory.size[available] | 18446744072916017152 | 793534464
      100100000024119 | vm.memory.size[available] | 18446744072045858816 | 1663692800
      100100000023867 | vm.memory.size[available] | 18446744072916017152 | 793534464
      100100000024119 | vm.memory.size[available] | 18446744072044445696 | 1665105920
      100100000023867 | vm.memory.size[available] | 18446744072916017152 | 793534464
      100100000023975 | vm.memory.size[available] | 18446744071566131200 | 2143420416
      (10 rows)
      ---------

      [1] http://www.postgresql.org/docs/current/static/datatype-numeric.html#DATATYPE-NUMERIC-DECIMAL

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              Unassigned Unassigned
              Reporter:
              seanc Sean Chittenden
              Votes:
              1 Vote for this issue
              Watchers:
              1 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: