d'oh, just realized what you're telling me here. /me smacks forehead.
Let's try effective_cache of 183105... (75%). Starting both servers,
waiting for big fetch to start, and...
procs memoryswap io
system cpu
r b w swpd free buff cache si so
Why first example, where recid is given as numeric constant, is using
sequential scan, but second example, where recid is given as string
constant works with index scan, as expected? Third example shows, that
numeric constant must be typecasted in order to function properly.
Is this normal behaviou
I have played around with explain and explain analyze and noticed one
interesting oddity:
===
explain UPDATE table1 SET status = 'SKIP' WHERE recid = 196641;
Seq Scan on table1 (cost=0.00..16709.97 rows=1 width=199)
Filter: (recid = 196641)
===
explain UPDATE table1 SET status = 'SKIP' WHER
On Thu, Dec 04, 2003 at 10:45:21PM +, Richard Huxton wrote:
> If you've got the time, try putting together a small test-script with some
> dummy data and see if it's reproducible. I'm sure the other Python users
> would be interested in seeing where the problem is.
Tried with test-script, b
On Thu, 2003-12-04 at 15:47, Richard Huxton wrote:
> On Thursday 04 December 2003 23:16, Jack Coates wrote:
> >
> > > > effective_cache_size = 1
> > >
> > > This is way the heck too low. it's supposed to be the size of all
> > > available RAM; I'd set it to 2GB*65% as a start.
> >
> > This mak
On Thursday 04 December 2003 23:16, Jack Coates wrote:
>
> > > effective_cache_size = 1
> >
> > This is way the heck too low. it's supposed to be the size of all
> > available RAM; I'd set it to 2GB*65% as a start.
>
> This makes a little bit of difference. I set it to 65% (15869 pages).
That
On Thu, 2003-12-04 at 14:59, Eric Soroos wrote:
> >
> > IO is active, but hardly saturated. CPU load is hefty though, load
> > average is at 4 now.
> >
> >procs memoryswap io
> > system cpu
> > r b w swpd free buff cache si sobibo
On Thu, 2003-12-04 at 13:24, Josh Berkus wrote:
> Jack,
>
> > latest changes:
> > shared_buffers = 35642
>
> This is fine, it's about 14% of available RAM. Though the way you calculated
> it still confuses me. It's not complicated; it should be between 6% and 15%
> of available RAM; since yo
IO is active, but hardly saturated. CPU load is hefty though, load
average is at 4 now.
procs memoryswap io
system cpu
r b w swpd free buff cache si sobibo incs
us sy id
0 2 1 2808 11432 39616 1902984 0 0 204
On Thursday 04 December 2003 22:13, Ivar Zarans wrote:
> On Thu, Dec 04, 2003 at 08:23:36PM +, Richard Huxton wrote:
> > Ah - it's probably not the update but the IN. You can rewrite it using
> > PG's non-standard FROM:
> >
> > UPDATE t1 SET status='done' FROM t_tmp WHERE t1.rec_id = t_tmp.rec_
On Thursday 04 December 2003 19:59, William Yu wrote:
> Ivar Zarans wrote:
> > I am experiencing strange behaviour, where simple UPDATE of one field is
> > very slow, compared to INSERT into table with multiple indexes. I have
> > two tables - one with raw data records (about 24000), where one fiel
On Thu, Dec 04, 2003 at 08:23:36PM +, Richard Huxton wrote:
> Ah - it's probably not the update but the IN. You can rewrite it using PG's
> non-standard FROM:
>
> UPDATE t1 SET status='done' FROM t_tmp WHERE t1.rec_id = t_tmp.rec_id;
This was one *very useful* hint! Using this method i got
> "LR" == Larry Rosenman <[EMAIL PROTECTED]> writes:
>> I'd be curious to see the output of this program on other platforms
>> and other compilers. I'm using gcc 2.95.4 as shipped with FreeBSD
>> 4.8+.
LR> this is with the UnixWare compiler:
LR> $ cc -O -o testvk testvk.c
LR> $ ./testvk
LR> s
On Thu, 4 Dec 2003, Jack Coates wrote:
> On Thu, 2003-12-04 at 12:27, Richard Huxton wrote:
> > On Thursday 04 December 2003 19:50, Jack Coates wrote:
> > >
> > > I'm trying to set Postgres's shared memory usage in a fashion that
> > > allows it to return requested results quickly. Unfortunately,
--On Thursday, December 04, 2003 16:20:22 -0500 Vivek Khera
<[EMAIL PROTECTED]> wrote:
"MTO" == Matthew T O'Connor <[EMAIL PROTECTED]> writes:
MTO> Could this be the recently reported bug where time goes backwards on
MTO> FreeBSD? Can anyone who knows more about this problem chime in, I
know i
Jack,
> latest changes:
> shared_buffers = 35642
This is fine, it's about 14% of available RAM. Though the way you calculated
it still confuses me. It's not complicated; it should be between 6% and 15%
of available RAM; since you're doing a data-transformation DB, yours should
be toward the
Actually, you can simplify the fix thusly:
diff = (long long)(now.tv_sec - then.tv_sec) * 100 + (now.tv_usec -
then.tv_usec);
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
> "MTO" == Matthew T O'Connor <[EMAIL PROTECTED]> writes:
MTO> Could this be the recently reported bug where time goes backwards on
MTO> FreeBSD? Can anyone who knows more about this problem chime in, I know it
MTO> was recently discussed on Hackers.
Time does not go backwards -- the now an
>> "MTO" == Matthew T O'Connor <[EMAIL PROTECTED]> writes:
>
> MTO> I don't run FreeBSD, so I haven't tested with FreeBSD. Recently
> Craig MTO> Boston reported and submitted a patch for a crash on FreeBSD,
> but that
>
> some more debugging data:
>
> (gdb) print now
> $2 = {tv_sec = 107056507
On Thursday 04 December 2003 19:51, Ivar Zarans wrote:
>
> My second tests were done with temporary table and update query as:
> "UPDATE table1 SET Status = 'done' WHERE recid IN (SELECT recid FROM
> temptable)". It is still slower than INSERT, but more or less
> acceptable. Compared to my first te
On Thursday 04 December 2003 19:50, Jack Coates wrote:
>
> I'm trying to set Postgres's shared memory usage in a fashion that
> allows it to return requested results quickly. Unfortunately, none of
> these changes allow PG to use more than a little under 300M RAM.
> vacuumdb --analyze is now taking
Ivar Zarans wrote:
I am experiencing strange behaviour, where simple UPDATE of one field is
very slow, compared to INSERT into table with multiple indexes. I have
two tables - one with raw data records (about 24000), where one field
In Postgres and any other DB that uses MVCC (multi-version concurr
On Thu, Dec 04, 2003 at 08:23:36PM +, Richard Huxton wrote:
> Ah - it's probably not the update but the IN. You can rewrite it using PG's
> non-standard FROM:
>
> UPDATE t1 SET status='done' FROM t_tmp WHERE t1.rec_id = t_tmp.rec_id;
Thanks for the hint. I'll try this.
> Now that doesn't e
On Thu, 2003-12-04 at 12:27, Richard Huxton wrote:
> On Thursday 04 December 2003 19:50, Jack Coates wrote:
> >
> > I'm trying to set Postgres's shared memory usage in a fashion that
> > allows it to return requested results quickly. Unfortunately, none of
> > these changes allow PG to use more tha
> "MTO" == Matthew T O'Connor <[EMAIL PROTECTED]> writes:
MTO> I don't run FreeBSD, so I haven't tested with FreeBSD. Recently Craig
MTO> Boston reported and submitted a patch for a crash on FreeBSD, but that
some more debugging data:
(gdb) print now
$2 = {tv_sec = 1070565077, tv_usec = 216
On Thu, Dec 04, 2003 at 02:23:20PM -0500, Jeff wrote:
> > Most interesting is, that insert takes 0.004 seconds in average, but
> > update takes 0.255 seconds in average. Processing of 24000 records
> > took around 1 hour 20 minutes.
>
> Do you have an index on recid?
Yes, this is primary key of
On Thu, 2003-12-04 at 11:20, Josh Berkus wrote:
> Jack,
>
> > Following this, I've done:
> > 2gb ram
> > =
> > 2,000,000,000
> > bytes
>
> This calculation is fun, but I really don't know where you got it from. It
> seems quite baroque. What are you trying to set, exactly?
Message-ID: <[EMA
> "MTO" == Matthew T O'Connor <[EMAIL PROTECTED]> writes:
>> I'm running Postgres 7.4 release on FreeBSD 4.9-RELEASE.
MTO> I don't run FreeBSD, so I haven't tested with FreeBSD. Recently Craig
MTO> Boston reported and submitted a patch for a crash on FreeBSD, but that
MTO> doesn't sound like
Jack,
> Following this, I've done:
> 2gb ram
> =
> 2,000,000,000
> bytes
This calculation is fun, but I really don't know where you got it from. It
seems quite baroque. What are you trying to set, exactly?
> getting the SQL query better optimized for PG is on my todo list, but
> not somethi
On Thu, 4 Dec 2003 20:57:51 +0200
Ivar Zarans <[EMAIL PROTECTED]> wrote:
.
> table1 is updated with new value (done). Update statement itself is
> extremely simple: "update table1 set status = 'done' where recid =
> ..."
>
> Most interesting is, that insert takes 0.004 seconds in average, but
> u
On Dec 4, 2003, at 10:11 AM, Andrew Sullivan wrote:
On Thu, Dec 04, 2003 at 09:57:38AM -0800, Dror Matalon wrote:
I've seen this comment several times from different people.
Would someone care to explain how you would get data corruption? I
thought that the whole idea of the log is to provide a jo
On Thu, 2003-12-04 at 09:12, Rob Fielding wrote:
> >
> > I've tweaked shared buffers to 8192, pushed sort memory to 2048, vacuum
> > memory to 8192, and effective cache size to 1.
> > /proc/sys/kernel/shmmax is set to 16 and /proc/sys/fs/file-max
> > is set to 65536. Ulimit -n 3192.
>
Hello!
I am relative newcomer to SQL and PostgreSQL world, so please forgive me
if this question is stupid.
I am experiencing strange behaviour, where simple UPDATE of one field is
very slow, compared to INSERT into table with multiple indexes. I have
two tables - one with raw data records (about
Vivek Khera wrote:
"MTO" == Matthew T O'Connor <[EMAIL PROTECTED]> writes:
Then it just sits there. I started it at 11:35am, and it is now
3:30pm.
MTO> Weird Alphabetically speaking, is vkmlm."public"."user_list" be the
MTO> last table in the last schema in the last database? You are run
On Thu, 4 Dec 2003, Josh Berkus wrote:
> Scott,
>
> > Just to add to what the others have said here, you probably want to run
> > the pg_autovacuum daemon in the background. It comes with 7.4 but will
> > work fine with 7.3.
>
> I don't recommend using pg_autovacuum with a data transformati
Scott,
> Just to add to what the others have said here, you probably want to run
> the pg_autovacuum daemon in the background. It comes with 7.4 but will
> work fine with 7.3.
I don't recommend using pg_autovacuum with a data transformation task. pg_av
is designed for "regular use" not hu
On Thu, Dec 04, 2003 at 09:57:38AM -0800, Dror Matalon wrote:
>
> I've seen this comment several times from different people.
> Would someone care to explain how you would get data corruption? I
> thought that the whole idea of the log is to provide a journal similar
> to what you get in a journal
If I understand the problem correctly, the issue is that IDE drives
signal that data has been written to disk when they actually are holding
the data in the write cache. In the case of a power down (and I remember
someone showing some test results confirming this, check the list
archive) the data i
On Thu, Dec 04, 2003 at 11:59:32AM -0500, Jeff wrote:
> On Thu, 04 Dec 2003 08:06:23 -0800
> Jack Coates <[EMAIL PROTECTED]> wrote:
>
> > testbed:
> > dual P3 1.3 GHz box with 2GB RAM
> > two IDE 120G drives on separate channels (DMA on), OS on one, DB on
> > the other, some swap on each (totallin
On Thu, 4 Dec 2003, Jack Coates wrote:
> Another problem is that performance of the 6 million row job is decent
> if I stop the job and run a vacuumdb --analyze before letting it
> continue; is this something that 7.4 will help with? vacuumb --analyze
> doesn't seem to have much effect on the 18 m
Sean Shanny wrote:
First question is do we gain anything by moving the RH Enterprise
version of Linux in terms of performance, mainly in the IO realm as we
are not CPU bound at all? Second and more radical, has anyone run
postgreSQL on the new Apple G5 with an XRaid system? This seems like a
I've tweaked shared buffers to 8192, pushed sort memory to 2048, vacuum
memory to 8192, and effective cache size to 1.
/proc/sys/kernel/shmmax is set to 16 and /proc/sys/fs/file-max
is set to 65536. Ulimit -n 3192.
Your sharedmemory is too high, and not even being used effectivey. Your
On Thu, 04 Dec 2003 08:06:23 -0800
Jack Coates <[EMAIL PROTECTED]> wrote:
> testbed:
> dual P3 1.3 GHz box with 2GB RAM
> two IDE 120G drives on separate channels (DMA on), OS on one, DB on
> the other, some swap on each (totalling 2.8G).
> RH Linux 8.
Side Note: be sure to turn off write caching
Jack,
> The application is on another server, and does this torture test: it
> builds a large table (~6 million rows in one test, ~18 million in
> another). Rows are then pulled in chunks of 4 to 6 thousand, acted on,
> and inserted back into another table (which will of course eventually
> grow t
> "MTO" == Matthew T O'Connor <[EMAIL PROTECTED]> writes:
>> Then it just sits there. I started it at 11:35am, and it is now
>> 3:30pm.
MTO> Weird Alphabetically speaking, is vkmlm."public"."user_list" be the
MTO> last table in the last schema in the last database? You are running
conv
Hi,
sorry for duplication, I asked this on pgsql-admin first before
realizing it wasn't the appropriate list.
I'm having trouble optimizing PostgreSQL for an admittedly heinous
worst-case scenario load.
testbed:
dual P3 1.3 GHz box with 2GB RAM
two IDE 120G drives on separate channels (DMA on),
On Thu, 4 Dec 2003, Andrei Bintintan wrote:
> Hi,
>
> I have the following table:
> CREATE TABLE public.rights (
> id int4 DEFAULT nextval('"rights_id_seq"'::text) NOT NULL,
> id_user int4 NOT NULL,
> id_modull int4 NOT NULL,
> CONSTRAINT rights_pkey PRIMARY KEY (id)
> )
>
> and I created the fol
[EMAIL PROTECTED] wrote:
> Sorry for my mistake on the 15000 recs per day.
It was useful for us to pick at that a bit; it was certainly looking a
mite suspicious.
> In fact, this server is planned as a OLTP database server for a retailer.
> Our intention is either to setup 1 or 2 Postgresql db in
I am using Asynchronous Query Processing interface from libpq library.
And I got some strange results on Solaris
My test select query is 'SELECT * from pg_user;'
and I use select system synchronous I/O multiplexer in 'C'
The first test sends 1 select queries using 10 nonblocking connections
t
(hope I'm posting this correctly)
You wrote:
>First question is do we gain anything by moving the RH Enterprise
>version of Linux in terms of performance, mainly in the IO realm as we
>are not CPU bound at all? Second and more radical, has anyone run
>postgreSQL on the new Apple G5 with an XRaid
50 matches
Mail list logo
