Thread: Can PostGreSQL handle 100 user database - more info
Thanks for responding, I suppose what I was really asking is if PostGreSQL works 'as good' as other databases like Oracle. The rumour I heard implied that I would have problems trying to run PostGreSQL with a lot of clients As for the queries: I have a 6000 record customer file and a bigger file containing orders The VB programs randomly retrieved records with a like clause on the first letter of the customer name E.G. B%, F%, Z%. This SQL also had a join to the order file and returned a few hundered records at a time The slow SQL I entered simply returned the MAX value for a column in the order file. This did seem to lock things down, perhaps p-sql was just being cautious or maybe it just slowed everything down quite a lot? Thanks a lot, M Chantler -- NOTICE: The information contained in this electronic mail transmission is intended by Convergys Corporation for the use of the named individual or entity to which it is directed and may contain information that is privileged or otherwise confidential. If you have received this electronic mail transmission in error, please delete it from your system without copying or forwarding it, and notify the sender of the error by reply email or by telephone (collect), so that the sender's address records can be corrected.
martin.chantler@convergys.com schrieb:
> Thanks for responding,
>
> I suppose what I was really asking is if PostGreSQL works 'as good' as
> other databases like Oracle. The rumour I heard implied that I would have
> problems trying to run PostGreSQL with a lot of clients
>
> As for the queries:
> I have a 6000 record customer file and a bigger file containing orders
> The VB programs randomly retrieved records with a like clause on the first
> letter of the customer name
> E.G. B%, F%, Z%. This SQL also had a join to the order file and returned a few
> hundered records at a time
> The slow SQL I entered simply returned the MAX value for a column in the order
> file. This did seem to lock things
> down, perhaps p-sql was just being cautious or maybe it just slowed everything
> down quite a lot?
>
> Thanks a lot,
>
> M Chantler
In my opinion PostgreSQL is even better than Oracle. Postgres has some nice
features I missed in Oracle (eg. sending large amount of data to the SQL-Loader
via pipe).
MAX: As fas as I know, PostgreSQL can't use an index in combination with max on a
certain column. I read somewhere that this is going to be changed in a following
versions (is it already fixed in 7.03 or 7.1 beta ???).
Hans
On Thu, 30 Nov 2000 martin.chantler@convergys.com wrote:
>
>
> Thanks for responding,
>
> I suppose what I was really asking is if PostGreSQL works 'as good' as
> other databases like Oracle. The rumour I heard implied that I would have
> problems trying to run PostGreSQL with a lot of clients
>
> As for the queries:
> I have a 6000 record customer file and a bigger file containing orders
> The VB programs randomly retrieved records with a like clause on the first
> letter of the customer name
> E.G. B%, F%, Z%. This SQL also had a join to the order file and returned a few
> hundered records at a time
> The slow SQL I entered simply returned the MAX value for a column in the order
> file. This did seem to lock things
> down, perhaps p-sql was just being cautious or maybe it just slowed everything
> down quite a lot?
Take a look here for a recent comparison. This is page three of the
article and shows graphs for concurrent users and performance.
http://www.phpbuilder.com/columns/tim20001112.php3?page=3
Vince.
--
==========================================================================
Vince Vielhaber -- KA8CSH email: vev@michvhf.com http://www.pop4.net
128K ISDN from $22.00/mo - 56K Dialup from $16.00/mo at Pop4 Networking
Online Campground Directory http://www.camping-usa.com
Online Giftshop Superstore http://www.cloudninegifts.com
==========================================================================
One thing to consider since you mentioned VB is the lack of ole db provider. It really puts a black mark on the DB for Windows developers. Unfortunately only one person has volunteered to try to make one, but he is extremely busy (as most of us are). Part of the drawback of making one is software and knowledge needed. You need a good understanding of the way the database talks to the client, the database schema and other topics. As for the software, it seems atleast VC++ 6.0 is best. ODBC can be used, but again, there is faltering support for it also. I'm not pointing fingers, just things to look out for since you are a VB programmer. I'd be more than willing to give the provider a try (while asking many questions along the way), but the problem of not having VC++ is a major obstacle. Adam Lang Systems Engineer Rutgers Casualty Insurance Company http://www.rutgersinsurance.com ----- Original Message ----- From: <martin.chantler@convergys.com> To: <pgsql-general@postgresql.org> Sent: Thursday, November 30, 2000 11:03 AM Subject: [GENERAL] Can PostGreSQL handle 100 user database - more info > > > Thanks for responding, > > I suppose what I was really asking is if PostGreSQL works 'as good' as > other databases like Oracle. The rumour I heard implied that I would have > problems trying to run PostGreSQL with a lot of clients > > As for the queries: > I have a 6000 record customer file and a bigger file containing orders > The VB programs randomly retrieved records with a like clause on the first > letter of the customer name > E.G. B%, F%, Z%. This SQL also had a join to the order file and returned a few > hundered records at a time > The slow SQL I entered simply returned the MAX value for a column in the order > file. This did seem to lock things > down, perhaps p-sql was just being cautious or maybe it just slowed everything > down quite a lot? > > Thanks a lot, > > M Chantler > > > > -- > NOTICE: The information contained in this electronic mail transmission is > intended by Convergys Corporation for the use of the named individual or entity > to which it is directed and may contain information that is privileged or > otherwise confidential. If you have received this electronic mail transmission > in error, please delete it from your system without copying or forwarding it, > and notify the sender of the error by reply email or by telephone (collect), so > that the sender's address records can be corrected. >
we're running ~189 backend processes right now witha loadavg on the
machine of about 89 *rofl* our bottleneck riht now appears to actually be
in disk I/O though, which we are working on by moving from multiple
spindles, multiple file systems over to multiple spindles, stripe'd file
system, so that load is better distributed ...
it can run multiple queries in parrellel, as parrallel as the operating
system itself will allow ... but your bottleneck will generally be:
disk first, memory second and cpu third ... so watch and correct
in that order ... basically, you want to be able to move from disk->memory
as fast as possible, and the more you can store in memory, the less the
CPUs have to wait for the data ...
On Thu, 30 Nov 2000 martin.chantler@convergys.com wrote:
>
>
> Thanks for responding,
>
> I suppose what I was really asking is if PostGreSQL works 'as good' as
> other databases like Oracle. The rumour I heard implied that I would have
> problems trying to run PostGreSQL with a lot of clients
>
> As for the queries:
> I have a 6000 record customer file and a bigger file containing orders
> The VB programs randomly retrieved records with a like clause on the first
> letter of the customer name
> E.G. B%, F%, Z%. This SQL also had a join to the order file and returned a few
> hundered records at a time
> The slow SQL I entered simply returned the MAX value for a column in the order
> file. This did seem to lock things
> down, perhaps p-sql was just being cautious or maybe it just slowed everything
> down quite a lot?
>
> Thanks a lot,
>
> M Chantler
>
>
>
> --
> NOTICE: The information contained in this electronic mail transmission is
> intended by Convergys Corporation for the use of the named individual or entity
> to which it is directed and may contain information that is privileged or
> otherwise confidential. If you have received this electronic mail transmission
> in error, please delete it from your system without copying or forwarding it,
> and notify the sender of the error by reply email or by telephone (collect), so
> that the sender's address records can be corrected.
>
>
>
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
I am not sure what an ole db provider is? This must be another method of talking to the server from a client application. What advantages does it have? I have the open source ODBC client (and I know a Java version exists), it seems ok but I don't know if it handles things like transactions and other advanced functions. It would obviously be important to have a good method of talking to P-sql from Windows since a lot of people will want to do this. I have only just started looking at this but if I find any limitations I'm sure I will post them!!! Martin "Adam Lang" <aalang@rutgersinsurance.com> on 30/11/2000 16:54:16 To: pgsql-general@postgresql.org cc: (bcc: Martin Chantler/CIMG/CVG) Subject: Re: [GENERAL] Can PostGreSQL handle 100 user database - more info One thing to consider since you mentioned VB is the lack of ole db provider. It really puts a black mark on the DB for Windows developers. Unfortunately only one person has volunteered to try to make one, but he is extremely busy (as most of us are). Part of the drawback of making one is software and knowledge needed. You need a good understanding of the way the database talks to the client, the database schema and other topics. As for the software, it seems atleast VC++ 6.0 is best. ODBC can be used, but again, there is faltering support for it also. I'm not pointing fingers, just things to look out for since you are a VB programmer. I'd be more than willing to give the provider a try (while asking many questions along the way), but the problem of not having VC++ is a major obstacle. Adam Lang Systems Engineer Rutgers Casualty Insurance Company http://www.rutgersinsurance.com ----- Original Message ----- From: <martin.chantler@convergys.com> To: <pgsql-general@postgresql.org> Sent: Thursday, November 30, 2000 11:03 AM Subject: [GENERAL] Can PostGreSQL handle 100 user database - more info > > > Thanks for responding, > > I suppose what I was really asking is if PostGreSQL works 'as good' as > other databases like Oracle. The rumour I heard implied that I would have > problems trying to run PostGreSQL with a lot of clients > > As for the queries: > I have a 6000 record customer file and a bigger file containing orders > The VB programs randomly retrieved records with a like clause on the first > letter of the customer name > E.G. B%, F%, Z%. This SQL also had a join to the order file and returned a few > hundered records at a time > The slow SQL I entered simply returned the MAX value for a column in the order > file. This did seem to lock things > down, perhaps p-sql was just being cautious or maybe it just slowed everything > down quite a lot? > > Thanks a lot, > > M Chantler > > > > -- > NOTICE: The information contained in this electronic mail transmission is > intended by Convergys Corporation for the use of the named individual or entity > to which it is directed and may contain information that is privileged or > otherwise confidential. If you have received this electronic mail transmission > in error, please delete it from your system without copying or forwarding it, > and notify the sender of the error by reply email or by telephone (collect), so > that the sender's address records can be corrected. >
Replies inline. Adam Lang Systems Engineer Rutgers Casualty Insurance Company http://www.rutgersinsurance.com ----- Original Message ----- From: <martin.chantler@convergys.com> To: "Adam Lang" <aalang@rutgersinsurance.com> Sent: Thursday, November 30, 2000 12:40 PM Subject: Re: [GENERAL] Can PostGreSQL handle 100 user database - more info > > > I am not sure what an ole db provider is? This must be another method of > talking to the server from a client application. What advantages does it have? Yes. It is an abstraction layer, but it is Windows only technology. The way it works is that The database has an ole db provider (much like ODBC). ADO connects to the database using the ole db provider. You write your application using the ADO object model to interact with the database. Biggest advantage: as long as you have an ole db provider for that datasource, your ADO code is universal. Example. You have an application that connects to MS SQL Server using ADO and you extract data, run queries, etc. Later you migrate to Oracle. You change your connection string (which is one line) and in most cases, you can run your app without any other changes. ADO is also able to connect to non relational data sources: Text files, VSAM, AS/400, etc. Plus, the ole db provider should be made to expose the database schema... so you can manipulate data in an object oriented way, as well as poll the data source for structure information. A lot more information is at microsoft's website. Also, in a scenario where the data source does not have an ole db provider, there is one supplied that will connect through ODBC. > > I have the open source ODBC client (and I know a Java version exists), it seems > ok but I don't know if it handles things like transactions and other advanced > functions. If the ole db provider is made correctly, it should support anything that the database allows. I'm not too familiar with using the postgres ODBC driver. For the most part, I've come to the point where I have not really made too many VB apps with a postgres bckend, due to the fact I have to use the ODBC driver, which is a bit outdated (but it does work). > > It would obviously be important to have a good method of talking to P-sql from > Windows since a lot of people will want to do this. That has been my argument that a good connection method is needed to get into the Windows arena. Windows developers are spoiled. No matter how much you want to bad mouth MS, they do give us some great development tools. Unfortunately, postgres doesn't have anything to woo any windows develoeprs over.
martin.chantler@convergys.com wrote: > > I am not sure what an ole db provider is? This must be another method of > talking to the server from a client application. What advantages does it have? If you intend to use ADO you need an OLE-DB provider. > I have the open source ODBC client (and I know a Java version exists), it seems > ok but I don't know if it handles things like transactions and other advanced > functions. The ODBC midht cause some "interesting" trouble since VisualBasic tends to open multiple connections to the Server. This has tow disadvantages: 1. While using transaction isolation (not reading uncommitted data) you cannot read the data written on one connection over another one. If this does happen you might not immediately notive the rubbish happening. 2. With 100 users it might significant if there are 500 simultaneous connections open. At leas you have to raise the connection-limit. Elmar
But there is an OLE DB provider for ODBC, so you can use ADO with an ODBC; just not as nice. As for the multiple connections thing, I do not know anything about that. Adam Lang Systems Engineer Rutgers Casualty Insurance Company http://www.rutgersinsurance.com ----- Original Message ----- From: "Elmar Haneke" <elmar@haneke.de> To: <pgsql-general@postgresql.org> Sent: Thursday, November 30, 2000 1:59 PM Subject: Re: [GENERAL] Can PostGreSQL handle 100 user database - more info > > > martin.chantler@convergys.com wrote: > > > > I am not sure what an ole db provider is? This must be another method of > > talking to the server from a client application. What advantages does it have? > > > If you intend to use ADO you need an OLE-DB provider. > > > I have the open source ODBC client (and I know a Java version exists), it seems > > ok but I don't know if it handles things like transactions and other advanced > > functions. > > > The ODBC midht cause some "interesting" trouble since VisualBasic > tends to open multiple connections to the Server. This has tow > disadvantages: > > 1. While using transaction isolation (not reading uncommitted data) > you cannot read the data written on one connection over another one. > If this does happen you might not immediately notive the rubbish > happening. > > 2. With 100 users it might significant if there are 500 simultaneous > connections open. At leas you have to raise the connection-limit. > > Elmar