PostgreSQL Query Cache released

[Satoshi Nagayasu]

Hi all,

I would like to introduce a new open source software,
PostgreSQL Query Cache, which enables to improve query performance
extremely (10x~100x) by caching query results in front of backends.

http://code.google.com/p/pqc/

PostgreSQL Query Cache:
- waits connections on the different port from the clients.
- delegates queries in front of the backends, like a proxy.
- intercepts and caches SELECT query results.
- also manages lifecycle of the query cache.

For more information, please visit following presentation:
http://www.slideshare.net/uptimeforce/postgresql-query-cache-pqc

Please visit the project page, and enjoy extreme performance. :)

Regards,
--
NAGAYASU Satoshi <satoshi....@gmail.com>

[ipConfig]

Nice done!

I will try to test it in productivity...

Are there any known benchmarks?
Or do you have an idea how to measure your programm e.g. a little
programm in the background which counts the hits?
Or some kind of flag in the conf-file which will write hits/misses in
a log file?

[ipConfig]

I have some problems executing the Program...
This is what i get:

Test-DB-84:/opt/uptime/querycache/bin # ./pqcd -d
2011-03-07 10:24:19 DEBUG: pid 26187: key: listen_addresses
2011-03-07 10:24:19 DEBUG: pid 26187: value: '*' kind: 4
2011-03-07 10:24:19 DEBUG: pid 26187: key: port
2011-03-07 10:24:19 DEBUG: pid 26187: value: 9999 kind: 2
2011-03-07 10:24:19 DEBUG: pid 26187: key: socket_dir
2011-03-07 10:24:19 DEBUG: pid 26187: value: '/tmp' kind: 4
2011-03-07 10:24:19 DEBUG: pid 26187: key: backend_host_name
2011-03-07 10:24:19 DEBUG: pid 26187: value: '192.168.44.183' kind: 4
2011-03-07 10:24:19 DEBUG: pid 26187: :192.168.44.183:
2011-03-07 10:24:19 DEBUG: pid 26187: key: backend_port
2011-03-07 10:24:19 DEBUG: pid 26187: value: 5432 kind: 2
2011-03-07 10:24:19 DEBUG: pid 26187: key: backend_socket_dir
2011-03-07 10:24:19 DEBUG: pid 26187: value: '/tmp' kind: 4
2011-03-07 10:24:19 DEBUG: pid 26187: key: secondary_backend_host_name
2011-03-07 10:24:19 DEBUG: pid 26187: value: '' kind: 4
2011-03-07 10:24:19 DEBUG: pid 26187: ::
2011-03-07 10:24:19 DEBUG: pid 26187: key: secondary_backend_port
2011-03-07 10:24:19 DEBUG: pid 26187: value: 0 kind: 2
2011-03-07 10:24:19 DEBUG: pid 26187: key: num_init_children
2011-03-07 10:24:19 DEBUG: pid 26187: value: 32 kind: 2
2011-03-07 10:24:19 DEBUG: pid 26187: key: max_pool
2011-03-07 10:24:19 DEBUG: pid 26187: value: 4 kind: 2
2011-03-07 10:24:19 DEBUG: pid 26187: key: child_life_time
2011-03-07 10:24:19 DEBUG: pid 26187: value: 300 kind: 2
2011-03-07 10:24:19 DEBUG: pid 26187: key: connection_life_time
2011-03-07 10:24:19 DEBUG: pid 26187: value: 0 kind: 2
2011-03-07 10:24:19 DEBUG: pid 26187: key: child_max_connections
2011-03-07 10:24:19 DEBUG: pid 26187: value: 0 kind: 2
2011-03-07 10:24:19 DEBUG: pid 26187: key: logdir
2011-03-07 10:24:19 DEBUG: pid 26187: value: '/tmp' kind: 4
2011-03-07 10:24:19 DEBUG: pid 26187: key: replication_mode
2011-03-07 10:24:19 DEBUG: pid 26187: value: false kind: 1
2011-03-07 10:24:19 DEBUG: pid 26187: key: replication_strict
2011-03-07 10:24:19 DEBUG: pid 26187: value: true kind: 1
2011-03-07 10:24:19 DEBUG: pid 26187: key: replication_timeout
2011-03-07 10:24:19 DEBUG: pid 26187: value: 5000 kind: 2
2011-03-07 10:24:19 DEBUG: pid 26187: key: load_balance_mode
2011-03-07 10:24:19 DEBUG: pid 26187: value: false kind: 1
2011-03-07 10:24:19 DEBUG: pid 26187: key: weight_master
2011-03-07 10:24:19 DEBUG: pid 26187: value: 0.5 kind: 3
2011-03-07 10:24:19 DEBUG: pid 26187: weight_master: 0.500000
2011-03-07 10:24:19 DEBUG: pid 26187: key: weight_secondary
2011-03-07 10:24:19 DEBUG: pid 26187: value: 0.5 kind: 3
2011-03-07 10:24:19 DEBUG: pid 26187: weight_secondary: 0.500000
2011-03-07 10:24:19 DEBUG: pid 26187: key:
replication_stop_on_mismatch
2011-03-07 10:24:19 DEBUG: pid 26187: value: false kind: 1
2011-03-07 10:24:19 DEBUG: pid 26187: replication_stop_on_mismatch: 0
2011-03-07 10:24:19 DEBUG: pid 26187: key: replicate_select
2011-03-07 10:24:19 DEBUG: pid 26187: value: false kind: 1
2011-03-07 10:24:19 DEBUG: pid 26187: replicate_select: 0
2011-03-07 10:24:19 DEBUG: pid 26187: key: reset_query_list
2011-03-07 10:24:19 DEBUG: pid 26187: value: 'ABORT; RESET ALL; SET
SESSION AUTHORIZATION DEFAULT' kind: 4
2011-03-07 10:24:19 DEBUG: pid 26187: extract_string_tokens: token:
ABORT
2011-03-07 10:24:19 DEBUG: pid 26187: extract_string_tokens: token:
RESET ALL
2011-03-07 10:24:19 DEBUG: pid 26187: extract_string_tokens: token:
SET SESSION AUTHORIZATION DEFAULT
2011-03-07 10:24:19 DEBUG: pid 26187: key: print_timestamp
2011-03-07 10:24:19 DEBUG: pid 26187: value: true kind: 1
2011-03-07 10:24:19 DEBUG: pid 26187: key: master_slave_mode
2011-03-07 10:24:19 DEBUG: pid 26187: value: false kind: 1
2011-03-07 10:24:19 DEBUG: pid 26187: key: connection_cache
2011-03-07 10:24:19 DEBUG: pid 26187: value: true kind: 1
2011-03-07 10:24:19 DEBUG: pid 26187: key: health_check_timeout
2011-03-07 10:24:19 DEBUG: pid 26187: value: 20 kind: 2
2011-03-07 10:24:19 DEBUG: pid 26187: key: health_check_period
2011-03-07 10:24:19 DEBUG: pid 26187: value: 0 kind: 2
2011-03-07 10:24:19 DEBUG: pid 26187: key: health_check_user
2011-03-07 10:24:19 DEBUG: pid 26187: value: 'nobody' kind: 4
2011-03-07 10:24:19 DEBUG: pid 26187: key: insert_lock
2011-03-07 10:24:19 DEBUG: pid 26187: value: false kind: 1
2011-03-07 10:24:19 DEBUG: pid 26187: key: ignore_leading_white_space
2011-03-07 10:24:19 DEBUG: pid 26187: value: false kind: 1
2011-03-07 10:24:19 DEBUG: pid 26187: key: log_statement
2011-03-07 10:24:19 DEBUG: pid 26187: value: true kind: 1
2011-03-07 10:24:19 DEBUG: pid 26187: key: log_connections
2011-03-07 10:24:19 DEBUG: pid 26187: value: false kind: 1
2011-03-07 10:24:19 DEBUG: pid 26187: key: log_hostname
2011-03-07 10:24:19 DEBUG: pid 26187: value: false kind: 1
2011-03-07 10:24:19 DEBUG: pid 26187: key: enable_pool_hba
2011-03-07 10:24:19 DEBUG: pid 26187: value: true kind: 1
2011-03-07 10:24:19 DEBUG: pid 26187: key: memcached_bin
2011-03-07 10:24:19 DEBUG: pid 26187: value: '/opt/uptime/querycache/
bin/memcached' kind: 4
2011-03-07 10:24:19 DEBUG: pid 26187: key: query_cache_mode
2011-03-07 10:24:19 DEBUG: pid 26187: value: 'active' kind: 4
2011-03-07 10:24:19 DEBUG: pid 26187: key: query_cache_expiration
2011-03-07 10:24:19 DEBUG: pid 26187: value: 30 kind: 2
2011-03-07 10:24:19 DEBUG: pid 26187: weight: 1073741823
2011-03-07 10:24:19 DEBUG: pid 26187: loading "/opt/uptime/querycache/
etc/pqcd_hba.conf" for client authentication configuration file



