Dne 19.11.2011 23:34, Phoenix Kiula napsal(a): > On Sat, Nov 19, 2011 at 1:29 AM, Steve Crawford > <scrawford@xxxxxxxxxxxxxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
