Re: [PERFORM] Storing Digital Video
A Dimecres 01 Febrer 2006 01:32, Rodrigo Madera va escriure: > I am concerned with performance issues involving the storage of DV on > a database. > > I though of some options, which would be the most advised for speed? > > 1) Pack N frames inside a "container" and store the container to the db. > 2) Store each frame in a separate record in the table "frames". > 3) (type something here) > > Thanks for the help, What if you store meta data in the database and use some PL/Python/Java/Perl functions to store and retrieve video files from the server. The function would store files to the files system, not a table. This avoids the need for a file server for your application while making your relational queries fast. Any experiences/thoughts on this solution? > > Rodrigo > > ---(end of broadcast)--- > TIP 9: In versions below 8.0, the planner will ignore your desire to >choose an index scan if your joining column's datatypes do not >match ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Default autovacuum settings too conservative
On Feb 1, 2006, at 4:37 PM, Matthew T. O'Connor wrote: As far I as I know, we are still looking for real world feedback. 8.1 is the first release to have the integrated autovacuum. The thresholds in 8.1 are a good bit less conservative than the thresholds in the contrib version. The contrib thresholds were universally considered WAY to conservative, but that was somewhat necessary since you couldn't set them on a per table basis as you can in 8.1. If we continue to hear from people that the current 8.1 default thresholds are still to conservative we can look into lowering them. I spent the weekend researching and pondering this topic as well. For me the per-table tuning is vital, since I have some tables that are very small and implement a queue (ie, update very often several million times per day and have at most 10 or so rows), some that are fairly stable with O(10k) rows which update occasionally, and a couple of tables that are quite large: 20 million rows which updates a few million times per day and inserts a few thousand, and another table with ~275 million rows in which we insert and update roughly 3 million per day. The 40% overhead would kill these large tables both in terms of performance and disk usage. I'm pondering a global 10% and having the big tables at or below 1% based on the rate of change. Is there a way to make the autovacuum process log more verbosely while leaving the rest of the logging minimal? This would help tune it. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] execution plan : Oracle vs PostgreSQL
Indeed there is: you can use an ARRAY constructor with SELECT. Here's some PGPLSQL code I have (simplified and with the variable names shrouded). SELECT INTO m ARRAY(SELECT d FROM hp WHERE hp.ss=$1 ORDER BY 1); FERREIRA, William (VALTECH) wrote: maybe there is an other way to query children directly into an array and having query plan caching ? begin:vcard fn:Andrew Lazarus n:Lazarus;Andrew org:Pillette Investment Management;Research and Development adr;dom:;;3028 Fillmore;San Francisco;CA;94123 email;internet:[EMAIL PROTECTED] title:Director tel;work:800-366-0688 tel;fax:415-440-4093 url:http://www.pillette.com version:2.1 end:vcard ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Default autovacuum settings too conservative
matthew@zeut.net ("Matthew T. O'Connor") writes:
> I think the default settings should be designed to minimize the
> impact autovacuum has on the system while preventing the system from
> ever getting wildly bloated (also protect xid wraparound, but that
> doesn't have anything to do with the thresholds).
That would suggest setting the "base threshold"
autovacuum_vacuum_threshold relatively low, and the "scale factor"
autovacuum_vacuum_scale_factor fairly high.
--
let name="cbbrowne" and tld="ntlug.org" in name ^ "@" ^ tld;;
http://cbbrowne.com/info/nonrdbms.html
I think it may be possible to simplify and condense the content of
this thread somewhat:
"GX is an ex-API. It is no longer supported" - The Rest of Us
"No it isn't. It's just pining for the fjords!" - Lawson
-- Michael Paquette
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [PERFORM] partitioning and locking problems
On Wed, Feb 01, 2006 at 10:20:21AM -0500, Tom Lane wrote: > "Marc Morin" <[EMAIL PROTECTED]> writes: > > Do you mean it would be impossible to change the code so that existing > > selects continue to use the pre-truncated table until they commit? > > Yes, because that table won't exist any more (as in the file's been > unlinked) once the TRUNCATE commits. Is there a reason the truncate must happen in 'real time'? If TRUNCATE marked a table as "truncated as of tid, cid" and created a new set of empty objects to be used by all transactions after that, then it should be possible to truncate without waiting on existing selects. Unfortunately, I can't think of any way to avoid blocking new inserters, but in the partitioning case that shouldn't matter. > > The update/insert rule change appears to be more more doable? No? > > You've still got race conditions there: do onlooker transactions see the > old set of rules, or the new set, or some unholy mixture? Removing the > lock as you suggest would make it possible for the rule rewriter to pick > up non-self-consistent data from the system catalogs, leading to > arbitrarily bad behavior ... if you're lucky, it'll just crash, if > you're not lucky the incorrect rule will do a fandango on your data. Where can one read about why the catalogs can't/don't use MVCC (I'm assuming that's why this won't work...) -- 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 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Default autovacuum settings too conservative
On Wed, Feb 01, 2006 at 04:37:07PM -0500, Matthew T. O'Connor wrote: > I think the default settings should be designed to minimize the impact > autovacuum has on the system while preventing the system from ever > getting wildly bloated (also protect xid wraparound, but that doesn't > have anything to do with the thresholds). I don't really see the logic behind that. Problems caused by inadequate vacuuming seem to be much more prevalent than problems caused by vacuum impacting the system. If vacuum impact is a concern I think it more reasonable to make the default vacuum_cost_delay non-zero instead. -- 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 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] pgbench output
Well, it tells you how many transactions per second it was able to do. Do you have specific questions? On Thu, Feb 02, 2006 at 12:39:59PM +0530, Pradeep Parmar wrote: > Hi, > > I'm fairly new to PostgreSQL. I was trying pgbench , but could not > understand the output . Can anyone help me out to understand the output of > pgbench > > > Pradeep -- 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 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Default autovacuum settings too conservative
> On Wed, Feb 01, 2006 at 04:37:07PM -0500, Matthew T. O'Connor wrote: >> I think the default settings should be designed to minimize the impact >> autovacuum has on the system while preventing the system from ever >> getting wildly bloated (also protect xid wraparound, but that doesn't >> have anything to do with the thresholds). > > I don't really see the logic behind that. Problems caused by inadequate > vacuuming seem to be much more prevalent than problems caused by vacuum > impacting the system. If vacuum impact is a concern I think it more > reasonable to make the default vacuum_cost_delay non-zero instead. That's a good point. I would not be keen, on the other hand, on having the delays terribly high. Big tables, if delayed significantly, will take plenty longer to vacuum, and I always get paranoid about long running transactions :-). -- let name="cbbrowne" and tld="gmail.com" in name ^ "@" ^ tld;; http://linuxdatabases.info/info/postgresql.html This login session: $13.99 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Default autovacuum settings too conservative
On Mon, Feb 06, 2006 at 10:14:53PM -0800, Christopher Browne wrote: > > On Wed, Feb 01, 2006 at 04:37:07PM -0500, Matthew T. O'Connor wrote: > >> I think the default settings should be designed to minimize the impact > >> autovacuum has on the system while preventing the system from ever > >> getting wildly bloated (also protect xid wraparound, but that doesn't > >> have anything to do with the thresholds). > > > > I don't really see the logic behind that. Problems caused by inadequate > > vacuuming seem to be much more prevalent than problems caused by vacuum > > impacting the system. If vacuum impact is a concern I think it more > > reasonable to make the default vacuum_cost_delay non-zero instead. > > That's a good point. > > I would not be keen, on the other hand, on having the delays terribly > high. > > Big tables, if delayed significantly, will take plenty longer to > vacuum, and I always get paranoid about long running transactions :-). Very true, but I'd hope anyone running a table large enough for this to make a difference would have done some tuning of their own... What we really need is a replacement for vacuum_delay that takes PostgreSQL generated IO activity into account... -- 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 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
