Thread: Adding memory query cache to ODBC driver
Hi!
I wonder if it would be possible to add some kind of caching to the driver.
Something like memory cache in pgpool:
https://lists.postgresql.org/mj/mj_confirm/domain=postgresql.org?t=06E9-8DF3-1B50
In minimal it would one more input field in driver configuration with number.
If number is 0 – driver behaves as now.
If number is e.g. 60 – then the select queries sent in the last 60s together with their results are kept in memory
And if within these 60s they are sent from the client again – the answer is return from the memory.
The rationale for this change is certain class of client applications that have some kind of ORM and issue very many
Identical queries in short time. If such are run on WAN/VPN with many network hops.
I have run some tests and currently PGSQL ODBC issues 2x more queries than MS SQL or MySQL and seems 2x slower
Because of that so these dbs are chose over PGSQL.
With such option the PGSQL should be 3-4x faster than MS SQL/MySQL.
I am aware we are losing a bit of consistency here but pgpool does the same.
Best Regards,
Jacek
On 12/08/2013 10:51 AM, Pliszka, Jacek wrote: > Hi! > > I wonder if it would be possible to add some kind of caching to the driver. > > Something like memory cache in pgpool: I'm sure that would be useful for many applications, but I don't think that belongs in the driver. A standalone generic query cache, which would act as a wrapper around any ODBC connection, would be nice. - Heikki
OK, but as for the last 21 years I've developed for Linux 99% of the standalone ODBC cache is way beyond me. :) Doing the cache on the PostgreSQL protocol level looks easier for me. And this was an idea to give an option to make PostgreSQL ODBC driver look better than MS/My SQL. :) BR, Jacek -----Original Message----- From: Heikki Linnakangas [mailto:hlinnakangas@vmware.com] Sent: 8 grudnia 2013 20:54 To: Pliszka, Jacek Cc: pgsql-odbc@postgresql.org Subject: Re: [ODBC] Adding memory query cache to ODBC driver On 12/08/2013 10:51 AM, Pliszka, Jacek wrote: > Hi! > > I wonder if it would be possible to add some kind of caching to the driver. > > Something like memory cache in pgpool: I'm sure that would be useful for many applications, but I don't think that belongs in the driver. A standalone generic querycache, which would act as a wrapper around any ODBC connection, would be nice. - Heikki
Is this (https://code.google.com/p/pqc/) what you were looking for? It looks like the project has become stagnant but maybe somebody can resurrect it. Mike Landl -----Original Message----- From: pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Pliszka, Jacek Sent: Sunday, December 08, 2013 3:22 PM To: Heikki Linnakangas Cc: pgsql-odbc@postgresql.org Subject: Re: [ODBC] Adding memory query cache to ODBC driver OK, but as for the last 21 years I've developed for Linux 99% of the standalone ODBC cache is way beyond me. :) Doing thecache on the PostgreSQL protocol level looks easier for me. And this was an idea to give an option to make PostgreSQL ODBC driver look better than MS/My SQL. :) BR, Jacek -----Original Message----- From: Heikki Linnakangas [mailto:hlinnakangas@vmware.com] Sent: 8 grudnia 2013 20:54 To: Pliszka, Jacek Cc: pgsql-odbc@postgresql.org Subject: Re: [ODBC] Adding memory query cache to ODBC driver On 12/08/2013 10:51 AM, Pliszka, Jacek wrote: > Hi! > > I wonder if it would be possible to add some kind of caching to the driver. > > Something like memory cache in pgpool: I'm sure that would be useful for many applications, but I don't think that belongs in the driver. A standalone generic querycache, which would act as a wrapper around any ODBC connection, would be nice. - Heikki -- Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-odbc
Something like this but not quite: 1. pqc is Linux C - I could use myself but majority of users are Windows ODBC clients. 2. pqc looks like based on pgpool - I would rather use pgpool - but this on the server and my problem Is not load of the db - it is the Very Wide Area Network - for this I need the cache as close to client as possible - closest thing is ODBC. Last but not least... I hacked a small crude SQL cache in Python and : Oracle : 90s PostgreSQL : 85s MS SQL: 45s MySQL: 33s PostgreSQL with my crude cache: 22s BR, Jacek -----Original Message----- From: pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Mike Landl Sent: 9 grudnia 2013 17:03 To: Pliszka, Jacek Cc: pgsql-odbc@postgresql.org Subject: Re: [ODBC] Adding memory query cache to ODBC driver Is this (https://code.google.com/p/pqc/) what you were looking for? It looks like the project has become stagnant but maybe somebody can resurrect it. Mike Landl -----Original Message----- From: pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Pliszka, Jacek Sent: Sunday, December 08, 2013 3:22 PM To: Heikki Linnakangas Cc: pgsql-odbc@postgresql.org Subject: Re: [ODBC] Adding memory query cache to ODBC driver OK, but as for the last 21 years I've developed for Linux 99% of the standalone ODBC cache is way beyond me. :) Doing thecache on the PostgreSQL protocol level looks easier for me. And this was an idea to give an option to make PostgreSQL ODBC driver look better than MS/My SQL. :) BR, Jacek -----Original Message----- From: Heikki Linnakangas [mailto:hlinnakangas@vmware.com] Sent: 8 grudnia 2013 20:54 To: Pliszka, Jacek Cc: pgsql-odbc@postgresql.org Subject: Re: [ODBC] Adding memory query cache to ODBC driver On 12/08/2013 10:51 AM, Pliszka, Jacek wrote: > Hi! > > I wonder if it would be possible to add some kind of caching to the driver. > > Something like memory cache in pgpool: I'm sure that would be useful for many applications, but I don't think that belongs in the driver. A standalone generic querycache, which would act as a wrapper around any ODBC connection, would be nice. - Heikki -- Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-odbc -- Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-odbc
Okay, I see what you mean now. I wish I could help you out but I fear my limited knowledge won't be of much use here. -----Original Message----- From: pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Pliszka, Jacek Sent: Monday, December 09, 2013 12:42 PM To: Mike Landl Cc: pgsql-odbc@postgresql.org Subject: Re: [ODBC] Adding memory query cache to ODBC driver Something like this but not quite: 1. pqc is Linux C - I could use myself but majority of users are Windows ODBC clients. 2. pqc looks like based on pgpool - I would rather use pgpool - but this on the server and my problem Is not load of thedb - it is the Very Wide Area Network - for this I need the cache as close to client as possible - closest thing is ODBC. Last but not least... I hacked a small crude SQL cache in Python and : Oracle : 90s PostgreSQL : 85s MS SQL: 45s MySQL: 33s PostgreSQL with my crude cache: 22s BR, Jacek -----Original Message----- From: pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Mike Landl Sent: 9 grudnia 2013 17:03 To: Pliszka, Jacek Cc: pgsql-odbc@postgresql.org Subject: Re: [ODBC] Adding memory query cache to ODBC driver Is this (https://code.google.com/p/pqc/) what you were looking for? It looks like the project has become stagnant but maybe somebody can resurrect it. Mike Landl -----Original Message----- From: pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Pliszka, Jacek Sent: Sunday, December 08, 2013 3:22 PM To: Heikki Linnakangas Cc: pgsql-odbc@postgresql.org Subject: Re: [ODBC] Adding memory query cache to ODBC driver OK, but as for the last 21 years I've developed for Linux 99% of the standalone ODBC cache is way beyond me. :) Doing thecache on the PostgreSQL protocol level looks easier for me. And this was an idea to give an option to make PostgreSQL ODBC driver look better than MS/My SQL. :) BR, Jacek -----Original Message----- From: Heikki Linnakangas [mailto:hlinnakangas@vmware.com] Sent: 8 grudnia 2013 20:54 To: Pliszka, Jacek Cc: pgsql-odbc@postgresql.org Subject: Re: [ODBC] Adding memory query cache to ODBC driver On 12/08/2013 10:51 AM, Pliszka, Jacek wrote: > Hi! > > I wonder if it would be possible to add some kind of caching to the driver. > > Something like memory cache in pgpool: I'm sure that would be useful for many applications, but I don't think that belongs in the driver. A standalone generic querycache, which would act as a wrapper around any ODBC connection, would be nice. - Heikki -- Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-odbc -- Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-odbc -- Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-odbc
On 12/09/2013 07:42 PM, Pliszka, Jacek wrote: > 2. pqc looks like based on pgpool - I would rather use pgpool - but this on the server and my problem > Is not load of the db - it is the Very Wide Area Network - for this I need the cache as close to client as possible > - closest thing is ODBC. You could run pgpool in the client. - Heikki
On 12/09/2013 07:42 PM, Pliszka, Jacek wrote:You could run pgpool in the client.2. pqc looks like based on pgpool - I would rather use pgpool - but this on the server and my problem
Is not load of the db - it is the Very Wide Area Network - for this I need the cache as close to client as possible
- closest thing is ODBC.
Jonah H. Harris
Blog: http://www.oracle-internals.com/
Ø You could run pgpool in the client.
Unfortunately I cannot, firstly – as I wrote – clients are mostly Windows (no pgpool on Windows),
Secondly this customer, suppliers, third parties – I have nothing to say about their systems.
Ø Embedding this functionality directly into the driver seems like a lot of work for very little gain.
The functionality is in the pgpool/pqc – there is definitely work too factor it out into a library.
Ø That is, what is the probability of each client performing the exact same query?
For the test I did today (Enterprise Architect software) – over 95%. Out of around 3900 queries only 251 were unique.
Never underestimate innovative ways to write and integrate software. J
Ø And, as I assume there are multiple clients connecting to the database, why wouldn't it be more advantageous for multiple clients to share the same cache (e.g. pgpool being used as it is designed, to proxy and, as already implemented, cache the data?)
Because this is not the problem with the central DB – it is the problem with very complicated network configuration with dozens of
Firewalls, gateways etc. etc.
The cost is not in the db backend performance – cost is in the number of packets sent over the network.
Anyway it looks like consensus is that is not the best idea.
I’ll probably spend couple more hours on the crude Python cache I used for tests – make .exe out of it
And give it to the most problematic clients.
BR,
Jacek