Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1drLER-0001qB-Vh for pgsql-performance@arkaria.postgresql.org; Mon, 11 Sep 2017 09:42:20 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1drLER-0000IR-I4 for pgsql-performance@arkaria.postgresql.org; Mon, 11 Sep 2017 09:42:19 +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 1drLEG-0007pt-6X for pgsql-performance@postgresql.org; Mon, 11 Sep 2017 09:42:08 +0000 Received: from mail-wr0-x22a.google.com ([2a00:1450:400c:c0c::22a]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1drLEC-000871-QQ for pgsql-performance@postgresql.org; Mon, 11 Sep 2017 09:42:07 +0000 Received: by mail-wr0-x22a.google.com with SMTP id a43so13347020wrc.0 for ; Mon, 11 Sep 2017 02:42:04 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:from:date:message-id:subject:to; bh=nSO6w6oaifW+0zeLCVeNtIRsR6Y1NraCEvyIALyvzz0=; b=HvXfTYtnJKTrFv+Wx68tlWtid3Z36APBPZTmD58eSbpPnluNkqKu4skv04PpSPcqk8 a7B05OaLWkSEx5BJX831l+3ppA6+57oeN04KsJy3/QHvk73iSMg3ujD1Y9WW2G+/hbxW 12jBi4bDwNshI0ruFzd/lHjvSQctIg9ET2tE+uGDSM6jQs2RW0B+qhnQptJqlV1F3jHF CXDbXM7LuQJn6lBGxJDt95ygH7pmIbUqjEmfo/NbLDw8D8jpQnMABbEXasmkYffC+W9T y7FTuj+EJLE2dFzhT8vAUX09OFeu64eKJuqXxmXn/6SBrFeBXGVynStjp8cfp750zhDf IGuw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:from:date:message-id:subject:to; bh=nSO6w6oaifW+0zeLCVeNtIRsR6Y1NraCEvyIALyvzz0=; b=Tcrne4Bqv06JEAzeYXGLw9CVdFEJE2XecjldtbX4dbEd4p/1ooo+/LR3B2fiRm2OND Rx+DkWRBupl2AcqiQuLq2Zos88AUi1AxGo74hTt2HeUe/PxuKD8ChnVWgcHCOC/9lhXI SJ527eiQt619rWRCixoXQpbuYLcMX6o0aSXxULEJwg49VNGtPmxGjrZW9VlzJUJu1YBt WEnj9nlojrcXnAS7YVSE8IhjoVCeEl3CM+egPM6adpe40/eA/huSRyhtbNKXkyQQ6Tuf VeL3O7QzZhisEdkm3P5832mMrhIPoN5ppT6c0Nx5HEmdSrm/xpMdIDhYaZ8ffPtO1PR8 m/BA== X-Gm-Message-State: AHPjjUgvL4JbFpP6UgVQz5zFp+/f5CGUwyOi/ywcsAKBoWPnwZbBPdfG 0/9KDQ2la+/ApDSnOus+KAmtIuN4TlllJcA= X-Google-Smtp-Source: ADKCNb4PcL9EFc3BNWNfgtEY5kR9yKqaRoY0RZtA/135jI7dAIoC0u8feYDGqcbcyY1t8KQcsnxOAhOigdg8C9Pv6zA= X-Received: by 10.223.154.81 with SMTP id z75mr8956405wrb.124.1505122922699; Mon, 11 Sep 2017 02:42:02 -0700 (PDT) MIME-Version: 1.0 Received: by 10.28.32.193 with HTTP; Mon, 11 Sep 2017 02:42:01 -0700 (PDT) From: Mariel Cherkassky Date: Mon, 11 Sep 2017 12:42:01 +0300 Message-ID: Subject: postgresql 9.6 data directory fs becomes full To: pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary="f403045f4d7c26a12d0558e6bd92" 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 --f403045f4d7c26a12d0558e6bd92 Content-Type: text/plain; charset="UTF-8" I want to check something regarding postgresql performance during my app is running. My app does the next things on 20 tables in a loop : 1.truncate table. 2.drop constraints on table 3.drop indexes on table 4.insert into local_table select * from remote_oracle_table 4.1.Recently I'm getting an error in this part : SQLERRM = could not extend file "base/16400/124810.23": wrote only 4096 of 8192 bytes at block 3092001 5.create constraints on table 6.create indexes on table. This operation runs every night. Most of the tables are small 500M-2G but few tables are pretty big 24G-45G. My wals and my data directory are on different fs. My data directory fs size is 400G. During this operation the data directory fs becomes full. However, after this operation 100G are freed which means that 300G are used from the 400g of the data directory fs. Something regarding those sizes doesnt seems ok. When I check my database size : mydb=# SELECT mydb-# pg_database.datname, mydb-# pg_size_pretty(pg_database_size(pg_database.datname)) AS size mydb-# FROM pg_database; datname | size -----------+--------- template0 | 7265 kB mydb | 246 GB postgres | 568 MB template1 | 7865 kB (4 rows) When I check all the tables in mydb database : mydb-# relname as "Table", mydb-# pg_size_pretty(pg_total_relation_size(relid)) As "Size", mydb-# pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) as "External Size" mydb-# FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC; Table | Size | External Size -------------------+------------+--------------- table 1| 45 GB | 13 GB table 2| 15 GB | 6330 MB table 3| 9506 MB | 3800 MB table 4| 7473 MB | 1838 MB table 5| 7267 MB | 2652 MB table 6| 5347 MB | 1701 MB table 7| 3402 MB | 1377 MB table 8| 3092 MB | 1318 MB table 9| 2145 MB | 724 MB table 10| 1804 MB | 381 MB table 11 293 MB | 83 MB table 12| 268 MB | 103 MB table 13| 225 MB | 108 MB table 14| 217 MB | 40 MB table 15| 172 MB | 47 MB table 16| 134 MB | 36 MB table 17| 102 MB | 27 MB table 18| 86 MB | 22 MB ..... In the data directory the base directory`s size is 240G. I have 16G of ram in my machine. Waiting for help, thanks. --f403045f4d7c26a12d0558e6bd92 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

I want to check something regarding postgresql perform= ance during my app is running.

My app does the next things on 20 tables in a loop :

1.truncate<=
span class=3D"gmail-m_6942716923766193486gmail-pln" style=3D"font-family:in=
herit;font-style:inherit;font-variant:inherit;font-weight:inherit;white-spa=
ce:inherit;font-size:inherit;margin:0px;padding:0px;border:0px;font-stretch=
:inherit;line-height:inherit;vertical-align:baseline;color:rgb(48,51,54)"> =
table.
2.drop constraints on table
3.drop<=
/span> indexes on table
4.insert into local_table s=
elect * from remote_oracle_table=

4.1.Recently I'm getting an err=
or in this part : SQLERRM =3D<=
span class=3D"gmail-m_6942716923766193486gmail-pln" style=3D"font-family:in=
herit;font-style:inherit;font-variant:inherit;font-weight:inherit;white-spa=
ce:inherit;font-size:inherit;margin:0px;padding:0px;border:0px;font-stretch=
:inherit;line-height:inherit;vertical-align:baseline;color:rgb(48,51,54)"> =
could not extend 
   =
file =
"base/16400/124810.23": wrote only 4096 of 8192 bytes at block 
   3092001
5.creat=
e constraints on table
6.create indexes on t=
able.

This operation runs every night. Most of the tables are= small 500M-2G but few tables are pretty big 24G-45G.

My wals and my data directory are on differ= ent fs. My data directory fs size is 400G. During this operation the data d= irectory fs becomes full. However, after this operation 100G are freed whic= h means that 300G are used from the 400g of the data directory fs. Somethin= g regarding those sizes doesnt seems ok.

When I check my database size :

mydb=3D# SELECT
mydb-#     pg_database.datname,
mydb-#     pg_size_pretty(pg_database_size(pg_database.datname<=
/span>)) AS size
mydb-# = FROM pg_database= ;
datname | size
<= /span>-----------+---------
templ= ate0 |= 7265 kB
mydb | 246 GB
postgres | 568 MB
template1 = | 786= 5 kB
(4 rows)

