Re: Performance query about large tables, lots of concurrent access

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Tom Lane wrote:
Karl Wright <kwright@xxxxxxxxxxxxx> writes:
- At any given time, there are up to 100 of these operations going on at once against the same database.

It sounds like your hardware is far past "maxed out".  Which is odd
since tables with a million or so rows are pretty small for modern
hardware.  What's the CPU and disk hardware here, exactly?  What do you
see when watching vmstat or iostat (as appropriate for OS, which you
didn't mention either)?

			regards, tom lane


Yes, I was surprised as well, which is why I decided to post.

The hardware is a Dell 2950, two processor, dual-core each processor, 16 GB memory, with a RAID disk controller. The operating system is Debian Linux (sarge plus mods, currently using the Postgresql 8.1 backport).

Also, as I said before, I have done extensive query analysis and found that the plans for the queries that are taking a long time are in fact very reasonable. Here's an example from the application log of a query that took way more time than its plan would seem to indicate it should:

>>>>>>
[2007-06-18 09:39:49,783]ERROR Found a query that took more than a minute: [UPDATE intrinsiclink SET isnew=? WHERE ((jobid=? AND childidhash=? AND childid=?)) AND (isnew=? OR isnew=?)]
[2007-06-18 09:39:49,783]ERROR   Parameter 0: 'B'
[2007-06-18 09:39:49,783]ERROR   Parameter 1: '1181766706097'
[2007-06-18 09:39:49,783]ERROR Parameter 2: '7E130F3B688687757187F1638D8776ECEF3009E0' [2007-06-18 09:39:49,783]ERROR Parameter 3: 'http://norwich.openguides.org/?action=index;index_type=category;index_value=Cafe;format=atom'
[2007-06-18 09:39:49,783]ERROR   Parameter 4: 'E'
[2007-06-18 09:39:49,783]ERROR   Parameter 5: 'N'
[2007-06-18 09:39:49,797]ERROR Plan: Index Scan using i1181764142395 on intrinsiclink (cost=0.00..14177.29 rows=5 width=253) [2007-06-18 09:39:49,797]ERROR Plan: Index Cond: ((jobid = $2) AND ((childidhash)::text = ($3)::text)) [2007-06-18 09:39:49,797]ERROR Plan: Filter: ((childid = ($4)::text) AND ((isnew = ($5)::bpchar) OR (isnew = ($6)::bpchar)))
[2007-06-18 09:39:49,797]ERROR
<<<<<<
(The intrinsiclink table above is the "child table" I was referring to earlier, with 13,000,000 rows at the moment.)

Overnight I shut things down and ran a VACUUM operation to see if that might help. I'll post again when I find out if indeed that changed any performance numbers. If not, I'll be able to post vmstat output at that time.

Karl





[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux