Re: [PERFORM] go for a script! / ex: PostgreSQL vs. MySQL

> >NB> shared_buffers = 1/16th of total memory > >NB> effective_cache_size = 80% of the supposed kernel cache. > > I think Sean(?) mentioned this one for FreeBSD (Bash code): sh, not bash. :) > echo "effective_cache_size = $((`sysctl -n vfs.hibufspace` / 8192))" > > I've used it for my dedicat

Re: [PERFORM] sql performance and cache

PostgreSQL does not have, and has never had a query cache - so nothing you do is going to make that second query faster. Let me clarify that. PostgreSQL will of course cache the disk pages used in getting the data for your query, which is why the second time you run it, it is 3 seconds faster.

Re: [PERFORM] go for a script! / ex: PostgreSQL vs. MySQL

On Sat, 2003-10-11 at 05:22, Harald Fuchs wrote: > In article <[EMAIL PROTECTED]>, > Rod Taylor <[EMAIL PROTECTED]> writes: > > >> Would anyone object to a patch that exports the blocksize via a > >> readonly GUC? Too many tunables are page dependant, which is > >> infuriating when copying config

Re: [PERFORM] [SQL] sql performance and cache

Nested Loop (cost=0.00..147.11 rows=1 width=148) (actual time=84.00..12323.00 rows=67 loops=1) The planner estimate doesn't seem to match reality in that particular step. Are you sure you've run: ANALYZE oscar_node; ANALYZE oscar_point; And you could even run VACUUM FULL on them just to make

[PERFORM] sql performance and cache

Hello all I have two very similar queries which I need to execute. They both have exactly the same from / where conditions. When I execute the first, it takes about 16 seconds. The second is executed almost immediately after, it takes 13 seconds. In short, I'd like to know why the query result isn

Re: [PERFORM] sql performance and cache

I have two very similar queries which I need to execute. They both have exactly the same from / where conditions. When I execute the first, it takes about 16 seconds. The second is executed almost immediately after, it takes 13 seconds. In short, I'd like to know why the query result isn't being c

Re: [PERFORM] [SQL] sql performance and cache

On Saturday 11 October 2003 10:43, Chris Faulkner wrote: > Hello all > > I have two very similar queries which I need to execute. They both have > exactly the same from / where conditions. When I execute the first, it > takes about 16 seconds. The second is executed almost immediately after, it > t

Re: [PERFORM] [SQL] sql performance and cache

Hello Thanks for the reply. > The short answer is that PG doesn't cache query results. The only > way it could > do so safely is to lock all tables you access to make sure that no other > process changes them. That would effectively turn PG into a > single-user DB in > short notice. I am not sur

Re: [PERFORM] [SQL] sql performance and cache

Nested Loop (cost=0.00..147.11 rows=1 width=148) (actual time=84.00..12323.00 rows=67 loops=1) The planner estimate doesn't seem to match reality in that particular step. Are you sure you've run: ANALYZE oscar_node; ANALYZE oscar_point; And you could even run VACUUM FULL on them just to make

Re: [PERFORM] [SQL] sql performance and cache

On Saturday 11 October 2003 12:12, Chris Faulkner wrote: > Hello > > Thanks for the reply. > > > The short answer is that PG doesn't cache query results. The only > > way it could > > do so safely is to lock all tables you access to make sure that no other > > process changes them. That would effec

Re: [PERFORM] go for a script! / ex: PostgreSQL vs. MySQL

Josh Berkus wrote: shared_buffers = 1/16th of total memory effective_cache_size = 80% of the supposed kernel cache. But only if it's a dedicated DB machine. If it's not, all memory values should be cut in half. What I would prefer would be an interactive script which would, by asking th

[PERFORM] Another weird one with an UPDATE

I am running an update-query to benchmark various databases; the postgres version is,   UPDATE user_account SET last_name = 'abc'WHERE user_account_id IN (SELECT user_account_id FROM commercial_entity, commercial_service WHERE yw_account_id IS NULLAND commercial_entity.commercial_entity_id =

Re: [PERFORM] Another weird one with an UPDATE

Sorry - just found the FAQ (http://jamesthornton.com/postgres/FAQ/faq-english.html#4.22) on how IN is very slow.   So I rewrote the query:   \o ./data/temp.txt SELECT current_timestamp; UPDATE user_account SET last_name = 'abc'WHERE EXISTS (SELECT ua.user_account_id FROM user_account ua, comme

Re: [PERFORM] Another weird one with an UPDATE

On Sat, 11 Oct 2003, David Griffiths wrote: > Sorry - just found the FAQ ( > http://jamesthornton.com/postgres/FAQ/faq-english.html#4.22 > ) on how > IN is very slow. > > So I rewrote the query: > > \o ./data/temp.txt > SELECT current_t

Re: [PERFORM] go for a script! / ex: PostgreSQL vs. MySQL

If shmmax and shmmall are too small, then: PostgreSQL requires some more shared memory to cache some tables, x Mb, do you want to increase your OS kernel parameters? Tweak shmmax and shmmall Note that this still requires a kernel recompile on FreeBSD :( Chris ---(en

Re: [PERFORM] Another weird one with an UPDATE

Thanks - that worked. David - Original Message - From: "Stephan Szabo" <[EMAIL PROTECTED]> To: "David Griffiths" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Saturday, October 11, 2003 3:34 PM Subject: Re: [PERFORM] Another weird one with an UPDATE > On Sat, 11 Oct 2003, David Griff