Arm-Koji buildsystem will be down for maintenance

[Dmitry Kozunov]

On Monday November 19 Arm-Koji will be going down for scheduled maintenance at 1500 hours UTC.
Expected downtime is 3-4 hours.

[Peter Robinson]

Hi Dmitry,

On Thu, Nov 15, 2012 at 10:18 PM, Dmitry Kozunov
 wrote:
> On Monday November 19 Arm-Koji will be going down for scheduled maintenance
> at 1500 hours UTC.
> Expected downtime is 3-4 hours.

Can you please outline what that maintenance is?

Peter

[Dmitry Kozunov]

This maintenance was scheduled to address koji-arm database performance issues.

________________________________________
From: Peter Robinson [[e-mail]]
Sent: Friday, November 16, 2012 1:46 AM
To: Dmitry Kozunov
Cc: [e-mail]
Subject: Re: [fedora-arm] Arm-Koji buildsystem will be down for maintenance

Hi Dmitry,

Can you please outline what that maintenance is?

Peter

[Jon Masters]

On 11/16/2012 12:22 PM, Dmitry Kozunov wrote:
> This maintenance was scheduled to address koji-arm database performance issues.

Hey Dmitry,

This is great. But if I may, I think what Peter is getting at is that he
(and the rest of us) would like more visibility into what is being
changed. Sometimes, outages run into many hours or even days because an
unforeseen situation arises. This is frustrating for people like Peter
who spend their dayjobs designing and building very fancy Enterprise
computing solutions because they really have a lot of expertise and
experience to bring to the table, if they know what is happening.

So, can you perhaps provide a bit more detail? We know you've been
looking at options around the tables, but...more info?

Thanks!

Jon.

[Chris Tyler]

On Fri, 2012-11-16 at 13:41 -0500, Jon Masters wrote:
> So, can you perhaps provide a bit more detail? We know you've been
> looking at options around the tables, but...more info?

Long story short: db dump & reload for storage packing, with an LVM
snapshot as a safety net; should be done in about 20 minutes. Tests on a
parallel machine show a 500x performance improvement.

(Investigations into PostgreSQL being insanely brain-dead continue.
Example: query strategy for "SELECT COUNT(*) FROM FOO;" involves a
"Sequential scan over 1496312 records." Srsly?)

-Chris

[Jon Masters]

On 11/16/2012 02:00 PM, Chris Tyler wrote:
> On Fri, 2012-11-16 at 13:41 -0500, Jon Masters wrote:
> > So, can you perhaps provide a bit more detail? We know you've been
> > looking at options around the tables, but...more info?
>
> Long story short: db dump & reload for storage packing, with an LVM
> snapshot as a safety net; should be done in about 20 minutes. Tests on a
> parallel machine show a 500x performance improvement.
>
> (Investigations into PostgreSQL being insanely brain-dead continue.
> Example: query strategy for "SELECT COUNT(*) FROM FOO;" involves a
> "Sequential scan over 1496312 records." Srsly?)

Thanks! That's just the kind of stuff I think we wanted to know about,
and yea, I've never been a huge postgres fan :)

Jon.

[Peter Robinson]

On Fri, Nov 16, 2012 at 7:00 PM, Chris Tyler  wrote:
> On Fri, 2012-11-16 at 13:41 -0500, Jon Masters wrote:
> > So, can you perhaps provide a bit more detail? We know you've been
> > looking at options around the tables, but...more info?
>
> Long story short: db dump & reload for storage packing, with an LVM
> snapshot as a safety net; should be done in about 20 minutes. Tests on a
> parallel machine show a 500x performance improvement.

500x on a box without random queries coming from all over, I suspect
it won't be as impressive on production. I suspect you'll get pretty
decent perf improvements if you drop and recreate the indexes and
vacuum the DB.
> (Investigations into PostgreSQL being insanely brain-dead continue.
> Example: query strategy for "SELECT COUNT(*) FROM FOO;" involves a
> "Sequential scan over 1496312 records." Srsly?)

Probably either a bad index or a lack of an index in the right spot.

Peter

[Jared K. Smith]

On Fri, Nov 16, 2012 at 2:00 PM, Chris Tyler  wrote:
> (Investigations into PostgreSQL being insanely brain-dead continue.
> Example: query strategy for "SELECT COUNT(*) FROM FOO;" involves a
> "Sequential scan over 1496312 records." Srsly?)

Just curious... have you VACUUM'd the database?  Are you running
AUTO-VACUUM?  Have you run EXPLAIN ANALYZE SELECT COUNT(*) FROM FOO?
In general, once you learn a few tricks like this, it's typically much
easier to figure out why PostgreSQL is choosing a particular query
plan than it is with other databases.

--
Jared Smith

[Jeffrey Ollie]

On Fri, Nov 16, 2012 at 1:00 PM, Chris Tyler  wrote:
> (Investigations into PostgreSQL being insanely brain-dead continue.
> Example: query strategy for "SELECT COUNT(*) FROM FOO;" involves a
> "Sequential scan over 1496312 records." Srsly?)

http://wiki.postgresql.org/wiki/Slow_Counting

Short answer: upgrade to 9.2.

--
Jeff Ollie

[Jared K. Smith]

On Mon, Nov 19, 2012 at 10:26 AM, Jeffrey Ollie  wrote:
> http://wiki.postgresql.org/wiki/Slow_Counting

Ah, right... I knew this at one time in the past -- funny how my brain
is failing me as I get old.  Thanks for reminding me :-)

--
Jared Smith