[PERFORM] Joining views disables indexes?
I have a client that is testing an internal data platform, and they were happy with PostgreSQL until they tried to join views - at that time they discovered PostgreSQL was not using the indexes, and the queries took 24 hours to execute as a result. Is this a known issue, or is this possibly a site-specific problem? They just implemented the exact same datamodel in MySQL 5.0, with views and InnoDB tables, and performance is still subsecond. Would love to know if this is a known issue. -- Mitch ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] PostgreSQL is extremely slow on Windows
On Tue, 22 Feb 2005 16:00:59 +0100, Vig, Sandor (G/FI-2) <[EMAIL PROTECTED]> wrote: > > > Hi, > > I've downloaded the latest release (PostgreSQL 8.0) for windows. > Installation was OK, but I have tried to restore a database. > It had more than ~100.000 records. Usually I use PostgreSQL > under Linux, and it used to be done under 10 minutes. > > Under W2k und XP it took 3 hours(!) Why is it so slow Can you tell us your postgresql.conf configuration settings? We cannot help without some information about your environment... -- Mitch ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Benchmark
On Fri, 11 Feb 2005 01:38:13 -0500, Tom Lane <[EMAIL PROTECTED]> wrote:
>
> If Oracle doesn't eat your rear for lunch,
That would be more like an appetizer at a california cuisine place.
> it would only be because you
> hadn't annoyed them sufficiently for them to bother. Under the terms of
> the license agreement that you presumably clicked through, you gave up
> your rights to publish anything they don't like. Do a little Google
> research. For instance
> http://www.infoworld.com/articles/op/xml/01/04/16/010416opfoster.html
I did do the research, but couldn't find one instance where someone
was actually taken to task over it. So far it appears to be bluster.
Horrifying to some, but still bluster.
> The impression I get is that if you are willing to spend lots of $$
> you could *maybe* win the case, if you can still find a judge who thinks
> that the public good outweighs private contract law (good luck, with the
> Republicans in office). Do you have a larger budget for legal issues
> than Oracle does? If so, step right up.
The reason I asked is because this has a lot more to do with than just
money. This is restriction of speech as well, and publishing
benchmarks (simply as statistical data) cannot in any way be construed
as defamation or libel. Just because it is in the click-wrap contract
doesn't mean you waive certain rights, and this has been proven (and
now has precedence). Again, I would love to know of any instances
where someone published (forbidden) benchmarks and was actually
pursued in a court of law. Well, and the result, too ;-)
I ask not to cause trouble, but to learn if this is just a deterrent
that has never been tested ("small pebble") or a well-defined threat
that will be enforced ("plasma cannon").
-- Mitch, thinking this is off topic but still fascinating
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Benchmark
On Thu, 10 Feb 2005 08:21:09 -0500, Jeff <[EMAIL PROTECTED]> wrote: > > If you plan on making your results public be very careful with the > license agreements on the other db's. I know Oracle forbids the > release of benchmark numbers without their approval. ...as all of the other commercial databases do. This may be off-topic, but has anyone actually suffered any consequences of a published benchmark without permission? For example, I am a developer of Mambo, a PHP-based CMS application, and am porting the mysql functions to ADOdb so I can use grown-up databases ;-) What is keeping me from running a copy of Mambo on a donated server for testing and performance measures (including the commercial databases) and then publishing the results based on Mambo's performance on each? It would be really useful to know if anyone has ever been punished for doing this, as IANAL but that restriction is going to be very, VERY difficult to back up in court without precedence. Is this just a deterrent, or is it real? -- Mitch ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] SQL Performance Guidelines
On Thu, 27 Jan 2005 00:02:29 -0800, Dustin Sallings wrote: > > On Jan 26, 2005, at 10:27, Van Ingen, Lane wrote: > > > Clarification: I am talking about SQL coding practices in Postgres > > (how to write queries for best > > results), not tuning-related considerations (although that would be > > welcomed too). > > Your question is a bit too vague. At this point in your development, > all that really can be said is to understand relational database > concepts in general, and use explain a lot when developing queries. > (Oh, and don't forget to analyze before asking specific questions). I disagree - there are plenty of tricks that are PostgreSQL only, and many people on this list have that knowledge but it is not documented anywhere, or is hidden within thousands of mailing list posts. For example, IIRC when joining an integer column with a SERIAL column, you must expicitly cast it as an integer or the planner will not use the indexes, right? (This is a guess, as I remember reading something like this and thinking, "How in the world is someone supposed to figure that out, even with EXPLAIN?") There is another thread about how a query using a WHERE NOT NULL clause is faster than one without. These things are PostgreSQL specific, and documenting them would go a long way towards educating the switchover crowd. The closest thing I have seen to this is the PostgreSQL Gotchas page: http://sql-info.de/postgresql/postgres-gotchas.html HTH, -- Mitch ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] PG versus FreeBSD, startup and connections problems
Just a quick shout-out to Mark, as you provided the winning answer. I found numerous mailing list discussions and web pages, but all were either fragmented or out of date. Again, many thanks! -- Mitch On Wed, 26 Jan 2005 10:08:58 +1300, Mark Kirkwood <[EMAIL PROTECTED]> wrote: > > in /etc/sysctl.conf : > kern.ipc.shmmax=1 > kern.ipc.shmall=32768 > (can be set online using systcl -w) > > Semaphores need to be set in /boot/loader.conf > kern.ipc.semmni=256 > kern.ipc.semmns=256 > (can typed at the loader prompt using set) > > These settings should let you have ~100 connections and use about 100M > of shared memory for shared_buffers. ---(end of broadcast)--- TIP 8: explain analyze is your friend
[PERFORM] PG versus FreeBSD, startup and connections problems
Hi gang, I just inherited a FreeBSD box, and it is horribly sick. So we moved everything to a new machine (power supply failures) and finally got stuff running again. Ok, for two days (rimshot) Here are the two problems, and for the life of me I cannot find any documentation on either: 1) freebsd will only let PostgreSQL have 38 connections at a time, regardless of kernel settings or postgresql.conf settings. Where exactly (and how, exactly) does one remedy that problem? 2) As of this morning, the machine was down again, this time apache fired up normally but pg refuses to start - without errors. When I start with postmaster on the CLI I also get no errors, just no postmaster. Why am I not seeing the errors, is this a FreeBSD or PostgreSQL issue? For example: $ pg_ctl start postmaster successfully started $ pg_ctl status pg_ctl: postmaster or postgres not running OR: $ postmaster -D /usr/local/pgsql/data $ (no response) There are no errors in /var/log/pgsql either, so I have absolutely no idea how to troubleshoot :-( -- Mitch ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] PostgreSQL clustering VS MySQL clustering
On Thu, 20 Jan 2005 09:33:42 -0800, Darcy Buskermolen <[EMAIL PROTECTED]> wrote: > > Another Option to consider would be pgmemcache. that way you just build the > farm out of lots of large memory, diskless boxes for keeping the whole > database in memory in the whole cluster. More information on it can be found > at: http://people.freebsd.org/~seanc/pgmemcache/ Which brings up another question: why not just cluster at the hardware layer? Get an external fiberchannel array, and cluster a bunch of dual Opterons, all sharing that storage. In that sense you would be getting one big PostgreSQL 'image' running across all of the servers. Or is that idea too 90's? ;-) -- Mitch ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Hardware purchase question
You are right, I now remember that setup was originally called "RAID 10 plus 1", and I believe is was an incorrect statement from an overzealous salesman ;-) Thanks for the clarification! - Mitch On Mon, 03 Jan 2005 15:19:04 -0500, Madison Kelly <[EMAIL PROTECTED]> wrote: > Madison Kelly wrote: > > Nope, Raid 10 (one zero) is a mirror is stripes, no parity. with r10 > > Woops, that should be "mirror of stripes". > > By the way, what you are thinking of is possible, it would be 51 (five > one; a raid 5 built on mirrors) or 15 (a mirror of raid 5 arrays). > Always be careful, 10 and 01 are also not the same. You want to think > carefully about what you want out of your array before building it. ---(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: [PERFORM] Hardware purchase question
On Mon, 13 Dec 2004 09:23:13 -0800, Joshua D. Drake <[EMAIL PROTECTED]> wrote: > > RAID 10 will typically always outperform RAID 5 with the same HD config. Isn't RAID10 just RAID5 mirrored? How does that speed up performance? Or am I missing something? -- Mitch ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PERFORM] Caching of Queries
On Mon, 27 Sep 2004 18:20:48 +0100, Matt Clark <[EMAIL PROTECTED]> wrote: > This is very true. Client side caching is an enormous win for apps, but it > requires quite a lot of logic, triggers to update last-modified fields on > relevant tables, etc etc. Moving some of this logic to the DB would perhaps > not usually be quite as efficient as a bespoke client caching solution, but > it will above all be a lot easier for the application developer! In the world of PHP it is trivial thanks to PEAR's Cache_Lite. The project lead for Mambo implemented page-level caching in a day, and had all the triggers for clearing the cache included in the content management interface - not difficult at all. Basically you set a default in seconds for the HTML results to be cached, and then have triggers set that force the cache to regenerate (whenever CRUD happens to the content, for example). Can't speak for Perl/Python/Ruby/.Net/Java, but Cache_Lite sure made a believer out of me! -- Mitch ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
