| pgpool-II 3.7.26 Documentation | |||
|---|---|---|---|
| Prev | Up | Chapter 5. Server Configuration | Next |
In memory query cache can be used with all modes of Pgpool-II. Pgpool-II does not need a restart when the cache gets outdated because of the underlying table updates.
In memory cache saves the pair of SELECT statement and its result (along with the Bind parameters, if the SELECT is an extended query). If the same SELECTs comes in, Pgpool-II returns the value from cache. Since no SQL parsing nor access to PostgreSQL are involved, the serving of results from the in memory cache is extremely fast.
Note: Basically following SELECTs will not be cached:
SELECTs including non immutable functions SELECTs including temp tables, unlogged tables SELECTs including TIMESTAMP WITH TIMEZONE or TIME WITH TIMEZONE SELECTs including CAST to TIMESTAMP WITH TIMEZONE or TIME WITH TIMEZONE SELECTs including SQLValueFunction (CURRENT_TIME, CURRENT_USER etc.) SELECT result is too large (memqcache_maxcache) SELECT FOR SHARE/UPDATE SELECT starting with "/*NO QUERY CACHE*/" comment SELECT including system catalogs SELECT uses TABLESAMPLEHowever, VIEWs and SELECTs accessing unlogged tables can be cached by specifying in the white_memqcache_table_list.
On the other hand, it might be slower than the normal path in some cases, because it adds some overhead to store cache. Moreover when a table is updated, Pgpool-II automatically deletes all the caches related to the table. Therefore, the performance will be degraded by a system with a lot of updates. If the query cache hit ratio (it can be checked by using SHOW POOL_CACHE) is lower than 70%, you might want to disable in memory cache.
Specifies the storage type to be used for the cache. Below table contains the list of all valid values for the parameter.
Table 5-10. Memqcache method options
| Value | Description |
|---|---|
| 'shmem' | Use shared memory |
| 'memcached' | Use memcached |
In general shmem is faster than memcached because the former does not involve any network access. However, the upper limit size of memqcache_total_size is limited by the total shared memory size defined by the system. The size is large enough in modern Linux systems, but in other systems there maybe smaller upper limit. In this case you need to change the system settings regarding the maximum shared memory size. There's a good explanation about shared memory in PostgreSQL documentation.
If you are not sure which memqcache_method to be used, start with shmem.
Default is 'shmem'.
This parameter can only be set at server start.
These below parameter are valid for both shmem and memcached type query cache.
Specifies the life time of query cache in seconds. Default is 0. which means no cache expiration and cache remains valid until the table is updated.
This parameter can only be set at server start.
Note: memqcache_expire and memqcache_auto_cache_invalidation are orthogonal to each other.
Setting to on, automatically deletes the cache related to the updated tables. When off, cache is not deleted.
Default is on.
Note: This parameters memqcache_auto_cache_invalidation and memqcache_expire are orthogonal to each other.
This parameter can be changed by reloading the Pgpool-II configurations.
Specifies the maximum size in bytes of the SELECT query result to be cached. The result with data size larger than this value will not be cached by Pgpool-II. When the caching of data is rejected because of the size constraint the following message is shown.
LOG: pid 13756: pool_add_temp_query_cache: data size exceeds memqcache_maxcache. current:4095 requested:111 memq_maxcache:4096
Note: For the shared memory query('shmem') cache the memqcache_maxcache must be set lower than memqcache_cache_block_size and for 'memcached' it must be lower than the size of slab (default is 1 MB).
This parameter can only be set at server start.
Specifies a comma separated list of table names whose SELECT results should be cached by Pgpool-II. This parameter only applies to VIEWs and SELECTs accessing unlogged tables. Regular tables can be cached unless specified by black_memqcache_table_list.
You can use regular expression into the list to match table name (to which ^ and $ are automatically added).
Note: If the queries can refer the table with and without the schema qualification then you must add both entries(with and without schema name) in the list.
#For example: #If the queries sometime use "table1" and other times "public.table1" #to refer the table1 then the white_memqcache_table_list #would be configured as follows. white_memqcache_table_list = "table1,public.table1"
This parameter can be changed by reloading the Pgpool-II configurations.
Specifies a comma separated list of table names whose SELECT results should NOT be cached by the Pgpool-II.
You can use regular expression into the list to match table name (to which ^ and $ are automatically added),
Note: If the queries can refer the table with and without the schema qualification then you must add both entries(with and without schema name) in the list.
#For example: #If the queries sometime use "table1" and other times "public.table1" #to refer the table1 then the black_memqcache_table_list #would be configured as follows. black_memqcache_table_list = "table1,public.table1"
This parameter can be changed by reloading the Pgpool-II configurations.
Note: black_memqcache_table_list precedence over white_memqcache_table_list
Specifies the full path to the directory for storing the oids of tables used by SELECT queries.
memqcache_oiddir directory contains the sub directories for the databases. The directory name is the OID of the database. In addition, each database directory contains the files for each table used by SELECT statement. Again the name of the file is the OID of the table. These files contains the pointers to query cache which are used as key for deleting the caches.
Note: Normal restart of Pgpool-II does not clear the contents of memqcache_oiddir.
This parameter can be changed by reloading the Pgpool-II configurations.
These are the parameters used with shared memory as the cache storage.
Specifies the shared memory cache size in bytes.
This parameter can only be set at server start.
Specifies the number of cache entries. This is used to define the size of cache management space.
Note: The management space size can be calculated by: memqcache_max_num_cache * 48 bytes. Too small number will cause an error while registering cache. On the other hand too large number will just waste space.
This parameter can only be set at server start.
Specifies the cache block size. Pgpool-II uses the cache memory arranged in memqcache_cache_block_size blocks. SELECT result is packed into the block and must fit in a single block. And the results larger than memqcache_cache_block_size are not cached.
memqcache_cache_block_size must be set to at least 512.
This parameter can only be set at server start.
These are the parameters used with memcached as the cache storage.
Specifies the host name or the IP address on which memcached works. You can use 'localhost' if memcached and Pgpool-II resides on same server.
This parameter can only be set at server start.
Specifies the port number of memcached. Default is 11211.
This parameter can only be set at server start.