<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40"><head><meta http-equiv=Content-Type content="text/html; charset=us-ascii"><meta name=Generator content="Microsoft Word 14 (filtered medium)"><style><!--
/* Font Definitions */
@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:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
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="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]--></head><body lang=EN-US link=blue vlink=purple><div class=WordSection1><p class=MsoNormal>I recently had the chance to run benchmarking on my jxapi server before and after a VACUUM ANALYZE; on a database that had been running since April with AUTOVACUUM off. The DB was imported with osmosis 0.38 and maintained with osmosis 0.39. With the exception of the initial catch-up and for some time after server downtimes it has been keeping up with minutely diffs.<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>The server is an AMD Athelon II x3 with a software RAID10 array of 4 7200 RPM consumer SATA drives. The drives are 3x WD Caviar Blue and 1x WD Caviar Black. This represents about the top end of disk performance before upgrading to server drives or SSDs. Sequential read speeds are 90-130 MB/s. iostat reported 40-80% utilization on the physical drives and 300-800 iops during the VACUUM ANALYZE; The server has 4 GB of ram.<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>Before benchmarking I stopped minutely updates, shut down postgres and dropped memory caches. The DB was on sequenceNumber=1018433 (Sun Oct 09 15:11:31 PDT 2011)<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>Restarting postgres and dropping memory caches added 1.353s to a 49.75s query.<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>I ran 10 queries that cover a range of common jxapi queries. These, as well as the individual times, are listed at <a href="http://wiki.openstreetmap.org/wiki/User:Pnorman/jxapi">http://wiki.openstreetmap.org/wiki/User:Pnorman/jxapi</a><o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>The VACUUM ANALYZE; reduced the query time by 16.47%. Small queries were not significantly impacted.<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal> The vacuum analyze took approximately 59 hours. I am told this could have been improved by increasing maintenance_work_mem over the default .25 GB.<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>Faster disks would of course increase all times. More memory might help too.<o:p></o:p></p></div></body></html>