speedup
constantly utilizing about 120% CPU (Remember we have a 2-way machine).
I think that there are some internal postgres locks that prohibit
further concurrency for inserts in the same table.
Thanks for any advice!
Hope, that helps,
Markus Schaber
--
markus schaber | dipl. informatiker
machine. We did some small benches on the
sync() / fsync() calls that seem to prove this conclusion.)
--
markus schaber | dipl. informatiker
logi-track ag | rennweg 14-16 | ch 8001 zürich
phone +41-43-888 62 52 | fax +41-43-888 62 53
mailto:[EMAIL PROTECTED] | www.logi-track.com
be automatically converted into hints how to
tweak the config file.
Markus
--
markus schaber | dipl. informatiker
logi-track ag | rennweg 14-16 | ch 8001 zürich
phone +41-43-888 62 52 | fax +41-43-888 62 53
mailto:[EMAIL PROTECTED] | www.logi-track.com
---(end of broadcast
:
'SELECT * FROM t WHERE a=a1 AND b=b1 AND c=c1 ORDER BY a,b,c ASC
LIMIT 1 OFFSET 1'
HTH,
Markus
--
markus schaber | dipl. informatiker
logi-track ag | rennweg 14-16 | ch 8001 zürich
phone +41-43-888 62 52 | fax +41-43-888 62 53
mailto:[EMAIL PROTECTED] | www.logi-track.com
* FROM t WHERE a=a1 AND b=b1 AND c=c1 ORDER BY a,b,c ASC LIMIT 1'
HTH,
Markus
--
markus schaber | dipl. informatiker
logi-track ag | rennweg 14-16 | ch 8001 zürich
phone +41-43-888 62 52 | fax +41-43-888 62 53
mailto:[EMAIL PROTECTED] | www.logi-track.com
---(end
enough selectivity, the planner should
be able to select the correct index. Maybe increasing the number of
random samples for the rows is useful.
HTH,
Markus
--
markus schaber | dipl. informatiker
logi-track ag | rennweg 14-16 | ch 8001 zürich
phone +41-43-888 62 52 | fax +41-43-888 62 53
mailto
,
Markus
--
markus schaber | dipl. informatiker
logi-track ag | rennweg 14-16 | ch 8001 zürich
phone +41-43-888 62 52 | fax +41-43-888 62 53
mailto:[EMAIL PROTECTED] | www.logi-track.com
---(end of broadcast)---
TIP 2: you can get off all lists at once
Hi, Greg,
On 02 Sep 2004 15:33:38 -0400
Greg Stark [EMAIL PROTECTED] wrote:
Markus Schaber [EMAIL PROTECTED] writes:
logigis=# explain select count(id) from (select ref_in_id as id from streets
union select nref_in_id as id from streets) as blubb
?
Thanks,
Markus Schaber
--
markus schaber | dipl. informatiker
logi-track ag | rennweg 14-16 | ch 8001 zürich
phone +41-43-888 62 52 | fax +41-43-888 62 53
mailto:[EMAIL PROTECTED] | www.logi-track.com
---(end of broadcast)---
TIP 4: Don't 'kill -9
.
But beware, that you have to re-CLUSTER after modifications.
HTH,
Markus
--
markus schaber | dipl. informatiker
logi-track ag | rennweg 14-16 | ch 8001 zürich
phone +41-43-888 62 52 | fax +41-43-888 62 53
mailto:[EMAIL PROTECTED] | www.logi-track.com
---(end of broadcast
constraints restricting the time after adding
the partition?
Thanks,
Markus
--
markus schaber | dipl. informatiker
logi-track ag | rennweg 14-16 | ch 8001 zürich
phone +41-43-888 62 52 | fax +41-43-888 62 53
mailto:[EMAIL PROTECTED] | www.logi-track.com
---(end of broadcast
--
markus schaber | dipl. informatiker
logi-track ag | rennweg 14-16 | ch 8001 zürich
phone +41-43-888 62 52 | fax +41-43-888 62 53
mailto:[EMAIL PROTECTED] | www.logi-track.com
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Hi, Steve,
On Wed, 15 Sep 2004 21:17:03 -0700
Steve Atkins [EMAIL PROTECTED] wrote:
On Wed, Sep 15, 2004 at 11:16:44AM +0200, Markus Schaber wrote:
But you have to add table constraints restricting the time after adding
the partition?
Uhm... unless I'm confused that's not a meaningful
and close may be the
reason for the slowdown. Can you use connection pooling in your service?
HTH,
Markus
--
markus schaber | dipl. informatiker
logi-track ag | rennweg 14-16 | ch 8001 zürich
phone +41-43-888 62 52 | fax +41-43-888 62 53
mailto:[EMAIL PROTECTED] | www.logi-track.com
the old way
everywhere else.
Maybe Reiser4 is a step into the right way, and maybe even a postgres
plugin for Reiser4 will be worth the effort. Maybe XFS/JFS etc. already
have such capabilities. Maybe that's completely wrong.
cheers,
Markus
--
markus schaber | dipl. informatiker
logi-track ag
sort_mem higher makes it
swap, setting it lower makes it thrashing disk directly).
Is Postgresql 8 more intelligend in this case?
Thanks for your hints,
Markus
--
markus schaber | dipl. informatiker
logi-track ag | rennweg 14-16 | ch 8001 zürich
phone +41-43-888 62 52 | fax +41-43-888 62 53
mailto
as your index, and the planner will get it.
Thanks, that seems to help.
Seems weird to order by a column that is all the same value, but well,
why not :-)
Thanks a lot,
Markus
--
markus schaber | dipl. informatiker
logi-track ag | rennweg 14-16 | ch 8001 zürich
phone +41-43-888 62 52 | fax +41-43
Hi, Christopher,
[sorry for the delay of my answer, we were rather busy last weks]
On Thu, 04 Nov 2004 21:29:04 -0500
Christopher Browne [EMAIL PROTECTED] wrote:
In an attempt to throw the authorities off his trail, [EMAIL PROTECTED]
(Markus Schaber) transmitted:
We should create a list
not exist?
Markus
--
markus schaber | dipl. informatiker
logi-track ag | rennweg 14-16 | ch 8001 zürich
phone +41-43-888 62 52 | fax +41-43-888 62 53
mailto:[EMAIL PROTECTED] | www.logi-track.com
signature.asc
Description: OpenPGP digital signature
about it?
Markus
--
markus schaber | dipl. informatiker
logi-track ag | rennweg 14-16 | ch 8001 zürich
phone +41-43-888 62 52 | fax +41-43-888 62 53
mailto:[EMAIL PROTECTED] | www.logi-track.com
---(end of broadcast)---
TIP 4: Don't 'kill -9
.
Markus
--
markus schaber | dipl. informatiker
logi-track ag | rennweg 14-16 | ch 8001 zürich
phone +41-43-888 62 52 | fax +41-43-888 62 53
mailto:[EMAIL PROTECTED] | www.logi-track.com
signature.asc
Description: OpenPGP digital signature
Hi, all,
Markus Schaber schrieb:
As a small workaround, I could imagine to add a small additional column
in the table that contains the geometry's bbox, and which I use the
operator against. This should avoid touching the TOAST for the skipped rows.
For your personal amusement: I just
Hi, Tom,
Tom Lane schrieb:
Markus Schaber [EMAIL PROTECTED] writes:
[Query optimizer misestimation using lossy GIST on TOASTed columns]
What I would be inclined to do is to extend ANALYZE to make an estimate
of the extent of toasting of every toastable column, and then modify
cost_qual_eval
;
QUERY PLAN
---
Seq Scan on adminbndy1 (cost=0.00..3.83 rows=83 width=89) (actual
time=0.089..0.499 rows=83 loops=1)
Total runtime: 0.820 ms
(2 Zeilen)
Markus
--
markus
to be finished.
You can discuss this issue on psql-jdbc list or search the archives if
you need more info.
Markus
--
markus schaber | dipl. informatiker
logi-track ag | rennweg 14-16 | ch 8001 zrich
phone +41-43-888 62 52 | fax +41-43-888 62 53
mailto:[EMAIL PROTECTED] | www.logi-track.com
on
different drives. However, separating out single tables or indices
involves some black symlink magic. See google and
http://www.postgresql.org/docs/7.4/interactive/manage-ag-alternate-locs.html
HTH,
Markus
--
markus schaber | dipl. informatiker
logi-track ag | rennweg 14-16 | ch 8001 zürich
phone +41-43
. But they need loads
of storage (because of very redundant data storage), and I don't know
any open source or cheap software.
Markus
--
markus schaber | dipl. informatiker
logi-track ag | rennweg 14-16 | ch 8001 zürich
phone +41-43-888 62 52 | fax +41-43-888 62 53
mailto:[EMAIL PROTECTED] | www.logi-track.com
or cheaper using any other DBMS, neither commercial nor open source. For
all of them, you'll need big iron hardware, and a skilled team of
admins to set up and maintain the database.
Markus
--
markus schaber | dipl. informatiker
logi-track ag | rennweg 14-16 | ch 8001 zürich
phone +41-43-888 62 52 | fax
Hi, Jeremiah,
Jeremiah Jahn schrieb:
yes, it does. I forgot to mention, that I also have clustering on that
table by my name_field index. My Bad.
Fine. Did you run ANALYZE and CLUSTER on the table after every large
bunch of insertions / updates?
Markus
--
Markus Schaber | Dipl
the returned total would not always match the number of results on the
second query.
Did you run both queries in the same transaction, with transaction
isolation level set to serializable? If yes, you found a serious bug in
PostgreSQL transaction engine.
Markus
--
Markus Schaber | Dipl. Informatiker
Hi, Josh,
Josh Berkus wrote:
Yes, actually. We need 3 different estimation methods:
1 for tables where we can sample a large % of pages (say, = 0.1)
1 for tables where we sample a small % of pages but are easily estimated
1 for tables which are not easily estimated by we can't afford to
Hi, all,
David Parker wrote:
We ran into the need to use COPY, but our application is also in Java.
We wrote a JNI bridge to a C++ routine that uses the libpq library to do
the COPY. The coding is a little bit weird, but not too complicated -
the biggest pain in the neck is probably getting
performance-wise, if it does make any
difference at all:
Having autocommit on or off? (I presume off)
Using commit or rollback?
Committing / rolling back occasionally (e. G. when returning the
connection to the pool) or not at all (until the pool closes the
connection)?
Thanks,
Markus
--
Markus
:
Having ACID-Level SERIALIZABLE or READ COMMITED?
Markus
--
Markus Schaber | Logical TrackingTracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
---(end of broadcast
each bunch of work).
Thanks,
Markus
--
Markus Schaber | Logical TrackingTracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
---(end of broadcast)---
TIP 6
.
Markus
--
Markus Schaber | Logical TrackingTracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
---(end of broadcast)---
TIP 6: explain analyze is your
and possibly a few others.
The reason is that the postmaster assumes some internal data structure
corruption in the shared memory pages is possible on an unclean
backend abort, and thus quits immediately to minimize the possibility of
those corruptions to propagate to the disks.
HTH,
Markus
--
Markus
.
HTH,
Markus
--
Markus Schaber | Logical TrackingTracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
---(end of broadcast)---
TIP 2: Don't 'kill -9
and accessible?
You're not doing regular vacuums often enough.
It may also help to increase the max_fsm_pages setting, so postmaster
has more memory to remember freed pages between VACUUMs.
HTH,
Markus
--
Markus Schaber | Logical TrackingTracing International AG
Dipl. Inf. | Software
. Also, I believe
that newer PostgreSQL versions allow VACUUM to truncate files when free
pages happen to appear at the very end of the file.)
HTH,
Markus
--
Markus Schaber | Logical TrackingTracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in EU
get bigger or there are more
files, but this effect should not really be noticeable here.
HTH,
Markus
--
Markus Schaber | Logical TrackingTracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
interface are you using to connect to PostgreSQL? libpq, libpqxx,
pgjdbc, python-popy?
E. G. PGJDBC handles prepared plans transparently by using the
PreparedStatement class.
If you use command line PSQL, you can use the PREPARE commands.
Markus
--
Markus Schaber | Logical TrackingTracing
to call the stored procedure (plpgsql).
So your statements are inside a plpgsql stored procedure, important to
know that.
AFAIK, plpgsql uses prepared statements internally, so it should not be
necessary to use them explicitly.
Markus
--
Markus Schaber | Logical TrackingTracing International AG
using a connection pool (most web application
servers provide pooling facilities) or some other means to keep the
connection between several http requests.
Worried,
Markus
--
Markus Schaber | Logical TrackingTracing International AG
Dipl. Inf. | Software Development GIS
Fight against
.
Or the query is misoptimized (low work_mem, missing indices) and cause
much more I/O than necessary.
HTH,
Markus
--
Markus Schaber | Logical TrackingTracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
finish
before it's time for #3.
You could also use the LOCK command on an empty lock table at the
beginning of each #1 or #3 transaction to prevent #3 from getting the
view lock before #1 is finished.
HTH,
Markus
--
Markus Schaber | Logical TrackingTracing International AG
Dipl. Inf. | Software
, but although I'm not a postgresql
core developer, I am keen enough to invite you to send patches. :-)
Markus
--
Markus Schaber | Logical TrackingTracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
be necessary to block e. G. simultaneous VACUUMs,
CLUSTERs or other maintainance commands for the whole VACUUM run, but
normal SELECT, INSERT and UPDATE statement should be able to interleave
with the VACUUM transaction.
Markus
--
Markus Schaber | Logical TrackingTracing International AG
Dipl. Inf
will not be useful if you merge the tables.
Btw, I don't know whether PostgreSQL can make use of partial indices
when building other partial indices. If yes, you could temporarily drop
all but one of the partial indices for a specific client.
HTH,
Markus
--
Markus Schaber | Logical TrackingTracing
Unstable but I intend to be running RHEL 4.0
if and when I do this refactoring )
This should not make too much difference.
HTH,
Markus
--
Markus Schaber | Logical TrackingTracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in EU! www.ffii.org
server, and will be upgrading to
pg8.0 on a new server, but have some migration issues (that’s for
another list!)
Ignore 8.0 and go to 8.1 directly.
HTH,
Markus
--
Markus Schaber | Logical TrackingTracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents
may be even
better.
If most of your data is different in the first 8 bytes, it may also make
sense to duplicate them into a bigint, and create the bigint on them.
Then you can use AND in your query to test for the 8 bytes (uses index)
and the bytea. Ugly, but may work.
HTH,
Markus
--
Markus
app should see the change inside their transaction.
HTH,
Markus
--
Markus Schaber | Logical TrackingTracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
---(end of broadcast
by changing postgresql configuration
file.
But which parameters i need to manipulate and with which values ?
Increase the size of the wal.
If its just a develpoment environment, or you don't mind data
inconsistency in case of a crash, disable fsync.
HTH,
Markus
--
Markus Schaber | Logical
pointers (names, URLs,
papers) to such algorithms?
Thanks a lot,
Markus
--
Markus Schaber | Logical TrackingTracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
---(end
Hi, Mark,
Mark Lewis schrieb:
It seems that instead of maintaining a different sorting code path for
each data type, you could get away with one generic path and one
(hopefully faster) path if you allowed data types to optionally support
a 'sortKey' interface by providing a function f which
Hi, PFC,
PFC schrieb:
By the way, I'd like to declare my zipcode columns as SQL_ASCII
while the rest of my database is in UNICODE, so they are faster to
index and sort. Come on, MySQL does it...
Another use case for parametric column definitions - charset definitions
- and the first
Hi, David,
David Lang schrieb:
In SQL_ASCII, just take the first 4 characters (or 8, if using a 64-bit
sortKey as elsewhere suggested). The sorting key doesn't need to be a
one-to-one mapping.
that would violate your second contraint ( f(a)==f(b) iff (a==b) )
no, it doesn't.
When both
Hi, Ron,
Ron schrieb:
OK, so here's _a_ way (there are others) to obtain a mapping such that
if a b then f(a) f (b) and
if a == b then f(a) == f(b)
Pretend each row is a integer of row size (so a 2KB row becomes a 16Kb
integer; a 4KB row becomes a 32Kb integer; etc)
Since even a 1TB
, compiled by GCC gcc (GCC) 3.3.3
(SuSE Linux)
Btw, you should update to 7.4.12, there are importand bug fixes and it
is upgradable in place, without dumping and reloading the database.
HTH,
Markus
--
Markus Schaber | Logical TrackingTracing International AG
Dipl. Inf. | Software
if FSM is not high enough, maybe you can
find useful hints in the log file.
HTH
Markus
--
Markus Schaber | Logical TrackingTracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
---(end
Hi, George,
george young wrote:
Looks like a hash join might be faster. What is your work_mem set to?
work_mem= 1024
This is 1 Megabyte. By all means, increase it, if possible.
HTH,
Markus
--
Markus Schaber | Logical TrackingTracing International AG
Dipl. Inf. | Software Development
INTO original_table (col1, col2, col3) SELECT col1, col2, col3
FROM temp_table WHERE ...
HTH,
Markus
--
Markus Schaber | Logical TrackingTracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
, every transaction sync()s through to the disk.
Markus
--
Markus Schaber | Logical TrackingTracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
---(end of broadcast
content, or
http://www.tntnet.org/
Markus
--
Markus Schaber | Logical TrackingTracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
---(end of broadcast)---
TIP
index on
activity_id where state in (1,10001).
Btw, PostgreSQL 8.1 could AND two bitmap index scans on the activity and
state indices, and get the result faster (i presume).
Markus
--
Markus Schaber | Logical TrackingTracing International AG
Dipl. Inf. | Software Development GIS
Fight
floating point maths, it will be very slow. All
8 CPUs / 32 Threads share a single FPU. So if you need floating point
(e. G. Mapserver, PostGIS geoprocessing, Java2D chart drawing or
something), T2000 is not the right thing for you.
HTH,
Markus
--
Markus Schaber | Logical TrackingTracing
, to allow CLUSTERing on mixed search order.
[2] But I admit that I currently don't have enough knowledge in
PostgreSQL index scan internals to know whether it really is easy to
implement.
--
Markus Schaber | Logical TrackingTracing International AG
Dipl. Inf. | Software Development GIS
Fight
work_mem (for the bitmap scans) and
prepared statements will pay off.
HTH,
Markus
--
Markus Schaber | Logical TrackingTracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
---(end
columns of
character(10), if only because it is approx. 1/25th in size.
HTH,
Markus
--
Markus Schaber | Logical TrackingTracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
---(end
the locality of search results (and so reducing disk I/O). In case your
table has low write activity, but high read-only activity, the overhead
that comes with the additional index is neglible compared to the
performance improvement proper CLUSTERing can generate.
Markus
--
Markus Schaber | Logical
for them.
Markus
--
Markus Schaber | Logical TrackingTracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
---(end of broadcast)---
TIP 5: don't forget to increase
finished a commit
can be shure their data is on the platters.[1]
HTH,
Markus
[1] As long as the platters don't lie, but that's another subject.
--
Markus Schaber | Logical TrackingTracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in EU
. :-)
Hmm, how does effective_cach_size correspond with it? Shouldn't a high
effective_cache_size have a similar effect?
Thanks,
Markus
--
Markus Schaber | Logical TrackingTracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in EU! www.ffii.org
delays.
Well, if you read the documentation, you will see that it will only wait
if there are at least commit_siblings other transactions active. So when
Bacula serializes access, there will be no delays, as there is only a
single transaction alive.
HTH
Markus
--
Markus Schaber | Logical
, but
mysql-lowest-common-factors, which means myisam etc)
Well, most of those projects don't need a database, they need a bunch of
tables and a lock.
Heck, they even use client-side SELECT-loops in PHP instead of a JOIN
because I always confuse left and right.
Markus
--
Markus Schaber | Logical
for now.
Are they capable to index custom datatypes like the PostGIS geometries
that use the GIST mechanism? This could probably speed up our Geo
Databases for Map rendering, containing static data that is updated
approx. 2 times per year.
Markus
--
Markus Schaber | Logical TrackingTracing
)?
Thanks,
Markus
--
Markus Schaber | Logical TrackingTracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
---(end of broadcast)---
TIP 4: Have you searched
to access a
not-completely-restored wal file.
HTH,
Markus
--
Markus Schaber | Logical TrackingTracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
---(end of broadcast
with commit_delay and
commit_siblins may improve your situation.
HTH,
Markus
--
Markus Schaber | Logical TrackingTracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
---(end of broadcast
planning to use the array flattening approach, but are
there any plans to enhance the query planner for the direct ARRAY approach?
Thanks,
Markus
--
Markus Schaber | Logical TrackingTracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in EU! www.ffii.org
, the free_space_map setting has to be high enough, it has
to cover enough space to put in all pages that get dead rows between two
vacuum runs.
HTH,
Markus
--
Markus Schaber | Logical TrackingTracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in EU
.
HTH,
Markus
--
Markus Schaber | Logical TrackingTracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
---(end of broadcast)---
TIP 1: if posting/reading
functions have an implicit return parameter of int8. Parameters may be
NULL when they are not known at query planning time.
What do you think about this idea?
The same scheme could be used to add a CPUCOST_ESTIMATOR to expensive
functions.
HTH,
Markus
--
Markus Schaber | Logical TrackingTracing
, and an older linux cryptoloop implementations,
IIRC).
If you're interested, I can dig for the C source...
HTH,
Markus
--
Markus Schaber | Logical TrackingTracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in EU! www.ffii.org
can be captured by the other script, but not by my tool.
HTH,
Markus
--
Markus Schaber | Logical TrackingTracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
---(end of broadcast
Hi, Bruce,
Markus Schaber wrote:
It does not find as much liers as the script above, but it is less
Why does it find fewer liers?
It won't find liers that have a small lie-queue-length so their
internal buffers get full so they have to block. After a small burst at
start which usually hides
return ($2-$1)/$3, and for some functions
even constant estimates will be good enough.
- please execute me and store my results in a temporary storage,
count the rows returned, and plan the outer query accordingly
That's an interesting idea.
Markus
--
Markus Schaber | Logical TrackingTracing
solution would be to cache the
result of the estimator function.
Sophisticated estimator functions are free to use the pg_statistics
views for their row count estimation.
HTH,
Markus
--
Markus Schaber | Logical TrackingTracing International AG
Dipl. Inf. | Software Development GIS
Fight against
,
and then give them at least 30 megs of RAM each.
This should also cut down the connection creation overhead.
HTH,
Markus
--
Markus Schaber | Logical TrackingTracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in EU! www.ffii.org
DB and will lasts only for a
month.
I guess it is a simple table with a single PK (some subscription numer)
- no joins or other things.
For this cases, a special non-RDBMS like MySQL, SQLite, or even some
hancrafted thingy may give you better results.
Markus
--
Markus Schaber | Logical
want to have one postgresql backend per apache
frontend.
Did you try running pgpool on the Apache machine, and have only a few
(hundred) connections to the backend?
Maybe http://en.wikipedia.org/wiki/Memcached could be helpful, too.
Markus
--
Markus Schaber | Logical TrackingTracing
of the cases when we had database bloat despite running autovacuum,
it was due to a low free_space_map setting.
Markus
--
Markus Schaber | Logical TrackingTracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
the WAL size /
checkpoint segments.
When most of the restore time was spent in index creation, increase the
sort mem / maintainance work mem settings.
HTH,
Markus
--
Markus Schaber | Logical TrackingTracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents
Hi, Tim,
Seems I sent my message to fast, cut in middle of a sencence:
Markus Schaber wrote:
A pg_dump/pg_restore cycle reduced the total
database size from 81G to 36G.
If you still have the original database around,
... can you check wether VACUUM FULL and REINDEX achieve the same effect
...
Some older JDBC driver versions had the bug that they always had an open
transaction, thus an application server having some pooled connections
lingering around could block vacuum forever.
Markus
--
Markus Schaber | Logical TrackingTracing International AG
Dipl. Inf. | Software Development GIS
for
log messages telling to increase it?
HTH,
Markus
--
Markus Schaber | Logical TrackingTracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
---(end of broadcast
listing all schemas (or even
all databases in the same user), regardless whether he/she has access
rights.
But it is not always acceptable that a customer knows which other
customers one has.
This forces the use of the one cluster per customer paradigm.
Thanks,
Markus
--
Markus Schaber | Logical
customer
paradigm quite successfully.
My $0.02 (not worth what it was)
Oh, I think the're at least $0.03 cents worth. :-)
Markus
--
Markus Schaber | Logical TrackingTracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in EU! www.ffii.org
,
Markus
--
Markus Schaber | Logical TrackingTracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
1 - 100 of 160 matches
Mail list logo