Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1bkAdH-0003Fc-4f for pgsql-performance@arkaria.postgresql.org; Wed, 14 Sep 2016 13:53:47 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1bkAdG-0005wZ-Er for pgsql-performance@arkaria.postgresql.org; Wed, 14 Sep 2016 13:53:46 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1bkAdE-0005qj-UO for pgsql-performance@postgresql.org; Wed, 14 Sep 2016 13:53:45 +0000 Received: from mail-wm0-x232.google.com ([2a00:1450:400c:c09::232]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1bkAd7-0001P5-Lb for pgsql-performance@postgresql.org; Wed, 14 Sep 2016 13:53:43 +0000 Received: by mail-wm0-x232.google.com with SMTP id b187so48464513wme.1 for ; Wed, 14 Sep 2016 06:53:37 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=from:subject:message-id:date:to:mime-version; bh=f1d6DzUQZMb4Lqz9FBb2cd9zhNfWSRWh38JmDD+ti10=; b=u6kfce/ITNNohmoZ8dat/PQsaSgjRTyaI/o56y/1Rfwudn9x7Ca1Qnzrk2tfsiKEev y9/jumNq1y52o21E+eu7kH8oweF3JgDzVm9qPvLkMis9mBFwl8M/JycISsV0YAK7It4G tUwQvI5B1BY0DpD0h81CY3SbQ7M+P+r+gbo77bRJHx8DbB+96rQZDBAFMKArpo8Kec/x ctqgQnnlS53XBYqRehHM9ttYUKp8dPANlMCjdCTl5z+fFM0yePuLdckyDibfe1FZLn33 x4Qg3ij+URicrAmrOUUPU2QiaaGLbUaZ4Kmu67Gebv+wHeIH7ssbiUbdjMQANpSVA0yf IzOQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20130820; h=x-gm-message-state:from:subject:message-id:date:to:mime-version; bh=f1d6DzUQZMb4Lqz9FBb2cd9zhNfWSRWh38JmDD+ti10=; b=bxaFqcyvfQo0aG3rKzop5i3B+4qZLN3O5kh0YrFLlm0ZG+osagDla0e3Sl2FxbQ03J B70Omj7baqsK2PZyccScspduw6DmrN+BFcI//n/Qn7WM/TYWOSL/QL1z17sXoPauXJyb Pi3zYB/DgprB7D+Xrxwco4WATrE1D/dJLhibV7xbeBEKqDFZ84oIj6OdsxaqfHi8iRX1 inwWmG72LvOongQxUrfcTbCACi56u5iRKeQuWBn5/al0FZLxBavZdcYf+hP57dOaQ9sF uCinJG2Zg4roGtLwiXfmBqedhS/qLsWsXr65WmJc83ASpWZwhZRlwevP/mDkTBDQJcmm qNWw== X-Gm-Message-State: AE9vXwMgNjxPRZLyG6ib47wAhCnrEVkTah03yXTZ3Hkl89O4kspK7hK499Qoa5D6PzfFSw== X-Received: by 10.194.235.166 with SMTP id un6mr2717448wjc.169.1473861215194; Wed, 14 Sep 2016 06:53:35 -0700 (PDT) Received: from [192.168.2.5] (2-230-140-245.ip202.fastwebnet.it. [2.230.140.245]) by smtp.gmail.com with ESMTPSA id i127sm4360037wmf.4.2016.09.14.06.53.33 for (version=TLS1 cipher=ECDHE-RSA-AES128-SHA bits=128/128); Wed, 14 Sep 2016 06:53:33 -0700 (PDT) From: Pietro Pugni Content-Type: multipart/alternative; boundary="Apple-Mail=_31254E46-C4BA-4DA9-BED7-484827434780" Subject: Disk filled-up issue after a lot of inserts and drop schema Message-Id: Date: Wed, 14 Sep 2016 15:53:33 +0200 To: pgsql-performance@postgresql.org Mime-Version: 1.0 (Mac OS X Mail 9.3 \(3124\)) X-Mailer: Apple Mail (2.3124) X-Pg-Spam-Score: -2.7 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org --Apple-Mail=_31254E46-C4BA-4DA9-BED7-484827434780 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 I=E2=80=99m running PostgreSQL 9.5.4 on a virtual machine for production = purposes. It runs Ubuntu 16.04.1 LTS 64bit, 32GB RAM, 461GB disk space = and 4 x logical CPUs. Postgres executes the following activities: - many INSERTS for ETL - a lot of read and write operations for the main OLTP application The ETL job is still under development, so I=E2=80=99m launching several = sequential =E2=80=9Ctries=E2=80=9D in order to get the whole thing = working. The ETL procedure consists of a lot of inserts packed inside = transactions. At the moment each transaction consists of 100k inserts, = so for a 90mln rows table I get 90mln inserts packed in 900 = transactions. I know it=E2=80=99s not the best, but JDBC drivers = combined with Pentaho doesn=E2=80=99t seem to pack more inserts into = one, so I get a lot of overhead. I can see INSERT, BIND and PARSE called = for each insert.. I think it=E2=80=99s Pentaho which embeds the INSERT = in a parametric query.. I hate Pentaho.. anyway.. The ETL procedure does the following: 1) DROP SCHEMA IF EXISTS data_schema CASCADE; 2) creates the =E2=80=9Cdata_schema=E2=80=9D schema and populates it = with tables and rows using INSERTs as described before; 3) if an error occurs, drop the schema I=E2=80=99m repeating the previous steps many times because of some = Pentaho errors which the team is working on in order to get it working. = This stresses the WAL because the interruption of the process interrupts = the current transaction and is followed by a DROP SCHEMA .. CASCADE. After few days since we began debugging the ETL elaboration, the disk = filled up and the last ETL job was automatically aborted. Note that the = DB data directory is located on the same root disk at = /var/lib/postgresql/9.5/main What shocked me was that the data directory of Postgres was just 815MB = in size ($ du -h /var/lib/postgresql/9.5/main ) and pg_xlog was 705MB, = but the entire disk was full ("df -h" returned a disk usage of 100%). I looked for any postgres activity and only noticed a checkpoint writer = process that was writing at low speeds (IO usage was about 5%). Also, "SELECT * FROM pg_stat_activity" returned nothing and the most = shocking part was that the "du -h /=E2=80=9C command returned 56GB as = the total size of files stored on the whole disk!!! The same was for = =E2=80=9Cdu -ha /=E2=80=9C, which returns the apparent size.=20 The total disk size is 461GB, so how is it possible that =E2=80=9Cdf = -h=E2=80=9D resulted in 461GB occupied while =E2=80=9Cdu -h /=E2=80=9C = returned just 56GB? After executing: $ service postgresql stop $ service postgresql start the disk was freed and =E2=80=9Cdf -h=E2=80=9D returned a usage of just = 16%! The other questions are: - how can I prevent the disk from filling up? I=E2=80=99m using the = default configuration for the WAL (1GB max size). - how can I tune Postgres to speed up the INSERTs? The actual configuration is the following: listen_addresses =3D 'localhost' max_connections =3D 32 shared_buffers =3D 16GB work_mem =3D 128MB maintenance_work_mem =3D 512MB effective_io_concurrency =3D 10 checkpoint_completion_target =3D 0.9 cpu_tuple_cost =3D 0.02 cpu_index_tuple_cost =3D 0.01 cpu_operator_cost =3D 0.005 effective_cache_size =3D 24GB default_statistics_target =3D 1000 May be that some of these parameters causes this strange behavior? = checkpoint_completion_target? Thanks to everyone for the support. Best regards, Pietro Pugni --Apple-Mail=_31254E46-C4BA-4DA9-BED7-484827434780 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8 I=E2=80=99m running PostgreSQL 9.5.4 on a virtual machine for = production purposes. It runs Ubuntu 16.04.1 LTS 64bit, 32GB RAM, 461GB = disk space and 4 x logical CPUs.

Postgres executes the following activities:
- many INSERTS for ETL
- a lot of read = and write operations for the main OLTP application

The ETL job is still = under development, so I=E2=80=99m launching several sequential = =E2=80=9Ctries=E2=80=9D in order to get the whole thing working. The ETL = procedure consists of a lot of inserts packed inside transactions. At = the moment each transaction consists of 100k inserts, so for a 90mln = rows table I get 90mln inserts packed in 900 transactions. I know it=E2=80= =99s not the best, but JDBC drivers combined with Pentaho doesn=E2=80=99t = seem to pack more inserts into one, so I get a lot of overhead. I can = see INSERT, BIND and PARSE called for each insert.. I think it=E2=80=99s = Pentaho which embeds the INSERT in a parametric query.. I hate Pentaho.. = anyway..

The = ETL procedure does the following:
1) DROP SCHEMA IF = EXISTS data_schema CASCADE;
2) creates the = =E2=80=9Cdata_schema=E2=80=9D schema and populates it with tables and = rows using INSERTs as described before;
3) if an = error occurs, drop the schema

