Re: Huge number of INSERTs - Mailing list pgsql-general

Dne 19.11.2011 23:34, Phoenix Kiula napsal(a):
> On Sat, Nov 19, 2011 at 1:29 AM, Steve Crawford
> <scrawford@pinpointresearch.com> wrote:
>> You still didn't answer what "massive traffic" means.
>
>
> Thousands of website hits per minute. (At peak time)
>
> Average is a few hundred per minute.

This is pretty vague description of the workload, as we have no clue how
demanding the request processing is. It might be a simple script that
does almost nothing (in that case the thousands of hits is easy to
handle) but it might be rather expensive.

Anyway I personally see this as a rather unrelated to the problem we're
trying to help you with - setting the right number of connections and
maybe fixing some of the queries.

>> 3Com? Perhaps you mean 3Ware? And (again) what are the RAID cache
>> *settings*? In particular, the write-back/write-through setting.
>
>
> Yes 3Ware.
>
> RAID cache settings:
>
>
> ----------------------------------------------------------------------
> Logical device information
> ----------------------------------------------------------------------
> Logical device number 0
> Logical device name : RAID10-A
> RAID level : 10
> Status of logical device : Optimal
> Size : 1906678 MB
> Stripe-unit size : 256 KB
> Read-cache mode : Enabled
> MaxIQ preferred cache setting : Disabled
> MaxIQ cache setting : Disabled
> Write-cache mode : Enabled (write-back)
> Write-cache setting : Enabled (write-back) when protected by battery/ZMM
> Partitioned : Yes
> Protected by Hot-Spare : No
> Bootable : Yes
> Failed stripes : No
> Power settings : Disabled
> --------------------------------------------------------
> Logical device segment information
> --------------------------------------------------------
> Group 0, Segment 0 : Present (0,0) 9QJ00FMB
> Group 0, Segment 1 : Present (0,1) 9QJ1R3NW
> Group 1, Segment 0 : Present (0,2) 9QJ00L58
> Group 1, Segment 1 : Present (0,3) 9QJ01JJ5

So how much write cache is there and what 3Ware model is that? Because I
don't see this information there.

>> So most of your selects aren't hitting the database. Since we are talking db
>> tuning, it would have been nice to know how many queries are hitting the
>> database, not the number of requests hitting the webserver. But the question
>> was "what is the typical duration of the queries" - specifically the queries
>> hitting the database.
>
>
> Right now single SELECTs with just that one "WHERE indexed_column =
> 'Value' LIMIT 1" type queries are taking 3.0 of CPU, and so on. Why
> should these queries be taking so much time and resources?

3.0 of CPU? Is that seconds or what? Anyway post a more detailed
description of the tables (columns, data types, indexes) and EXPLAIN
ANALYZE of the queries (using explain.depesz.com).

>> Earlier you said you were doing 200 inserts/minute. Is that an average
>> throughout the day or is that at peak time. Peak load is really what is of
>> interest. 200 inserts/minute is not even 4/second.
>
> As above.

As above what? Please, be more specific, it's a bit difficult to know
which paragraph above you're refering to.

Again - post an EXPLAIN ANALYZE of the queries using explain.depesz.com.

>> Look at your log. If it isn't set to record request time, set it to do so. I
>> set my Apache servers to log request time in microseconds.
>
>
> Could you specify how precisely you have set up this log? Through
> CustomLog? Thanks!

log_line_prefix = '%t'

And you should probably add a few more fields (session ID, ...)



Tomas