Thread: Re: [HACKERS] custom types and optimization
[ CC'ing general list so you can see what we are working on, and my plea for help in getting the word out about PostgreSQL's speed and features. Replies will go the the hackers list because we don't want long discussions like this in the general list.] First, let me say you are thinking exactly like me. I agree 100% with your ideas, and analysis of the issues. > I know that custom types account for a portion of overhead, and I'm > not by any means advocating their removal. I also know that the > efficiency of postgres has improved greatly since the early days, and > I'm wondering what more can be done. Good question, and a question I have been asking myself. > For instance, would it be possible to cache results of the > input/output functions for the types? i.e. if we've already called > foobar_out for a peice of data, why call it again? We could store the > previous result in a hash, and then use that. Not sure if that would help. We cache system tables lookups, and data blocks. gprof does not show a huge problem in the type extensibility area, at least in my tests. gprof is your friend. Try compiling with the options, and run it and analyze gmon.out (See FAQ for info.) That usually tells me quite a bit. > Note that I next to nothing about how the query node tree gets > executed (I'm reading up on it now) so this may not be possible or > could even introduce extra overhead. Also, I hope people are reading the developers FAQ, because I think that can help people get started with coding. > I'd like to get postgres up to speed. I know it is a great database, > and I tell all my friends this, but there is too much pg bashing > because of the early days. People think mysql rocks because it is so > fast, but in reality, well.. It's all IMHO, and the right tool for > the right job. Yes, this has frustrated me too. Why are we not getting better mention from people? I think we can now be classified as the 'most advanced' free database. Can we do something about mentioning that to others? We certainly are growing market share, but i guess I would like to see more transfers from other databases. The highly-biased MySQL comparison page hurts us too, but other people explaining real issues can counter that. > So my real question is: have we hit the limit on optimization and > reduction of overhead, or is there more work to be done? Or should we > concentrate on other aspects such as inheritance issues? I'm not > quite as interested in ANSI compliance. Not sure. I just removed exec(), so that saves us 0.01 on startup, which is pretty major. We can move some of the initialization that is done in every backend to the postmaster, but these will only do major speedups for backends that do startup, short query, exit. Longer queries and long-running backends don't see much change. I have tested the throughput of sequential table scan, and it appears to run pretty quickly, almost as quick as dd on the same file. That is pretty good. Faster than wc on my system. So why are we considered slow? First, historically, performance has not been a major concern, first not at Berkeley(?), and second there were so many other problems, that we did not have the resources to concentrate on it. Only in the past nine months have there been real improvements, and it takes time to get the word out. Second, it is our features that make us slower. Transactions, type system, optimizer all add to the slowness. We are very modular, and have a large call overhead moving in and out of modules, though profiling has enabled us to reduce this. MySQL also has certain limitations that allow them to be faster, like being able to specify indexes ONLY at table creation time, so their indexes are in with the data. They use ISAM, which doesn't grow well, but does provide good performance because the data is kind of pre-sorted on the disk. Our CLUSTER command now does a similar function, without the problems of ISAM. I am glad David Gould and others are involved, because I am starting to run out of tricks to speed things up. I need new ideas and perhaps redesigned modules to get better performance. -- Bruce Momjian | 830 Blythe Avenue maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026 + If your life is a hard drive, | (610) 353-9879(w) + Christ can be your backup. | (610) 853-3000(h)
> I am glad David Gould and others are involved, because I am starting to > run out of tricks to speed things up. I need new ideas and perhaps > redesigned modules to get better performance. Aw shucks guys, you shouldn't have... I haven't even done anything yet. -dg David Gould dg@illustra.com 510.628.3783 or 510.305.9468 Informix Software 300 Lakeside Drive Oakland, CA 94612 - A child of five could understand this! Fetch me a child of five.
On Sun, 31 May 1998, Bruce Momjian wrote:
> Yes, this has frustrated me too. Why are we not getting better mention
> from people? I think we can now be classified as the 'most advanced'
> free database. Can we do something about mentioning that to others? We
> certainly are growing market share, but i guess I would like to see more
> transfers from other databases.
I hate to use myself as an example...but why do I hate Linux? And
why wouldn't I recommend anyone to use it? Past Experience.
When we first took this on, we were *very* problematic. But,
since we considered it to be the best that was out there, we
persevered(sp?) with the problems and improved it overall. There are
bound to be alot that, at the beginning, just didn't want to waste time
with it, saw all the problems and left...taking their bad experience with
them.
My experience is that "bad experiences" are heard more often then
good ones.
Neil built up a 'registration page' that I'm curious as to how
many ppl are actually using it...just checked, and:
postgresql=> select count(name) from register;
count
-----
1361
(1 row)
Not bad...but I don't imagine that's a tenth of all the users, is
it?
On Sun, 31 May 1998, Bruce Momjian wrote: > > and I tell all my friends this, but there is too much pg bashing > > because of the early days. People think mysql rocks because it is so > > fast, but in reality, well.. It's all IMHO, and the right tool for > > the right job. > > Yes, this has frustrated me too. Why are we not getting better mention > from people? I think we can now be classified as the 'most advanced' > free database. Can we do something about mentioning that to others? We > certainly are growing market share, but i guess I would like to see more > transfers from other databases. > > The highly-biased MySQL comparison page hurts us too, but other people > explaining real issues can counter that. I just about had a hard time getting our system admin to install Postgresql. All I ever heard about was MSQL is already installed. I was under the impression that Postgresql was a more full featured SQL server than msql. And besides that, it was what I was able to install on my home MkLinux box, to learn. We're slowly making a switch from Microsoft SQL and Everyware's Butler SQL server to a Linux/Postgresql combo. I've been very pleased. The only problem I haven't been able to fix to date is calling "Dates" from a database and displaying them like "Sunday May 31, 1998" instead "05-31-1998" Currently using PHP2.x not PHP3 yet... Kevin -------------------------------------------------------------------- Kevin Heflin | ShreveNet, Inc. | Ph:318.222.2638 x103 VP/Mac Tech | 333 Texas St #619 | FAX:318.221.6612 kheflin@shreve.net | Shreveport, LA 71101 | http://www.shreve.net --------------------------------------------------------------------
On Sun, 31 May 1998, The Hermit Hacker wrote:
> Neil built up a 'registration page' that I'm curious as to how
> many ppl are actually using it...just checked, and:
>
> postgresql=> select count(name) from register;
> count
> -----
> 1361
> (1 row)
>
> Not bad...but I don't imagine that's a tenth of all the users, is
> it?
Let me know if Camping-USA isn't one of them. I'm getting over 400 hits
per day and without PostgreSQL I'd be lucky to be getting 3 a month.
Mysql wouldn't even compile without spending three weeks upgrading every
single library that was current for FreeBSD at that time (2.0.5 or 2.1.0)
and I never did figure out how many of my kids I'd have to sell to comply
with whatever licensing msql had.
I program with Sybase for a living (among the many other things admins get
to do) and the only thing that I wish were in libpq that isn't in the
Sybase dblibraries is bind. I find it convenient to bind a program
variable to a column and not have to screw with it during the retrieval
process. BUT!! at the same time, libpq has things that dblibrary doesn't
have. psql absolutely blows isql away. I've had to write applications to
give me table definitions that psql is happy to provide. Take note,
however, since it's a pain to upgrade Sybase every time it comes out and
we're stuck with the microsoft <gag> libraries that come with our license,
our upgrade path is slow and although we acquire the upgrade quickly, we
don't necessarily perform the upgrade with the same speed!
Vince.
--
==========================================================================
Vince Vielhaber -- KA8CSH email: vev@michvhf.com flame-mail: /dev/null
# include <std/disclaimers.h> TEAM-OS2
Online Searchable Campground Listings http://www.camping-usa.com
"I'm just not a fan of promoting stupidity!
We have elected officials for that job!" -- Rock
==========================================================================
> On Sun, 31 May 1998, Bruce Momjian wrote:
>
> > Yes, this has frustrated me too. Why are we not getting better mention
> > from people? I think we can now be classified as the 'most advanced'
> > free database. Can we do something about mentioning that to others? We
> > certainly are growing market share, but i guess I would like to see more
> > transfers from other databases.
>
> I hate to use myself as an example...but why do I hate Linux? And
> why wouldn't I recommend anyone to use it? Past Experience.
Ok, why do you hate Linux? I have been using it since 94 and am happier than
a pig in mud. Maybe I am easy to please (doubtful) or maybe I am missing
something? I don't want to start an OS war here (there are enough of those
in other places), so please reply (if you choose to do so) privately.
-dg
David Gould dg@illustra.com 510.628.3783 or 510.305.9468
Informix Software (No, really) 300 Lakeside Drive Oakland, CA 94612
"Of course, someone who knows more about this will correct me if I'm wrong,
and someone who knows less will correct me if I'm right."
--David Palmer (palmer@tybalt.caltech.edu)
On Sun, 31 May 1998, David Gould wrote:
> > On Sun, 31 May 1998, Bruce Momjian wrote:
> >
> > > Yes, this has frustrated me too. Why are we not getting better mention
> > > from people? I think we can now be classified as the 'most advanced'
> > > free database. Can we do something about mentioning that to others? We
> > > certainly are growing market share, but i guess I would like to see more
> > > transfers from other databases.
> >
> > I hate to use myself as an example...but why do I hate Linux? And
> > why wouldn't I recommend anyone to use it? Past Experience.
>
> Ok, why do you hate Linux? I have been using it since 94 and am happier than
> a pig in mud. Maybe I am easy to please (doubtful) or maybe I am missing
> something? I don't want to start an OS war here (there are enough of those
> in other places), so please reply (if you choose to do so) privately.
No no, this wasn't meant to start a flame war...most of the
oldtimers here know of my hatred for Linux, and I've admitted often that
with Linux today, it is pretty unfounded...
I used Linux pre-94...pre-v1.0..in a business/production
environment. At that time, I was hard-core Linux advocate...it was the
greatest thing since sliced bread, but, the day I hoooked it onto the
Internet, keeping it alive more then 24hrs was a chore, and it was all in
the TCP/IP networking code...switched to *BSD and have been here ever
since...
But, that first *bad* experience tends to stick with you, no
matter how good things become over time *shrug* One becomes jaded, such
that when someone asks you what OS (or RDBMS) to use, you tend to
automatically warn against the one that you've personally had bad
experiences with...*shrug*
Again, not a flame war, and not a "you should try it now"...I
have, and even looked at running Linux for a projeect here at the office
(but I found a simpler/better solution)...
On Sun, 31 May 1998, The Hermit Hacker wrote:
> On Sun, 31 May 1998, David Gould wrote:
>
> > > I hate to use myself as an example...but why do I hate Linux? And
> > > why wouldn't I recommend anyone to use it? Past Experience.
> >
> > Ok, why do you hate Linux? I have been using it since 94 and am happier than
> > a pig in mud. Maybe I am easy to please (doubtful) or maybe I am missing
> > something? I don't want to start an OS war here (there are enough of those
> > in other places), so please reply (if you choose to do so) privately.
>
> No no, this wasn't meant to start a flame war...most of the
> oldtimers here know of my hatred for Linux, and I've admitted often that
> with Linux today, it is pretty unfounded...
>
> I used Linux pre-94...pre-v1.0..in a business/production
> environment. At that time, I was hard-core Linux advocate...it was the
> greatest thing since sliced bread, but, the day I hoooked it onto the
> Internet, keeping it alive more then 24hrs was a chore, and it was all in
> the TCP/IP networking code...switched to *BSD and have been here ever
> since...
I'd gone thru similar, but a more recent set of experiences have soured
me. I've seen some out-of-the-box linux installations failing from
overload where the equivelent in a FreeBSD environment wasn't. When I'd
have 200+ users getting their email and a bunch of 'em also getting their
web pages hit, nasty things were hitting the fan. As an admin it's a
problem that eventual tuning wasn't good enuf. When you have PAYING
customers crabbing about the services they're paying for not being up
to their expectations, as a provider you have to answer to 'em. We were
able to get a couple of linux boxen going to meet the need, but our
experiences beyond that were that a FreeBSD box was able to instantly
provide a much higher level of service with a much higher level of
reliability to it for a lower cost (the cost of setting things up is,
of course, figured into the overall cost).
Do I wanna see all of the linux boxen removed? No way! Most of the
security exploits are written to run on a linux platform. I don't need
to waste any time porting an exploit to a FreeBSD machine (no matter how
easy it is) just to make sure my machines aren't vulnerable.
My personal opinion? Use the proper OS for the job at hand. I can come
up with jobs that are best suited to many operating systems. If you want
to choose an operating system that's not up to par with what you need to
do or what your PAYING customer needs? Then you need to rethink your
business strategies. For what I do, OS/2 provides me with the tools I
need. For what my wife does, 95 is her choice. For my news machines
and web servers, it has to be UN*X and currently that platform is FreeBSD.
At work, it's HP-UX. You gotta use the proper tool for the job or you're
only screwing yourself. The way you evaluate the tool is noone's
responsibility but your own. Make the proper decision and you keep your
customers; blow it and someone else gets your customers. Personally I
don't like those all nite panic sessions.
Vince.
--
==========================================================================
Vince Vielhaber -- KA8CSH email: vev@michvhf.com flame-mail: /dev/null
# include <std/disclaimers.h> TEAM-OS2
Online Searchable Campground Listings http://www.camping-usa.com
"I'm just not a fan of promoting stupidity!
We have elected officials for that job!" -- Rock
==========================================================================
Agreed -- my experiences with postgresql have usually been good (except documentation/speed wise) and I'll go near msql (or php for that matter) just because my experiences with them at first were truly painful. On Sun, 31 May 1998, at 19:38:20, The Hermit Hacker wrote: > But, that first *bad* experience tends to stick with you, no > matter how good things become over time *shrug* One becomes jaded, such > that when someone asks you what OS (or RDBMS) to use, you tend to > automatically warn against the one that you've personally had bad > experiences with...*shrug*
I'm "in the process of" preparing a contrib module for this purpose. Real Soon Now. On Sun, 31 May 1998, at 17:45:07, Kevin Heflin wrote: > The only problem I haven't been able to fix to date is calling "Dates" > from a database and displaying them like "Sunday May 31, 1998" instead > "05-31-1998" > > Currently using PHP2.x not PHP3 yet... >
> > On Sun, 31 May 1998, Bruce Momjian wrote: > > > Yes, this has frustrated me too. Why are we not getting better mention > > from people? I think we can now be classified as the 'most advanced' > > free database. Can we do something about mentioning that to others? We > > certainly are growing market share, but i guess I would like to see more > > transfers from other databases. > > I hate to use myself as an example...but why do I hate Linux? And > why wouldn't I recommend anyone to use it? Past Experience. > > When we first took this on, we were *very* problematic. But, > since we considered it to be the best that was out there, we > persevered(sp?) with the problems and improved it overall. There are > bound to be alot that, at the beginning, just didn't want to waste time > with it, saw all the problems and left...taking their bad experience with > them. > > My experience is that "bad experiences" are heard more often then > good ones. > > Neil built up a 'registration page' that I'm curious as to how > many ppl are actually using it...just checked, and: > > postgresql=> select count(name) from register; > count > ----- > 1361 > (1 row) > > Not bad...but I don't imagine that's a tenth of all the users, is > it? Wow, that is a big number, and the 10% is probably correct. I don't think I am even in there. How can we reverse the "bad publicity" and get people to start looking at us again? Users, we need to hear from you on this, and why you chose to use PostgreSQL. We don't need people foaming at the mouth, but we do need our users to give use good visibility and publicity. [Moved to general.] -- Bruce Momjian | 830 Blythe Avenue maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026 + If your life is a hard drive, | (610) 353-9879(w) + Christ can be your backup. | (610) 853-3000(h)
At 7:12 +0300 on 1/6/98, Bruce Momjian wrote: > Users, we need to hear from you on this, and why you chose to use > PostgreSQL. We don't need people foaming at the mouth, but we do need > our users to give use good visibility and publicity. Here is my story: We needed to write some web-based applications, and they needed to rely on a database, as the data stored in them needed something more complex than ndbm. The head of my programming team said PostgreSQL. Our sysadmin insisted on deciding from a list of alternatives. So I set about with three main goals: (1) ANSI compatibility (the more compatibility, the less migration pain in case migration was needed). (2) Support for multiuser access. (3) Interfaces to Perl and Java. I saw the MySQL page. It seemed to be more ANSI compatible. We downloaded it, and then it turned out that MySQL doesn't support transactions. No transactions? That means no multiuser access. We want people to be able to update the database. That immediately classified MySQL as "not a real database", and put us back on the PostgreSQL route, as no other free database was even close to the required feature list. PostgreSQL has all the interfaces we need, it supports transactions and locks, it is becoming more ANSI compatible with every version update, and it seems to perform well enough. Herouth -- Herouth Maoz, Internet developer. Open University of Israel - Telem project http://telem.openu.ac.il/~herutma
On Mon, 1 Jun 1998, Bruce Momjian wrote:
> > postgresql=> select count(name) from register;
> > count
> > -----
> > 1361
> > (1 row)
> >
> > Not bad...but I don't imagine that's a tenth of all the users, is
> > it?
>
> Wow, that is a big number, and the 10% is probably correct. I don't
> think I am even in there.
I know I'm not ;(
> How can we reverse the "bad publicity" and get people to start looking
> at us again?
Testimonials? Not fanatical statements...something intelligent
like what Herouth Maoz wrote? Maybe with a pointer to the project that
he/she used PostgreSQL for? Start up a 'User Comments and Projects'
page...?
How many ppl are actually using the 'Powered by' logo on their
sites? Actually, just looking at that, and am I the only one that finds
it unreadable? I can barely even see that its a 'cat'...