postgres@Test-DB-84:/root> psql mo_koenig_home
could not change directory to "/root"
psql (8.4.1)
Type "help" for help.

mo_koenig_home=# \timing
Timing is on.
mo_koenig_home=# /* cache:off */select count(*) from t_kvpos;
count
--------
188560
(1 row)

Time: 258.459 ms
mo_koenig_home=# /* cache:off */select count(*) from t_kvpos;
count
--------
188560
(1 row)

Time: 22.425 ms
mo_koenig_home=# /* cache:off */select count(*) from t_kvpos;
count
--------
188560
(1 row)

Time: 20.919 ms
mo_koenig_home=# select count(*) from t_kvpos;
count
--------
188560
(1 row)

Time: 22.856 ms
mo_koenig_home=# /* cache:off */select count(*) from t_kvpos;
count
--------
188560
(1 row)

Time: 20.909 ms
mo_koenig_home=# select count(*) from t_kvpos;
count
--------
188560
(1 row)

Time: 21.989 ms
mo_koenig_home=#






Test-DB-84:/opt/uptime/querycache/bin # ./pqcd stop
2011-03-07 10:28:16 ERROR: pid 26362: PID file can't be opened. - /tmp/
memcached.pid
2011-03-07 10:28:16 ERROR: pid 26362: could not read pid file

