Re: [PERFORM] Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000"
Indeed... I looked through the official TODO list and was unable to find an entry for global temporary tables- such a thing would be ideal for any transient data such as web sessions or materialized views. Is there any reason why global temp tables shouldn't be implemented? (And, no, I'm not simply referring to "in-memory" tables- they can simply be handled with a ram disk.) -M ---(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] Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000"
On Apr 3, 2007, at 15:39 , C. Bergström wrote: A.M. wrote: Indeed... I looked through the official TODO list and was unable to find an entry for global temporary tables- such a thing would be ideal for any transient data such as web sessions or materialized views. Is there any reason why global temp tables shouldn't be implemented? (And, no, I'm not simply referring to "in-memory" tables- they can simply be handled with a ram disk.) Not exactly what you're looking for and a simple API, but the performance is very nice and has a lot of potential. http://pgfoundry.org/projects/pgmemcache/ I would like to use transactional semantics over tables that can disappear whenever the server fails. memcached does not offer that. Cheers, M ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Equivalents in PostgreSQL of MySQL's "ENGINE=MEMORY" "MAX_ROWS=1000"
On Apr 3, 2007, at 16:00 , Alan Hodgson wrote: On Tuesday 03 April 2007 12:47, "A.M." <[EMAIL PROTECTED]> wrote: On Apr 3, 2007, at 15:39 , C. Bergström wrote: I would like to use transactional semantics over tables that can disappear whenever the server fails. memcached does not offer that. How would temporary tables? The only difference between temporary tables and standard tables is the WAL. Global temporary tables would be accessible by all sessions and would be truncated on postmaster start. For a further potential speed boost, global temp tables could be put in a ramdisk tablespace. Well, that's at least how I envision them. Cheers, M ---(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] Apache2 PostgreSQL http authentication
On Oct 7, 2007, at 9:14 , Jeffrey Brower wrote: Greetings All, I have to authenticate against an existing (constantly modified) PostgreSQL database under Solaris 10 (X86). While my PHP scripts are an obvious no-brainer, the rest of the contents need to be protected as well (images, etc) so the http authentication is required. I am using the blastwave Apache2 and PostgreSQL packages. I found it trivial to install mod_auth_pgsql. http://www.giuseppetanzilli.it/mod_auth_pgsql/ As far as performance, only your testing will tell if it is sufficient. In my setup, the authentication overhead is the least of my worries. Cheers, M ---(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] PostgreSQL as a local in-memory cache
On Jun 24, 2010, at 4:01 PM, Pavel Stehule wrote: > 2010/6/24 Joshua D. Drake : >> On Thu, 2010-06-24 at 21:14 +0200, Pavel Stehule wrote: >>> 2010/6/24 Josh Berkus : > And I'm also planning to implement unlogged tables, which have the > same contents for all sessions but are not WAL-logged (and are > truncated on startup). >>> >>> this is similar MySQL's memory tables. Personally, I don't see any >>> practical sense do same work on PostgreSQL now, when memcached exists. >> >> Because memcache is yet another layer and increases overhead to the >> application developers by adding yet another layer to work with. Non >> logged tables would rock. > > I see only one positive point - it can help to people with broken > design application with migration to PostgreSQL. The broken design is being required to work around PostgreSQL's lack of this optimization. > > There are different interesting feature - cached procedure's results > like Oracle 11. - it's more general. > > only idea. > > For me memory tables are nonsens, but what about memory cached > materialised views (maybe periodically refreshed)? Non-WAL-logged, non-fsynced tables are not equivalent to MySQL "memory tables". Such tables simply contain transient information. One can already make "memory tables" in PostgreSQL by making a tablespace in a tmpfs partition. I have been eagerly waiting for this feature for six years so that I can write proper queries against ever-changing session data with transactional semantics (which memcached cannot offer). The only restriction I see for these transient data tables is that they cannot be referenced by standard tables using foreign key constraints. Otherwise, these tables behave like any other. That's the benefit. Cheers, M -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] MVCC and Implications for (Near) Real-Time Application
On Oct 25, 2010, at 2:46 PM, Steve Wong wrote: > Hi experts, > > I have a (near) real-time application in which inserts into the database > needs > to be visible to queries from other threads with minimal delay. The inserts > are > triggered by real-time events and are therefore asynchronous (i.e. many > performance tips I read related to batch inserts or copy do not apply here, > since these events cannot be predicted or batched), and the inserted data > need > to be available within a couple of seconds to other threads (for example, an > inserted row that only appears to other query threads 5 seconds or more after > the insert is not acceptable). The delay should be under 2 seconds maximum, > sub-1 second would be great. > > My questions are: (1) Does the MVCC architecture introduce significant delays > between insert by a thread and visibility by other threads (I am unclear > about > how multiple versions are "collapsed" or reconciled, as well as how different > query threads are seeing which version)? (2) Are there any available > benchmarks > that can measure this delay? (3) What are relevant config parameters that > will > reduce this delay? There is no way to know without testing whether your hardware, OS, database schema, and database load can meet your demands. However, there is no technical reason why PostgreSQL could not meet your timing goals- MVCC does not inherently introduce delays, however the PostgreSQL implementation requires a cleanup process which can introduce latency. If you find that your current architecture is not up to the task, consider using LISTEN/NOTIFY with a payload (new in 9.0), which we are using for a similar "live-update" system. Cheers, M -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Some queries starting to hang
Explain analyze could at least put an asterisk around actual time that deviated by some factor from the estimated time. On Tue, June 6, 2006 10:39 am, Simon Riggs wrote: > > This is a good example of a case where the inefficiency of EXPLAIN > ANALYZE would be a contributory factor to it not actually being > available for diagnosing a problem. > > Maybe we need something even more drastic than recent proposed changes > to EXPLAIN ANALYZE? > > Perhaps we could annotate the query tree with individual limits. That > way a node that was expecting to deal with 1 row would simply stop > executing the EXPLAIN ANALYZE when it hit N times as many rows (default=no > limit). That way, we would still be able to see a bad plan even without > waiting for the whole query to execute - just stop at a point where the > plan is far enough off track. That would give us what we need: pinpoint > exactly which part of the plan is off-track and see how far off track it > is. If the limits were configurable, we'd be able to opt for > faster-but-less-accurate or slower-yet-100% accuracy behaviour. We > wouldn't need to worry about timing overhead either then. > > e.g. EXPLAIN ANALYZE ERRLIMIT 10 SELECT ... > > -- > Simon Riggs > EnterpriseDB http://www.enterprisedb.com > > > > ---(end of broadcast)--- > TIP 5: don't forget to increase your free space map settings > > ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Some queries starting to hang
Hmmm...It could generate NOTICEs whenever there is a drastic difference in rowcount or actual time... On Tue, June 6, 2006 11:29 am, Andrew Sullivan wrote: > On Tue, Jun 06, 2006 at 11:06:09AM -0400, Tom Lane wrote: > >>> it was properly instrumented. That way, the OP might have been able >>> to discover the root cause himself... >> >> I don't think that helps, as it just replaces one uncertainty by >> another: how far did the EXPLAIN really get towards completion of the >> plan? You still don't have any hard data. > > Well, you _might_ get something useful, if you're trying to work on a > maladjusted production system, because you get to the part that trips the > limit, and then you know, "Well, I gotta fix it that far, anyway." > > Often, when you're in real trouble, you can't or don't wait for the > full plan to come back from EXPLAIN ANALYSE, because a manager is helpfully > standing over your shoulder asking whether you're there yet. Being able > to say, "Aha, we have the first symptom," might be helpful to users. > Because the impatient simply won't wait for the > full report to come back, and therefore they'll end up flying blind > instead. (Note that "the impatient" is not always the person logged in > and executing the commands.) > > A > > > -- > Andrew Sullivan | [EMAIL PROTECTED] > I remember when computers were frustrating because they *did* exactly what > you told them to. That actually seems sort of quaint now. --J.D. Baldwin > > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > > > http://archives.postgresql.org > > ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Performance of pg_dump on PGSQL 8.0
On Wed, June 14, 2006 1:04 pm, John Vincent wrote: > I know it is but that's what we need for some of our queries. Our ETL > tool (informatica) and BI tool (actuate) won't let us set those things as > part of our jobs. We need it for those purposes. We have some really nasty > queries that will be fixed in our new server. You could modify pgpool to insert the necessary set commands and point the tools at pgpool. -M ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] victory!
On Thu, July 6, 2006 1:53 pm, Merlin Moncure wrote: > with all these unsubscribe requests, we can only extrapolate that the > server has no serious performance issues left to solve. good work! :-) Well, either that or the performance issues are so severe that users are dropping like flies... -M ---(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] viewing source code
On Dec 20, 2007, at 11:30 AM, Roberts, Jon wrote: -Original Message- From: Merlin Moncure [mailto:[EMAIL PROTECTED] Sent: Thursday, December 20, 2007 8:30 AM To: Roberts, Jon Cc: Alvaro Herrera; Trevor Talbot; Joshua D. Drake; Kris Jurka; Jonah H. Harris; Bill Moran; pgsql-performance@postgresql.org Subject: Re: [PERFORM] viewing source code On Dec 20, 2007 9:07 AM, Roberts, Jon <[EMAIL PROTECTED]> wrote: So your suggestion is first to come up with a query that dynamically checks permissions and create a view for it. Secondly, change pgAdmin to reference this view in place of pg_proc. Actually, it should be extended to all This solution will not work. It requires cooperation from pgAdmin which is not going to happen and does nothing about psql or direct queries from within pgadmin. Considered from a security/obfuscation perspective, its completely ineffective. As I've said many times, there are only two solutions to this problem: 1. disable permissions to pg_proc and deal with the side effects (mainly, pgadmin being broken). 2. wrap procedure languages in encrypted handler (pl/pgsql_s) so that the procedure code is encrypted in pg_proc. this is an ideal solution, but the most work. I think there is an option 3. Enhance the db to have this feature built in which is more inline with commercial databases. This feature would drive adoption of PostgreSQL. It isn't feasible in most companies to allow everyone with access to the database to view all code written by anyone and everyone. For instance, you could have a Finance group writing functions to calculate your financial earnings. These calculations could be changing frequently and should only be visible to a small group of people. If the calculations were visible by anyone with database access, they could figure out earnings prior to the release and thus have inside information on the stock. Does everyone in your organization have login access to your database? That seems like the main issue. Perhaps you should stick an application server in between. The application server could also upload functions from the "Finance group" and ensure that no one can see stored procedures. Cheers, M ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Making the most of memory?
On Jan 23, 2008, at 2:57 PM, Guy Rouillier wrote: Scott Marlowe wrote: I assume you're talking about solid state drives? They have their uses, but for most use cases, having plenty of RAM in your server will be a better way to spend your money. For certain high throughput, relatively small databases (i.e. transactional work) the SSD can be quite useful. Unless somebody has changes some physics recently, I'm not understanding the recent discussions of SSD in the general press. Flash has a limited number of writes before it becomes unreliable. On good quality consumer grade, that's about 300,000 writes, while on industrial grade it's about 10 times that. That's fine for mp3 players and cameras; even professional photographers probably won't rewrite the same spot on a flash card that many times in a lifetime. But for database applications, 300,000 writes is trivial. 3 million will go a lot longer, but in non-archival applications, I imagine even that mark won't take but a year or two to surpass. Please let outdated numbers rest in peace. http://www.storagesearch.com/ssdmyths-endurance.html Conclusion: "With current technologies write endurance is not a factor you should be worrying about when deploying flash SSDs for server acceleration applications - even in a university or other analytics intensive environment. " That said, postgresql is likely making assumptions about non-volatile storage that will need to be shattered once SSDs become more widely deployed. Perhaps SSDs will replace RAID BBUs and then the HDs themselves? Cheers, M ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] pl/pgsql functions outperforming sql ones?
On Jan 27, 2012, at 2:59 PM, Carlo Stonebanks wrote: > Was I even right in thinking I would gain any performance by converting to > SQL? As always, it depends. I converted an immutable pl/pgsql function to an SQL function and the body of the function barely changed. However, I experienced an order-of-magnitude speed-up because the SQL function could be folded into the plan (like a view) while a pl/pgsql function will never be folded (and the planner punts and assumes the function will return 100 rows for set-returning functions). However, not all SQL functions can be folded into the plan. On the other hand, a pl/pgsql function can make use of memoization for number-crunching routines and make business-logical short-circuiting decisions. Cheers, M -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
