second is
where the cache update happens and the commit later fails, or the
commit
happens and the cache update never happens.
Having pgmemcache delete, not replace data addresses this second issue.
-sc
--
Sean Chittenden
---(end of broadcast)---
owever, I advise going to the database unless you're
willing to swallow the financial cost of cache discrepancies.
-sc
--
Sean Chittenden
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
ld a proper make/release
structure. -sc
--
Sean Chittenden
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Since you've
freed up more ram by disabling persistent connections, this shouldn't
be a problem. -sc
--
Sean Chittenden
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
ce copying.
Even assuming that that number consists entirely of reads and writes of
shared buffers (and of course no other kernel call ever transfers any
data across that boundary ;-)), there's no way we are going to buy into
this sort of project in hopes of a 3% win.
Would it be helpful if I cr
e(2) call, which can give the OS the following
hints: MADV_NORMAL, MADV_SEQUENTIAL, MADV_RANDOM, MADV_WILLNEED,
MADV_DONTNEED, and MADV_FREE. :) -sc
--
Sean Chittenden
---(end of broadcast)---
TIP 9: the planner will ignore your desire to cho
tem buffer cache should ever
use mmap(2). In order for this to work on HPUX, msync(2) would need to
be used. -sc
--
Sean Chittenden
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joinin
catalogs (permissions,
etc.), pages that are loaded from the catalogs could be loaded with the
protection PROT_READ, which would prevent changes to the catalogs. All
DDL and permission altering commands (anything that touches the system
catalogs) would then load the page with the P
becoz of malformed SQLs. [ specially on database of my
> choice ;-) ]
Post EXPLAIN ANALYZES of the queries you're running, then maybe you'll
be able to get some useful help from this list. Until then, it's very
hard to speculate as to why PostgreSQL
not asked.
Real quick, this isn't true, the block size is tunable, but does not
change the default. You can set PGBLOCKSIZE to the values "16K" or
"32K" to change the block size, but the default remains 8K.
http://lists.freebsd.org/pipermail/freebsd-database/2003-October/
$PGDATA/data/shared dir as mmap() is by far and away
the fastest shared memory mechanism and certainly is very widely
deployed (I would be surprised if any of the supported PG platforms
didn't have mmap()).
-sc
--
Sean Chittenden
---(end of broadcast)-
ing like twice the default one, and a BKVASIZE of 4 times the
default. vfs.maxbufspace ends up at 445MB on the machine with 1GB, so
it is maxed out now."
YMMV.
-sc
--
Sean Chittenden
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
of it is used for a disk cache and don't use a multiplier. The 8192,
however, is the size of a PG page, so, if you tweak PG's page size,
you have to change this constant (*grumbles*).
-sc
--
Sean Chittenden
---(end of broadcast)---
TIP 2: you c
GUC? Too many tunables are page dependant, which is
infuriating when copying configs from DB to DB. I wish pgsql had some
notion of percentages for values that end with a '%'. -sc
--
Sean Chittenden
---(end of broadcast)---
TIP 8: explain analyze is your friend
cisions. Or, lastly, does anyone think that this should be in a
different, external program? -sc
--
Sean Chittenden
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
ution that I use and it
works quite well. I didn't explain the use of the qual column, but I
think those who grasp the above way of handling things probably grok
how to use the qual column in a dynamically executed query.
CREATE AGGREGATE CACHE anyone?
-sc
--
Sean Chittenden
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
s/room
[snip]
> I am running Red hat 8. Some of my conf entries that I have changed
> follow
> shared_buffers = 3700
> effective_cache_size = 4000
> sort_mem = 32168
Have you twiddled with your wal_buffers or checkpoint_segments? Might
be something to l
r than to an IDE drive, please let me know. :) -sc
--
Sean Chittenden
UNIX(TM), a BSD like Operating System
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
s pthreads (chears on
KSE!), and returns more fine grained timing information for the
various activities.
-sc
--
Sean Chittenden
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
rk that's much better.
From: Manfred Koizar <[EMAIL PROTECTED]>
Cc: [EMAIL PROTECTED]
Subject: Re: [HACKERS] Correlation in cost_index()
Date: Wed, 20 Aug 2003 19:57:12 +0200
Message-ID: <[EMAIL PROTECTED]>
and
From: Manfred Koizar <[EMAIL PROTECTED]>
To: [E
e
we are but I haven't used it yet to see if it's the magic ticket for
many of these index problems.
-sc
--
Sean Chittenden
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
o test and see. If you find something that works,
however, let me know. -sc
--
Sean Chittenden
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so t
in PG is
two read calls to the OS, one reads 16K of data off disk and returns
the 1st page, the 2nd call pulls the 2nd block from the FS cache. In
making things 16K, it avoids the need for the 2nd system call which is
where the performance difference is coming from, afaikt. -sc
--
Sean Chittenden
lly expect that FreeBSD 4.8 will perform at
least faster than 5.1 (5.x is still being unwound from Giant), but
should out perform Linux as well if industry experience iss any
indicator.
-sc
--
Sean Chittenden
---(end of broadcast)---
TIP 2: you c
uick... the faster the drives, the less important it is to move
WAL onto a different drive. The slower the drives, the more important
this is... which is why this isn't as necessary (if at all) for large
production environments.
-sc
--
Sean Chittenden
-
and I will do my best to either
address or correct the problem.
Now, back to our regularly scheduled and on topic programming... -sc
--
Sean Chittenden
"(PostgreSQL|FreeBSD).org - The Power To Serve"
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
27;::INTERVAL)
> AND NOT action;
>
> SELECT *
> FROM mss_fwevent
>WHERE sensorid = 7
> AND evtime > (now() - '6 hours'::INTERVAL);
>
> SELECT *
> FROM mss_fwevent
>WHERE evtime > (now() - '6 hours
-> Index Scan using mss_fwevent_evtime_sensorid_idx on
mss_fwevent (cost=0.00..210202.76 rows=168478 width=12) (actual time=0.35..17.61
rows=320 loops=1)
Index Cond: ((evtime > (now() - '06:00'::interval))
AND (sensorid = 7))
Filter: (NOT "action")
Total runtime: 29.09 msec
(11 rows)
--
Sean Chittenden
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
o "effective_cache_size = $((`sysctl -n vfs.hibufspace` / 8192))"
;;
*)
echo "Unable to automatically determine the effective cache size" >>
/dev/stderr
;;
esac
-sc
--
Sean Chittenden
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
t database and test
> battery that we can run on a variety of machines and platforms.
Works for me, though a benchmark will be less valuable than adding a
disk concurrency stat, improving data trend/distribution analysis, and
using numbers that are concrete and obtainable through the
the applications' needs. Some of those parameters are based on
hardware constraints and should be pooled and organized as such.
random_page_cost ==
avg cost of a random disk seek/read (eg: disk seek time) ==
constant integer for a given piece of hardware
There are other set
> Sean Chittenden <[EMAIL PROTECTED]> writes:
> > Getting the planner to pick
> > using the index to filter out data inserted in the last 3 days over
> > doing a seq scan... well, I don't know how you could do that without
> > changing the random_page_cost.
27;s the max connections setting or sort_mem,
etc... having the values dup'ed in the SGML, however, would be good
too, but it's of most practical relevance in the actual config: as an
admin setting up a DB, I'd rather not have to fish around on
postgresql.org to find a recommended sett
un query three times]
SET random_page_cost = 0.32; -- Double check that 0.32 is the magic number
[run query three times]
[edit postgresql.conf && killall -SIGHUP postmaster]
-sc
--
Sean Chittenden
pgp0.pgp
Description: PGP signature
gram
that'd do exactly what you're thinking of.
http://archives.postgresql.org/pgsql-performance/2002-10/msg00101.php
http://gborg.postgresql.org/project/pgautotune/projdisplay.php
--
Sean Chittenden
---(end of broadcast)---
TIP 8: explain analyze is your friend
ulprits I wonder? I've wandered through that page and wasn't sure
> what to play with.
random_page_cost should be proportional to the seek time necessary for
the disk to find a page of data on its platters. It makes sense that
this value, as time progresses, gets smaller as hardware gets faster.
-sc
--
Sean Chittenden
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
a win
for everyone. The person who I was helping also had the same data,
schema, and query running on MySQL and the fastest it could go was
2.7s (about 40M rows in the table).
-sc
--
Sean Chittenden
---(end of broadcast)---
TIP 3: if posting/readi
tions));
VACUUM ANALYZE;
Just make sure that you set your function to be IMMUTABLE. -sc
PS It'd be slick if PostgreSQL would collapse adjacent booleans into a
bit in a byte: it'd save some apps a chunk of space. 32 options ==
32 bytes with the type BOOL, but if adjacent BOOLs wer
38 matches
Mail list logo