[satoshi....@gmail.com]

Hi,

Sorry for a delayed respoonse.

2011/3/7 ipConfig <Pohlt...@aol.com>:

> Are there any known benchmarks?
> Or do you have an idea how to measure your programm e.g. a little
> programm in the background which counts the hits?
> Or some kind of flag in the conf-file which will write hits/misses in
> a log file?

Well, pqc does not have dedicated statistics feature right now.
And, yes, pqc should have it.

I think memcached working behind pqc has its statistics feature,
but I have not tried yet.

Statistic feature should be in top of the feature list.

Thanks for the comments.

Regards,
--
Satoshi Nagayasu <satoshi....@gmail.com>

[satoshi....@gmail.com]

Hi,

2011/3/7 ipConfig <Pohlt...@aol.com>:

> I have some problems executing the Program...
> This is what i get:

I'm afraid that I can't see what the problems are.

The attached debug log seems to be showing only messages at its startup.
You can see more details if you catch logs during executing queries
with/without hints.

[ipConfig]

Hi @ all,

When using the following statement i get an error:

mo_rehmann_home=# /* cache:on */SELECT i.TABLE_name
mo_rehmann_home-# FROM information_schema.TABLEs i, pg_class c
mo_rehmann_home-# WHERE TABLE_schema != 'information_schema'
mo_rehmann_home-# AND i.TABLE_name = c.relname
mo_rehmann_home-# AND c.relkind = 'v'
mo_rehmann_home-# ;
Time: 44.119 ms
mo_rehmann_home=# /* cache:on */SELECT i.TABLE_name
FROM information_schema.TABLEs i, pg_class c
WHERE TABLE_schema != 'information_schema'
AND i.TABLE_name = c.relname
AND c.relkind = 'v'
;
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
mo_rehmann_home=#