I=E2=80=99m repeating = the previous steps many times because of some Pentaho errors which the = team is working on in order to get it working. This stresses the WAL = because the interruption of the process interrupts the current = transaction and is followed by a DROP SCHEMA .. CASCADE.

After few = days since we began debugging the ETL elaboration, the disk filled up = and the last ETL job was automatically aborted. Note that the DB = data directory is located on the same root disk at = /var/lib/postgresql/9.5/main

What shocked me was that the data directory of Postgres was just 815MB in size ($ du = -h /var/lib/postgresql/9.5/main ) and pg_xlog was 705MB, but= the entire disk was full ("df -h" returned a disk usage of = 100%).

I looked for any postgres activity and only = noticed a checkpoint writer process that was writing at low speeds (IO = usage was about 5%).
Also, "SELECT * FROM = pg_stat_activity" returned nothing and the most shocking part was that = the "du -h /=E2=80=9C command returned 56GB as the total size of files = stored on the whole disk!!! The same was for =E2=80=9Cdu -ha /=E2=80=9C, = which returns the apparent size. 

The total disk size is 461GB, so how is it possible that =E2=80=9Cdf -h=E2=80=9D resulted = in 461GB occupied while =E2=80=9Cdu -h /=E2=80=9C returned just = 56GB?

After executing:
$ service postgresql = stop
$ service postgresql start

the disk = was freed and =E2=80=9Cdf -h=E2=80=9D returned a usage of just = 16%!

The = other questions are:
- how can I = prevent the disk from filling up? I=E2=80=99m using the default = configuration for the WAL (1GB max size).
- how can I tune Postgres to speed up the = INSERTs?

The= actual configuration is the following:
listen_addresses =3D 'localhost'
max_connections =3D 32
shared_buffers =3D = 16GB
work_mem =3D 128MB
maintenance_work_mem =3D 512MB
effective_io_concurrency =3D 10
checkpoint_completion_target =3D 0.9
cpu_tuple_cost =3D 0.02
cpu_index_tuple_cost =3D 0.01
cpu_operator_cost =3D 0.005
effective_cache_size =3D 24GB
default_statistics_target =3D 1000

May be = that some of these parameters causes this strange behavior? = checkpoint_completion_target?
Thanks to everyone for the = support.

Best = regards,
 Pietro Pugni



= --Apple-Mail=_31254E46-C4BA-4DA9-BED7-484827434780--