On Wed, Apr 19, 2006 at 04:35:11AM +0200, Terje Elde wrote:
> Jim C. Nasby wrote:
> >>That said, it's the transactions against disk that typically matter. On
> >>FreeBSD, you can get an impression of this using 'systat -vmstat', and
> >>watch the KB
On Wed, Apr 19, 2006 at 04:47:40PM +1200, Mark Kirkwood wrote:
> Jim C. Nasby wrote:
> >On Tue, Apr 18, 2006 at 06:22:26PM -0400, Tom Lane wrote:
> >>"Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> >>>Actually, if you run with stats_block_level turned o
On Wed, Apr 19, 2006 at 01:25:28AM -0400, Tom Lane wrote:
> Mark Kirkwood <[EMAIL PROTECTED]> writes:
> > Jim C. Nasby wrote:
> >> Good point. :/ I'm guessing there's no easy way to see how many blocks
> >> for a given relation are in shared memory, e
t; situation...
Depending on your performance requirements, you should look at
contrib/userlock as well, since it will probably be much more performant
than locking a row in a table.
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.comw
is very fast when looking for a small subset of the
> data. For servers, I am using white box intel
> XEON and P4 systems with SATA disks, 4G of memory. SCSI is out of our price
> range, but if I had unlimited $ I would go
> with SCSI /SCSI raid instead.
--
Jim C. Nasby, Sr. Engi
re that
you're vacuuming the database frequently enough. Autovacuum is a good
way to do that.
> My test was on Windows XP SP2.
> I have AMD 64 2.1 GHz cpu with
> 1GB ram.
One think to keep in mind is that the windows code is rather new, so it
is possible to find some performance issues
in PostgreSQL would be such a case.
The opposite example would be an index scan of a highly uncorrelated
index, which would produce mostly random reads from the table. In that
case, reading ahead probably makes very little sense, though your logic
might have a better idea of the access pattern tha
order by i,j,k limit 50;
Note that in 8.2 you'll be able to do:
WHERE (i, j, k) >= (i1, j1, k1)
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell
sting this when he gets a chance,
but you could also try running dbt2 and dbt3 against it.
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf
ost down.
Also, 8.1.3 has a lot of new config settings compared to 7.4.x; it'd
probably be best to take the default 8.1 config and tweak it, rather
than bringing the 7.4 config over.
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasiv
e/performance-tips.html
http://www.pervasive-postgres.com/lp/newsletters/2006/Insights_postgres_Apr.asp#4
might also be worth your time to read...
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.comwork: 512-231-6117
vcard: http:
inet data type offer comparison/search performance benefits
> over plain text for ip addresses..
Well, benchmark it and find out. :) But it'd be hard to be slower than
like or regex...
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervas
ay it can use an
index on col1 (maybe a function index, but that's a different story).
Is there some reason you're not doing
WHERE col1 <<= '172/8'::inet
?
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.com
annonce_id;
>QUERY PLAN
> -----
> Hash Join (cost=7.98..534.24 rows=140 width=203) (actual
> time=0.811..5.557 row
of 40. Testing
was 40 connections and 100 transactions. With HT he saw 47.6 TPS,
without it was 21.1.
I actually had IT build put w2k3 server on a HT box specifically so I
could do more testing.
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pe
d be a
> great place to start. Mock up some benchmark with a couple dozen
> threads hitting the server at once and see if the Intel can keep up. It
Or better yet, use dbt* or even pgbench so others can reproduce...
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive
On Wed, Apr 26, 2006 at 10:17:58AM -0500, Scott Marlowe wrote:
> On Tue, 2006-04-25 at 18:55, Jim C. Nasby wrote:
> > On Tue, Apr 25, 2006 at 01:33:38PM -0500, Scott Marlowe wrote:
> > > On Tue, 2006-04-25 at 13:14, Bill Moran wrote:
> > > > I've been giv
uldn't be seeing such a
dramatic difference between AMD and Intel though. Even in a disk-bound
server, caching is going to have a tremendous impact, and that's
essentially entirely bound by memory bandwith and latency.
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
on your version of
PostgreSQL, and data fields have to start on the proper alignment
(generally 4 bytes). So essentially the smallest row you can get is 28
bytes.
I know that tuple headers are dealt with as a C structure, but I don't
know if that means accessing any of the header cos
t; >
> > The patch is open to fine-tuning advices :)
> > Comments and benchmarking results are highly appreciated.
> >
> > Thanks,
> > Wu
> >
> > ---(end of broadcast)---
> >
On Wed, Apr 26, 2006 at 06:16:46PM -0400, Bruce Momjian wrote:
> Jim C. Nasby wrote:
> > On Wed, Apr 26, 2006 at 10:27:18AM -0500, Scott Marlowe wrote:
> > > If you haven't actually run a heavy benchmark of postgresql on the two
> > > architectures, please don
it performance. If
it doesn't then it's likely that fsync isn't being obeyed, which means 0
data integrity.
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervas
the
> failover in software instead of hardware.
BTW, I know a company here in Austin that does capacity planning for
complex systems like this; contact me off-list if you're interested in
talking to them.
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Per
ease> see
> >http://developer.postgresql.org/~wieck/vacuum_cost/
> >20:26 < rtfm_please> or
> >http://www.postgresql.org/docs/current/static/sql-vacuum.html
> >20:26 < rtfm_please> or http://www.varlena.com/varlena/GeneralBits/116.php
> >
> >20:
At 03:00 06/04/29, Bruno Wolff III wrote:
On Fri, Apr 28, 2006 at 17:37:30 +,
Bealach-na Bo <[EMAIL PROTECTED]> wrote:
> >The above shows that the indexes contained 10M rows and 160M of dead
> >space each. That means you weren't vacuuming nearly enough.
>
> How is it that a row in the tabl
At 10:39 06/04/29, Tom Lane wrote:
K C Lau <[EMAIL PROTECTED]> writes:
> Without knowing the internals, I have this simplistic idea: if Postgres
> maintains the current lowest transaction ID for all active
transactions, it
> probably could recycle dead tuples on the fly.
[
mance of this query is quite worse as longer it gets, its
> possible that this query gets over 20 to 30 OR comparisons, but then
> the performance is really worse, is it possible to speed it up?
> Thanks
> Clemens
>
>
> ---(end of broadcast)----
f the issue.
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
---(end of broadcast)---
TIP 4: Have y
On Sat, Apr 29, 2006 at 11:18:10AM +0800, K C Lau wrote:
>
> At 10:39 06/04/29, Tom Lane wrote:
> >K C Lau <[EMAIL PROTECTED]> writes:
> >> Without knowing the internals, I have this simplistic idea: if Postgres
> >> maintains the current lowest transaction ID
done once to get you back on track, assuming that #1 is
> done properly.
You'll also want to reindex since vacuum full won't clean the indexes
up. You might also want to read
http://www.pervasivepostgres.com/instantkb13/article.aspx?id=10087 and
http://www.pervasivepostgres.com/instan
been unable to track down any more
information than that, other than the fact that I haven't been able to
reproduce this on any single-CPU machines.
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.comwork: 512-231-61
ainly not a good start for fair benchmarking :-)
If you want a more realistic test, try dbt2:
http://sourceforge.net/projects/osdldbt
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.comwork: 512-231-6117
vca
so
I'd rather not go mucking about with testing such a change here.
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
---(e
lem with pg_autovacuum is the need for pg_statio, which itself
> will reduce performance at all times.
>
> Any suggestions?
>
> Thanks!
>
> - Chris
>
>
>
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasi
on-wide vacuum if it just got rid of
all my 'dead rows'...
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
-
t platforms.
I'm intending to submit a patch to clean some of that code up (put all
the thresholds in one .h file rather than how they're spread through
source code right now); if you drop me an email off-list I'll send you
info once I do that.
--
Jim C. Nasby, Sr. Engineering Consultan
zed" for RAID 5. Not
> >>sure if we made the right decision though. They give an option for
> >>formatting as RAID 0+1. Is that the same as RAID 10 that everyone
> >>talks about? Or is it the reverse?
> >>
> >>Thanks,
> >>
> >>_______
m_cost_delay *|wc -l
8
> I've come to the conclusion I need to simply start tracking all transactions
> and determining a cost/performance for the larger and frequently updated
> tables without the benefit and penalty of pg_statio.
Huh? pg_statio shouldn't present a lar
On Tue, May 02, 2006 at 06:49:48PM -0400, Jan de Visser wrote:
> On Tuesday 02 May 2006 16:28, Jim C. Nasby wrote:
> > On Sun, Apr 30, 2006 at 10:59:56PM +1200, Mark Kirkwood wrote:
> > > Pgadmin can give misleading times for queries that return large result
> > >
a rough idea of disk performance, beyond that you really need
> to see how your disk is performing with your actual workload.
Well, in this case the question was about random write access, which dd
won't show you.
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasi
es then I suspect that this is not an issue for XP...
On Tue, May 02, 2006 at 11:27:02PM -0500, Gregory Stewart wrote:
> Jim,
>
> Have you seen this happening only on W2k3? I am wondering if I should try
> out 2000 Pro or XP Pro.
> Not my first choice, but if it works...
>
elp?
>
> There is no SP2 for Windows 2003.
>
> Have you tried this with latest-and-greatest CVS HEAD? Meaning with the
> new semaphore code that was committed a couple of days ago?
I'd be happy to test this if someone could provide a build, or if
there's instructions somew
scratch. But there was some kind of issue with
doing that that was fixed in HEAD, but I don't think it's been
back-ported. I also don't remember exactly what the issue was... :/
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pe
im's
database is approximately 4G in size, so the 5.3G of disk cache makes
sense if the system was recently rebooted.
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf
t; >error messages.
>
> If you're talking about the shared memory limits, postgres will bomb out
> fairly quickly in that case, IIRC.
More importantly I don't think it would result in trying to allocate 10
TB or whatever that huge number was.
--
Jim C. Nasby, Sr. Engi
ome money invested there would result in a lot less
frustration. It'd also certainly be cheaper than switching to Oracle.
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/perva
be a win to add some constraints at the same time, but RI can't
be added until all tables are indexed.
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 5
g your indices using REINDEX command.
And if that doesn't work we need at least the output of EXPLAIN, if not
EXPLAIN ANALYZE.
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.comwork: 512-231-6117
vcard: http:/
autovac thresholds in half; that's
what I typically do.
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
nce.
>
> Yeah, I prefer my surgeons to work this way too. training is for the
> birds.
I think you read too quickly past the part where Tim said he'd taking a
week-long training class.
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://
99,$600,$601,$602,$603,$604,$605,$606,$607,$608,$609,$610,$611,$612,$613,$614,$615,$616,$617,$618,$619,$620,$621,$622,$623,$624,$625,$626,$627,$628,\
> $629,$630,$631,$632,$633,$634,$635,$636,$637,$638,$639,$640,$641,$642,$643,$644,$645,$646,$647,$648,$649,$650)
>
> ORDER BY f,c
>
ettings allow. But even there, is there any real reason you want to
have 40% bloat? To make matters worse, those settings ensure that all
but the smallest databases will suffer runaway bloat unless you bump up
the FSM settings.
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Perv
off running a
> bunch of small selects than in trying to optimize things with one
> gargantuan select.
Ever experiment with loading the parameters into a temp table and
joining to that?
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://per
On Tue, May 09, 2006 at 11:33:42AM +0200, PFC wrote:
> - Repeating the query might yield different results if records were
> added or deleted in the meantime.
BTW, SET TRANSACTION ISOLATION LEVEL serializeable or BEGIN ISOLATION
LEVEL serializeable would cure that.
--
Jim C.
le to see what was rolled back.
Speaking of which, if a temp table is defined as ON COMMIT DROP or
DELETE ROWS, there shouldn't be any need to store xmin/xmax, only
cmin/cmax, correct?
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http:
On Tue, May 09, 2006 at 01:29:56PM +0200, PFC wrote:
> 0.101 ms BEGIN
> 1.451 ms CREATE TEMPORARY TABLE tmp ( a INTEGER NOT NULL, b INTEGER NOT
> NULL, c TIMESTAMP NOT NULL, d INTEGER NOT NULL ) ON COMMIT DROP
> 0.450 ms INSERT INTO tmp SELECT * FROM bookmarks ORDER BY annonce_id DE
ing is not using the normal catalog methods
for storing information about temp tables, but hacking that together
would probably be a rather large task.
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.comwork: 512-231-6117
vcard: http
Index Cond: (ticketing_codes.code_id =
> "outer".code_id)
Anyone have any idea why on earth it's doing that instead of a hash or
merge join?
In any case, try swapping the order of ticketing_codes_played and
ticketing_codes. Actually, that'd probably make it worse.
Try SET ena
ribers are far worse.
Does majordomo have an option to automagically handle such posts that
are sent to the post address instead of the admin address? I know
mailman can do that...
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.comw
reply
to it. From that standpoint, it's substantially easier and simpler than
unsubscribing is...
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervas
On Wed, May 10, 2006 at 08:31:54PM -0400, Tom Lane wrote:
> "Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> > On Tue, May 09, 2006 at 03:13:01PM -0400, Tom Lane wrote:
> >> PFC <[EMAIL PROTECTED]> writes:
> >>> Fun thing is, the rowcount from a temp
On Thu, May 11, 2006 at 08:03:19PM +0200, Martijn van Oosterhout wrote:
> On Thu, May 11, 2006 at 12:18:06PM -0500, Jim C. Nasby wrote:
> > > Yes, because there can be more than one active snapshot within a single
> > > transaction (think about volatile functions in part
OBAL TEMPORARY TABLE ...
I always found it a bit odd, since it always seemed to me like a global
temporary table would be one that every backend could read... something
akin to a real table that doesn't worry about fsync or any of that (and
is potentially not backed on disk at all).
--
Jim C
task.
>
> But the timings suggest, that it cannot be the catalogs in the worst
> case
> he showed.
>
> > 0.101 ms BEGIN
> > 1.451 ms CREATE TEMPORARY TABLE tmp ( a INTEGER NOT NULL, b INTEGER
> NOT
> > NULL, c TIMESTAMP NOT NULL, d INTEGER NOT NULL ) ON COMM
end if;
>
> inRow.game_rating := inGameRating;
> inGameResult := inRow.game_result;
> return next inRow;
>
> end loop;
> return;
> end;
> $$;
> --
> Witold Strzelczyk
> [EMAIL PROTECTED]
>
> ---(end of
On Fri, May 05, 2006 at 03:47:53PM -0700, Adam Palmblad wrote:
> Hi,
> We've got a C function that we use here and we find that for every
> connection, the first run of the function is much slower than any
> subsequent runs. ( 50ms compared to 8ms)
>
> Besides using connec
ache_size.
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
---(end of broadcast)---
TIP 5: don&#x
more expensive options.
>
> You want an in-depth comparison of how a server disk drive is internally
> better than a desktop drive:
>
>
> http://www.seagate.com/content/docs/pdf/whitepaper/D2c_More_than_Interface_ATA_vs_SCSI_042003.pdf
BTW, someone (Western Digital?) is now offering S
a valid concern, since the backup will
be nowhere near as up-to-date as the database was unless you have a
pretty low DML rate.
> BUT a hardware controler is about EUR2000 and a (ATA/SATA) 500GB HD
> is ~ EUR350.
Huh? You can get 3ware controllers for about $500, and they're pretty
dec
to read
http://www.pervasivepostgres.com/instantkb13/article.aspx?id=10120&query=explain
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
--
e know.
Note that it's not terribly uncommon for the default stats target to be
woefully inadequate for large sets of data, not that 100 rows a day is
large. But it probably wouldn't hurt to bump the defaulst stats target
up to 30 or 50 anyway.
--
Jim C. Nasby, Sr. Engineering Consul
On Thu, May 11, 2006 at 06:08:36PM -0400, Tom Lane wrote:
> "Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> > I'd hope that wasn't what's happening... is the backend smart enough to
> > know not to fsync anything involved with the temp table?
>
get
'server reliability' in some of their SATA drives, but maybe now
everyone's starting to do it. I suspect the premium you can charge for
it offsets the costs, provided that you switch all your production over
rather than trying to segregate production lines.
--
Jim C. Nasby, Sr. Eng
n if it did, if the OS was
catching them I'd hope it would pop up a warning or something. But from
what I've heard, some drives now-a-days will silently remap dead sectors
without telling the OS anything, which is great until you've used up all
of the spare sectors and there's nowh
cache
query plans, PostgreSQL prefers not to spend cycles on more esoteric
cases so that query planning is fast.
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell
the the timings of firebird and
> postgresql.
> The weird problem are the 2 queries that firebird executes in less than 2
> seconds and postgresql took almost half hour to complete at 100% cpu.
How about posting EXPLAIN ANALYZE for those two queries, as well as the
queries themselves?
--
Ji
basis would probably
eliminate a lot of those (It wouldn't work for any executor node that
has to read it's entire input before returning anything, though, such as
sort).
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.comwor
directly from
different partition tables, which probably be even faster than using a
single table. The downside is it's more work to setup.
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.comwork: 512-231-6117
vcard: http://jim.nas
nner's strategy at all. We were surprised by this.
Is it really not very selective? If there's 1000 rows in the table,
and id starts at 1 with very few gaps, then >= 1000 should actually
be very selective...
Also, I hope you understand there's a big difference betwe
t have any need to ever
VACUUM FULL...
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
---(end of broadcast)--
On Thu, May 18, 2006 at 02:44:40PM +0200, Chris Mair wrote:
> Yes, pg_xlog ist also used with fsync=off. you might gain quite some
> performance if you can manage to put pg_xlog on its own disk (just
> symlink the directory).
Substantially increasing wal buffers might help too.
--
Jim
not; if
it's not, you'll see big spikes every time there's a checkpoint.
> A question for you: after setting up your test database, did you launch
> a vacuum full analyze of it ?
Why would you vacuum a newly loaded database that has no dead tuples?
--
Jim C. Nasby, Sr. Engin
. In particular, "Is PostgreSQL
remembering what I vacuumed" has some critical information about
managing the free space map.
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/perv
e index scan, results
> immediately come back through the cursor method (which is more desirable).
> Thoughts?
Do you really need to use a cursor? It's generally less efficient than
doing things with a single SQL statement, depending on what exactly
you're doing.
-
ance that says diferent at high concurancy?
Best bet is to try it and see. Generally, people find HT hurts, but I
recently saw it double the performance of pgbench on a windows XP
machine, so it's possible that windows is just more clever about how to
use it than linux is.
--
Jim C.
On Fri, May 19, 2006 at 07:37:45PM -0700, Kenji Morishige wrote:
> Where can I find any documentation to partition the tablespace disk files onto
> different physical arrays for improved performance?
Other than CREATE TABLESPACE??
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PRO
has 8GB of RAM and I have
> effective_cache_size set to 2/3 of that.
That's rather low for that much memory; I'd set it to 7GB.
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.comwork: 512-231-6117
vca
otal runtime: 1676.727 ms
>
>
> the tables involved with the query have all been vacuum analyzed. I
> also have default_statistics_target = 100.
>
> There's something definitely wrong with that Nested Loop with the
> high row count. That row count appears to be close to th
xecuted
(though you can see how many tuples were inserted/updated/deleted), or
how many transactions have occured (well, you can hack the last one, but
it's a bit of a mess).
It would be nice if all of this was available.
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Perv
they're building web pages that make 50 queries to the database.
Being able to identify that and determine how many were selects vs. DML
would be useful.
Bonus points if there are seperate counters for statements from
functions.
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTE
ults. It's also
possible that this is fixed be a recent patch to HEAD that reduces the
amount of traffic on the index metapage, something gprof would probably
confirm.
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.comwork: 512-2
hat doesn't surprise me...
In any case, if we at least provide a raw counter, it's not that hard to
turn that into selects per second over some period of time.
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.comwork:
qcfat
> left join arqfagr on fagrorig = 'CFAT' and fagrdocu = cfatdocu and fagrempe
> = cfatempe and fagrseri = cfatseri
> where cfatdata between '2006-01-01' and '2006-01-31'
> and cfattipo = 'VD'
> and cfatstat <> 'C'
>
ommended' way to merge data into
> the DB, i.e.
>
> http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING
>
> however I did it with a trigger on insert, i.e. (not my schema :) ):
--
Jim C. Nasby, Sr. Engineering Consultant [EM
to commit Postgres won't actually detect a deadlock, it'll
> just sit waiting until the lock becomes available.
Wow, are you sure that's how it works? I would think it would be able to
detect deadlocks as soon as both processes are waiting on each other's
locks.
--
Ji
f
possible. If not, the FSM is consulted. Appending to the end of the
table is a last resort.
Update is more effecient than delete/insert. First, it's one less
statement to parse and plan. Second, AFAIK insert always goes to the
FSM; it has no way to know you're replacing the row(s) yo
EXT: SQL statement "SELECT 1 FROM
> ONLY "public"."t1" x WHERE "a" = $1 FOR SHARE OF x"
> stark=> >
>
> COMMIT
I tried duplicating thi
HT doesn't give you anything close to having 2 CPUs,
so for all but the most trivial and limited cases it's not going to be a
win.
Incidentally, the only good results I've seen with HT are on windows.
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive S
ate' (a lot of updates) but are
supposed to be small? In cases like that autovacuum may not be enough.
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 5
ll almost certainly lose some of your data.
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
---(end of
501 - 600 of 819 matches
Mail list logo
