HTTPS Clone URL: https://github.com/colemanc0709/pqc.git
A while back I was having some major issues with a project regarding the PostgreSQL database queries. The fact was the queries were poorly written and the application was built in such a way that every page view yielded 50+ queries. Performance was an issue and we did not have the time to re-factor the code or clean up the queries. Next step? Cache the results!
That is when I stumbled upon the PostgreSQL Query Cache project by Satoshi Nagayasu on GitHub. It is light-weight, written in C, and was an excellent start. The challenge was that it was nearly two years old and we found it had significant issues with the majority of our queries due to length limitations when caching the queries in memcache.
I modified the source and added md5 hashing to avoid this problem and later converted to sha1. The md5 definitely worked at one point but I have not worked on the project in a while and do not remember if I completed the implementation of sha1. At any rate, I forked the initial repository this morning, committed my changes (and a few unnecessary extras, sorry) so that anyone else who would like to resume or contribute may. Feel free to contribute or “cleanup” the unnecessary files!
Thank you, Satoshi Nagayasu – excellent work! I hope that I’ve added some benefit (my application was able to leverage the hashing and ultimately cache 100% of the queries resulting in a massive performance improvement).
Satoshi provided this presentation about the project on his blog:
And, in case it is any help, here are some notes I jotted down last I was working on my revisions:
I went to re-enable PQCD in order to test some things and I encountered errors trying to start the service due to an invalid version of memcached.so.5. I determined that the 32-bit version was installed instead of the 64-bit version. This is more likely due to a bad conversion of the RPM to DEB (with alien, it doesn’t always work). As such I had to compile PQCD from scratch (source is in /opt/pqcd-source) so I’m adding some notes:
- This link has a viewable version of the PQCD presentation (was VERY helpful): http://pgsnaga.blogspot.com/2011/03/postgresql-query-cache-pqc.html
- “automake” on the source did not work until I did “autoreconf”.
- The “configure” command required a prefix so I specified “–prefix=/opt/pqcd”.
- “make” still failed because it could not find library, “event”. As such I created a symlink for /usr/lib/libevent.so to point to libevent-2.0.so.5.1.4.
- I had to then re-run the “configure” command and follow these steps again.
- I copied the pqcd.conf file from the previous installation (and corrected the memcached_bin path) and created pqcd_hba.conf from the sample (with no modifications) in /opt/pqcd/etc.
- At any rate, it’s now installed (I left the old version if you care to look at the error, I’ll remove it tomorrow). I started PQCD from command line with:
* You can run in debug mode with:
And, I re-enabled (un-commented) the following line in postgresql.conf (followed by a postgresql restart):
custom_variable_classes = 'pgmemcache' # list of custom variable class names
I got this working. I ran pqcd in debug mode and noticed that it wasn’t “binding” (indicating a problem with the socket). I created the directory for it, /var/run/pqcd and it started running. I then switched postgresql port to 5433 and set the pgpool to run on 5432. And then, I ran it in debug mode and confirmed that queries were passing through!
- PostgreSQL When it’s not Your Job (databasetube.com)
- PostgreSQL Statistics: pg_statistic and pg_stat(io)_* (amitlangote.wordpress.com)
- pypgwrap 0.1.3 (pypi.python.org)
- Slow Counting (wiki.postgresql.org)