buffers
196608
(1 row)
db=# select version();
version
PostgreSQL 8.0.3 on x86_64-pc-linux-gnu, compiled by GCC cc (GCC) 3.3.6
(Debian 1:3.3.6-7)
(1 row)
--
Hannu
< 1/2 of MySQL with WAL on different spindle and/or WAL
disabled ?
--
Hannu Krosing <[EMAIL PROTECTED]>
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
oblem with things that are "extremely remote" possibilities are
> > that they tend to be less remote than we expect ;)
>
> ... and they know just when they can happen despite all the odds to
> maximise the pain and chaos caused.
>
> --
> Craig Ringer
>
--
Ha
ned would be degraded by interleaving index builds, compared with
> doing them in succession.
I guess that tweaking file systems to allocate in bigger chunks help
here ? I know that xfs can be tuned in that regard, but how about other
common file systems like ext3 ?
-
Hannu Krosing http://w
On Thu, 2010-03-18 at 16:12 -0400, Justin Pitts wrote:
> It seems to me that a separate partition / tablespace would be a much simpler
> approach.
Do you mean a separate partition/ tablespace for _each_ index built
concurrently ?
> On Mar 17, 2010, at 5:18 PM, Hannu Krosing wrote:
>
postgreSQL database, unless it was
using disks which lie about write caching.
Now need for WAL replica for that
> regards,
> Yeb Havinga
>
--
Hannu Krosing http://www.2ndQuadrant.com
PostgreSQL Scalability and Availability
Services, Consulting and Training
--
Sent via pgsql-
of a SELECT query as well.
--
Hannu Krosing http://www.2ndQuadrant.com
PostgreSQL Scalability and Availability
Services, Consulting and Training
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.o
une to avoid excessive checkpointing.
> Hope i could get the information from the other Thread in other
> catagory.
Nah, actually [PERFORM] is the right place to ask.
Just most people got the impression that you may be doing unnecessary
REINDEXing, and the best way to speed up unneeded t
REATE INDEX tgrm_deleted_recipent_index ON telegrams(recipient_id)
WHERE recipient_deleted=FALSE;
CREATE INDEX tgrm_deleted_user_index ON telegrams(user_id)
WHERE user_deleted=FALSE;
(if on live system, use "CREATE INDEX CONCURRENTLY ...")
--
Hannu Krosing http://www.2ndQuadrant
ve form of plan rewrite, nor
that it would make much sense to do so unless there was a really small
number of rows where x_.company_id>5000
--
Hannu Krosing http://www.2ndQuadrant.com
PostgreSQL Scalability and Availability
Services, Consulting and Training
--
Sent via pgsql-p
/ resource limit features would be great to have in
> core, and can't really be done fully in external modules ... but could
> be designed in ways that would allow external poolers to add
> functionality on top. Josh Berkus has made some good points on why this
> isn't as easy
self is running.
For example pgbouncer had to add option to use incoming unix sockets,
because they run into the IP socket port number limit (a little above
31k, or more exactly 63k/2.
And unix sockets can be used only on local host .
--
Hannu Krosing http://www.2ndQuadrant.com
PostgreSQL Scal
On Wed, 2010-07-14 at 08:58 -0500, Kevin Grittner wrote:
> Scott Marlowe wrote:
> > Hannu Krosing wrote:
> >> One example where you need a separate connection pool is pooling
> >> really large number of connections, which you may want to do on
> >> anot
On Sun, 2010-07-18 at 21:48 +0530, Rajesh Kumar Mallah wrote:
> Hi,
>
> Sorry, if posting here was not proper instead of starting new thread
> (I am really not sure if its bad thing to do)
>
> I would like to share my recent experience on implementation of
> client side pooling using pgbouncer
On Thu, 2010-07-22 at 14:36 -0400, Robert Haas wrote:
> On Mon, Jul 12, 2010 at 6:58 AM, Craig Ringer
> wrote:
> > So rather than asking "should core have a connection pool" perhaps
> > what's needed is to ask "what can an in-core pool do that an external
> > pool cannot do?"
>
> Avoid sending ev
Contributor
> Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
> Consulting, Training, Support, Custom Development, Engineering
> http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt
>
>
>
--
Hannu Krosing http://www.2ndQuadrant.com
Post
On Thu, 2010-07-22 at 20:57 -0400, Robert Haas wrote:
> On Thu, Jul 22, 2010 at 3:15 PM, Hannu Krosing wrote:
> > On Thu, 2010-07-22 at 14:36 -0400, Robert Haas wrote:
> >> On Mon, Jul 12, 2010 at 6:58 AM, Craig Ringer
> >> wrote:
> >> > So rather than ask
On Fri, 2010-07-23 at 09:52 -0700, Joshua D. Drake wrote:
> On Thu, 2010-07-22 at 20:56 +0100, Hannu Krosing wrote:
> >
> > > Let's extend this shall we:
> > >
> > > Avoid adding yet another network hop
> >
> > postgreSQL is multi-process,
.d)(proxy_client_post:n)(something
else))(run_query:query)" in one packet (for performance) and have these
things be available in logging and pg_stat_activity
I see no need to try to somehow restrict these if you can always be sure
that they are set by the direct client. proxy can dec
reg Smith 2ndQuadrant US Baltimore, MD
> PostgreSQL Training, Services and Support
> g...@2ndquadrant.com www.2ndQuadrant.us
>
>
--
Hannu Krosing http://www.2ndQuadrant.com
PostgreSQL Scalability and Availability
Services, Consulting and Training
--
Sent via pgsql-performance m
on tool:
Are you sure that you are not writing full WAL pages ?
Do you have any stats on how much WAL is written for 8kb and 4kb test
cases ?
And for other disk i/o during the tests ?
--
Hannu Krosing http://www.2ndQuadrant.com
PostgreSQL Scalability and Availability
Services, Consu
'm looking for a configuration solution that I can set on a
> database-wide basis and have it work well for all queries.
Keep trying. The close you get with your conf to real conditions, the
better choices the optimiser can make ;)
--
Hannu Krosing http://www.2ndQuadrant.com
Postgr
On Wed, 2010-08-04 at 09:14 -0400, Robert Haas wrote:
> On Tue, Aug 3, 2010 at 3:03 AM, Hannu Krosing wrote:
> > In case of fully cached database it is closer to 1.
>
> In the case of a fully cached database I believe the correct answer
> begins with a decimal point.
The
On Wed, 2010-08-04 at 14:00 -0400, Tom Lane wrote:
> Hannu Krosing writes:
> > Of course there are more variables than just *_page_cost, so if you nail
> > down any other one, you may end with less than 1 for both page costs.
>
> > I have always used seq_page_cost = 1 in
On Wed, 2010-08-04 at 21:41 +0300, Hannu Krosing wrote:
> On Wed, 2010-08-04 at 14:00 -0400, Tom Lane wrote:
> > regression=# select name, setting from pg_settings where name like '%cost';
> > name | setting
> > --+-
&g
On Wed, 2010-08-04 at 22:03 +0300, Hannu Krosing wrote:
> On Wed, 2010-08-04 at 21:41 +0300, Hannu Krosing wrote:
> > On Wed, 2010-08-04 at 14:00 -0400, Tom Lane wrote:
>
> > > regression=# select name, setting from pg_settings where name like
> > > '%cost'
On Wed, 2010-08-04 at 15:16 -0400, Greg Smith wrote:
> Hannu Krosing wrote:
> > There was ample space for keeping the indexes in linux cache (it has 1GB
> > cached currently) though the system may have decided to start writing it
> > to disk, so I suspect that most of the t
On Wed, 2010-08-04 at 22:03 +0300, Hannu Krosing wrote:
> On Wed, 2010-08-04 at 21:41 +0300, Hannu Krosing wrote:
> > On Wed, 2010-08-04 at 14:00 -0400, Tom Lane wrote:
>
> > > regression=# select name, setting from pg_settings where name like
> > > '%cost'
On R, 2004-10-01 at 19:34, Tom Lane wrote:
> Josh Berkus <[EMAIL PROTECTED]> writes:
> >> Most of the problem here comes from the fact that "current_date - 7"
> >> isn't reducible to a constant and so the planner is making bad guesses
> >> about how much of each table will be scanned.
>
> > I th
On P, 2004-12-05 at 21:43, Rodrigo Carvalhaes wrote:
> Hi !
>
> Thanks for the lots of tips that I received on this matter.
>
...
> There is something more that I can try to improve this performance?
check the speed of your ide drive. maybe tweak some params with
/sbin/hdparm . Sometimes the def
s
how much free space to leave in each page vacuumed. If there were room
the new tuple could be placed near the old one in most cases and thus
avoid lots of disk head movement when updating huge tables in one go.
Hannu Krosing <[EMAIL PROTECTED]>
-
take into account the need to get rid of their index entries first.
Why is removing index entries essential ?
In pg yuo always have to visit data page, so finding the wrong tuple
there could just produce the same result as deleted tuple (which in this
case it actually is). The cleaning of index ent
QL ... but how a company like Google do to
> get an incredible database in size and so quick access ?
They use lots of boxes and lots custom software to implement a very
specific kind of cluster.
> regards,
--
Hannu Krosing <[EMAIL PROTECTED]>
---(end of b
n master. If you are able to run pg_dump
from slave, it should be ok.
--
Hannu Krosing <[EMAIL PROTECTED]>
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Ühel kenal päeval (teisipäev, 25. jaanuar 2005, 10:41-0500), kirjutas
Tom Lane:
> Hannu Krosing <[EMAIL PROTECTED]> writes:
> > Why is removing index entries essential ?
>
> Because once you re-use the tuple slot, any leftover index entries would
> be pointing to the
your function so it's a little more sensible?
Also, you could at least use a temp table for intermediate steps. This
will at least save WAL traffic.
--
Hannu Krosing <[EMAIL PROTECTED]>
---(end of broadcast)---
TIP 8: explain analyze is your friend
o
determine storage location from AND of all "clustered" bitmap indexes
and corresponding fast and clustered lookups.
This could/should/maybe :) possibly be combined with clustering as well.
--
Hannu Krosing <[EMAIL PROTECTED]>
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
u switch partitions.
> This could be achieved also by storing the time of last modification of
> a
> table somewhere.
Would we still need regular VACUUMing of read-only table to avoid
OID-wraparound ?
--
Hannu Krosing <[EMAIL PROTECTED]>
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
ions to 1G subfiles of (possibly different) tables. This map will
be quite small - for 1Tb of data it will be only 1k entries - this will
fit in cache on all modern processors and thus should add only tiny
slowdown from current direct tid.page/128k method
--
Hannu Krosing <[EMAIL PROTECTED]&g
the 1G boundary is now.
removing the partition would be just plain vacuum (if we can make pg
shring each 1G subfile independently)
> ---(end of broadcast)---
> TIP 8: explain analyze is your friend
--
Hannu Krosing <[EMAIL PROTECTED]
because of "partitioning on PK only" would be a
good design in any way.
So please don't design the system to partition on PK only.
--
Hannu Krosing <[EMAIL PROTECTED]>
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
On T, 2005-03-22 at 09:10 -0400, Alvaro Herrera wrote:
> On Mon, Mar 21, 2005 at 08:26:24PM +0200, Hannu Krosing wrote:
> > On P, 2005-03-20 at 00:52 +0100, PFC wrote:
>
> > > Also note the possibility to mark a partition READ ONLY. Or even a
> > > table.
>
be 2 to 3 _times_ slower for large tables with lots
of indexes when indexes were in system cache vs. when they were in
shared buffers (if I remember correctly, it was 1G shared buffers vs. 3G
on a 4G machine). It was probably due to all kinds of index page splits
etc which shuffled index pages back a
Brian Tarbox kirjutas R, 04.07.2003 kell 15:27:
> I recently took a system from MySQL to Postgres. Same HW, SW, same data.
> The major operations where moderately complex queries (joins on 8 tables).
> The results we got was that Postgres was fully 3 times slower than MySql.
For each and every qu
Steve Wampler kirjutas P, 13.07.2003 kell 23:46:
> On Sun, Jul 13, 2003 at 08:09:17PM +0100, Richard Huxton wrote:
> > > I'm not an SQL or PostgreSQL expert.
> > >
> > > I'm getting abysmal performance on a nested query and
> > > need some help on finding ways to improve the performance:
> > [snip]
Joe Conway kirjutas N, 17.07.2003 kell 07:52:
> To an extent it depends on how big the drives are and how large you
> expect the database to get. For maximal performance you want RAID 1+0
> for data and WAL; and you want OS, data, and WAL each on their own
> drives. So with 5 drives one possible
Trevor Astrope kirjutas T, 05.08.2003 kell 18:59:
> I was wondering if anyone found a sweet spot regarding how many inserts to
> do in a single transaction to get the best performance? Is there an
> approximate number where there isn't any more performance to be had or
> performance may drop off
Shridhar Daithankar kirjutas E, 18.08.2003 kell 09:21:
> On 16 Aug 2003 at 11:40, Josh Berkus wrote:
>
> > Shridhar,
> >
> > > Unfortunately he can not use copy due to some constraints.
> >
> > Why not use COPY to load the table, and then apply the constraints by query
> > afterwords? It migh
Shridhar Daithankar kirjutas E, 18.08.2003 kell 19:02:
> On 18 Aug 2003 at 18:52, Hannu Krosing wrote:
> > My own experimentation also got numbers in 9k/sec range (on a quad
> > 1.3GHz Xeons, 2GM mem, 50MB/sec raid) when doing 10-20 parallel runs of
> > ~100
Rod Taylor kirjutas N, 04.09.2003 kell 06:36:
> Another alternative is
> to buy a small 15krpm disk dedicated for WAL. In theory you can achieve
> one commit per rotation.
One commit per rotation would still be only 15000/60. = 250 tps, but
fortunately you can get better results if you use multipl
Relaxin kirjutas N, 04.09.2003 kell 03:28:
> I have a table with 102,384 records in it, each record is 934 bytes.
I created a test database on my Linux (RH9) laptop with 30GB/4200RPM ide
drive and P3-1133Mhz, 768MB, populated it with 128000 rows of 930 bytes
each and did
[EMAIL PROTECTED] hannu]
h is known to have several problems,
and if you run both your client and server on the same machine, then it
can also be an interaction of the two processes (cygwin/pgsql server and
native win32 ODBC client) not playing together very well.
> "Hannu Krosing" <[EMAIL PROTECTED]> wrote
[EMAIL PROTECTED] kirjutas L, 06.09.2003 kell 00:58:
> Hi,
>
> i'm having _serious_ issues of postgres hogging up the CPU over time. A graph
> showing this can be seen at http://andri.estpak.ee/cpu0.png .
>
> The database is running on Redhat 9 (stock 2.4.20-8 kernel), on a reiserfs
> partition (
Mindaugas Riauba kirjutas T, 09.09.2003 kell 15:40:
> Hello,
>
> I have small table (up to 1 rows) and every row will be updated
> once per minute. Table also has "before update on each row" trigger
> written in plpgsql. But trigger 99.99% of the time will do nothing
> to the database. It
Mindaugas Riauba kirjutas K, 10.09.2003 kell 13:21:
>
> router_db=# explain analyze update ifdata set ifspeed=256000,
> ifreason='12121', iflastupdate=CURRENT_TIMESTAMP WHERE clientid='#0003904#';
> QUERY PLAN
>
Christopher Browne kirjutas K, 01.10.2003 kell 19:21:
>
> The FS-related result appeared surprising, as the "stories" I had
> heard suggested that JFS hadn't been particularly heavily tuned on
> Linux, whereas XFS was supposed to be the "speed demon."
Gentoo linux recommends XFS only for SAN+fib
Christopher Browne kirjutas R, 03.10.2003 kell 00:57:
> [EMAIL PROTECTED] (Jean-Luc Lachance) writes:
> > That's one of the draw back of MVCC.
> > I once suggested that the transaction number and other house keeping
> > info be included in the index, but was told to forget it...
> > It would solv
Tom Lane kirjutas L, 04.10.2003 kell 19:07:
> Hannu Krosing <[EMAIL PROTECTED]> writes:
> > Christopher Browne kirjutas R, 03.10.2003 kell 00:57:
> >> A while back I outlined how this would have to be done, and for it to
> >> be done efficiently, it would be an
Josh Berkus kirjutas N, 09.10.2003 kell 08:36:
> Chris,
> > The need to do a lot of joins would likely hurt performance somewhat,
> > as well as the way that it greatly increases the number of rows.
> > Although you could always split it into several tables, one for each
> > "value_type", and UNIO
Andrew Sullivan kirjutas P, 12.10.2003 kell 22:28:
> On Fri, Oct 10, 2003 at 09:01:12PM -0500, Ron Johnson wrote:
> >
> > > Does PostgreSQL only pick one index per table on the select statements?
> >
> > That's it's preference.
>
> As far as I know, that's all it can do. Do you know something
>
Alexander Priem kirjutas E, 20.10.2003 kell 15:29:
> Thanks for your reply, Jeff.
>
> If we are going to use a NAS device for storage, then it will be attached
> through a gigabit ethernet connection. Fiber will not be an option, since
> that would negate the savings we can make by using an IDE NA
Alexander Priem kirjutas E, 20.10.2003 kell 16:04:
> Even better than the four-disk NAS I mentioned earlier is the following:
>
> Promise UltraTrak RM8000. This is a so-called SCSI-to-IDE RAID system.
While you are at it, you could also check out http://www.3ware.com/
I guess one of these with 1
Rhaoni Chiu Pereira kirjutas E, 20.10.2003 kell 17:13:
> Hi List,
>
>I got a P4 1.7Ghz , 512MB RAM , HD 7200 RPM, on RED HAT 9 running PostgreSQL
> 7.3.2-3 Database.
>I have a Delphi aplication that updates the Oracle database using .dbf
> file's information ( converting the data from t
Christopher Browne kirjutas R, 24.10.2003 kell 22:10:
> That might be something of an improvement, but it oughtn't be
> cripplingly different to use a text field rather than an integer.
I suspect his slowness comes from not running analyze when it would be
time to start using indexes for fk check
John Pagakis kirjutas L, 25.10.2003 kell 10:16:
> Christopher -
> Thanks.
>
> Answer 1:
> I believe auto commit was off (but I'm not at my dev box right now). I'll
> double-check that and the commit interval.
>
> Answer 2:
> Ah ha!! No indexes on FKs. I'll try that.
>
> Yes, each baz is a uni
John Pagakis kirjutas L, 25.10.2003 kell 12:56:
> I wrote a JAVA simulation of the above that did 1000 updates in 37 seconds.
> That left me scratching my head because in psql when I did the
> semi-equivalent:
>
> UPDATE baz SET customer_id = '1234' WHERE baz_key IN( SELECT baz_key FROM
> baz WHE
Yonatan Goraly kirjutas P, 26.10.2003 kell 00:25:
> I am in the process of adding PostgreSQL support for an application,
> in addition to Oracle and MS SQL.
> I am using PostgreSQL version 7.3.2, Red Hat 9.0 on Intel Pentium III
> board.
>
> I have a query that generally looks like this:
>
> SEL
Christopher Browne kirjutas N, 06.11.2003 kell 20:23:
> "Private" <[EMAIL PROTECTED]> writes:
> > Try this benchmark on PostgreSQL, MySQL, FireBird, Oracle:
> >
> > http://go.jitbot.com/dbbench-pg-fb-mys-orcl
>
> It looks like a good candidate for adding in a plpgsql stored
> procedure to get simi
Christopher Browne kirjutas R, 14.11.2003 kell 16:13:
> Martha Stewart called it a Good Thing when [EMAIL PROTECTED] (Rajesh Kumar Mallah)
> wrote:
> > INFO: "profiles": found 0 removable, 369195 nonremovable row versions in 43423
> > pages
> > DETAIL: 246130 dead row versions cannot be removed
Tom Lane kirjutas T, 25.11.2003 kell 23:29:
> Josh Berkus <[EMAIL PROTECTED]> writes:
> > In regular text fields containing words, your problem is solvable with full
> > text indexing (FTI). Unfortunately, FTI is not designed for arbitrary
> > non-language strings. It could be adapted, but wou
Jeremiah Jahn kirjutas K, 26.11.2003 kell 22:14:
> I was wondering if there is something I can do that would act similar to
> a index over more than one table.
>
> I have about 3 million people in my DB at the moment, they all have
> roles, and many of them have more than one name.
>
> for exam
Chadwick, Russell kirjutas L, 13.12.2003 kell 00:40:
>
> Hello everyone.
> Can anyone explain why this table which has never had more than a
> couple rows in it shows > 500k in the query planner even after running
> vacuum full. Its terribly slow to return 2 rows of data. The 2 rows
> in it are
Spiegelberg, Greg kirjutas P, 11.01.2004 kell 18:21:
> It would seem we're experiencing somthing similiar with our scratch
> volume (JFS mounted with noatime).
Which files/directories do you keep on "scratch" volume ?
All postgres files or just some (WAL, tmp) ?
-
Hannu
---
Greg Spiegelberg kirjutas E, 12.01.2004 kell 19:03:
> Hannu Krosing wrote:
> > Spiegelberg, Greg kirjutas P, 11.01.2004 kell 18:21:
> >
> >>It would seem we're experiencing somthing similiar with our scratch
> >>volume (JFS mounted with noatime).
> >
Shea,Dan [CIS] kirjutas N, 22.01.2004 kell 22:35:
> Something that I do not understand is why if you use a valid_time =
> '2004-01-22 00:00:00' the query will use the index but if you do a
> valid_time > '2004-01-22 00:00:00' it does not use the index?
It probably can't tell if > is selective eno
Hannu Krosing kirjutas N, 22.01.2004 kell 22:46:
> Shea,Dan [CIS] kirjutas N, 22.01.2004 kell 22:35:
> > Something that I do not understand is why if you use a valid_time =
> > '2004-01-22 00:00:00' the query will use the index but if you do a
> > valid_time >
Shea,Dan [CIS] kirjutas N, 22.01.2004 kell 23:32:
> The end date in the previous example was actually invalid between
> '2004-01-12'::date and '2003-01-12'::date;
> There have been multiple inserts since I recreated the index but it took
> quite some time to complete the following
> PWFPM_DEV=# ex
Orion Henry kirjutas N, 05.02.2004 kell 07:16:
> I've done some testing of 7.3.4 vs 7.4.1 and found 7.4.1 to be 20%-30%
> slower than 7.3.4. Is this common knowledge or am I just unlucky with
> my query/data selection?
>
> Things of note that might matter: the machine is a dual Opteron 1.4GHz
> r
Christopher Browne kirjutas N, 05.02.2004 kell 07:32:
> Oops! [EMAIL PROTECTED] (Orion Henry) was seen spray-painting on a wall:
> > Oh... as a side note I'm happy to announce that the 2.6 Linux kernel
> > has more than DOUBLED the speed of all my Postgres queries over the
> > 2.4. =)
>
> I did so
Saleem Burhani Baloch kirjutas N, 19.02.2004 kell 11:01:
> Hi,
>
> Thanks every one for helping me. I have upgraded to 7.4.1 on
> redhat 8 ( rh 9 require a lot of lib's) and set the configuration
> sent by Chris. Now the query results in 6.3 sec waooo. I m thinking
> that why the 7.1 process ag
test pg (9.1). On some operations this
already may give you a considerable performance boost
> Cheers,
>
> WBL
> --
> "Quality comes from focus and clarity of purpose" -- Mark Shuttleworth
>
--
---
Hannu Krosing
PostgreSQL Unlimited Scalability and Performance
81 matches
Mail list logo
