Request for feedback on your db vacuum status
Hi all, Following the thread about vacuum tool [1] I would like to gather some feedback about your deployment's db vacuum status The info is completely anonymous and function running it is a read only reporting one and should have little or no effect on the db. The result can be pretty verbose but again will not disclose sensitive info. Anyway review it before pasting it. It should look something like that(a snippet of one table): INFO: vacuuming "pg_catalog.pg_ts_template" INFO: index "pg_ts_template_tmplname_index" now contains 5 row versions in 2 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. 1. sudo su - postgres -c "psql engine -c 'vacuum verbose'" &> /tmp/vacuum.log 2. review the /tmp/vacuum.log 3. paste it to http://paste.fedoraproject.org/ and reply with the link here [1] http://lists.ovirt.org/pipermail/devel/2016-December/014484.html Thanks, Roy
------=_Part_36891129_1203489647.1481209273258 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable ----- Le 8 D=C3=A9c 16, =C3=A0 15:18, Roy Golan <rgolan@redhat.com> a =C3= =A9crit :=20
Hi all,
Following the thread about vacuum tool [1] I would like to gather some fe= edback about your deployment's db vacuum status The info is completely anonymous= and function running it is a read only reporting one and should have little o= r no effect on the db.
The result can be pretty verbose but again will not disclose sensitive in= fo. Anyway review it before pasting it. It should look something like that(a snippet of one table):
INFO: vacuuming "pg_catalog.pg_ts_template" INFO: index "pg_ts_template_tmplname_index" now contains 5 row versions i= n 2 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec.
1. sudo su - postgres -c "psql engine -c 'vacuum verbose'" &> /tmp/vacuum= .log
2. review the /tmp/vacuum.log
3. paste it to http://paste.fedoraproject.org/ and reply with the link he= re
[1] http://lists.ovirt.org/pipermail/devel/2016-December/014484.html
Thanks, Roy
_______________________________________________ Users mailing list Users@ovirt.org http://lists.ovirt.org/mailman/listinfo/users
http://paste.fedoraproject.org/501769/48120789/=20 But, we run a full vacuum about one month ago that have free about 8GB of s= pace and we set DWH_SAMPLING=3D60 to decrease data size of DWH (install is = ~ 1y and half old, updated from 3.5 to 3.6 to 4.0).=20 Have a nice day.=20 Regards.=20 --=20 Baptiste=20 ------=_Part_36891129_1203489647.1481209273258 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable <html><body><div style=3D"font-family: arial, helvetica, sans-serif; font-s= ize: 12pt; color: #000000"><div><br></div><div><br></div><div><span id=3D"z= wchr" data-marker=3D"__DIVIDER__">----- Le 8 D=C3=A9c 16, =C3=A0 15:18, Roy= Golan <rgolan@redhat.com> a =C3=A9crit :<br></span></div><div data-m= arker=3D"__QUOTED_TEXT__"><blockquote style=3D"border-left: 2px solid #1010= FF; margin-left: 5px; padding-left: 5px; color: #000; font-weight: normal; = font-style: normal; text-decoration: none; font-family: Helvetica,Arial,san= s-serif; font-size: 12pt;" data-mce-style=3D"border-left: 2px solid #1010FF= ; margin-left: 5px; padding-left: 5px; color: #000; font-weight: normal; fo= nt-style: normal; text-decoration: none; font-family: Helvetica,Arial,sans-= serif; font-size: 12pt;"><div dir=3D"ltr"><div><div><div><div><div><div>Hi = all,<br><br></div>Following the thread about vacuum tool [1] I would like t= o gather some feedback about your deployment's db vacuum status The info is= completely anonymous and function running it is a read only reporting one = and should have little or no effect on the db.<br><br></div>The result can = be pretty verbose but again will not disclose sensitive info. Anyway = review it before pasting it. It should look something like that(a snippet o= f one table):<br><br><span style=3D"font-family: monospace,monospace;" data= -mce-style=3D"font-family: monospace,monospace;">INFO: vacuuming "pg_= catalog.pg_ts_template"<br>INFO: index "pg_ts_template_tmplname_index= " now contains 5 row versions in 2 pages<br>DETAIL: 0 index row versi= ons were removed.<br>0 index pages have been deleted, 0 are currently reusa= ble.<br>CPU 0.00s/0.00u sec elapsed 0.00 sec.<br></span><br><br></div>1. su= do su - postgres -c "psql engine -c 'vacuum verbose'" &> /tmp/= vacuum.log<br><br></div>2. review the /tmp/vacuum.log<br><br></div>3. paste= it to <a href=3D"http://paste.fedoraproject.org/" target=3D"_blank" data-m= ce-href=3D"http://paste.fedoraproject.org/">http://paste.fedoraproject.org/= </a> and reply with the link here<br><br><br>[1] <a href=3D"http://lists.ov= irt.org/pipermail/devel/2016-December/014484.html" target=3D"_blank" data-m= ce-href=3D"http://lists.ovirt.org/pipermail/devel/2016-December/014484.html= ">http://lists.ovirt.org/pipermail/devel/2016-December/014484.html</a><br><= br><br></div><div>Thanks,<br></div><div>Roy<br></div></div><br>____________= ___________________________________<br>Users mailing list<br>Users@ovirt.or= g<br>http://lists.ovirt.org/mailman/listinfo/users<br></blockquote></div><d= iv><br></div><div>http://paste.fedoraproject.org/501769/48120789/</div><div=
<br data-mce-bogus=3D"1"></div><div>But, we run a full vacuum about one mo= nth ago that have free about 8GB of space and we set DWH_SAMPLING=3D60 to d= ecrease data size of DWH (install is ~ 1y and half old, updated from 3.5 to= 3.6 to 4.0).<br data-mce-bogus=3D"1"></div><div><br data-mce-bogus=3D"1"><= /div><div>Have a nice day.<br data-mce-bogus=3D"1"></div><div><br data-mce-= bogus=3D"1"></div><div>Regards.<br data-mce-bogus=3D"1"></div><div><br data= -mce-bogus=3D"1"></div><div data-marker=3D"__SIG_POST__">-- <br></div><div>= Baptiste<br><br></div></div></body></html> ------=_Part_36891129_1203489647.1481209273258--
On 8 December 2016 at 17:01, Baptiste Agasse < baptiste.agasse@lyra-network.com> wrote:
----- Le 8 Déc 16, à 15:18, Roy Golan <rgolan@redhat.com> a écrit :
Hi all,
Following the thread about vacuum tool [1] I would like to gather some feedback about your deployment's db vacuum status The info is completely anonymous and function running it is a read only reporting one and should have little or no effect on the db.
The result can be pretty verbose but again will not disclose sensitive info. Anyway review it before pasting it. It should look something like that(a snippet of one table):
INFO: vacuuming "pg_catalog.pg_ts_template" INFO: index "pg_ts_template_tmplname_index" now contains 5 row versions in 2 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec.
1. sudo su - postgres -c "psql engine -c 'vacuum verbose'" &> /tmp/vacuum.log
2. review the /tmp/vacuum.log
3. paste it to http://paste.fedoraproject.org/ and reply with the link here
[1] http://lists.ovirt.org/pipermail/devel/2016-December/014484.html
Thanks, Roy
_______________________________________________ Users mailing list Users@ovirt.org http://lists.ovirt.org/mailman/listinfo/users
http://paste.fedoraproject.org/501769/48120789/
But, we run a full vacuum about one month ago that have free about 8GB of space and we set DWH_SAMPLING=60 to decrease data size of DWH (install is ~ 1y and half old, updated from 3.5 to 3.6 to 4.0).
Have a nice day.
Regards.
-- Baptiste
Baptiste thank you very much for submitting this, very helpful. I wonder if it was the engine or dwh db who took most of the 8gb. FYI there is a discussion on increasing the interval to 60 on *Bug 1395608* <https://bugzilla.redhat.com/show_bug.cgi?id=1395608> - DWH sampling is too high
Hi Baptiste, Thank you very much for your reply. I understand that you updated your DWH to collect every 60 seconds instead of 20. I'm the oVirt DWH maintainer and I would really appreciate if you can share what led you to this decision? And some details on your setup. Do you have it installed on the same machine as the engine or on a remote one? Is your database remote or local? What is the scale of you environment ? Number of hosts/vms... This will may help us with the bug Roy mentioned. Best regards, Shirly Radco BI Software Engineer Red Hat Israel Ltd. 34 Jerusalem Road Building A, 4th floor Ra'anana, Israel 4350109 On Thu, Dec 8, 2016 at 5:01 PM, Baptiste Agasse < baptiste.agasse@lyra-network.com> wrote:
----- Le 8 Déc 16, à 15:18, Roy Golan <rgolan@redhat.com> a écrit :
Hi all,
Following the thread about vacuum tool [1] I would like to gather some feedback about your deployment's db vacuum status The info is completely anonymous and function running it is a read only reporting one and should have little or no effect on the db.
The result can be pretty verbose but again will not disclose sensitive info. Anyway review it before pasting it. It should look something like that(a snippet of one table):
INFO: vacuuming "pg_catalog.pg_ts_template" INFO: index "pg_ts_template_tmplname_index" now contains 5 row versions in 2 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec.
1. sudo su - postgres -c "psql engine -c 'vacuum verbose'" &> /tmp/vacuum.log
2. review the /tmp/vacuum.log
3. paste it to http://paste.fedoraproject.org/ and reply with the link here
[1] http://lists.ovirt.org/pipermail/devel/2016-December/014484.html
Thanks, Roy
_______________________________________________ Users mailing list Users@ovirt.org http://lists.ovirt.org/mailman/listinfo/users
http://paste.fedoraproject.org/501769/48120789/
But, we run a full vacuum about one month ago that have free about 8GB of space and we set DWH_SAMPLING=60 to decrease data size of DWH (install is ~ 1y and half old, updated from 3.5 to 3.6 to 4.0).
Have a nice day.
Regards.
-- Baptiste
_______________________________________________ Users mailing list Users@ovirt.org http://lists.ovirt.org/mailman/listinfo/users
------=_Part_39021438_1406860475.1481553333248 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable Hi,=20 ----- Le 12 D=C3=A9c 16, =C3=A0 13:54, Shirly Radco <sradco@redhat.com> a = =C3=A9crit :=20
Hi Baptiste,
Thank you very much for your reply.
I understand that you updated your DWH to collect every 60 seconds instea= d of 20. I'm the oVirt DWH maintainer and I would really appreciate if you can sha= re what led you to this decision? And some details on your setup.
Do you have it installed on the same machine as the engine or on a remote= one? Is your database remote or local? What is the scale of you environment ? Number of hosts/vms...
This will may help us with the bug Roy mentioned.
From my mind, it was the ovirt_engine_history DB, i don't remember if there= was one or more tables that reported a lot of disk space usage. A full vac= uum corrected this size issue.=20 For the bugzilla mentioned, i saw it and i applied the sampling suggestion = to see if the DB grows more slowly.=20
For our environment we have today (and growing)=20 * 4 DC=20 * 5 Clusters=20 * 9 Storages domains (iscsi)=20 * About 360 virtual disks in storage domains=20 * 13 Hosts (growing)=20 * About 250 VMs (growing)=20 * The engine + DWH + DB server are all on the same server (hosted engine)= =20 * DB Size is about 3.2 GB (after the vacuum)=20 * As all was on the same box, the engine setup via appliance was preferred = and it was not possible to customize the size of the appliance at install/u= pdate, we wanted to keep the DB size as small as possible, but with some hi= story. I saw that the engine appliance size will be customizable soon, so w= e will maybe extend the engine disk at update and keep a little bit more hi= story or decrease the sampling interval again.=20 Have a nice day.=20 Regards.=20
Best regards, Shirly Radco BI Software Engineer Red Hat Israel Ltd. 34 Jerusalem Road Building A, 4th floor Ra'anana, Israel 4350109
On Thu, Dec 8, 2016 at 5:01 PM, Baptiste Agasse < baptiste.agasse@lyra-network.com > wrote:
----- Le 8 D=C3=A9c 16, =C3=A0 15:18, Roy Golan < rgolan@redhat.com > a = =C3=A9crit :
Hi all,
Following the thread about vacuum tool [1] I would like to gather some = feedback about your deployment's db vacuum status The info is completely anonymo= us and function running it is a read only reporting one and should have little= or no effect on the db.
The result can be pretty verbose but again will not disclose sensitive = info. Anyway review it before pasting it. It should look something like that(= a snippet of one table):
INFO: vacuuming "pg_catalog.pg_ts_template" INFO: index "pg_ts_template_tmplname_index" now contains 5 row versions= in 2 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec.
1. sudo su - postgres -c "psql engine -c 'vacuum verbose'" &> /tmp/vacu= um.log
2. review the /tmp/vacuum.log
3. paste it to http://paste.fedoraproject.org/ and reply with the link = here
[1] http://lists.ovirt.org/pipermail/devel/2016-December/014484.html
Thanks, Roy
_______________________________________________ Users mailing list Users@ovirt.org http://lists.ovirt.org/mailman/listinfo/users
But, we run a full vacuum about one month ago that have free about 8GB o= f space and we set DWH_SAMPLING=3D60 to decrease data size of DWH (install is ~ = 1y and half old, updated from 3.5 to 3.6 to 4.0).
Have a nice day.
Regards.
-- Baptiste
_______________________________________________ Users mailing list Users@ovirt.org http://lists.ovirt.org/mailman/listinfo/users
--=20 Baptiste=20 ------=_Part_39021438_1406860475.1481553333248 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable <html><body><div style=3D"font-family: arial, helvetica, sans-serif; font-s= ize: 12pt; color: #000000"><div>Hi,<br></div><div><br></div><span id=3D"zwc= hr" data-marker=3D"__DIVIDER__">----- Le 12 D=C3=A9c 16, =C3=A0 13:54, Shir= ly Radco <sradco@redhat.com> a =C3=A9crit :<br></span><div data-marke= r=3D"__QUOTED_TEXT__"><blockquote style=3D"border-left:2px solid #1010FF;ma= rgin-left:5px;padding-left:5px;color:#000;font-weight:normal;font-style:nor= mal;text-decoration:none;font-family:Helvetica,Arial,sans-serif;font-size:1= 2pt;"><div dir=3D"ltr"><span style=3D"font-size:12.8px">Hi Baptiste,</span>= <div><span style=3D"font-size:12.8px"><br></span></div><div><span style=3D"= font-size:12.8px">Thank you very much for your reply.</span><br></div><div>= <span style=3D"font-size:12.8px"><br></span></div><div><span style=3D"font-= size:12.8px">I understand that you updated your DWH to collect every 60 sec= onds instead of 20.</span></div><div><span style=3D"font-size:12.8px">I'm t= he oVirt DWH maintainer and </span><span style=3D"font-size:12.8px">I = would really appreciate if you can share what led you to this decision= ?</span><span style=3D"font-size:12.8px"> </span></div><div><span styl= e=3D"font-size:12.8px">And some details on your setup.</span></div><di= v><span style=3D"font-size:12.8px"><br></span></div><div><span style=3D"fon= t-size:12.8px">Do you have it installed on the same machine as the engine o= r on a remote one? </span></div><div><span style=3D"font-size:12.8px">= Is your database remote or local?</span></div><div><span style=3D"font-size= :12.8px">What is the scale of you environment ? Number of hosts/vms...</spa= n></div><div><span style=3D"font-size:12.8px"><br></span></div><div><span s= tyle=3D"font-size:12.8px">This will may help us with the bug Roy mentioned.= </span></div><div><span style=3D"font-size:12.8px"><br></span></div></div><= /blockquote>From my mind, it was the ovirt_engine_history DB, i don't remem= ber if there was one or more tables that reported a lot of disk space usage= . A full vacuum corrected this size issue.<br></div><div data-marker=3D"__Q= UOTED_TEXT__"><div>For the bugzilla mentioned, i saw it and i applied the s= ampling suggestion to see if the DB grows more slowly. </div><div><br data-= mce-bogus=3D"1"></div><div>For our environment we have today (and growing)<= br></div><div>* 4 DC<br data-mce-bogus=3D"1"></div><div>* 5 Clusters<br dat= a-mce-bogus=3D"1"></div><div>* 9 Storages domains (iscsi)<br data-mce-bogus= =3D"1"></div><div>* About 360 virtual disks in storage domains<br data-mce-= bogus=3D"1"></div><div>* 13 Hosts (growing)<br data-mce-bogus=3D"1"></div><= div>* About 250 VMs (growing)<br data-mce-bogus=3D"1"></div><div><br data-m= ce-bogus=3D"1"></div><div>* The engine + DWH + DB server are all on the sam= e server (hosted engine)<br data-mce-bogus=3D"1"></div><div>* DB Size is ab= out 3.2 GB (after the vacuum)<br data-mce-bogus=3D"1"></div><div>* As = all was on the same box, the engine setup via appliance was preferred and = it was not possible to customize the size of the appliance at install/updat= e, we wanted to keep the DB size as small as possible, but with some histor= y. I saw that the engine appliance size will be customizable soon, so we wi= ll maybe extend the engine disk at update and keep a little bit more histor= y or decrease the sampling interval again.<br data-mce-bogus=3D"1"></div><d= iv><br data-mce-bogus=3D"1"></div>Have a nice day.</div><div data-marker=3D= "__QUOTED_TEXT__"><br data-mce-bogus=3D"1"></div><div data-marker=3D"__QUOT= ED_TEXT__">Regards.<br><div><br data-mce-bogus=3D"1"></div><blockquote styl= e=3D"border-left:2px solid #1010FF;margin-left:5px;padding-left:5px;color:#= 000;font-weight:normal;font-style:normal;text-decoration:none;font-family:H= elvetica,Arial,sans-serif;font-size:12pt;"><div class=3D"gmail_extra"><br c= lear=3D"all"><div><div class=3D"gmail_signature"><div dir=3D"ltr"><div><div= dir=3D"ltr"><div><div dir=3D"ltr"><div><div dir=3D"ltr"><pre style=3D"whit= e-space:pre-wrap;color:rgb(80,0,80)"><span style=3D"font-family:arial,helve= tica,sans-serif;font-size:12.8px">Best regards,</span></pre><pre style=3D"w= hite-space:pre-wrap;color:rgb(80,0,80)"><span style=3D"font-family:arial,he= lvetica,sans-serif;font-size:12.8px">Shirly Radco</span></pre><pre style=3D= "white-space:pre-wrap;color:rgb(80,0,80)"><span style=3D"font-family:arial,= helvetica,sans-serif">BI Software Engineer Red Hat Israel Ltd. 34 Jerusalem Road Building A, 4th floor Ra'anana, Israel 4350109</span></pre></div></div></div></div></div></div></= div></div></div><br><div class=3D"gmail_quote">On Thu, Dec 8, 2016 at 5:01 = PM, Baptiste Agasse <span dir=3D"ltr"><<a href=3D"mailto:baptiste.agasse= @lyra-network.com" target=3D"_blank">baptiste.agasse@lyra-network.com</a>&g= t;</span> wrote:<br><blockquote class=3D"gmail_quote" style=3D"margin:0 0 0= .8ex;border-left:1px #ccc solid;padding-left:1ex"><div><div style=3D"font-= family:arial,helvetica,sans-serif;font-size:12pt;color:#000000"><br><br><di= v><span id=3D"m_-3923956886864290678zwchr">----- Le 8 D=C3=A9c 16, =C3=A0 1= 5:18, Roy Golan <<a href=3D"mailto:rgolan@redhat.com" target=3D"_blank">= rgolan@redhat.com</a>> a =C3=A9crit :<br></span></div><div><blockquote s= tyle=3D"border-left:2px solid #1010ff;margin-left:5px;padding-left:5px;colo= r:#000;font-weight:normal;font-style:normal;text-decoration:none;font-famil= y:Helvetica,Arial,sans-serif;font-size:12pt"><div><div class=3D"h5"><div di= r=3D"ltr"><div><div><div><div><div><div>Hi all,<br><br></div>Following the = thread about vacuum tool [1] I would like to gather some feedback about you= r deployment's db vacuum status The info is completely anonymous and functi= on running it is a read only reporting one and should have little or no eff= ect on the db.<br><br></div>The result can be pretty verbose but agai= n will not disclose sensitive info. Anyway review it before pasting it. It = should look something like that(a snippet of one table):<br><br><span style= =3D"font-family:monospace,monospace">INFO: vacuuming "pg_catalog.pg_t= s_template"<br>INFO: index "pg_ts_template_tmplname_index" now contai= ns 5 row versions in 2 pages<br>DETAIL: 0 index row versions were rem= oved.<br>0 index pages have been deleted, 0 are currently reusable.<br>CPU = 0.00s/0.00u sec elapsed 0.00 sec.<br></span><br><br></div>1. sudo su - post= gres -c "psql engine -c 'vacuum verbose'" &> /tmp/vacuum.log<b= r><br></div>2. review the /tmp/vacuum.log<br><br></div>3. paste it to <a hr= ef=3D"http://paste.fedoraproject.org/" target=3D"_blank">http://paste.fedor= aproject.org/</a> and reply with the link here<br><br><br>[1] <a href=3D"ht= tp://lists.ovirt.org/pipermail/devel/2016-December/014484.html" target=3D"_= blank">http://lists.ovirt.org/pipermail/devel/2016-December/014484.html</a>= <br><br><br></div><div>Thanks,<br></div><div>Roy<br></div></div><br></div><= /div>_______________________________________________<br>Users mailing list<= br><a href=3D"mailto:Users@ovirt.org" target=3D"_blank">Users@ovirt.org</a>= <br><a href=3D"http://lists.ovirt.org/mailman/listinfo/users" target=3D"_bl= ank">http://lists.ovirt.org/mailman/listinfo/users</a><br></blockquote></di= v><br><div><a href=3D"http://paste.fedoraproject.org/501769/48120789/" targ= et=3D"_blank">http://paste.fedoraproject.org/501769/48120789/</a><br data-m= ce-bogus=3D"1"></div><br><div>But, we run a full vacuum about one month ago= that have free about 8GB of space and we set DWH_SAMPLING=3D60 to decrease= data size of DWH (install is ~ 1y and half old, updated from 3.5 to 3.6 to= 4.0).<br></div><br><div>Have a nice day.<br></div><br><div>Regards.<span c= lass=3D"HOEnZb"><span style=3D"color: #888888;" data-mce-style=3D"color: #8= 88888;" color=3D"#888888"><br></span></span></div><span class=3D"HOEnZb"><s= pan style=3D"color: #888888;" data-mce-style=3D"color: #888888;" color=3D"#= 888888"><br><div>-- <br></div><div>Baptiste<br><br></div></span></span></di= v></div><br>_______________________________________________<br> Users mailing list<br><a href=3D"mailto:Users@ovirt.org" target=3D"_blank">= Users@ovirt.org</a><br><a href=3D"http://lists.ovirt.org/mailman/listinfo/u= sers" rel=3D"noreferrer" target=3D"_blank">http://lists.ovirt.org/mailman/l= istinfo/users</a><br><br></blockquote></div></div><br></blockquote></div><b= r><div data-marker=3D"__SIG_POST__">-- <br></div><div>Baptiste</div></div><= /body></html> ------=_Part_39021438_1406860475.1481553333248--
participants (3)
-
Baptiste Agasse -
Roy Golan -
Shirly Radco