Re: [PERFORM] PostgreSQL Configuration Tool for Dummies
Campbell, Lance wrote: Now I am at the difficult part, what parameters to calculate and how to calculate them. Everything below has to do with PostgreSQL version 8.2: The parameters I would think we should calculate are: max_connections shared_buffers work_mem maintenance_work_mem effective_cache_size random_page_cost Any other variables? I am open to suggestions. we also should scale max_fsm_pages according to the database size and workload answers - I also note that the configuration file it generates seems to look like on for PostgreSQL 7.x or something - I think we should just include the specific parameters to change. Stefan ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Volunteer to build a configuration tool
On Wed, 20 Jun 2007, Campbell, Lance wrote: If everything I said is correct then I agree Why have effective_cache_size? Why not just go down the approach that Oracle has taken and require people to rely more on shared_buffers and the general memory driven approach? Why rely on the disk caching of the OS? First off, it may help explain the dynamics here if you know that until fairly recent releases, the PostgreSQL shared_buffers cache had some performance issues that made it impractical to make it too large. It hasn't been that long that relying more heavily on the Postgres cache was technically feasible. I think the user community at large is still assimilating all the implications of that shift, and as such some of the territory with making the Postgres memory really large is still being mapped out. There are also still some issues left in that area. For example, the bigger your shared_buffers cache is, the worse the potential is for having a checkpoint take a really long time and disrupt operations. There are OS tunables that can help work around that issue; similar ones for the PostgreSQL buffer cache won't be available until the 8.3 release. In addition to all that, there are still several reasons to keep relying on the OS cache: 1) The OS cache memory is shared with other applications, so relying on it lowers the average memory footprint of PostgreSQL. The database doesn't have to be a pig that constantly eats all the memory up, while still utilizing it when necessary. 2) The OS knows a lot more about the disk layout and similar low-level details and can do optimizations a platform-independant program like Postgres can't assume are available. 3) There are more people working on optimizing the caching algorithms in modern operating systems than are coding on this project. Using that sophisticated cache leverages their work. The Oracle Way presumes that you've got such a massive development staff that you can solve these problems better yourself than the community at large, and then support that solution on every platform. This is why they ended up with solutions like raw partitions, where they just put their own filesystem on the disk and figure out how to make that work well everywhere. If you look at trends in this area, at this point the underlying operating systems have gotten good enough that tricks like that are becoming marginal. Pushing more work toward the OS is a completely viable design choice that strengthens every year. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Hardware suggestions
Scott Marlowe writes: and a bit more resiliant to drive failure, RAID-5 can give you a lot of storage and very good read performance, so it works well for reporting / New controllers now also have Raid 6, which from the few reports I have seen seems to have a good compromise of performance and space. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[PERFORM] Data transfer very slow when connected via DSL
Hello all, one of my customers installed Postgres on a public server to access the data from several places. The problem is that it takes _ages_ to transfer data from the database to the client app. At first I suspected a problem with the ODBC driver and my application, but using pgAdminIII 1.6.3.6112 (on Windows XP) gives the same result. In table tblItem there are exactly 50 records stored. The table has 58 columns: 5 character varying and the rest integer. As far as I can tell the Postgres installation is o.k. SELECT VERSION() PostgreSQL 8.2.4 on i386-portbld-freebsd6.2, compiled by GCC cc (GCC) 3.4.6 [FreeBSD] 20060305 EXPLAIN ANALYZE SELECT * FROM tblItem Seq Scan on tblItem (cost=0.00..2.50 rows=50 width=423) (actual time=0.011..0.048 rows=50 loops=1) Total runtime: 0.150 ms The database computer is connected via a 2MBit SDL connection. I myself have a 768/128 KBit ADSL connection and pinging the server takes 150ms on average. In the pgAdminIII Query Tool the following command takes 15-16 seconds: SELECT * FROM tblItem During the first 2 seconds the D/L speed is 10-15KB/s. The remaining time the U/L and D/L speed is constant at 1KB/s. My customer reported that the same query takes 2-3 seconds for him (with 6MBit ADSL and 50ms ping). So my questions are: * Could there be anything wrong with the server configuration? * Is the ping difference between the customers and my machine responsible for the difference in the query execution time? * Is this normal behaviour or could this be improved somehow? Thanks in advance for any help. Rainer PS: I tried selecting only selected columns from the table and the speed is proportional to the no. of rows which must be returned. For example selecting all 5 character columns takes 2 seconds. Selecting 26 integer columns takes 7-8 seconds and selecting all integer columns takes 14 seconds. ---(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] Volunteer to build a configuration tool
Greg Smith [EMAIL PROTECTED] writes: On Wed, 20 Jun 2007, Campbell, Lance wrote: If everything I said is correct then I agree Why have effective_cache_size? Why not just go down the approach that Oracle has taken and require people to rely more on shared_buffers and the general memory driven approach? Why rely on the disk caching of the OS? [ reasons why snipped ] There's another reason for not setting shared_buffers huge, beyond the good ones Greg listed: the kernel may or may not consider a large shared-memory segment as potentially swappable. If the kernel starts swapping out low-usage areas of the shared-buffer arena, you lose badly: accessing a supposedly in cache page takes just as long as fetching it from the disk file would've, and if a dirty page gets swapped out, you'll have to swap it back in before you can write it; making a total of *three* I/Os expended to get it down to where it should have been, not one. So unless you can lock the shared memory segment in RAM, it's best to keep it small enough that all the buffers are heavily used. Marginal-use pages will be handled much more effectively in the O/S cache. I'd also like to re-emphasize the point about don't be a pig if you don't have to. It would be very bad if Postgres automatically operated on the assumption that it should try to consume all available resources. Personally, I run half a dozen postmasters (of varying vintages) on one not-especially-impressive development machine. I can do this exactly because the default configuration doesn't try to eat the whole machine. To get back to the comparison to Oracle: Oracle can assume that it's running on a dedicated machine, because their license fees are more than the price of the machine anyway. We shouldn't make that assumption, at least not in the out-of-the-box configuration. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] PostgreSQL Configuration Tool for Dummies
Campbell, Lance [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Now I am at the difficult part, what parameters to calculate and how to calculate them. Everything below has to do with PostgreSQL version 8.2: The parameters I would think we should calculate are: max_connections shared_buffers work_mem maintenance_work_mem effective_cache_size random_page_cost Any other variables? I am open to suggestions. I know this is mainly about tuning for performance but I do think you ought to give the option to change at least 'listen_address'. Something like: Accept connections on: - Local connections (Unix sockets/localhost) - All TCP/IP interfaces - Specific IP addresses: ___ (comma-seperated list) and maybe a pointer to the pg_hba.conf docs for further info. Regards, Ben ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Data transfer very slow when connected via DSL
Hello Rainer, The database computer is connected via a 2MBit SDL connection. I myself have a 768/128 KBit ADSL connection and pinging the server takes 150ms on average. I do not have a solution, but I can confirm the problem :) One PostgreSQL-Installation: Server 8.1 and 8.2 on Windows in the central; various others connected via VPN. Queries are subsecond when run locally (including data transfer), and up to 10 seconds and more via VPN, even in off-hours The data-transfer is done via PG-Admin or via psycopg2 Python-Database adapter; nothing with ODBC or similiar in between. I did not find a solution so far; and for bulk data transfers I now programmed a workaround. Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 fx 01212-5-13695179 - EuroPython 2007 will take place in Vilnius, Lithuania from Monday 9th July to Wednesday 11th July. See you there!
Re: [PERFORM] Volunteer to build a configuration tool
Tom Lane wrote: There's another reason for not setting shared_buffers huge, beyond the good ones Greg listed: the kernel may or may not consider a large shared-memory segment as potentially swappable. Another is that on Windows, shared memory access is more expensive and various people have noted that the smallest value for shared_buffers you can get away with can yield better performance as it leaves more free for the kernel to use, more efficiently. Regards, Dave. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] PostgreSQL Configuration Tool for Dummies
Greg Smith wrote: On Tue, 19 Jun 2007, Josh Berkus wrote: I don't think the mostly reads / mostly writes question covers anything, nor is it likely to produce accurate answers. Instead, we need to ask the users to characterize what type of application they are running: T1) Please characterize the general type of workload you will be running on this database. Choose one of the following four... We've hashed through this area before, but for Lance's benefit I'll reiterate my dissenting position on this subject. If you're building a tool for dummies, my opinion is that you shouldn't ask any of this information. I think there's an enormous benefit to providing something that takes basic sizing information and gives conservative guidelines based on that--as you say, safe, middle-of-the-road values--that are still way, way more useful than the default values. The risk in trying to make a complicated tool that satisfies all the users Josh is aiming his more sophisticated effort at is that you'll lose the newbies. Generally I agree, however, how about a first switch, for beginner / intermediate / advanced. The choice you make determines how much detail we ask you about your setup. Beginners get two or three simple questions, intermediate a handful, and advanced gets grilled on everything. Then, just write the beginner and maybe intermediate to begin with and ghost out the advanced until it's ready. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Replication
Hi, Andrew Sullivan wrote: This isn't quite true. Slony-II was originally conceived by Jan as an attempt to implement some of the Postgres-R ideas. Oh, right, thanks for that correction. Part of the problem, as near as I could tell, was that we had no group communication protocol that would really work. Spread needed a _lot_ of work (where lot of work may mean rewrite), and I just didn't have the humans to put on that problem. Another part of the problem was that, for high-contention workloads like the ones we happened to be working on, an optimistic approach like Postgres-R is probably always going to be a loser. Hm.. for high-contention on single rows, sure, yes - you would mostly get rollbacks for conflicting transactions. But the optimism there is justified, as I think most real world transactions don't conflict (or else you can work around such high single row contention). You are right in that the serialization of the GCS can be bottleneck. However, there's lots of research going on in that area and I'm convinced that Postgres-R has it's value. Regards Markus ---(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] Data transfer very slow when connected via DSL
Rainer Bauer [EMAIL PROTECTED] writes: one of my customers installed Postgres on a public server to access the data from several places. The problem is that it takes _ages_ to transfer data from the database to the client app. At first I suspected a problem with the ODBC driver and my application, but using pgAdminIII 1.6.3.6112 (on Windows XP) gives the same result. I seem to recall that we've seen similar reports before, always involving Windows :-(. Check whether you have any nonstandard components hooking into the network stack on that machine. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Performance query about large tables, lots of concurrent access
Karl Wright wrote: Scott Marlowe wrote: Karl Wright wrote: Shaun Thomas wrote: On Wednesday 20 June 2007 12:55:20 pm Karl Wright wrote: I am afraid that I did answer this. My largest tables are the ones continually being updated. The smaller ones are updated only infrequently. You know, it actually sounds like you're getting whacked by the same problem that got us a while back. It sounds like you weren't vacuuming frequently enough initially, and then tried vacuuming later, only after you noticed performance degrade. Unfortunately what that means, is for several weeks or months, Postgres has not been reusing rows on your (admittedly) active and large tables; it just appends at the end, and lets old rows slowly bloat that table larger and larger. Indexes too, will suffer from dead pages. As frightening/sickening as this sounds, you may need to dump/restore the really huge table, or vacuum-full to put it on a crash diet, and then maintain a strict daily or bi-daily vacuum schedule to keep it under control. A nice try, but I had just completed a VACUUM on this database three hours prior to starting the VACUUM that I gave up on after 27 hours. So I don't see how much more frequently I could do it. (The one I did earlier finished in six hours - but to accomplish that I had to shut down EVERYTHING else that machine was doing.) So, have you ever run vacuum full or reindex on this database? No. However, this database has only existed since last Thursday afternoon. Well, a couple of dozen update statements with no where clause on large tables could bloat it right up. It's not about age so much as update / delete patterns. You are aware of the difference between how vacuum and vacuum full work, right? vacuum := mark deleted tuples as available, leave in table vacuum full := compact tables to remove deleted tuples. While you should generally avoid vacuum full, if you've let your database get so bloated that the majority of space in your tables is now empty / deleted tuples, you likely need to vacuuum full / reindex it. If the database is continually growing, should VACUUM FULL be necessary? If it's only growing, with no deletes or updates, then no. Generally, on a properly vacuumed database, vacuum full should never be needed. For instance, on my tiny little 31 Gigabyte reporting database, the main table takes up about 17 Gigs. This query gives you some idea how many bytes each row is taking on average: select relname, relpages::float*8192 as size, reltuples, (relpages::double precision*8192)/reltuples::double precision as bytes_per_row from pg_class where relname = 'businessrequestsummary'; relname |size | reltuples | bytes_per_row +-+-+- businessrequestsummary | 17560944640 | 5.49438e+07 | 319.61656229454 Note that these numbers are updated by running analyze... What does it say about your DB? I wish I could tell you. Like I said, I had to abandon this project to test out an upgrade procedure involving pg_dump and pg_restore. (The upgrade also seems to take a very long time - over 6 hours so far.) When it is back online I can provide further information. Well, let us know. I would definitely recommend getting more / faster disks. Right now I've got a simple 4 disk RAID10 on the way to replace the single SATA drive I'm running on right now. I can't wait. ---(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] Volunteer to build a configuration tool
On Thu, Jun 21, 2007 at 03:14:48AM -0400, Greg Smith wrote: The Oracle Way presumes that you've got such a massive development staff that you can solve these problems better yourself than the community at large, and then support that solution on every platform. Not that Greg is suggesting otherwise, but to be fair to Oracle (and other large database vendors), the raw partitions approach was also a completely sensible design decision back when they made it. In the late 70s and early 80s, the capabilities of various filesystems were wildly uneven (read the _UNIX Hater's Handbook_ on filesystems, for instance, if you want an especially jaundiced view). Moreover, since it wasn't clear that UNIX and UNIX-like things were going to become the dominant standard -- VMS was an obvious contender for a long time, and for good reason -- it made sense to have a low-level structure that you could rely on. Once they had all that code and had made all those assumptions while relying on it, it made no sense to replace it all. It's now mostly mature and robust, and it is probably a better decision to focus on incremental improvements to it than to rip it all out and replace it with something likely to be buggy and surprising. The PostgreSQL developers' practice of sighing gently every time someone comes along insisting that threads are keen or that shared memory sucks relies on the same, perfectly sensible premise: why throw away a working low-level part of your design to get an undemonstrated benefit and probably a whole lot of new bugs? A -- Andrew Sullivan | [EMAIL PROTECTED] In the future this spectacle of the middle classes shocking the avant- garde will probably become the textbook definition of Postmodernism. --Brad Holland ---(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] Volunteer to build a configuration tool
Greg, I have a PostgreSQL database that runs on a dedicated server. The server has 24Gig of memory. What would be the max size I would ever want to set the shared_buffers to if I where to relying on the OS for disk caching approach? It seems that no matter how big your dedicated server is there would be a top limit to the size of shared_buffers. Thanks, Lance Campbell Project Manager/Software Architect Web Services at Public Affairs University of Illinois 217.333.0382 http://webservices.uiuc.edu -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Greg Smith Sent: Thursday, June 21, 2007 2:15 AM To: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Volunteer to build a configuration tool On Wed, 20 Jun 2007, Campbell, Lance wrote: If everything I said is correct then I agree Why have effective_cache_size? Why not just go down the approach that Oracle has taken and require people to rely more on shared_buffers and the general memory driven approach? Why rely on the disk caching of the OS? First off, it may help explain the dynamics here if you know that until fairly recent releases, the PostgreSQL shared_buffers cache had some performance issues that made it impractical to make it too large. It hasn't been that long that relying more heavily on the Postgres cache was technically feasible. I think the user community at large is still assimilating all the implications of that shift, and as such some of the territory with making the Postgres memory really large is still being mapped out. There are also still some issues left in that area. For example, the bigger your shared_buffers cache is, the worse the potential is for having a checkpoint take a really long time and disrupt operations. There are OS tunables that can help work around that issue; similar ones for the PostgreSQL buffer cache won't be available until the 8.3 release. In addition to all that, there are still several reasons to keep relying on the OS cache: 1) The OS cache memory is shared with other applications, so relying on it lowers the average memory footprint of PostgreSQL. The database doesn't have to be a pig that constantly eats all the memory up, while still utilizing it when necessary. 2) The OS knows a lot more about the disk layout and similar low-level details and can do optimizations a platform-independant program like Postgres can't assume are available. 3) There are more people working on optimizing the caching algorithms in modern operating systems than are coding on this project. Using that sophisticated cache leverages their work. The Oracle Way presumes that you've got such a massive development staff that you can solve these problems better yourself than the community at large, and then support that solution on every platform. This is why they ended up with solutions like raw partitions, where they just put their own filesystem on the disk and figure out how to make that work well everywhere. If you look at trends in this area, at this point the underlying operating systems have gotten good enough that tricks like that are becoming marginal. Pushing more work toward the OS is a completely viable design choice that strengthens every year. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(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
[PERFORM] vacuum a lot of data when insert only
Hi there,
Reading different references, I understand there is no need to vacuum a
table where just insert actions perform. So I'm surprising to see a table
with just historical data, which is vacuumed at the nightly cron with a
simple VACUUM VERBOSE on about 1/3 of indexes amount.
Take a look on the fragment log concerning this table:
INFO: vacuuming public.tbTEST
INFO: scanned index tbTEST_pkey to remove 1357614 row versions
DETAIL: CPU 0.31s/1.38u sec elapsed 4.56 sec.
INFO: tbTEST: removed 1357614 row versions in 16923 pages
DETAIL: CPU 0.70s/0.13u sec elapsed 2.49 sec.
INFO: index tbTEST_pkey now contains 2601759 row versions in 12384 pages
DETAIL: 1357614 index row versions were removed.
5415 index pages have been deleted, 2452 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: tbTEST: found 1357614 removable, 2601759 nonremovable row versions
in 49153 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 29900 unused item pointers.
16923 pages contain useful free space.
0 pages are entirely empty.
CPU 2.12s/1.87u sec elapsed 11.41 sec.
INFO: tbTEST: truncated 49153 to 32231 pages
DETAIL: CPU 0.23s/0.06u sec elapsed 0.31 sec.
I found the following statistics in pg_stat_user_tables:
n_tup_ins = 11444229
n_tup_upd = 0
n_tup_del = 0
The structure of the table is the following:
CREATE TABLE tbTEST
(
PK_ID integer NOT NULL DEFAULT nextval('tbTEST_PK_ID_seq'::regclass),
FK_SourceTypeID integer,
SourceID integer DEFAULT -1,
Message character varying(500) NOT NULL DEFAULT ''::character varying,
DateAndTime timestamp without time zone NOT NULL,
CONSTRAINT tbTEST_pkey PRIMARY KEY (PK_ID),
CONSTRAINT tbTEST_FK_SourceTypeID_fkey FOREIGN KEY (FK_SourceTypeID)
REFERENCES tbLISTS (PK_ID) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
Postgres version is 8.2.3.
What's happen ?
TIA,
Sabin
---(end of broadcast)---
TIP 6: explain analyze is your friend
[PERFORM] Database-wide VACUUM ANALYZE
We recently upgraded a very large database (~550 GB) from 8.1.4 to 8.2.4 via a pg_dump and pg_restore. (Note that the restore took several days.) We had accepted the default settings: vacuum_freeze_min_age = 100 million autovacuum_freeze_max_age = 200 million Due to our very high transaction rate, it appears that a database-wide vacuum kicked off approximately 2 weeks after the restore. (Aside: after reading the docs and considering our system characteristics, I know now that our autovacuum_freeze_max_age should be more like 2 billion. However on this machine I haven't changed the config settings yet.) Also, I believe, that due to the bulk of our data having the same age after the restore, the db-wide vacuum had *a lot* of rows to mark with the FrozenXID. The good thing is that the db-wide vacuum, which ran for a long time, was reasonably non-intrusive to other database activity (somewhat, but reasonable for the short term). The other good thing was that concurrent autovacuum processes were still vacuuming/analyzing tables as necessary. The bad thing, which I don't totally understand from reading the docs, is that another db-wide vacuum kicked off exactly 24 hours after the first db-wide vacuum kicked off, before the first one had finished. (Note that these vacuums seem to go through the tables alphabetically.) I managed to explain this to myself in that there were still rows in tables not yet touched by the first db-wide vacuum that could have XIDs older than autovacuum_freeze_max_age. Fine, so two db-wide vacuums were now taking place, one behind the other. The first db-wide vacuum finished approximately 36 hours after it started. At this point I was convinced that the second db-wide vacuum would run to completion with little or no work to do and all would be good. The thing I can't explain is why a third db-wide vacuum kicked off exactly 24 hours (again) after the second db-wide vacuum kicked off (and the second vacuum still running). Wouldn't the first db-wide vacuum have marked any rows that needed it with the FrozenXID? Why would a third db-wide vacuum kick off so soon after the first db-wide vacuum had completed? Surely there haven't been 100 million more transactions in the last two days? Can someone explain what is going on here? I can't quite figure it out based on the docs. Thanks, Steve
Re: [PERFORM] Performance query about large tables, lots of concurrent access
On Thu, Jun 21, 2007 at 12:29:49PM -0400, Karl Wright wrote: I checked the disk picture - this is a RAID disk array with 6 drives, with a bit more than 1Tbyte total storage. 15,000 RPM. It would be hard to get more/faster disk than that. What kind of RAID? It's _easy_ to get faster disk that 6 drives in RAID5, even if they're 15,000 RPM. The rotation speed is the least of your problems in many RAID implementations. A -- Andrew Sullivan | [EMAIL PROTECTED] The year's penultimate month is not in truth a good way of saying November. --H.W. Fowler ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] vacuum a lot of data when insert only
On Thu, Jun 21, 2007 at 07:53:54PM +0300, Sabin Coanda wrote: Reading different references, I understand there is no need to vacuum a table where just insert actions perform. That's false. First, you must vacuum at least once every 2 billion transactions. Second, if a table is INSERTed to, but then the INSERTing transaction rolls back, it leaves a dead tuple in its wake. My guess, from your posted example, is that you have the latter case happening, because you have removable rows (that's assuming you aren't mistaken that there's never a delete or update to the table). A -- Andrew Sullivan | [EMAIL PROTECTED] Unfortunately reformatting the Internet is a little more painful than reformatting your hard drive when it gets out of whack. --Scott Morris ---(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] Data transfer very slow when connected via DSL
Hello Tom, I seem to recall that we've seen similar reports before, always involving Windows :-(. Check whether you have any nonstandard components hooking into the network stack on that machine. I just repeated the test by booting into Safe Mode with Network Support, but the results are the same. So I don't think that's the cause. Apart from that, what response times could I expect? Rainer ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Data transfer very slow when connected via DSL
Hello Harald, I do not have a solution, but I can confirm the problem :) At least that rules out any misconfiguration issues :-( I did not find a solution so far; and for bulk data transfers I now programmed a workaround. But that is surely based on some component installed on the server, isn't it? To be honest I didn't expect top performance, but the speed I got suggested some error on my part. Rainer ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[PERFORM] Very long SQL strings
I can't seem to find a definitive answer to this. It looks like Postgres does not enforce a limit on the length of an SQL string. Great. However is there some point at which a query string becomes ridiculously too long and affects performance? Here's my particular case: consider an INSERT statement where you're using the new multi-row VALUES clause or SELECT ... UNION ALL to group together tuples. Is it always better to group as many together as possible? For example, on a toy table with two columns, I noticed about a 20% increase when bulking together 1000 tuples in one INSERT statement as opposed to doing 1000 individual INSERTS. Would this be the same for 1? 10? Does it depend on the width of the tuples or the data types? Are there any values A and B such that grouping together A tuples and B tuples separately and running two statements, will be faster than grouping A+B tuples in one statement? Steve
Re: [PERFORM] Database-wide VACUUM ANALYZE
Steven Flatt wrote: The bad thing, which I don't totally understand from reading the docs, is that another db-wide vacuum kicked off exactly 24 hours after the first db-wide vacuum kicked off, before the first one had finished. (Note that these vacuums seem to go through the tables alphabetically.) I managed to explain this to myself in that there were still rows in tables not yet touched by the first db-wide vacuum that could have XIDs older than autovacuum_freeze_max_age. Fine, so two db-wide vacuums were now taking place, one behind the other. Are you sure there's no cron job starting the vacuums? 24h sounds too good to be a coincidence, and there's no magic constant of 24h in the autovacuum code. Besides, autovacuum can only be running one VACUUM at a time, so there must be something else launching them. What's your vacuuming strategy in general, before and after upgrade? -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(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] Very long SQL strings
Steven Flatt wrote: It looks like Postgres does not enforce a limit on the length of an SQL string. Great. However is there some point at which a query string becomes ridiculously too long and affects performance? Here's my particular case: consider an INSERT statement where you're using the new multi-row VALUES clause or SELECT ... UNION ALL to group together tuples. Is it always better to group as many together as possible? I'm sure you'll reach a point of diminishing returns, and eventually a ceiling where you run out of memory etc, but I don't know what the limit would be. The most efficient way to do bulk inserts is to stream the data with COPY. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(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] Very long SQL strings
Steven Flatt [EMAIL PROTECTED] writes: It looks like Postgres does not enforce a limit on the length of an SQL string. Great. However is there some point at which a query string becomes ridiculously too long and affects performance? Yes, but it'll depend a whole lot on context; I'd suggest experimentation if you want to derive a number for your particular situation. For starters, whether you are on 32- or 64-bit hardware is hugely relevant. FYI, when we developed multi-row-VALUES quite a bit of thought was put into maintaining performance with lots of rows, and IIRC we saw reasonable performance up into the tens of thousands of rows (depending on how wide the rows are). Other ways of making a query long, such as lots of WHERE clauses, might send performance into the tank a lot quicker. So the short answer is it all depends. regards, tom lane PS: for the record, there is a hard limit at 1GB of query text, owing to restrictions built into palloc. But I think you'd hit other memory limits or performance bottlenecks before that one. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Very long SQL strings
Steven Flatt [EMAIL PROTECTED] schrieb: For example, on a toy table with two columns, I noticed about a 20% increase when bulking together 1000 tuples in one INSERT statement as opposed to doing 1000 individual INSERTS. Would this be the same for 1? 10? Does it depend on the width of the tuples or the data types? I guess you can obtain the same if you pack all INSERTs into one transaction. And, faster than INSERT: COPY. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) If I was god, I would recompile penguin with --enable-fly.(unknow) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° ---(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] Database-wide VACUUM ANALYZE
Steven Flatt writes: Can someone explain what is going on here? I can't quite figure it out based on the docs. Are you on FreeBSD by any chance? I think the FreeBSD port by default installs a script that does a daily vacuum. If using another OS, perhaps you want to see if you used some sort of package system and if that package added a nightly vacuum. ---(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] Database-wide VACUUM ANALYZE
On 6/21/07, Francisco Reyes [EMAIL PROTECTED] wrote: Are you on FreeBSD by any chance? I think the FreeBSD port by default installs a script that does a daily vacuum. Yes, FreeBSD. Do you know what script that is? And it does a db-wide VACUUM ANALYZE every day?! That is certainly not necessary, and in fact, costly for us. Hmmm... I wonder why this would just start now, three days ago. Everything seemed to be normal for the last two weeks. Steve
Re: [PERFORM] Database-wide VACUUM ANALYZE
On Thu, 21 Jun 2007, Steven Flatt wrote: On 6/21/07, Francisco Reyes [EMAIL PROTECTED] wrote: Are you on FreeBSD by any chance? I think the FreeBSD port by default installs a script that does a daily vacuum. Yes, FreeBSD. Do you know what script that is? And it does a db-wide VACUUM ANALYZE every day?! That is certainly not necessary, and in fact, costly for us. Hmmm... I wonder why this would just start now, three days ago. Everything seemed to be normal for the last two weeks. The current FreeBSD port places the script in: /usr/local/etc/periodic/daily/502.pgsql And it can be controlled from /etc/periodic.conf See the top of that script. LER Steve -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 512-248-2683 E-Mail: [EMAIL PROTECTED] US Mail: 430 Valona Loop, Round Rock, TX 78681-3893 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Very long SQL strings
[EMAIL PROTECTED] (Tom Lane) writes: PS: for the record, there is a hard limit at 1GB of query text, owing to restrictions built into palloc. But I think you'd hit other memory limits or performance bottlenecks before that one. It would be much funnier to set a hard limit of 640K of query text. The reasoning should be obvious :-). I once ran into the situation where Slony-I generated a query that made the parser blow out (some sort of memory problem / running out of stack space somewhere thing); it was just short of 640K long, and so we figured that evidently it was wrong to conclude that 640K ought to be enough for anybody. Neil Conway was an observer; he was speculating that, with some (possibly nontrivial) change to the parser, we should have been able to cope with it. The query consisted mostly of a NOT IN clause where the list had some atrocious number of entries in it (all integers). (Aside: I wound up writing a query compressor (now in 1.2) which would read that list and, if it was at all large, try to squeeze any sets of consecutive integers into sets of NOT BETWEEN clauses. Usually, the lists, of XIDs, were more or less consecutive, and frequently, in the cases where the query got to MBs in size, there would be sets of hundreds or even thousands of consecutive integers such that we'd be left with a tiny query after this...) -- select 'cbbrowne' || '@' || 'linuxfinances.info'; http://linuxfinances.info/info/linux.html As of next Monday, MACLISP will no longer support list structure. Please downgrade your programs. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Database-wide VACUUM ANALYZE
In response to Steven Flatt [EMAIL PROTECTED]: On 6/21/07, Francisco Reyes [EMAIL PROTECTED] wrote: Are you on FreeBSD by any chance? I think the FreeBSD port by default installs a script that does a daily vacuum. Yes, FreeBSD. Do you know what script that is? /usr/local/etc/periodic/daily/502.pgsql And it does a db-wide VACUUM ANALYZE every day?! That is certainly not necessary, and in fact, costly for us. You can control it with knobs in /etc/periodic.conf (just like other periodic job): daily_pgsql_vacuum_enable=YES daily_pgsql_backup_enable=NO are the defaults. Hmmm... I wonder why this would just start now, three days ago. Everything seemed to be normal for the last two weeks. Someone alter /etc/periodic.conf? Perhaps it's been running all along but you never noticed it before now? -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/ [EMAIL PROTECTED] Phone: 412-422-3463x4023 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Very long SQL strings
Thanks everyone for your responses. I don't think it's realistic to change our application infrastructure to use COPY from a stream at this point. It's good to know that multi-row-VALUES is good up into the thousands of rows (depending on various things, of course). That's a good enough answer for what I was looking for and we can revisit this if performance does start to hurt. On 6/21/07, Andreas Kretschmer [EMAIL PROTECTED] wrote: I guess you can obtain the same if you pack all INSERTs into one transaction. Well the 20% gain I referred to was when all individual INSERTs were within one transaction. When each INSERT does its own commit, it's significantly slower. Steve
Re: [PERFORM] Database-wide VACUUM ANALYZE
Thanks everyone. It appears that we had hacked the 502.pgsql script for our 8.1 build to disable the daily vacuum. I was not aware of this when building and upgrading to 8.2. So it looks like for the past two weeks, that 36 hour db-wide vacuum has been running every 24 hours. Good for it for being reasonably non-intrusive and going unnoticed until now. :) Although apparently not related anymore, I still think it was a good move to change autovacuum_freeze_max_age from 200 million to 2 billion. Steve
Re: [PERFORM] Very long SQL strings
Chris Browne [EMAIL PROTECTED] writes: I once ran into the situation where Slony-I generated a query that made the parser blow out (some sort of memory problem / running out of stack space somewhere thing); it was just short of 640K long, and so we figured that evidently it was wrong to conclude that 640K ought to be enough for anybody. Neil Conway was an observer; he was speculating that, with some (possibly nontrivial) change to the parser, we should have been able to cope with it. The query consisted mostly of a NOT IN clause where the list had some atrocious number of entries in it (all integers). FWIW, we do seem to have improved that as of 8.2. Assuming your entries were 6-or-so-digit integers, that would have been on the order of 80K entries, and we can manage it --- not amazingly fast, but it doesn't blow out the stack anymore. (Aside: I wound up writing a query compressor (now in 1.2) which would read that list and, if it was at all large, try to squeeze any sets of consecutive integers into sets of NOT BETWEEN clauses. Usually, the lists, of XIDs, were more or less consecutive, and frequently, in the cases where the query got to MBs in size, there would be sets of hundreds or even thousands of consecutive integers such that we'd be left with a tiny query after this...) Probably still a win. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Data transfer very slow when connected via DSL
I wrote: Hello Harald, I do not have a solution, but I can confirm the problem :) At least that rules out any misconfiguration issues :-( I did a quick test with my application and enabled the ODBC logging. Fetching the 50 rows takes 12 seconds (without logging 8 seconds) and examining the log I found what I suspected: the performance is directly related to the ping time to the server since fetching one tuple requires a round trip to the server. Rainer PS: I wonder why pgAdminIII requires twice the time to retrieve the data. ---(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] Data transfer very slow when connected via DSL
Hi Rainer, but did you try to execute your query directly from 'psql' ?... Why I'm asking: seems to me your case is probably just network latency dependent, and what I noticed during last benchmarks with PostgreSQL the SELECT query become very traffic hungry if you are using CURSOR. Program 'psql' is implemented to not use CURSOR by default, so it'll be easy to check if you're meeting this issue or not just by executing your query remotely from 'psql'... Rgds, -Dimitri On 6/21/07, Rainer Bauer [EMAIL PROTECTED] wrote: Hello Tom, I seem to recall that we've seen similar reports before, always involving Windows :-(. Check whether you have any nonstandard components hooking into the network stack on that machine. I just repeated the test by booting into Safe Mode with Network Support, but the results are the same. So I don't think that's the cause. Apart from that, what response times could I expect? Rainer ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(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] Data transfer very slow when connected via DSL
Hello Dimitri, but did you try to execute your query directly from 'psql' ?... munnin=\timing munnin=select * from tblItem; data snipped (50 rows) Time: 391,000 ms Why I'm asking: seems to me your case is probably just network latency dependent, and what I noticed during last benchmarks with PostgreSQL the SELECT query become very traffic hungry if you are using CURSOR. Program 'psql' is implemented to not use CURSOR by default, so it'll be easy to check if you're meeting this issue or not just by executing your query remotely from 'psql'... Yes, see also my other post. Unfortunatelly this means that using my program to connect via DSL to the Postgres database is not possible. Rainer ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Hardware suggestions
Francisco Reyes wrote: Scott Marlowe writes: and a bit more resiliant to drive failure, RAID-5 can give you a lot of storage and very good read performance, so it works well for reporting / New controllers now also have Raid 6, which from the few reports I have seen seems to have a good compromise of performance and space. Very true. And if they've gone to the trouble of implementing RAID-6, they're usually at least halfway decent controllers. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Data transfer very slow when connected via DSL
Let's stay optimist - at least now you know the main source of your problem! :)) Let's see now with CURSOR... Firstly try this: munnin=\timing munnin=\set FETCH_COUNT 1; munnin=select * from tblItem; what's the time you see here? (I think your application is working in this manner) Now, change the FETCH_COUNT to 10, then 50, then 100 - your query execution time should be better (at least I hope so :)) And if it's better - you simply need to modify your FETCH clause with adapted FORWARD # value (the best example is psql source code itself, you may find ExecQueryUsingCursor function implementation (file common.c))... Rgds, -Dimitri On 6/22/07, Rainer Bauer [EMAIL PROTECTED] wrote: Hello Dimitri, but did you try to execute your query directly from 'psql' ?... munnin=\timing munnin=select * from tblItem; data snipped (50 rows) Time: 391,000 ms Why I'm asking: seems to me your case is probably just network latency dependent, and what I noticed during last benchmarks with PostgreSQL the SELECT query become very traffic hungry if you are using CURSOR. Program 'psql' is implemented to not use CURSOR by default, so it'll be easy to check if you're meeting this issue or not just by executing your query remotely from 'psql'... Yes, see also my other post. Unfortunatelly this means that using my program to connect via DSL to the Postgres database is not possible. Rainer ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Performance query about large tables, lots of concurrent access
Andrew Sullivan wrote: On Thu, Jun 21, 2007 at 12:29:49PM -0400, Karl Wright wrote: I checked the disk picture - this is a RAID disk array with 6 drives, with a bit more than 1Tbyte total storage. 15,000 RPM. It would be hard to get more/faster disk than that. What kind of RAID? It's _easy_ to get faster disk that 6 drives in RAID5, even if they're 15,000 RPM. The rotation speed is the least of your problems in many RAID implementations. Also, the controller means a lot. I'd rather have a 4 disk RAID-10 with an Areca card with BBU Cache than a 16 disk RAID 5 on an adaptec (with or without cache... :) ) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Performance query about large tables, lots of concurrent access
Andrew Sullivan wrote: On Thu, Jun 21, 2007 at 12:29:49PM -0400, Karl Wright wrote: I checked the disk picture - this is a RAID disk array with 6 drives, with a bit more than 1Tbyte total storage. 15,000 RPM. It would be hard to get more/faster disk than that. What kind of RAID? It's _easy_ to get faster disk that 6 drives in RAID5, even if they're 15,000 RPM. The rotation speed is the least of your problems in many RAID implementations. Oh, and the driver rev means a lot too. Some older driver revisions for some RAID cards are very slow. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] Data transfer very slow when connected via DSL
Rainer Bauer wrote: Hello Dimitri, but did you try to execute your query directly from 'psql' ?... munnin=\timing munnin=select * from tblItem; data snipped (50 rows) Time: 391,000 ms Why I'm asking: seems to me your case is probably just network latency dependent, and what I noticed during last benchmarks with PostgreSQL the SELECT query become very traffic hungry if you are using CURSOR. Program 'psql' is implemented to not use CURSOR by default, so it'll be easy to check if you're meeting this issue or not just by executing your query remotely from 'psql'... Yes, see also my other post. Unfortunatelly this means that using my program to connect via DSL to the Postgres database is not possible. Note that I'm connected via wireless lan here at work (our wireless lan doesn't connecto to our internal lan directly due to PCI issues) then to our internal network via VPN. We are using Cisco with Cisco's vpn client software. I am running Fedora core 4 on my laptop and I can fetch 10,000 rather chubby rows (a hundred or more bytes) in about 7 seconds. So, postgresql over vpn works fine here. Note, no windows machines were involved in the making of this email. One is doing the job of tossing it on the internet when I hit send though. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Performance query about large tables, lots of concurrent access
Scott Marlowe wrote: Andrew Sullivan wrote: On Thu, Jun 21, 2007 at 12:29:49PM -0400, Karl Wright wrote: I checked the disk picture - this is a RAID disk array with 6 drives, with a bit more than 1Tbyte total storage. 15,000 RPM. It would be hard to get more/faster disk than that. What kind of RAID? It's _easy_ to get faster disk that 6 drives in RAID5, even if they're 15,000 RPM. The rotation speed is the least of your problems in many RAID implementations. Also, the controller means a lot. I'd rather have a 4 disk RAID-10 with an Areca card with BBU Cache than a 16 disk RAID 5 on an adaptec (with or without cache... :) ) Oh come on... Adaptec makes a great skeet. Joshua D. Drake ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(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] PITR Backups
Hi - I'm looking at ways to do clean PITR backups. Currently we're pg_dumping our data in some cases when compressed is about 100GB. Needless to say it's slow and IO intensive on both the host and the backup server. All of our databases are on NetApp storage and I have been looking at SnapMirror (PITR RO copy ) and FlexClone (near instant RW volume replica) for backing up our databases. The problem is because there is no write-suspend or even a 'hot backup mode' for postgres it's very plausible that the database has data in RAM that hasn't been written and will corrupt the data. NetApp suggested that if we do a SnapMirror, we do a couple in succession ( 1s) so should one be corrupt, we try the next one. They said oracle does something similar. Is there a better way to quiesce the database without shutting it down? Some of our databases are doing about 250,000 commits/min. Best Regards, Dan Gorman ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] PITR Backups
Dan Gorman [EMAIL PROTECTED] writes: All of our databases are on NetApp storage and I have been looking at SnapMirror (PITR RO copy ) and FlexClone (near instant RW volume replica) for backing up our databases. The problem is because there is no write-suspend or even a 'hot backup mode' for postgres it's very plausible that the database has data in RAM that hasn't been written and will corrupt the data. I think you need to read the fine manual a bit more closely: http://www.postgresql.org/docs/8.2/static/backup-file.html If the NetApp does provide an instantaneous-snapshot operation then it will work fine; you just have to be sure the snap covers both data and WAL files. Alternatively, you can use a PITR base backup as suggested here: http://www.postgresql.org/docs/8.2/static/continuous-archiving.html In either case, the key point is that you need both the data files and matching WAL files. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Data transfer very slow when connected via DSL
Rainer Bauer [EMAIL PROTECTED] writes: Fetching the 50 rows takes 12 seconds (without logging 8 seconds) and examining the log I found what I suspected: the performance is directly related to the ping time to the server since fetching one tuple requires a round trip to the server. Hm, but surely you can get it to fetch more than one row at once? This previous post says that someone else solved an ODBC performance problem with UseDeclareFetch=1: http://archives.postgresql.org/pgsql-odbc/2006-08/msg00014.php It's not immediately clear why pgAdmin would have the same issue, though, because AFAIK it doesn't rely on ODBC. I just finished looking through our archives for info about Windows-specific network performance problems. There are quite a few threads, but the ones that were solved seem not to bear on your problem (unless the one above does). I found one pretty interesting thread suggesting that the problem was buffer-size dependent: http://archives.postgresql.org/pgsql-performance/2006-12/msg00269.php but that tailed off with no clear resolution. I think we're going to have to get someone to watch the problem with a packet sniffer before we can get much further. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] PITR Backups
Tom Lane wrote: Dan Gorman [EMAIL PROTECTED] writes: All of our databases are on NetApp storage and I have been looking at SnapMirror (PITR RO copy ) and FlexClone (near instant RW volume replica) for backing up our databases. The problem is because there is no write-suspend or even a 'hot backup mode' for postgres it's very plausible that the database has data in RAM that hasn't been written and will corrupt the data. Alternatively, you can use a PITR base backup as suggested here: http://www.postgresql.org/docs/8.2/static/continuous-archiving.html I think Dan's problem is important if we use PostgreSQL to a large size database: - When we take a PITR base backup with hardware level snapshot operation (not filesystem level) which a lot of storage vender provide, the backup data can be corrupted as Dan said. During recovery we can't even read it, especially if meta-data was corrupted. - If we don't use hardware level snapshot operation, it takes long time to take a large backup data, and a lot of full-page-written WAL files are made. So, I think users need a new feature not to write out heap pages during taking a backup. Any comments? Best regards, -- Toru SHIMOGAKI[EMAIL PROTECTED] NTT Open Source Software Center ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] PITR Backups
Toru SHIMOGAKI wrote: Tom Lane wrote: - When we take a PITR base backup with hardware level snapshot operation (not filesystem level) which a lot of storage vender provide, the backup data can be corrupted as Dan said. During recovery we can't even read it, especially if meta-data was corrupted. - If we don't use hardware level snapshot operation, it takes long time to take a large backup data, and a lot of full-page-written WAL files are made. Does it? I have done it with fairly large databases without issue. Joshua D. Drake So, I think users need a new feature not to write out heap pages during taking a backup. Any comments? Best regards, -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] PITR Backups
On Jun 21, 2007, at 7:30 PM, Toru SHIMOGAKI wrote: Tom Lane wrote: Dan Gorman [EMAIL PROTECTED] writes: All of our databases are on NetApp storage and I have been looking at SnapMirror (PITR RO copy ) and FlexClone (near instant RW volume replica) for backing up our databases. The problem is because there is no write-suspend or even a 'hot backup mode' for postgres it's very plausible that the database has data in RAM that hasn't been written and will corrupt the data. Alternatively, you can use a PITR base backup as suggested here: http://www.postgresql.org/docs/8.2/static/continuous-archiving.html I think Dan's problem is important if we use PostgreSQL to a large size database: - When we take a PITR base backup with hardware level snapshot operation (not filesystem level) which a lot of storage vender provide, the backup data can be corrupted as Dan said. During recovery we can't even read it, especially if meta-data was corrupted. I can't see any explanation for how this could happen, other than your hardware vendor is lying about snapshot ability. What problems have you actually seen? Cheers, Steve ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