Does anyone has an idea?





On 9 Mrz., 07:21, satoshi.nagay...@gmail.com wrote:
> Hi,
>
> 2011/3/7 ipConfig <Pohltras...@aol.com>:

>
> > I have some problems executing the Program...
> > This is what i get:
>
> I'm afraid that I can't see what the problems are.
>
> The attached debug log seems to be showing only messages at its startup.
> You can see more details if you catch logs during executing queries
> with/without hints.
>
> Regards,
> --

> Satoshi Nagayasu <satoshi.nagay...@gmail.com>

[Satoshi Nagayasu]

Hi,

Thanks for reporting about that.

About 10 days ago, I fixed a bug which fails query caching
when a SELECT statement contains some control (non-ascii)
characters, such as '\n', '\t' or else.

http://code.google.com/p/pqc/source/detail?r=3#

If the query cache works correctly when you remove 'CR+LF'
thing from the SELECT statement, this has been already fixed
in the latest code in the repository.

Can you try to run the statement in just one line to determine
the problem?

I will check more details tomorrow, because I'm re-installing
my desktop pc right now...

Regards,

--
NAGAYASU Satoshi <satoshi....@gmail.com>

[Satoshi Nagayasu]

Hi,

2011/04/07 21:21, ipConfig wrote:

> Hi @ all,
>
> When using the following statement i get an error:
>
> mo_rehmann_home=# /* cache:on */SELECT i.TABLE_name
> mo_rehmann_home-# FROM information_schema.TABLEs i, pg_class c
> mo_rehmann_home-# WHERE TABLE_schema != 'information_schema'
> mo_rehmann_home-# AND i.TABLE_name = c.relname
> mo_rehmann_home-# AND c.relkind = 'v'
> mo_rehmann_home-# ;
> Time: 44.119 ms
> mo_rehmann_home=# /* cache:on */SELECT i.TABLE_name
> FROM information_schema.TABLEs i, pg_class c
> WHERE TABLE_schema != 'information_schema'
> AND i.TABLE_name = c.relname
> AND c.relkind = 'v'
> ;
> server closed the connection unexpectedly
> This probably means the server terminated abnormally
> before or while processing the request.
> The connection to the server was lost. Attempting reset: Succeeded.
> mo_rehmann_home=#

I tried same query to reproduce the issue, but I can't catch it.

What do you see when you start pqcd with '-d -n' options
to print more detailed debug messages?
It would be helpful to determine the issue.

Following is my result of the run.
---------------------------------------------------------------
[snaga@devsv02 src]$ /usr/local/pgsql/bin/psql -p 9999 testdb
psql (9.0.2)
Type "help" for help.

testdb=# \timing
Timing is on.
testdb=# /* cache:on */SELECT i.TABLE_name
testdb-# FROM information_schema.TABLEs i, pg_class c
testdb-# WHERE TABLE_schema != 'information_schema'
testdb-# AND i.TABLE_name = c.relname
testdb-# AND c.relkind = 'v'
testdb-# ;
table_name
--------------------------
pg_roles
pg_shadow
pg_group
<...snip...>
pg_stat_bgwriter
pg_user_mappings
pg_statio_user_tables
(36 rows)

Time: 6.371 ms
testdb=# /* cache:on */SELECT i.TABLE_name

FROM information_schema.TABLEs i, pg_class c
WHERE TABLE_schema != 'information_schema'
AND i.TABLE_name = c.relname
AND c.relkind = 'v'
;

table_name
--------------------------
pg_roles
pg_shadow
pg_group
<...snip...>
pg_stat_bgwriter
pg_user_mappings
pg_statio_user_tables
(36 rows)

Time: 0.718 ms
testdb=# \q
[snaga@devsv02 src]$
---------------------------------------------------------------