When I check all the tables in mydb database :

<= pre class=3D"gmail-m_6942716923766193486gmail-lang-sql gmail-m_694271692376= 6193486gmail-prettyprint gmail-m_6942716923766193486gmail-prettyprinted" st= yle=3D"white-space:pre-wrap;word-wrap:normal;max-width:800px;overflow:auto;= font-size:13px;margin-top:0px;margin-bottom:1em;padding:5px;border:0px;font= -variant-numeric:inherit;font-stretch:inherit;line-height:inherit;font-fami= ly:Consolas,Menlo,Monaco,"Lucida Console","Liberation Mono&q= uot;,"DejaVu Sans Mono","Bitstream Vera Sans Mono",&quo= t;Courier New",monospace,sans-serif;vertical-align:baseline;width:auto= ;max-height:600px;background-color:rgb(239,240,241);color:rgb(57,51,24)">mydb-# relname as "Table",
mydb-# pg_size_pretty(pg_total_re= lation_size(relid)) As &= quot;Size",
<= span class=3D"gmail-m_6942716923766193486gmail-pln" style=3D"font-family:in= herit;font-style:inherit;font-variant:inherit;font-weight:inherit;white-spa= ce:inherit;font-size:inherit;margin:0px;padding:0px;border:0px;font-stretch= :inherit;line-height:inherit;vertical-align:baseline">mydb-# pg_size_pretty(pg_total_relation_size(relid) -
pg_relation_size(relid)) as "External Size"
mydb-#= F= ROM pg_catalo= g.pg_statio_user_tabl= es ORDER BY
pg_total_relation_size(relid) DESC;
= Table= = | Size = | External Size
-------------------+---------= ---+---------------
= table 1| 45 GB = | 13 GB
table 2| 15 GB | 6330 MB
tab= le 3<= /span>| 9506 MB | 3800 MB
= table 4| 7473 MB | 1838 MB
table 5| 7267 MB | 2652 MB
= table 6|<= /span> 5347<= /span> MB = | 1701 MB
table 7| 3402 MB | 1377 MB=
=
table 8= | 3092 MB | 1318 MB
= table 9| 2145 MB= |<= span class=3D"gmail-m_6942716923766193486gmail-pln" style=3D"font-family:in= herit;font-style:inherit;font-variant:inherit;font-weight:inherit;white-spa= ce:inherit;font-size:inherit;margin:0px;padding:0px;border:0px;font-stretch= :inherit;line-height:inherit;vertical-align:baseline"> 724= MB
table 10| 1804 MB | 381 MB
11 = 293 MB <= /span>| 83 MB
table 12| 268 MB | 103= MB
table<= /span> 13| 225 MB | 108 MB
= table 14|= 217 = MB | 40= MB
table 15| 172 MB | 47 MB
= table |<= span class=3D"gmail-m_6942716923766193486gmail-pln" style=3D"font-family:in= herit;font-style:inherit;font-variant:inherit;font-weight:inherit;white-spa= ce:inherit;font-size:inherit;margin:0px;padding:0px;border:0px;font-stretch= :inherit;line-height:inherit;vertical-align:baseline"> 134= MB | 36 MB
table 17| 102 MB | <= span class=3D"gmail-m_6942716923766193486gmail-lit" style=3D"font-family:in= herit;font-style:inherit;font-variant:inherit;font-weight:inherit;white-spa= ce:inherit;font-size:inherit;margin:0px;padding:0px;border:0px;font-stretch= :inherit;line-height:inherit;vertical-align:baseline;color:rgb(125,39,39)">= 27 MB<= /div>
table 18| 86 MB | 22 MB
= .....=

In the data directory the base directory`s size is 240G. I have 16G of ram= in my machine.

Waitin= g for help, thanks.

--f403045f4d7c26a12d0558e6bd92--