On Tue, Jul 03, 2007 at 08:05:27AM +0200, Patric de Waha wrote:
Is there a way I can find out what postgres does, and where it hangs
around, so I know where the FK might not be indexed. (The dbase is
to big to analyze it by hand).
You could query the system catalogs to look for foreign key
All,
I'm very curious to know if we may expect or guarantee any data
consistency with WAL sync=OFF but using file system mounted in Direct
I/O mode (means every write() system call called by PG really writes
to disk before return)...
So may we expect data consistency:
- none?
- per
Dimitri wrote:
I'm very curious to know if we may expect or guarantee any data
consistency with WAL sync=OFF but using file system mounted in Direct
I/O mode (means every write() system call called by PG really writes
to disk before return)...
You'd have to turn that mode on on the data drives
Yes, disk drives are also having cache disabled or having cache on
controllers and battery protected (in case of more high-level
storage) - but is it enough to expect data consistency?... (I was
surprised about checkpoint sync, but does it always calls write()
anyway? because in this way it
On Tue, 2007-07-03 at 08:05 +0200, Patric de Waha wrote:
Hi,
I've dbase with about 80 relations.
On deleting a user, this cascades through all the tables.
This is very slow, for 20 users it takes 4 hours, with exclusive
access to the dbase.
No other users connected to the
Hi Greg.
2007/6/28, Greg Smith [EMAIL PROTECTED]:
On Thu, 28 Jun 2007, Ho Fat Tsang wrote:
I have tuned the checkpoint_timeout to 30 second which is ten times less
than default and the issue is still reproduced.
Doing a checkpoint every 30 seconds is crazy; no wonder your system is
2007/6/29, Richard Huxton [EMAIL PROTECTED]:
Ho Fat Tsang wrote:
Hi Richard,
I've tested again according your suggestion. I noticed that for each
time the pgsql slow down, there is a short period a process called
pdflush
eating up lot of I/O. I've goolgled and know it is a process for
2007/7/3, Greg Smith [EMAIL PROTECTED]:
On Fri, 29 Jun 2007, Ho Fat Tsang wrote:
I noticed that for each time the pgsql slow down, there is a short
period a process called pdflush eating up lot of I/O. I've goolgled
and know it is a process for writing dirty pages back to the disk by the
This query is taking less than 5 minutes on 7.4 but over 5 hours on 8.1...
PostgreSQL 8.1.4 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC)
4.1.0 (SUSE Linux)
Total runtime: 20448310.101 ms = 5.6800862 hour
(132 rows)
--postgresql.conf:
shared_buffers = 114688 # min 16
In response to smiley2211 [EMAIL PROTECTED]:
This query is taking less than 5 minutes on 7.4 but over 5 hours on 8.1...
PostgreSQL 8.1.4 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC)
4.1.0 (SUSE Linux)
Total runtime: 20448310.101 ms = 5.6800862 hour
(132 rows)
When was the last
Here is the EXPLAIN after I changed some conf file - now I am running another
EXPLAIN ANALYZE which may take 5 or more hours to complete :,(
effective_cache = 17
enable_seqscan = on
enable _bitmapscan = on
QUERY PLAN
smiley2211 wrote:
Here is the EXPLAIN after I changed some conf file - now I am running another
EXPLAIN ANALYZE which may take 5 or more hours to complete :,(
effective_cache = 17
Why has effective_cache changed from 80,000 to 170,000 - have you
stopped running some other application?
Here are the VIEWS in question: query = (explain analyze select id from
people_consent LIMIT 1;)
CREATE OR REPLACE VIEW temp_consent AS
SELECT p.id, max(en.enrolled_at) AS daterecorded, a.answer
FROM people p, enrollments en, encounters ec,
encounters_questions_answers eqa,
On 7/3/07, smiley2211 [EMAIL PROTECTED] wrote:
CREATE OR REPLACE VIEW temp_consent2 AS
SELECT DISTINCT temp_consent.id, temp_consent.daterecorded
FROM temp_consent
WHERE temp_consent.answer::text = 'Yes'::text
ORDER BY temp_consent.daterecorded DESC, temp_consent.id;
Get rid of the
TOP shows CPU at 100% while executed the EXPLAIN ANALYZE...what does this
mean?
17519 postgres 25 0 3470m 43m 39m R 100 0.3 28:50.53 postmaster
--
View this message in context:
http://www.nabble.com/Query-is-taking-5-HOURS-to-Complete-on-8.1-version-tf4019778.html#a11419885
Sent from
smiley2211 wrote:
TOP shows CPU at 100% while executed the EXPLAIN ANALYZE...what does this
mean?
17519 postgres 25 0 3470m 43m 39m R 100 0.3 28:50.53 postmaster
It means it's busy. Probably sorting/eliminating duplicates (see my
answer posted just before this one).
Keep an eye on
smiley2211 wrote:
Here are the VIEWS in question: query = (explain analyze select id from
people_consent LIMIT 1;)
First thing I notice - you don't have any ordering, so the LIMIT isn't
returning a well-defined record. Might not matter in your particular
context.
CREATE OR REPLACE VIEW
Dimitri [EMAIL PROTECTED] writes:
Yes, disk drives are also having cache disabled or having cache on
controllers and battery protected (in case of more high-level
storage) - but is it enough to expect data consistency?... (I was
surprised about checkpoint sync, but does it always calls
18 matches
Mail list logo
