unexpected result for wastedbytes query after vacuum full
Mike Schanne <[email protected]> Fri, 6 Dec 2019 17:18:20 +0000
| Newsgroups | gmane.comp.db.postgresql.performance |
|---|---|
| Message-ID | <[email protected]> |
--_000_db92c5c1a0904209a4ef2d857276ab4bUSEPRDEX1corpknscom_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
Hi all,
This question is somewhat related to my previous question:
https://www.postgresql.org/message-id/0871fcf35ceb4caa8a2204ca9c38e330%40US=
EPRDEX1.corp.kns.com
I was attempting to measure the benefit of doing a VACUUM FULL on my databa=
se. I was using the query found here:
https://wiki.postgresql.org/wiki/Show_database_bloat
However, I got an unexpected result in that the "wastedbytes" value actuall=
y increased for some tables after doing the vacuum.
Before VACUUM FULL:
current_database | schemaname | tablename | tbloat | wa=
stedbytes | iname =
| ibloat | wastedibytes
------------------+----------------+---------------------------+--------+--=
-----------+---------------------------------------------------------------=
--+--------+--------------
postgres | myschema | mytableA | 1.1 | 7=
4440704 | myindex1 =
| 0.2 | 0
postgres | myschema | mytableA | 1.1 | 7=
4440704 | myindex2 =
| 0.2 | 0
postgres | myschema | mytableA | 1.1 | 7=
4440704 | myindex3 =
| 0.2 | 0
postgres | myschema | mytableA | 1.1 | 7=
4440704 | myindex4 =
| 0.2 | 0
postgres | myschema | mytableB | 1.0 | 6=
3324160 | myindex5 =
| 0.0 | 0
...
After VACUUM FULL:
current_database | schemaname | tablename | tbloat | =
wastedbytes | iname =
| ibloat | wastedibytes
------------------+----------------+---------------------------+--------+--=
-----------+---------------------------------------------------------------=
--+--------+--------------
postgres | myschema | mytableA | 1.1 | 7=
4506240 | myindex4 =
| 0.2 | 0
postgres | myschema | mytableA | 1.1 | 7=
4506240 | myindex3 =
| 0.2 | 0
postgres | myschema | mytableA | 1.1 | 7=
4506240 | myindex2 =
| 0.2 | 0
postgres | myschema | mytableA | 1.1 | 7=
4506240 | myindex1 =
| 0.2 | 0
postgres | myschema | mytableB | 1.0 | 6=
3332352 | myindex5 =
| 0.0 | 0
...
This is the schema for mytableA above:
Column | Type | Mo=
difiers
---------------+-----------------------------+-----------------------------=
-----------------------------------
colA | integer | not null default nextval('mys=
chema.myseq'::regclass)
colB | integer |
colC | integer |
colD | timestamp without time zone |
colE | json |
colF | integer |
colG | integer |
I was wondering if the fact that we use a json column could be interfering =
with the wastedbytes calculation. Can anyone explain how wastedbytes could=
increase from a vacuum?
Thanks,
Mike
________________________________
This email is non-binding, is subject to contract, and neither Kulicke and =
Soffa Industries, Inc. nor its subsidiaries (each and collectively "K&S") s=
hall have any obligation to you to consummate the transactions herein or to=
enter into any agreement, other than in accordance with the terms and cond=
itions of a definitive agreement if and when negotiated, finalized and exec=
uted between the parties. This email and all its contents are protected by =
International and United States copyright laws. Any reproduction or use of =
all or any part of this email without the express written consent of K&S is=
prohibited.
--_000_db92c5c1a0904209a4ef2d857276ab4bUSEPRDEX1corpknscom_
Content-Type: text/html; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
<html xmlns:v=3D"urn:schemas-microsoft-com:vml" xmlns:o=3D"urn:schemas-micr=
osoft-com:office:office" xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:m=3D"http://schemas.microsoft.com/office/2004/12/omml" xmlns=3D"http:=
//www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3Dus-ascii"=
>
<meta name=3D"Generator" content=3D"Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:#0563C1;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:#954F72;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal-compose;
font-family:"Calibri","sans-serif";
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri","sans-serif";}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext=3D"edit" spidmax=3D"1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext=3D"edit">
<o:idmap v:ext=3D"edit" data=3D"1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang=3D"EN-US" link=3D"#0563C1" vlink=3D"#954F72">
<div class=3D"WordSection1">
<p class=3D"MsoNormal">Hi all,<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p> </o:p></p>
<p class=3D"MsoNormal">This question is somewhat related to my previous que=
stion:<o:p></o:p></p>
<p class=3D"MsoNormal"><a href=3D"https://www.postgresql.org/message-id/087=
1fcf35ceb4caa8a2204ca9c38e330%40USEPRDEX1.corp.kns.com">https://www.postgre=
sql.org/message-id/0871fcf35ceb4caa8a2204ca9c38e330%40USEPRDEX1.corp.kns.co=
m</a><o:p></o:p></p>
<p class=3D"MsoNormal"><o:p> </o:p></p>
<p class=3D"MsoNormal">I was attempting to measure the benefit of doing a V=
ACUUM FULL on my database. I was using the query found here:<o:p></o:=
p></p>
<p class=3D"MsoNormal"><o:p> </o:p></p>
<p class=3D"MsoNormal"><a href=3D"https://wiki.postgresql.org/wiki/Show_dat=
abase_bloat">https://wiki.postgresql.org/wiki/Show_database_bloat</a><o:p><=
/o:p></p>
<p class=3D"MsoNormal"><o:p> </o:p></p>
<p class=3D"MsoNormal">However, I got an unexpected result in that the R=
20;wastedbytes” value actually increased for some tables after doing =
the vacuum.
<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p> </o:p></p>
<p class=3D"MsoNormal">Before VACUUM FULL:<o:p></o:p></p>
<p class=3D"MsoNormal">current_database | schemaname  =
; | tablename &n=
bsp; | tbloat | wastedbytes | &nbs=
p; &=
nbsp; &nbs=
p; iname &=
nbsp; &nbs=
p; | ibloat | wastedibytes
<o:p></o:p></p>
<p class=3D"MsoNormal">------------------+----------------+--------=
-------------------+--------+-------------+--------------------=
---------------------------------------------+--------+------------=
--<o:p></o:p></p>
<p class=3D"MsoNormal">postgres &n=
bsp; | myschema | mytableA &=
nbsp; &nbs=
p; | 1.1 | 74440704 | myindex1 &nb=
sp; =
&nb=
sp; =
&nb=
sp; | 0.2 | &nbs=
p; 0<o:p></o:p></p>
<p class=3D"MsoNormal">postgres &n=
bsp; | myschema | mytableA =
&nb=
sp;| 1.1 | 74440704 | myindex2 &nb=
sp; =
&nb=
sp; =
&nb=
sp; | 0.2 | &nbs=
p; 0<o:p></o:p></p>
<p class=3D"MsoNormal">postgres &n=
bsp; | myschema | mytableA &=
nbsp; &nbs=
p; | 1.1 | 74440704 | myindex3 &nb=
sp; =
&nb=
sp;  =
; &n=
bsp; | 0.2 | &nbs=
p; 0<o:p></o:p></p>
<p class=3D"MsoNormal">postgres &n=
bsp; | myschema | mytableA &=
nbsp; &nbs=
p; | 1.1 | 74440704 | myindex4 &nb=
sp; =
&nb=
sp; =
&nb=
sp; | 0.2 | &nbs=
p; 0<o:p></o:p></p>
<p class=3D"MsoNormal">postgres &n=
bsp; | myschema | mytableB &=
nbsp; &nbs=
p; | 1.0 | 63324160 | myindex5 &nb=
sp; =
&nb=
sp; =
&nb=
sp; | 0.0 | &nbs=
p; 0<o:p></o:p></p>
<p class=3D"MsoNormal">...<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p></o:p></p>
<p class=3D"MsoNormal">After VACUUM FULL:<o:p></o:p></p>
<p class=3D"MsoNormal"> current_database | schemaname&nbs=
p; | tablename &=
nbsp; | tbloat | wastedbytes | &nb=
sp; =
&nb=
sp; iname =
&nb=
sp; | ibloat | wastedibytes
<o:p></o:p></p>
<p class=3D"MsoNormal">------------------+----------------+--------=
-------------------+--------+-------------+--------------------=
---------------------------------------------+--------+------------=
--<o:p></o:p></p>
<p class=3D"MsoNormal">postgres &n=
bsp; | myschema | mytableA &=
nbsp; &nbs=
p; | 1.1 | 74506240 | myindex4 &nb=
sp; =
&nb=
sp; =
&nb=
sp; | 0.2 | &nbs=
p; 0<o:p></o:p></p>
<p class=3D"MsoNormal">postgres &n=
bsp; | myschema | mytableA &=
nbsp; &nbs=
p; | 1.1 | 74506240 | myindex3 &nb=
sp; =
&nb=
sp; =
&nb=
sp; | 0.2 | &nbs=
p; 0<o:p></o:p></p>
<p class=3D"MsoNormal">postgres &n=
bsp; | myschema | mytableA &=
nbsp; &nbs=
p; | 1.1 | 74506240 | myindex2 &nb=
sp; =
&nb=
sp; =
&nb=
sp; | 0.2 | &nbs=
p; 0<o:p></o:p></p>
<p class=3D"MsoNormal">postgres &n=
bsp; | myschema | mytableA &=
nbsp; &nbs=
p; | 1.1 | 74506240 | myindex1 &nb=
sp; =
&nb=
sp; =
&nb=
sp; | 0.2 | &nbs=
p; 0<o:p></o:p></p>
<p class=3D"MsoNormal">postgres &n=
bsp; | myschema | mytableB &=
nbsp; &nb=
sp;| 1.0 | 63332352 | myindex5 &nb=
sp; =
&nb=
sp; =
&nb=
sp; | 0.0 | &nbs=
p; 0<o:p></o:p></p>
<p class=3D"MsoNormal">...<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p> </o:p></p>
<p class=3D"MsoNormal">This is the schema for mytableA above:<o:p></o:p></p=
>
<p class=3D"MsoNormal"><o:p> </o:p></p>
<p class=3D"MsoNormal"> Column |&=
nbsp; Type =
; | =
&n=
bsp;  =
;Modifiers<o:p></o:p></p>
<p class=3D"MsoNormal">---------------+-----------------------------=
3;----------------------------------------------------------------<o:p></o:=
p></p>
<p class=3D"MsoNormal">colA =
| integer  =
; | not null de=
fault nextval('myschema.myseq'::regclass)<o:p></o:p></p>
<p class=3D"MsoNormal">colB =
| integer  =
; |<o:p></o:p><=
/p>
<p class=3D"MsoNormal">colC =
| integer  =
; |<o:p></o:p><=
/p>
<p class=3D"MsoNormal">colD =
| timestamp without time zone |<o:p></o:p></p>
<p class=3D"MsoNormal">colE =
| json &n=
bsp;  =
; |<o:p></o:p></p>
<p class=3D"MsoNormal">colF =
| integer  =
; |<o:p></o:p><=
/p>
<p class=3D"MsoNormal">colG =
| integer  =
; |<o:p></o:p><=
/p>
<p class=3D"MsoNormal"><o:p> </o:p></p>
<p class=3D"MsoNormal">I was wondering if the fact that we use a json colum=
n could be interfering with the wastedbytes calculation. Can anyone e=
xplain how wastedbytes could increase from a vacuum?<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p> </o:p></p>
<p class=3D"MsoNormal">Thanks,<o:p></o:p></p>
<p class=3D"MsoNormal">Mike<o:p></o:p></p>
</div>
<br>
<hr>
<font face=3D"Arial" color=3D"Black" size=3D"2"><br>
This email is non-binding, is subject to contract, and neither Kulicke and =
Soffa Industries, Inc. nor its subsidiaries (each and collectively “K=
&S”) shall have any obligation to you to consummate the transacti=
ons herein or to enter into any agreement, other
than in accordance with the terms and conditions of a definitive agreement=
if and when negotiated, finalized and executed between the parties. This e=
mail and all its contents are protected by International and United States =
copyright laws. Any reproduction
or use of all or any part of this email without the express written consen=
t of K&S is prohibited.<br>
</font>
</body>
</html>
--_000_db92c5c1a0904209a4ef2d857276ab4bUSEPRDEX1corpknscom_--