3

I'm working with postgres for the first time. I have a lot of experience with small / medium size data analysis (i.e. things that fit in ram and can be analyzed in R, Stata, Matlab, etc.), but am now working with big data (300-750gb) for the first time.

As a result, I have no concept of how long things should be taking. I think my database is performing very poorly, but having never worked on these scales I don't really know.

So here's my question: even basic queries are taking me at least 8 hours on a 237gb table. Vacuum takes ~6 hours. And a query pulling out distinct pairs of values:

CREATE TABLE UserPairs AS
SELECT DISTINCT a, group_a, sum(quantity) FROM cdr GROUP BY (a, group_a) HAVING type = 'DATA' AND group_a IS NOT NULL;

ran for 8 hours before I aborted.

An attempt to build 4 hash indices over main columns ran for 24 hours then crashed.

Hardware: 3 cores, 12 gb ram Windows 8 server VM. (I know, but I don't have control over my hardware. Long story).

So basically: within an order of magnitude, how long should I expect basic queries to take in postgres for tables of this size?

And if this seems way off, how do I get more precise benchmarks? I'm running pgbench now, but can't find resources on how to interpret the results. This listserv exchange suggests there aren't any repositories of results...

  • 1.5 billion rows.
  • Settings are default, looks like work_mem = 1mb, maintanence_work_mem = 16mb.
  • IO System: it's a VM so the disk is just listed as a "VMware virtual disk SCSI disk device", but Performance Monitor says avg Disc sec/transfer is 0.010sec and average disk queue length is 2.16.

migrated from stackoverflow.com Apr 14 '14 at 0:24

This question came from our site for professional and enthusiast programmers.

  • At first glance - week hardware. In any case there is always a very big potential in optimization. – Str. Apr 13 '14 at 20:12
  • Please don't cross-post... this question is on it's way over there shortly, but now there's no point. If you're doing this then you're not going to be able to put it all on RAM and you're only going to use one core so the biggest potential problem is going to be disk I/O. You need good disks... – Ben Apr 13 '14 at 20:41
  • You haven't described your IO subsystem, which is critical. What configuration settings are you using, particularly work_mem and maintenance_work_mem? – jjanes Apr 13 '14 at 22:35
  • Re: settings are default, looks like work_mem = 1mb, maintanence_work_mem = 16mb. (I'm gonna guess that's a problem?). Re: IO: it's a VM so the disk is just listed as a "VMware virtual disk SCSI disk device", but Performance Monitor says avg Disc sec/transfer is 0.010sec and average disk queue length is 2.16. (guessing at what's useful based on this. Thanks everyone for bearing with such a noob (I'm a social scientist if that helps explain ignorance, but i'm learning)! Also, will avoid cross posting in the future. – nick_eu Apr 14 '14 at 1:30
4

You have to first set expectations - a screen that does such and such activities should complete each action in 1 second and all actions in 5 seconds and so on. For example, a search screen should retrieve results in 3 seconds, the booking actions (ticket booking) should be completed in 30 seconds etc.

Then work towards meeting those targets. That is the "normal" performance you want. Now go about meeting those targets. The database may be your bottleneck, it may not be. To identify issues at the database side, try using a tool like pgbadger. That will tell you which queries are taking time.

By the way, 8 hours for a query is probably not acceptable under any circumstance. Try the tool pgtune and see if there is scope for optimizing parameters.

2

If you are grouping you don't need a distinct and move the conditions from the having to the where

select a, group_a, sum(quantity)
from cdr
where type = 'DATA' and group_a is not null
group by a, group_a;
  • Thanks! But specifics aside, I'm trying to get a GENERAL sense of whether my system is performing normally... – nick_eu Apr 14 '14 at 3:02
  • 1
    @nick_eu: The "specifics" can make the resulting performance differ by orders of magnitude. With billions of rows, the type should also probably not be a text string to keep the size of the table small ... – Erwin Brandstetter Apr 14 '14 at 16:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.