Re: [PERFORM] wal_buffers
On Oct 5, 2005, at 8:23 AM, Ian Westmacott wrote: Can anyone tell me what precisely a WAL buffer contains, so that I can compute an appropriate setting for wal_buffers (in 8.0.3)? I know the documentation suggests there is little evidence that supports increasing wal_buffers, but we are inserting a large amount of data that, I believe, easily exceeds the default 64K in a single transaction. We are also very sensitive to write latency. As background, we are doing a sustained insert of 2.2 billion rows in 1.3 million transactions per day. Thats about 1700 rows per transaction, at (roughly) 50 bytes per row. Ian, The WAL Configuration chapter (25.2) has a pretty good discussion of how wal_buffers is used: http://www.postgresql.org/docs/8.0/static/wal-configuration.html You might also take a look at Josh Berkus' recent testing on this setting: http://www.powerpostgresql.com/ -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source: Open Your i™ http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-469-5150 615-469-5151 (fax) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] index on custom function; explain
Hi, On Mon, Oct 03, 2005 at 08:14:11AM -0700, [EMAIL PROTECTED] wrote: So my questions are: * Does anyone have any idea how I can integrate a function that lists all aliases for a given name into such a mapping query? what version are you using? 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 Cheers, Yann ---(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] wal_buffers
On Thu, 2005-10-06 at 02:39, Thomas F. O'Connell wrote: The WAL Configuration chapter (25.2) has a pretty good discussion of how wal_buffers is used: http://www.postgresql.org/docs/8.0/static/wal-configuration.html You might also take a look at Josh Berkus' recent testing on this setting: http://www.powerpostgresql.com/ Thanks; I'd seen the documentation, but not Josh Berkus' testing. For my part, I don't have a large number of concurrent connections, only one. But it is doing large writes, and XLogInsert is number 2 on the profile (with LWLockAcquire and LWLockRelease close behind). I suppose that is expected, but lead by the documentation I wanted to make sure XLogInsert always had some buffer space to play with. --Ian ---(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
[PERFORM] functions and execution plan caching
I am working on a system which will be heavily dependent on functions (some SQL, some PL/pgSQL). I am worried about the backend caching query execution plans for long running connections. Given: - Processes which are connected to the database for long periods of time (transactions are always short). - These processes will use some functions to query data. - Lots of data is being inserted into tables that these functions query. - Vacuums are done frequently. Am I at risk of degrading performance after some time due to stale execution plans? Thanks, -Kelly ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] wal_buffers
On Thu, Oct 06, 2005 at 08:56:31AM -0400, Ian Westmacott wrote: On Thu, 2005-10-06 at 02:39, Thomas F. O'Connell wrote: The WAL Configuration chapter (25.2) has a pretty good discussion of how wal_buffers is used: http://www.postgresql.org/docs/8.0/static/wal-configuration.html You might also take a look at Josh Berkus' recent testing on this setting: http://www.powerpostgresql.com/ Thanks; I'd seen the documentation, but not Josh Berkus' testing. For my part, I don't have a large number of concurrent connections, only one. But it is doing large writes, and XLogInsert is number 2 on the profile (with LWLockAcquire and LWLockRelease close behind). I suppose that is expected, but lead by the documentation I wanted to make sure XLogInsert always had some buffer space to play with. If you are using a single connection, you are wasting lots of cycles just waiting for the disk to spin. Were you to use multiple connections, some transactions could be doing some useful work while others are waiting for their transaction to be committed. -- Alvaro Herrera http://www.amazon.com/gp/registry/5ZYLFMCVHXC I suspect most samba developers are already technically insane... Of course, since many of them are Australians, you can't tell. (L. Torvalds) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] functions and execution plan caching
On Thu, Oct 06, 2005 at 08:17:54AM -0500, Kelly Burkhart wrote: Given: - Processes which are connected to the database for long periods of time (transactions are always short). - These processes will use some functions to query data. - Lots of data is being inserted into tables that these functions query. - Vacuums are done frequently. Am I at risk of degrading performance after some time due to stale execution plans? Yes, because plans are chosen based on the statistics that were current when the function was first called. For example, if a sequential scan made sense when you first called the function, then subsequent calls will also use a sequential scan. You can see this for yourself with a simple test: create a table, populate it with a handful of records, and call a function that issues a query that can (but won't necessarily) use an index. Then add a lot of records to the table and call the function again. You'll probably notice that the function runs slower than the same query run from outside the function, and that the function runs fast if you recreate it or call it in a new session. If you set debug_print_plan to on and client_min_messages to debug1, then you'll see the plan that the function chose (but only on the first call to the function). If you have statistics enabled, then you can query pg_stat_user_tables and pg_stat_user_indexes to see whether subsequent calls use sequential or index scans (this should be done when nobody else is querying the table so the statistics represent only what you did). You can avoid cached plans by using EXECUTE. You'll have to run tests to see whether the potential gain is worth the overhead. -- Michael Fuhr ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [PERFORM] A Better External Sort?
Martijn van Oosterhout kleptog@svana.org writes: Indeed, one of the things on my list is to remove all the lseeks in favour of pread. Halving the number of kernel calls has got to be worth something right? Portability is an issue ofcourse... Being sure that it's not a pessimization is another issue. I note that glibc will emulate these functions if the kernel doesn't have them; which means you could be replacing one kernel call with three. And I don't think autoconf has any way to determine whether a libc function represents a native kernel call or not ... regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PERFORM] A Better External Sort?
Martijn van Oosterhout kleptog@svana.org writes: Are we awfully worried about people still using 2.0 kernels? And it would replace two calls with three in the worst case, we currently lseek before every read. That's utterly false. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PERFORM] A Better External Sort?
On Thu, Oct 06, 2005 at 03:57:38PM -0400, Tom Lane wrote: Martijn van Oosterhout kleptog@svana.org writes: Indeed, one of the things on my list is to remove all the lseeks in favour of pread. Halving the number of kernel calls has got to be worth something right? Portability is an issue ofcourse... Being sure that it's not a pessimization is another issue. I note that glibc will emulate these functions if the kernel doesn't have them; which means you could be replacing one kernel call with three. And I don't think autoconf has any way to determine whether a libc function represents a native kernel call or not ... The problem kernels would be Linux 2.0, which I very much doubt is going to be present in to-be-deployed database servers. Unless someone runs glibc on top of some other kernel, I guess. Is this a common scenario? I've never seen it. -- Alvaro Herrera http://www.amazon.com/gp/registry/DXLWNGRJD34 Oh, oh, las chicas galacianas, lo harán por las perlas, ¡Y las de Arrakis por el agua! Pero si buscas damas Que se consuman como llamas, ¡Prueba una hija de Caladan! (Gurney Halleck) ---(end of broadcast)--- TIP 6: explain analyze is your friend
[PERFORM] Need Some Suggestions
I have an application that is prone to sudden, unscheduled high bursts of activity, and I am finding that the application design permits me to detect the activity bursts within an existing function. The bursts only affect 3 tables, but degradation becomes apparent after 2,000 updates, and significant after 8,000 updates. I already know that a plain vacuum (without full, analyze, or free options) solves my problem. Since vacuum is classified in the documentation as an SQL command, I tried to call it using a trigger function on one the tables (they all have roughly the same insert / update rate). However, I just found out that vacuum cannot be called by a function. Vacuums done by a scheduler at 3AM in the morning are adequate to handle my non-peak needs otherwise. autovacuum sounds like it would do the trick, but I am on a WINDOWS 2003 environment, but I have Googled up messages that it still has various problems (in Windows) which won't be resolved until 8.1 is out. But I have a problem NOW, and the application is deployed around the world. QUESTION: Is there anyway anyone knows of to permit me to execute an operating system program (even vacuumdb) or possibly to add a C function to the library which would allow me to do this (I am not a C programmer, but have access to some persons who are)? Very important to me for performance reasons. Does anybody have some suggestions on the best path for me to take? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[PERFORM] Status of Opteron vs Xeon
What's the current status of how much faster the Opteron is compared to the Xeons? I know the Opterons used to be close to 2x faster, but is that still the case? I understand much work has been done to reduce the contect switching storms on the Xeon architecture, is this correct? -- Jeff Frost, Owner [EMAIL PROTECTED] Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954 ---(end of broadcast)--- TIP 6: explain analyze is your friend
