Hi.
The test system has 1GB Ram.
The main table has 20+ million rows.
All the other ones account for less than 10K rows.
The values are here below. I suppose that the "hashed"
ones imply a default value.
shared_buffers = 24MB
#temp_buffers = 8MB
#max_prepared_transactions = 5
work_mem = 16MB
#maintenance_work_mem = 16MB
#max_stack_depth = 2MB
max_fsm_pages = 153600
#max_fsm_relations = 1000
max_files_per_process = 1000
#shared_preload_libraries = ''
By the way, it seems that the problem arises with only one query,
while the other ones behave almost the same all the time.
I thank you very much for your attention and help.
On Thursday 28 June 2007 13:17:54 Richard Huxton wrote:
> Vincenzo Romano wrote:
> >>>> The very same query on the very same db shows very variable
> >>>> timings. I'm the only one client on an unpupolated server so
> >>>> I'd expect a rather constant timing.
> >>>
> >>> What's really weird is that after some time the timings get
> >>> back to normal. With no explicit action. Then, later, timings
> >>> get worse again.
> >
> > From the "top" command (I'm running Linux) the only process that
> > jumps high with the load is just the postrgres instance managing
> > the SQL connection.
> > I agree about "something else must be happening in the
> > background". All rthe available RAM gets used as well as some
> > swap.
> > During "fast" operations the used RAM remains low and no swap
> > happens.
>
> That suggests it's not the "same query" that's causing problems. If
> you're going into swap then performance will vary wildly. You may
> have allocated more memory to PostgreSQL than is available on the
> machine.
>
> > I would exclude any other "system" process.
> >
> > How can I log what the PGSQL is actually doing?
>
> See the "when to log" and "what to log" parts of this:
> http://www.postgresql.org/docs/8.2/static/runtime-config.html
>
> As postgres (or other superuser) you can do:
> ALTER DATABASE <db> SET log_min_duration_statement = 1000;
> That will log all statements that take longer than 1 second.
>
> Alternatively log_statement = 'all' will show all queries executed.
>
> You probably want to read the section on "Resource Consumption"
> linked above too. In particular work_mem is *per sort*, which means
> one query can use several times the amount set.
>
> If you post the values for the settings listed in chapter 17.4.1 of
> the manuals and a description of what your machine is like, what
> else it is doing then we might be able to suggest some changes.
--
Vincenzo Romano
--
Maybe Computer will never become as intelligent as Humans.
For sure they won't ever become so stupid. [VR-1988]