On Thu, Oct 26, 2006 at 09:35:56PM +0100, Gavin Hamill wrote:
> On Thu, 26 Oct 2006 14:17:29 -0500
> "Jim C. Nasby" <[EMAIL PROTECTED]> wrote:
>
> > Are you sure that there's nothing else happening on the machine that
> > could affect the vacuum tim
On Thu, Oct 26, 2006 at 03:03:38PM -0700, George Pavlov wrote:
> i have wondered myself. i wouldn't do it through pgAdmin (not sure what
> the best test it, but i thought psql from the same machine might be
> better--see below). anyway, the funny thing is that if you concatenate
> them the time dro
On Tue, Nov 14, 2006 at 09:17:08AM -0500, Merlin Moncure wrote:
> On 11/14/06, Cosimo Streppone <[EMAIL PROTECTED]> wrote:
> >I must say I lowered "shared_buffers" to 8192, as it was before.
> >I tried raising it to 16384, but I can't seem to find a relationship
> >between shared_buffers and perfor
RS6000 hardware for database stuff (and that
gets expensive if you're paying per CPU!).
--
Jim C. Nasby, Database Consultant [EMAIL PROTECTED]
Member: Triangle Fraternity, Sports Car Club of America
Give your computer some brain candy! www.distributed.net Team #1828
Windows:
iled with -O3.
Also, if I set enable_hashagg = false, it runs in less than a second.
--
Jim C. Nasby, Database Consultant [EMAIL PROTECTED]
Member: Triangle Fraternity, Sports Car Club of America
Give your computer some brain candy! www.distributed.net Team #1828
Windows: "Wh
=254056
width=48) (actual time=786.515..1289.101 rows=49091 loops=1)
Filter: (project_id = 8)
Total runtime: 3548.087 ms
Even though the second case is only a select, it seems clear that
something's wrong...
--
Jim C. Nasby, Database Consultant
ested someplace you can grab it.
On Fri, Apr 16, 2004 at 12:34:11PM -0400, Tom Lane wrote:
> "Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> > Note the time for the hash join step:
>
> Have you ANALYZEd these tables lately?
>
> It looks to me like it's hashing o
Dammit, I somehow deleted a bunch of replies to this.
Did a TODO ever come out of this?
--
Jim C. Nasby, Database Consultant [EMAIL PROTECTED]
Member: Triangle Fraternity, Sports Car Club of America
Give your computer some brain candy! www.distributed.net Team #1828
Windows
of write
> traffic, the same kind of reorganization you would normally expect in a
> BTree index.
This isn't true, at least in 9i. You can create whatever indexes you
want on an index-organized table. I believe that the index stores the PK
value instead of the ROWID.
--
Jim
ll benefit some means of indicating what range of PK values
are on a page might be needed.
It's not as beneficial as a true IOT since you don't get the benefit of
storing your tuples inline with your B-Tree.
I'm sure there's a ton of things I'm missing, especially since I'm
g a set of
tools would make life much easier. I just looked but didn't see anything
on GBorg.
--
Jim C. Nasby, Database Consultant [EMAIL PROTECTED]
Give your computer some brain candy! www.distributed.net Team #1828
Windows: "Where do you want to go today
r each project. Storing project_id in that
table is an extra 4 bytes... doesn't sound like much until you consider
that the table has over 130M rows right now. So it would be nice to have
an easy way to partition the table based on unique project_id's and not
waste space in the partit
mp on seperate drives?
Specifically, we have a box with 8 drives, 2 in a mirror with the OS and
WAL and pg_temp; the rest in a raid10 with the database on it. Do you
think it would have been better to make one big raid10? What if it was
raid5? And what if it was only 6 drives total?
--
Jim C. Nasby
seperate drive would
still be a losing proposition.
BTW, my experience with our setup is that the raid10 is almost always
the IO bottleneck, and not the mirror with everything else on it.
--
Jim C. Nasby, Database Consultant [EMAIL PROTECTED]
Give your computer some brain candy! www.
s. This
means MVCC is used and no locking is required. Even if locks were
required, they would be shared read locks which wouldn't block each
other.
--
Jim C. Nasby, Database Consultant [EMAIL PROTECTED]
Give your computer some brain candy! www.distributed.net Team #1828
W
plan caching, because your connect overhead will swamp
the planning cost. This does not mean you have to use something like
pgpool (which makes some rather questionable claims IMO); any decent web
application language/environment will support connection pooling.
--
Jim C. Nasby, Database Consultant
nection. Pretty much all web apps have one connection
> per process, which is persistent (i.e. not dropped and remade for each
> request), but not shared between processes, therefore not pooled.
OK, that'd work too... the point is if you're re-connecting all the time
it doesn
me specific queries as being
cachable is an excellent idea; perfect for 'static data' scenarios. What
I don't know is how much will be saved.
--
Jim C. Nasby, Database Consultant [EMAIL PROTECTED]
Give your computer some brain candy! www.distributed.net Team #1828
Windo
atabase Solutions
> San Francisco
>
> ---(end of broadcast)---
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
>
--
Jim C. Nasby, Database
Basically, for a given workload, it
would take 2x the number of Sun CPUs as RS/6000 CPUs. The difference in
Oracle licensing costs was usually enough to pay for the new hardware in
one year.
--
Jim C. Nasby, Database Consultant [EMAIL PROTECTED]
Give your computer some brain candy! w
eeBSD will schedule disk I/O based on
process priority, while linux won't. This can be very handy for things
like vacuum.
--
Jim C. Nasby, Database Consultant [EMAIL PROTECTED]
Give your computer some brain candy! www.distributed.net Team #1828
Windows: "Where do you wan
all that easy to get right. :-) )
>
> /* Steinar */
> --
> Homepage: http://www.sesse.net/
>
> ---(end of broadcast)-----------
> TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
>
--
Jim C. Nasby, Database Consultant
mething that the
> statistics we keep are too crude to detect.
Isn't that exactly what pg_stats.correlation is?
--
Jim C. Nasby, Database Consultant [EMAIL PROTECTED]
Give your computer some brain candy! www.distributed.net Team #1828
Windows: "Where do you want to g
On Thu, Oct 28, 2004 at 07:49:28PM -0400, Tom Lane wrote:
> "Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> > On Sun, Oct 24, 2004 at 04:11:53PM -0400, Tom Lane wrote:
> >> The test case you are showing is probably suffering from nonrandom
> >> placem
ual time=0.009..3.368 rows=2439
loops=1)
-> Hash (cost=3.11..3.11 rows=10 width=4) (actual
time=0.061..0.061 rows=0 loops=1)
-> Index Scan using alert_type_pkey on alert_type t
(cost=0.00..3.11 rows=10 width=4) (actual time=0.018..0.038 rows=1
query only has a problem if it's run on a
> >large date/time window, which normally doesn't happen.
>
> Try increasing your statistics target for the column and then rerunning
> analyze.
>
> Sincerely,
>
> Joshua D. Drake
--
Jim C. Nasby, Database
ere it seems the
planer doesn't think it'll be getting a unique value or a small set of
values even though stats indicates that it should be.
One final question... would there be interest in a process that would
dynamically update the histogram settings for tables based on how
distinc
te never happens.
>
> Having pgmemcache delete, not replace data addresses this second issue.
> -sc
>
> --
> Sean Chittenden
>
>
> ---(end of broadcast)-------
> TIP 3: if posting/reading through Usenet, please send
.Net .NZ Ltd, PO Box 11-053, Manners St, Wellington
> WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St
> DDI: +64(4)803-2201 MOB: +64(272)DEBIAN OFFICE: +64(4)499-2267
>These PRESERVES should be FORCE-FED to PENTAGON OFFICIALS!!
> --
stem files and a 6x200G RAID10
for the database (all SATA drives). The largest table 120M rows and
825,000 8k pages. I can scan 1/5th of that table via an index scan in
about a minute. (The schema can be found at
http://minilink.org/cvs.distributed.net/l3.sql.
--
Jim C. Nasby, Database Consultant
's probably possible to fix it though, at least for
> cases where the child tables have rowtypes identical to the parent.
>
> regards, tom lane
>
> ---(end of broadcast)---
> TIP 3: if posting/reading through Us
++---+-------------+--+-
public | email_contrib | project_id | 0 | 4 | 6 |
{205,5,25,8,24,3} | {0.461133,0.4455,0.0444333,0.0418667,0.0049,0.00216667} | |
0.703936
--
Jim
find too many real-world examples where you could do
something with a PostgreSQL procedural language that you couldn't do
with PL/SQL.
--
Jim C. Nasby, Database Consultant [EMAIL PROTECTED]
Give your computer some brain candy! www.distributed.net Team #1828
Windows: "
>>'01-10-2005 23:59' order by id limit 30) as t;
> >>
> >>it will cost me about 3+ secs
> >
> >
> >The difference will be that in the final case you only make 30 calls
> >to long2ip() whereas in the first two you call it 30
> within a single server (doesn't it? I thought it did, I know it was
> discussed w/ the guy from Cox Communications and I thought he was using
> it :).
No, PostgreSQL doesn't support any kind of partitioning, unless you
write it yourself. I think there's some work being done
s for locking and consistency
across machines. So you better have fast access to the drive array, and
the array better have caching of some kind.
--
Jim C. Nasby, Database Consultant [EMAIL PROTECTED]
Give your computer some brain candy! www.distributed.net Team #1828
Windows: "Where d
kind of limited clustering scheme that could be
implemented without a great amount of effort by the core developers. In
that case I think there's a good chance you could find people willing to
work on it.
--
Jim C. Nasby, Database Consultant [EMAIL PROTECTED]
Give your computer
et is set to 1000.
Basically, it seems that it doesn't understand that each row in log will
match up with at most one row in bucket. There is a unique index on
bucket(rrs_id, end_time), so it should be able to tell this.
--
Jim C. Nasby, Database Consultant
uld be a source of contention on
> multi-processor machines running lots of concurrent update/deletes.
>
> --
> greg
>
>
> ---(end of broadcast)---
> TIP 6: Have you searched our list archives?
>
>http://arc
l carefully read the owner's manual."
> <http://www.eviloverlord.com/>
>
> -----------(end of broadcast)---
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to [EMAIL PROTECTED
On Sat, Jan 22, 2005 at 10:18:00PM -0500, Tom Lane wrote:
> "Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> > (SELECT b.bucket_id AS rrs_bucket_id, s.*
> > FROM rrs.bucket b
> > JOIN page_log
hanged areas of large tables would make for a significant
> reduction in the cost of VACUUM.
FWIW, that's already on the TODO. See also
http://lnk.nu/archives.postgresql.org/142.php.
--
Jim C. Nasby, Database Consultant [EMAIL PROTECTED]
Give your computer some brain candy!
thout numbers this is a bunch of hand-waving, but I don't
think it's valid to assume that minimizing the amount of work you do in
a transaction means better throughput without considering what it will
cost to do the work you're putting off until later.
--
Jim C. Nasby, Database C
xamples.
Are there any examples of how you can take numbers from pg_stats_* or
explain analize and turn them into configuration settings (such and
random page cost)?
--
Jim C. Nasby, Database Consultant [EMAIL PROTECTED]
Give your computer some brain candy! www.distributed.net Team #182
erformance
database is a high performance I/O system. If you look in the archives
you'll find people running postgresql on 30 and 40 drive arrays.
--
Jim C. Nasby, Database Consultant [EMAIL PROTECTED]
Give your computer some brain candy! www.distributed.net Team #1828
Windows
On Tue, Feb 01, 2005 at 12:06:27AM -0500, Tom Lane wrote:
> "Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> > On Mon, Jan 31, 2005 at 03:26:12PM -0500, Tom Lane wrote:
> >> Preferably a whole lot of queries. All the measurement techniques I can
> >> think
tead of
software raid. stats.distributed.net runs a 3ware controller and SATA
drives.
--
Jim C. Nasby, Database Consultant [EMAIL PROTECTED]
Give your computer some brain candy! www.distributed.net Team #1828
Windows: "Where do you want to go today?"
Linux: "Whe
le has some form of built-in connection pooling. I don't remember
the exact details of it off the top of my head, but I think it was a
'wedge' that clients would connect to as if it was the database, and the
wedge would then find an available database process to use.
--
Jim C. Nasby
ugh I would choose opterons not for memory size
but because of memory *bandwidth*).
--
Jim C. Nasby, Database Consultant [EMAIL PROTECTED]
Give your computer some brain candy! www.distributed.net Team #1828
Windows: "Where do you want to go today?"
Linux: "Where do you wa
dn't that be 50%?
--
Jim C. Nasby, Database Consultant [EMAIL PROTECTED]
Give your computer some brain candy! www.distributed.net Team #1828
Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
Free
On Tue, Mar 08, 2005 at 11:20:20PM -0600, Bruno Wolff III wrote:
> On Tue, Mar 08, 2005 at 22:55:19 -0600,
> "Jim C. Nasby" <[EMAIL PROTECTED]> wrote:
> > On Tue, Mar 08, 2005 at 10:38:21PM -0600, Bruno Wolff III wrote:
> > > Not exactly. If the number of row
that's not going to
work well at all for a case where you need to hit a relatively small
percentage of rows in a relatively large number of partitions. SELECT
... WHERE customer_id = 1 would be a good example of such a query
(assuming the table is partitioned on something like invoice_date).
--
in the current transaction would be able to recognize if the
partition that tuple was in had been removed, and just ignore that index
entry. Granted, you'd need to clean the index up at some point
(presumably via vacuum), but it doesn't need to occur at partition drop
time.
--
Jim C. Na
ldn't be done using inheritance, though I don't know if inheritence
or a union view is better for partitioning. In either case, this case
might not be a good candidate for phase 1, but I think partitioning
should be designed with it in mind.
--
Jim C. Nasby, Database Consultant
take the approach of 'give me as much
memory as you can; I'll take it from there, thankyouverymuch', which
makes effective_cache_size a bit of a mystery.
--
Jim C. Nasby, Database Consultant [EMAIL PROTECTED]
Member: Triangle Fraternity, Sports Car
t have better app management. Keeping pgsql up-to-date using
ports on FreeBSD is pretty painless (for that matter, so is keeping the
OS itself up-to-date).
--
Jim C. Nasby, Database Consultant [EMAIL PROTECTED]
Member: Triangle Fraternity, Sports Car Club of America
Give your com
esystem operates,
too. If it puts your WALs, temp_db, and database files very close to
each other on the drive, splitting them out to seperate spindles won't
help as much.
--
Jim C. Nasby, Database Consultant [EMAIL PROTECTED]
Member: Triangle Fraternity, Sports Car Club of Am
| 0 | 0 |0 |0 | 0 | f | f
| f | f |
(3 rows)
--
Jim C. Nasby, Database Consultant [EMAIL PROTECTED]
Member: Triangle Fraternity, Sports Car Club of America
Give your computer some brain candy! www.distributed.
On Thu, Jul 31, 2003 at 04:59:21PM -0400, Andrew Sullivan wrote:
> On Thu, Jul 31, 2003 at 02:51:45PM -0500, Jim C. Nasby wrote:
> If you really needed to set enable_seqscan=false (did you really?
> Are you sure that's not the cheapest way?), you might want to
> investiga
On Fri, Aug 01, 2003 at 08:16:12AM -0400, Andrew Sullivan wrote:
> On Thu, Jul 31, 2003 at 05:59:59PM -0500, Jim C. Nasby wrote:
> >
> > Well, if I don't do this it wants to seqscan a table that occupies 350k
> > pages, instead of pulling a couple thousand rows. I start
7;t the
pg_dump process be at 100% CPU? It does seem a bit coincidental that the
two procs seem to be taking 100% of one CPU (top shows them running on
different CPUs though).
This is version 7.3.4.
--
Jim C. Nasby, Database Consultant [EMAIL PROTECTED]
Member: Triangle Fraternity,
601 - 661 of 661 matches
Mail list logo
