Re: [GENERAL] inheritance vs performance
On Fri, Feb 13, 2004 at 01:51:24PM +, Richard Huxton wrote: > On Friday 13 February 2004 10:59, Karsten Hilbert wrote: > > > Well, thousands of tables is probably "too much", but a hundred tables or > > > two in a database shouldn't cause problems. Don't see why you'd want them > > > though. > > > > If that's your general advice (a hundred or more tables in a > > database not making sense) I should like to learn why. Is that > > a sure sign of overdesign ? Excess normalization ? Bad > > separation of duty ? I am asking since our schema is at > > about 200 relations and growing. > > The original mail mentioned many "C tables" all with the same columns. > Obviously you need as many different tables as required to model your data, > but many tables all with identical schema? Poor mans tablespaces. It's a trick I've had to resort to a few times when on a write-heavy steady-state system there's just not enough I/O bandwidth to delete and vacuum old data, or not enough to maintain an index. Segregate the incoming data by, say, day and put one days worth of data into each 'C' table. At the end of each day, index the days table. If you're maintaining six months of data, drop the 180th table. If most of the queries on the data are constrained by date it's reasonably efficient to search too. And if you have rare queries which aren't constrained by date you can just apply them to the parent table - not terribly efficient, but quite workable. Hideous hack, but it works. Cheers, Steve ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Quad Xeon vs. Dual Itanium
On Fri, Feb 13, 2004 at 12:19:39PM -0500, Bruce Momjian wrote: > 64-bits isn't faster than 32, and can be slower because of the longer > pointer length, decreasing cache performance. The major advantage to > 64-bits is accessing more the 4gb of RAM. I note, however, that all the Sun experts say you should get your database applications optimised for 64 bits because you can ship around more data at a time. I have no clue what they're basing it on, though. A -- Andrew Sullivan | [EMAIL PROTECTED] The plural of anecdote is not data. --Roger Brinner ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] Using NOTIFY... Slow Client Querys
I'm using PostgreSQL 7.4.1. I have 140 clients connected on average using libpq. When one client sends "NOTIFY timeclock;" to the server all 140 clients are listening for it. After receiving a notification from libpq (PQnotifies), each client proceeds to execute a query for the last five records in the timeclock table. SELECT * FROM timeclock ORDER BY touched DESC LIMIT 5; It varies, but it's often the case that clients wait up to 3 minutes before the results come back. This seems like a really long time for a query that I would think would go quickly. In fact, I can execute the same query from a third party client and it runs fine, even while my own client is still waiting for results. Any ideas? It seems to be related to NOTIFY/LISTEN. Thanks! ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] timestamp/date comparison
On Fri, 13 Feb 2004, Campano, Troy wrote: > Hi, > I'm trying to compare a timestamp to current_timestamp but I'm having > trouble. > I want to compare just the date piece of my timestamp column to just the > date piece of current_timestamp. > > I'm getting weird results that I don't understand. > When I use TO_DATE it changes the year, month, etc. I think you probably don't want to_date in any case. CAST(whatever AS DATE) is probably better. The to_date way probably is taking the timestamp converting it to text and then attempting to convert the text back. > anna=> SELECT request_date,TO_DATE(request_date,'-MM-DD HH24:MM:SS') Here, you're using the minutes as month information I think. > anna=> SELECT > TO_DATE(request_date,'MM/DD/'),TO_DATE(current_timestamp,'MM/DD/ > ') FROM anna_onestop_database_t WHERE TO_DATE(request_date,'MM/DD/') > > TO_DATE(current_timestamp,'MM/DD/'); I'm not sure why this is working at all, but using the standard output format for a timestamp, it doesn't follow the form MM/DD/ I believe, so the format string doesn't really line up with the data. ---(end of broadcast)--- TIP 3: 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
[GENERAL] client IP address
Does Postgres provide a builtin mechanism to obtain the IP address of the client that is executing the current query? Thanks, Iker ---(end of broadcast)--- TIP 3: 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
[GENERAL] resource monitoring
I am running a few web based applications with postgres on the backend. We have a few app servers load balanced all connecting to a dedicated postgres server. As usage on the applications increases I want to monitor my resources so that I can anticipate when I will hit bottlenecks on the db server. That way we can do upgrades or optimizations before our performance get's unacceptable. We are monitoring cpu usage, memory usage, and network traffic. I would also like to monitor io utilization but am not quite sure how to do that. Does anyone here know to effetively monitor io to the raid (I'm guessing that this could be measured with the raid management software but was hoping their were some standard system commands.) or other io that I should be measuring? What bottlenecks could I hit and what are some good stats to check to anticipate when I am moving towards one of those bottlenecks. Thanks, Rick ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] resource monitoring
Rick Gigger wrote: I am running a few web based applications with postgres on the backend. We have a few app servers load balanced all connecting to a dedicated postgres server. As usage on the applications increases I want to monitor my resources so that I can anticipate when I will hit bottlenecks on the db server. That way we can do upgrades or optimizations before our performance get's unacceptable. We are monitoring cpu usage, memory usage, and network traffic. I would also like to monitor io utilization but am not quite sure how to do that. Does anyone here know to effetively monitor io to the raid (I'm guessing that this could be measured with the raid management software but was hoping their were some standard system commands.) or other io that I should be measuring? What bottlenecks could I hit and what are some good stats to check to anticipate when I am moving towards one of those bottlenecks. Thanks, Rick ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html I use top to measure cpu and memory usage, vmstat to measure disk io, and pg_stat_activity to see which query is hogging the cpu, memory or io. We don't use raid so I'm not sure how vmstat deals with that. So your mileage may vary :-) Ron ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] How to determine current database?
I am using postgres 7.3.4 and need to be able to determine which database a query is being run in (from a script). pg_database lists databases but doesn't tell me which one is currently active. Is there a query I can use along the lines of: UPDATE tblUpdates SET xxx=1234 WHERE pg_current = TRUE; or UPDATE tblUpdates SET xxx=1234 WHERE pg_current = thisDBname; We have about 15 databases all set up identically and when the structure changes I run scripts to update them to ensure that they are all the same. I need to create two new databases which have slight changes (different default values - for now) and want to be able to have my scripts be able to determine which database their running from. Thanks Ron ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] How to determine current database?
On Feb 13, 2004, at 6:05 PM, Ron St-Pierre wrote: I am using postgres 7.3.4 and need to be able to determine which database a query is being run in (from a script). pg_database lists databases but doesn't tell me which one is currently active. Is there a query I can use along the lines of: The built-in function "current_database()" returns the current database name. =# select current_database(); current_database -- testing (1 row) Use it in your update statements too. eric UPDATE tblUpdates SET xxx=1234 WHERE pg_current = TRUE; or UPDATE tblUpdates SET xxx=1234 WHERE pg_current = thisDBname; We have about 15 databases all set up identically and when the structure changes I run scripts to update them to ensure that they are all the same. I need to create two new databases which have slight changes (different default values - for now) and want to be able to have my scripts be able to determine which database their running from. Thanks Ron ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] How to determine current database?
Ron St-Pierre wrote: I am using postgres 7.3.4 and need to be able to determine which database a query is being run in (from a script). pg_database lists databases but doesn't tell me which one is currently active. See: http://www.postgresql.org/docs/7.3/static/functions-misc.html HTH, Joe ---(end of broadcast)--- TIP 3: 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: [GENERAL] How to determine current database?
Eric Ridge wrote: On Feb 13, 2004, at 6:05 PM, Ron St-Pierre wrote: I am using postgres 7.3.4 and need to be able to determine which database a query is being run in (from a script). pg_database lists databases but doesn't tell me which one is currently active. Is there a query I can use along the lines of: The built-in function "current_database()" returns the current database name. =# select current_database(); current_database -- testing (1 row) Use it in your update statements too. eric That's exactly what I need! Joe Conway wrote: See: http://www.postgresql.org/docs/7.3/static/functions-misc.html Thanks guys. I was searching the docs looking for functions starting with pg_ Ron ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Quad Xeon vs. Dual Itanium
Wouldn't you only care about 64-bit Postgres if you wanted to make shared_buffers bigger than 4G? Various other posters have commented about the sweet-spot for shared_buffers being ~ 100-200M (or thereabouts). So it seems to me that there is nothing to be gained using a 64-bit binary with the current or previous Pg releases. However, with the new cache replacement system being used in 7.5devel, the situation *may* be different (wonder if anyone has tried this out yet?). regards Mark Andrew Sullivan wrote: On Mon, Feb 09, 2004 at 12:46:58PM -0500, Christopher Browne wrote: Lots of people have been running it on 64 bit systems for _years_ now. The Digital Alpha architecture, for instance, was introduced in the 1992, and Sun UltraSPARC in 1995. PostgreSQL has been running well on these sorts of systems for a lot of years now. But actually, there are problems with using postgres as a 64 bit application on Solaris. It works, and it's reliable, but I've never seen any evidence that it helps anything (and I've looked plenty). A ---(end of broadcast)--- TIP 3: 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: [GENERAL] Quad Xeon vs. Dual Itanium
> -Original Message- > From: Mark Kirkwood [mailto:[EMAIL PROTECTED] > Sent: Friday, February 13, 2004 5:30 PM > To: Andrew Sullivan > Cc: [EMAIL PROTECTED] > Subject: Re: [GENERAL] Quad Xeon vs. Dual Itanium > > > Wouldn't you only care about 64-bit Postgres if you wanted to make > shared_buffers bigger than 4G? > > Various other posters have commented about the sweet-spot for > shared_buffers being ~ 100-200M (or thereabouts). > > So it seems to me that there is nothing to be gained using a 64-bit > binary with the current or previous Pg releases. However, > with the new > cache replacement system being used in 7.5devel, the > situation *may* be > different (wonder if anyone has tried this out yet?). Where 64 bits matters (in general -- not restricted to PG database systems): Size of the database is huge (e.g. every toll paid in New Jersey in the last 5 years) Available memory is huge (e.g. you buy a machine with 24 gigs of ram) Data bus bandwidth is huge (e.g. You buy an 8-way Opteron with 40 GB/sec bandwidth) The 32 bit machines cannot compete in these arenas. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Quad Xeon vs. Dual Itanium
Mark Kirkwood <[EMAIL PROTECTED]> writes: > So it seems to me that there is nothing to be gained using a 64-bit > binary with the current or previous Pg releases. However, with the new > cache replacement system being used in 7.5devel, the situation *may* be > different (wonder if anyone has tried this out yet?). Quite honestly, I suspect we may be wasting our time hacking the Postgres buffer replacement algorithm at all. There are a bunch of reasons why the PG shared buffer arena should never be more than a small fraction of physical RAM, and under those conditions the cache replacement algorithm that will matter is the kernel's, not ours. I stand ready to be proven wrong, of course ... regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Quad Xeon vs. Dual Itanium
No disagreement from me about the 64-bit *hardware* and *os*... Now suppose you want to run a Pg database for such a situation may as well compile 32-bit. Why ? well you *dont* want to set shared_buffers to 20G... in fact 200M works better - why ? well your 64-bit os file cache is much more efficient at using your 24G or RAM than Pg's buffer cache logic is (at the moment anyway). regards Mark Dann Corbit wrote: Where 64 bits matters (in general -- not restricted to PG database systems): Size of the database is huge (e.g. every toll paid in New Jersey in the last 5 years) Available memory is huge (e.g. you buy a machine with 24 gigs of ram) Data bus bandwidth is huge (e.g. You buy an 8-way Opteron with 40 GB/sec bandwidth) The 32 bit machines cannot compete in these arenas. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Quad Xeon vs. Dual Itanium
Should have mentioned : assuming you are on a platform where you *have* a choice about compilation word-length! (Solaris and ?) Mark Kirkwood wrote: Now suppose you want to run a Pg database for such a situation may as well compile 32-bit. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Quad Xeon vs. Dual Itanium
On Fri, Feb 13, 2004 at 06:11:08PM -0800, Dann Corbit wrote: > > Size of the database is huge (e.g. every toll paid in New Jersey in the > last 5 years) > Available memory is huge (e.g. you buy a machine with 24 gigs of ram) > Data bus bandwidth is huge (e.g. You buy an 8-way Opteron with 40 GB/sec > bandwidth) > > The 32 bit machines cannot compete in these arenas. I'm not supporting immense databases with this, but I am using 8- and 10- way UltraSPARC II boxes with 16 G of ram. I've been unable to show a difference. There might _be_ one, mind, I just haven't shown it. 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: Don't 'kill -9' the postmaster
Re: [GENERAL] Quad Xeon vs. Dual Itanium
On Fri, Feb 13, 2004 at 10:46:18PM -0500, Tom Lane wrote: > Quite honestly, I suspect we may be wasting our time hacking the > Postgres buffer replacement algorithm at all. There are a bunch of > reasons why the PG shared buffer arena should never be more than a > small fraction of physical RAM, and under those conditions the cache > replacement algorithm that will matter is the kernel's, not ours. Well, unless the Postgres cache is more efficient than the OS's, no?. You could then use the nocache filesystem option, and just let Postgres handle the whole thing. Of course, that's a pretty big unless, and not one that I'm volunteering to make go away! A -- Andrew Sullivan ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Quad Xeon vs. Dual Itanium
> -Original Message- > From: Andrew Sullivan [mailto:[EMAIL PROTECTED] > Sent: Friday, February 13, 2004 9:05 PM > To: [EMAIL PROTECTED] > Subject: Re: [GENERAL] Quad Xeon vs. Dual Itanium > > > On Fri, Feb 13, 2004 at 10:46:18PM -0500, Tom Lane wrote: > > > Quite honestly, I suspect we may be wasting our time hacking the > > Postgres buffer replacement algorithm at all. There are a bunch of > > reasons why the PG shared buffer arena should never be more than a > > small fraction of physical RAM, and under those conditions > the cache > > replacement algorithm that will matter is the kernel's, not ours. > > Well, unless the Postgres cache is more efficient than the OS's, no?. > You could then use the nocache filesystem option, and just > let Postgres handle the whole thing. Of course, that's a > pretty big unless, and not one that I'm volunteering to make go away! Most database systems I have tried scale very well with increased memory. For instance, Oracle, and SQL*Server will definitely benefit greatly by adding more memory. I suspect (therefore) that there must be some way to squeeze some benefit out of it. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
