Re: [GENERAL] vacuum and 24/7 uptime
On Wed, Jul 11, 2001 at 03:55:46PM -0600, Mark wrote: : : We increased shared memory in the linux kernel, which decreased the vacuumdb : time from 40 minutes to 14 minutes on a 450 mhz processor. We calculate that : on our dual 1ghz box with ghz ethernet san connection this will go down to : under 5 minutes. This is acceptable to us. Sorry about the unnecessary post. The other thing you can do is run vacuum more frequently. The less it has to do, the less time it takes (it appears). We run vacuum on an active system every half-hour with little to no delay (the tables aren't very big, though, like 1 or 10 rows, depending on the table). We also recreate the indices on the most frequent table every 12 hours, since vacuum doesn't clean up after indices (again, it appears). Of course, we're still looking forward to 7.2 where some of this will hopefully be unnecessary. * Philip Molter * DataFoundry.net * http://www.datafoundry.net/ * [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] Performance tuning for linux, 1GB RAM, dual CPU?
On Tue, Jul 10, 2001 at 07:44:34AM -0400, Adam Manock wrote: : Hi, : : I am about to put a 7.1.2 server into production on RedHat 7.1 : The server will be dedicated to PostgreSQL, running a bare minimum of : additional services. : If anyone has already tuned the configurable parameters on a dual PIII w/ : 1GB RAM then I : will have a great place to start for my performance tuning! : When I'm done I'll be posting my results here for the next first timer that : comes along. I have a similar system. It's a dual PII-450MHz Xeon with 512MB of RAM running RH7.1 and 7.1.2. As far as performance tuning goes, here's the relevant lines from the postgresql.conf file we're using: max_connections = 64 # 1-1024 sort_mem = 8192 shared_buffers = 192 fsync = false Obviously, depending on your needs, you can adjust those. If you've got a 1GB of RAM, I'd set everything high and not worry about it. * Philip Molter * DataFoundry.net * http://www.datafoundry.net/ * [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [GENERAL] HUPing a database
On Fri, Jul 06, 2001 at 06:15:47PM +0200, Peter Eisentraut wrote: : Philip Molter writes: : : > I'm doing logging of PG information, and I'd like to rotate the logs. : > What's the proper way to HUP the server once the logs have been : > rotated? : : There isn't one. You're better off piping the postmaster output through a : separate rotate program. Hrmm, are there plans to implement this feature? That seems like an *EXTREMELY* large oversight, to not allow for a simple HUP for something as universal as daily log rotation. * Philip Molter * DataFoundry.net * http://www.datafoundry.net/ * [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Changing optimizations
On Thu, Jul 05, 2001 at 12:05:52PM -0400, Tom Lane wrote: : Hmm. Nothing out of the ordinary about the table schemas. The relevant : bits of the query seem to be : : > FROM percept p : > INNER JOIN perceptType pt ON pt.ptid=p.ptid : >AND pt.runinterval IS NOT NULL : : >WHERE p.deleted=0 AND UNIX_TIMESTAMP( p.nextrun )<=NOW() AND : > pt.runinterval IS NOT NULL AND p.running=0 AND h.active=1 : : What seems to be happening is that as you repeatedly VACUUM ANALYZE, : the statistics shift causing a shift in the estimated number of percept : rows that match the WHERE clauses. As that estimate rises, you get a : change in the selected plan types for the later joins, in a direction : that isn't favorable if the correct number of rows is small. Well the rowcount is always small (or should be; less than 15 rows per return). p.nextrun constantly adjusts to a time within the next 5 minutes and p.running constantly shifts between one and zero, but the other fields (deleted, runinterval, active, etc.) stay the same. Now, it could be that it's having a problem with the running or nextrun, but that should only affect approximately 750 of the rows. Certainly the query optimizer should never have to worry about the other 8500. Furthermore, at any moment, the number of rows matching any of those fields should be about the same. If it wasn't, I'd see problems elsewhere. : But it seems odd that you'd get a factor-of-100 change in that estimate : if the true underlying data distribution isn't changing much. Could : you keep track of the results of these two queries: : : select * from pg_class where relname = 'percept'; : : select attname,attdispersion,s.* : from pg_statistic s, pg_attribute a, pg_class c : where starelid = c.oid and attrelid = c.oid and staattnum = attnum : and relname = 'percept'; Is there anything I should specifically be looking for? I'd assume that attdispersion should stay relatively constant if the data distribution doesn't change much, but I'm not really familiar with how the system tables factor into the optimizations. : Another possibility is that what looks to be the same bottom-level join : plan isn't really the same, but is using different restriction/join : clauses for some weird reason. It would be good to look at EXPLAIN : VERBOSE output not just EXPLAIN output for the two plans, just to rule : that out. My brain hurts when I do that (literally thousands of lines of output). Thanks for the help, Philip * Philip Molter * DataFoundry.net * http://www.datafoundry.net/ * [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [GENERAL] Determining scan types
On Tue, Jul 03, 2001 at 05:12:43PM +0100, Richard Huxton wrote: : VACUUM ANALYZE frequency depends on numbers of updates. I believe someone : has been looking at a way of doing this in the background. Oh yeah, definitely depends on updates, or rather, changes to the table contents (insertions, deletions). : For the purposes of setting SEQSCAN try something like: : : SET ENABLE_SEQSCAN TO OFF; : : Can't remember if it applies to this transaction or this connection. Run a : grep on the docs - you'll only find a couple of hits. Well, I turned it off for the entire database (since right now, we're only using the db for this one application), but I lose the benefit of seqscans in situations where it's appropriate. That's why I was wondering if there's anyway to tell the optimizer to prefer index scans over sequential scans when it has a choice. Right now, it's using less efficient joining methods where it would normally use sequential scans (not that I'm complaining too much; CPU performance has more than doubled since taking out sequential scans). * Philip Molter * DataFoundry.net * http://www.datafoundry.net/ * [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] Determining scan types
On Tue, Jul 03, 2001 at 10:42:37AM -0400, Sam Tregar wrote: : On Tue, 3 Jul 2001, Philip Molter wrote: : : > What causes this and how can I fix it? : : Have you tried a VACUUM ANALYZE? For some reason Postgres isn't able to : use its indexes fully unless you VACUUM ANALYZE often. : : I consider this a bug but I gather the developers are OK with it. Yes. In fact, I have to VACUUM ANALYZE the tables every half hour on this constantly running system or else kernel CPU usage rises to unacceptable levels (another thing I consider a bug). Like I said, in the middle of the night (probably after one of these analyses), it switched from using index scans where appropriate to using sequential scans for everything. If I turn off sequential scans altogether, it uses the indices, but I don't get the performance of benefits of using sequential scans when appropriate. * Philip Molter * DataFoundry.net * http://www.datafoundry.net/ * [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[GENERAL] Determining scan types
Is there any way to force the query optimizer to prefer one scan type over another? I have a rather large query which the optimizer sometime last night decided to switch from using index scans in a variety of places to using sequential scans. This has resulted in a doubling of CPU usage and weird behavior where the number of rows the optimizer thinks it has to search through is rising slowly, even though new rows aren't being entered into any of the tables it's searching through. What causes this and how can I fix it? The query has not changed at all. The data in the rows have changed, but the just values, not the number of rows nor the indexing of the tables. It's baffling. All tables being joined (10) are being explicitly joined using INNER JOIN or LEFT JOIN in an order that should (at least it was) making optimal use of the indexing. * Philip Molter * DataFoundry.net * http://www.datafoundry.net/ * [EMAIL PROTECTED] ---(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] Re: Red Hat to support PostgreSQL
On Wed, Jun 27, 2001 at 06:58:18PM -0400, Bruce Momjian wrote: : My guess on this one is that Solaris is slower for PostgreSQL because : process switching is _much_ heavier on Solaris than other OS's. This is : because of the way they implemented processes in SVr4. They got quite : heavy, almost requiring kernel threads so you weren't switching : processes all the time. : : In a sense threads were a solution to a process bloating problem. : Linux/BSD have much lighter processes and hence work better for : PostgreSQL. Again, this is only a guess. : : MySQL does more stuff with threads while PostgreSQL switches process : because each backend is a process. Does more stuff with threads? It does all stuff with threads. Your guess was our guess, which is why we tried shoving the thing over to a Linux box. Now if I only I could figure out why kernel CPU usage keeps going up incrementally over time (went from roughly a 5% average to a 16% average in two days) the more we run the system. All signs are pointing to postgres. VACUUM ANALYZE-ing the tables used to reduce it back down, but now, it doesn't appear to be as effective (might go from 16% back down to 13%). Anyone know what causes that, and better yet, anyone know how to fix it? We see similar behavior under Solaris. * Philip Molter * DataFoundry.net * http://www.datafoundry.net/ * [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Re: Red Hat to support PostgreSQL
On Wed, Jun 27, 2001 at 05:03:33PM -0400, Lamar Owen wrote: : I think most people that say they'd not run RHL either simply don't like : Linux or just don't like Red Hat. Nothing different in this than the : attitude of MySQL users who just simply don't like PostgreSQL. Or they've : heard that Postgres95 1.01 was a dog, so they won't use PostgreSQL 7.1.2. : The same comparison holds for Red Hat -- the number of possible reasons to : not use it in a production, 24x7, high-load environment are shrinking with : every release. Well, to defend some of those people, we're writing a very database intensive app that's attempting to be SQL agnostic. For the most part it works with MySQL and Postgres both (one or two minor hacks in the database abstraction layer to support features that are just *too* different to code around). When we run this system under Solaris x86, MySQL kicks the pants off Postgres using the same data. When we switched that over to an identical box running Linux (it's RH7.1, but really, it's the kernel and underlying system that matter), Postgres runs much better than both the Solaris MySQL and Postgres installs with the same data and code. I had almost given up on using Postgres for this system because under Solaris, it just couldn't cut it (MySQL could do the work with one CPU while Postgres took up even more CPU and required *both* CPUs to be enabled), but when we moved the system to a Linux box, things worked much better. Go figure. I'm sure that many people's attitudes about RH are the same way. Older versions of RedHat just felt really bloated and slow. RH7.1 feels much tighter, but if I had been turned off by older versions, I never would've tried it. * Philip Molter * DataFoundry.net * http://www.datafoundry.net/ * [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [GENERAL] Weird error
On Wed, Jun 27, 2001 at 11:30:54AM -0400, Tom Lane wrote: : Philip Molter <[EMAIL PROTECTED]> writes: : > I am using 7.1.2. : : Don't suppose you want to dig in there with a debugger when it happens? : You must be seeing some hard-to-replicate problem in VACUUM's : tuple-chain-moving logic. That stuff is pretty hairy, and I doubt : anyone will be able to intuit what's wrong without close examination : of a failure case. Well, considering that we're pushing this into production and the server was installed from Rawhide RPMs, no, not really. :) Reproducing the RedHat install locations for this stuff is a pain in the ass. However, considering that it's not consistent and not continuous, I can work around it. In the meantime, I'll try to get some detailed logging so that perhaps I can get a good look at what goes on during a failure case. * Philip Molter * DataFoundry.net * http://www.datafoundry.net/ * [EMAIL PROTECTED] ---(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] Adding a NOT NULL column?
So I can add a column like: ALTER TABLE t ADD c SMALLINT and I can then set it's default value like: ALTER TABLE t ALTER c SET DEFAULT 0 but how do set that new column to be NOT NULL? The postgres docs say do something like: ALTER TABLE t ADD (table constraint definition) so I would do that like: ALTER TABLE ADD c NOT NULL ALTER TABLE ADD CONSTRAINT c NOT NULL each time it fails on the 'NOT'. How do I add that constraint? * Philip Molter * DataFoundry.net * http://www.datafoundry.net/ * [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
