Performance Analysis and Tuning v10

Advanced Server provides various tools for performance analysis and tuning. These features are described in this section.

Dynatune

Advanced Server supports dynamic tuning of the database server to make the optimal usage of the system resources available on the host machine on which it is installed. The two parameters that control this functionality are located in the postgresql.conf file. These parameters are:

• edb_dynatune
• edb_dynatune_profile

edb_dynatune

edb_dynatune determines how much of the host system's resources are to be used by the database server based upon the host machine's total available resources and the intended usage of the host machine.

When Advanced Server is initially installed, the edb_dynatune parameter is set in accordance with the selected usage of the host machine on which it was installed - i.e., development machine, mixed use machine, or dedicated server. For most purposes, there is no need for the database administrator to adjust the various configuration parameters in the postgresql.conf file in order to improve performance.

You can change the value of the edb_dynatune parameter after the initial installation of Advanced Server by editing the postgresql.conf file. The postmaster must be restarted in order for the new configuration to take effect.

The edb_dynatune parameter can be set to any integer value between 0 and 100, inclusive. A value of 0, turns off the dynamic tuning feature thereby leaving the database server resource usage totally under the control of the other configuration parameters in the postgresql.conf file.

A low non-zero, value (e.g., 1 - 33) dedicates the least amount of the host machine's resources to the database server. This setting would be used for a development machine where many other applications are being used.

A value in the range of 34 - 66 dedicates a moderate amount of resources to the database server. This setting might be used for a dedicated application server that may have a fixed number of other applications running on the same machine as Advanced Server.

The highest values (e.g., 67 - 100) dedicate most of the server's resources to the database server. This setting would be used for a host machine that is totally dedicated to running Advanced Server.

Once a value of edb_dynatune is selected, database server performance can be further fine-tuned by adjusting the other configuration parameters in the postgresql.conf file. Any adjusted setting overrides the corresponding value chosen by edb_dynatune. You can change the value of a parameter by un-commenting the configuration parameter, specifying the desired value, and restarting the database server.

edb_dynatune_profile

The edb_dynatune_profile parameter is used to control tuning aspects based upon the expected workload profile on the database server. This parameter takes effect upon startup of the database server.

The possible values for edb_dynatune_profile are:

Infinite Cache

Note: Infinite Cache has been deprecated and may be removed in a future release. Please contact your EnterpriseDB Account Manager or mailto:sales@enterprisedb.com for more information.

Database performance is typically governed by two competing factors:

• Memory access is fast; disk access is slow.
• Memory space is scarce; disk space is abundant.

Advanced Server tries very hard to minimize disk I/O by keeping frequently used data in memory. When the first server process starts, it creates an in-memory data structure known as the buffer cache. The buffer cache is organized as a collection of 8K (8192 byte) pages: each page in the buffer cache corresponds to a page in some table or index. The buffer cache is shared between all processes servicing a given database.

When you select a row from a table, Advanced Server reads the page that contains the row into the shared buffer cache. If there isn't enough free space in the cache, Advanced Server evicts some other page from the cache. If Advanced Server evicts a page that has been modified, that data is written back out to disk; otherwise, it is simply discarded. Index pages are cached in the shared buffer cache as well.

The following figure demonstrates the flow of data in a typical Advanced Server session:

A client application sends a query to the Postgres server and the server searches the shared buffer cache for the required data. If the requested data is found in the cache, the server immediately sends the data back to the client. If not, the server reads the page that holds the data into the shared buffer cache, evicting one or more pages if necessary. If the server decides to evict a page that has been modified, that page is written to disk.

As you can see, a query will execute much faster if the required data is found in the shared buffer cache.

One way to improve performance is to increase the amount of memory that you can devote to the shared buffer cache. However, most computers impose a strict limit on the amount of RAM that you can install. To help circumvent this limit, Infinite Cache lets you utilize memory from other computers connected to your network.

With Infinite Cache properly configured, Advanced Server will dedicate a portion of the memory installed on each cache server as a secondary memory cache. When a client application sends a query to the server, the server first searches the shared buffer cache for the required data; if the requested data is not found in the cache, the server searches for the necessary page in one of the cache servers.

The following figure shows the flow of data in an Advanced Server session with Infinite Cache:

When a client application sends a query to the server, the server searches the shared buffer cache for the required data. If the requested data is found in the cache, the server immediately sends the data back to the client. If not, the server sends a request for the page to a specific cache server; if the cache server holds a copy of the page it sends the data back to the server and the server copies the page into the shared buffer cache. If the required page is not found in the primary cache (the shared buffer cache) or in the secondary cache (the cloud of cache servers), Advanced Server must read the page from disk. Infinite Cache improves performance by utilizing RAM from other computers on your network in order to avoid reading frequently accessed data from disk.

Updating the Cache Node Configuration

You can add or remove cache servers without restarting the database server by adding or deleting cache nodes from the list defined in the edb_icache_servers configuration parameter.

When you add one or more cache nodes, the server re-allocates the cache, dividing the cache evenly amongst the servers; each of the existing cache servers loses a percentage of the information that they have cached. You can calculate the percentage of the cache that remains valid with the following formula:

(existing_nodes * 100) / (existing_nodes + new_nodes)

For example, if an Advanced Server installation with three existing cache nodes adds an additional cache node, 75% of the existing cache remains valid after the reconfiguration

If cache nodes are removed from a server, the data that has been stored on the remaining cache nodes is preserved. If one cache server is removed from a set of five cache servers, Advanced Server preserves the 80% of the distributed cache that is stored on the four remaining cache nodes.

When you change the cache server configuration (by adding or removing cache servers), the portion of the cache configuration that is preserved is not re-written unless the cache is completely re-warmed using the edb_icache_warm() function or edb_icache_warm utility. If you do not re-warm the cache servers, new cache servers will accrue cache data as queries are performed on the server.

Infinite Cache Offers a Second Performance Advantage: Compression.

Without Infinite Cache, Advanced Server will read each page from disk as an 8K chunk; when a page resides in the shared buffer cache, it consumes 8K of RAM. With Infinite Cache, Postgres can compress each page before sending it to a cache server. A compressed page can take significantly less room in the secondary cache, making more space available for other data and effectively increasing the size of the cache. A compressed page consumes less network bandwidth as well, decreasing the amount of time required to retrieve a page from the secondary cache.

The fact that Infinite Cache can compress each page may make it attractive to configure a secondary cache server on the same computer that runs your Postgres server. If, for example, your computer is configured with 6GB of RAM, you may want to allocate a smaller amount (say 1GB) for the primary cache (the shared buffer cache) and a larger amount (4GB) to the secondary cache (Infinite Cache), reserving 1GB for the operating system. Since the secondary cache resides on the same computer, there is very little overhead involved in moving data between the primary and secondary cache. All data stored in the Infinite Cache is compressed so the secondary cache can hold many more pages than would fit into the (uncompressed) shared buffer cache. If you had allocated 5GB to the shared buffer cache, the cache could hold no more than 65000 pages (approximately). By assigning 4GB of memory to Infinite Cache, the cache may be able to hold 130000 pages (at 2x compression), 195000 pages (at 3x compression) or more. The compression factor that you achieve is determined by the amount of redundancy in the data itself and the edb_icache_compression_level parameter.

To use Infinite Cache, you must specify a list of one or more cache servers (computers on your network) and start the edb_icache daemon on each of those servers.

Infinite Cache is supported on Linux, HPUX and Solaris systems only.

Please Note: Infinite Cache and the effective_io_concurrency parameter can potentially interfere with each other. You should disable asynchronous I/O requests (by setting the value of effective_io_concurrency to 0 in the postgresql.conf file) if you enable the Infinite Cache feature.

Installing Infinite Cache

Advanced Server includes Infinite Cache functionality as part of a standard installation with either the graphical installer or the RPM installer. You can also optionally install only the Infinite Cache daemon on a supporting cache server.

For information about using the RPM packages to install Infinite Cache, see the EDB Postgres Advanced Server Installation Guide available at:

https://www.enterprisedb.com/docs

To use the graphical installer to install Advanced Server with Infinite Cache functionality, confirm that the box next to the Database Server option (located on the Select Components dialog) is selected when running the installation wizard.

The Database Server option installs the following Infinite Cache components:

• The edb-icache service script.
• The Infinite Cache configuration file (edb-icache).
• A command line tool that allows you to pre-load the cache servers (edbi-icache-warm).
• The edb_icache libraries (code libraries required by the edb-icache daemon).

The graphical installation wizard can selectively install only the Infinite Cache daemon on a cache server. To install the edb-icache daemon on a cache server, deploy the installation wizard on the machine hosting the cache; when the Setup: Select Components window opens, de-select all options except Infinite Cache.

The Infinite Cache Daemon option installs the following:

• The edb-icache service script.
• The Infinite Cache configuration file (edb-icache).
• A command line tool that allows you to pre-load the cache servers (edb-icache-warm).
• The edb_icache libraries (code libraries required by the edb-icache daemon).

Configuring the Infinite Cache Server

Configuring Infinite Cache is a three-step process:

• Specify Infinite Cache server settings in the Infinite Cache configuration file.
• Modify the Advanced Server postgresql.conf file, enabling Infinite Cache, and specifying connection and compression settings.
• Start the Infinite Cache service.

Modifying Infinite Cache Settings

The Infinite Cache configuration file is named edb-icache, and contains two parameters and their associated values:

PORT=11211
CACHESIZE=500

To modify a parameter, open the edb-icache file (located in the /opt/edb/icache/etc directory) with your editor of choice, and modify the parameter values:

PORT

Use the PORT variable to specify the port where Infinite Cache will listen for connections from Advanced Server.

CACHESIZE

Use the CACHESIZE variable to specify the size of the cache (in MB).

Enabling Infinite Cache

The postgresql.conf file includes three configuration parameters that control the behavior of Infinite Cache. The postgresql.conf file is read each time you start the Advanced Server database server. To modify a parameter, open the postgresql.conf file (located in the $PGDATA directory) with your editor of choice, and edit the section of the configuration file shown below:

# - Infinite Cache
#edb_enable_icache = off
#edb_icache_servers = '' #'host1:port1,host2,ip3:port3,ip4'
#edb_icache_compression_level = 6

Lines that begin with a pound sign (#) are treated as comments; to enable a given parameter, remove the pound sign and specify a value for the parameter. When you've updated and saved the configuration file, restart the database server for the changes to take effect.

edb_enable_icache

Use the edb_enable_icache parameter to enable or disable Infinite Cache. When edb_enable_icache is set to on, Infinite Cache is enabled; if the parameter is set to off, Infinite Cache is disabled.

If you set edb_enable_icache to on, you must also specify a list of cache servers by setting the edb_icache_servers parameter (described in the next section).

The default value of edb_enable_icache is off.

edb_icache_servers

The edb_icache_servers parameter specifies a list of one or more servers with active edb-icache daemons. edb_icache_servers is a string value that takes the form of a comma-separated list of hostname:port pairs. You can specify each pair in any of the following forms:

• hostname

• IP-address

• hostname:portnumber

• IP-address:portnumber

  If you do not specify a port number, Infinite Cache assumes that the cache server is listening at port 11211. This configuration parameter will take effect only if edb_enable_icache is set to on. Use the edb_icache_servers parameter to specify a maximum of 128 cache nodes.

edb_icache_compression_level

The edb_icache_compression_level parameter controls the compression level that is applied to each page before storing it in the distributed Infinite Cache. This parameter must be an integer in the range 0 to 9.

• A compression level of 0 disables compression; it uses no CPU time for compression, but requires more storage space and network resources to process.
• A compression level of 9 invokes the maximum amount of compression; it increases the load on the CPU, but less data flows across the network, so network demand is reduced. Each page takes less room on the Infinite Cache server, so memory requirements are reduced.
• A compression level of 5 or 6 is a reasonable compromise between the amount of compression received and the amount of CPU time invested.

By default, edb_icache_compression_level is set to 6.

When Advanced Server reads data from disk, it typically reads the data in 8K increments. If edb_icache_compression_level is set to 0, each time Advanced Server sends an 8K page to the Infinite Cache server that page is stored (uncompressed) in 8K of cache memory. If the edb_icache_compression_level parameter is set to 9, Advanced Server applies the maximum compression possible before sending it to the Infinite Cache server, so a page that previously took 8K of cached memory might take 2K of cached memory. Exact compression numbers are difficult to predict, as they are dependent on the nature of the data on each page.

The compression level must be set by the superuser and can be changed for the current session while the server is running. The following command disables the compression mechanism for the currently active session:

SET edb_icache_compression_level = 0

The following example shows a typical collection of Infinite Cache settings:

edb_enable_icache            = on
edb_icache_servers           = 'localhost,192.168.2.1:11200,192.168.2.2'
edb_icache_compression_level = 6

Please Note: Infinite Cache and the effective_io_concurrency parameter can potentially interfere with each other. You should disable asynchronous I/O requests (by setting the value of effective_io_concurrency to 0 in the postgresql.conf file) if you enable the Infinite Cache feature. By default, effective_io_concurrency is set to 1.

Controlling the Infinite Cache Server

On Linux, the Infinite Cache service script is named edb-icache. The service script resides in the /etc/init.d directory. You can control the Infinite Cache service, or check the status of the service with the following command:

/etc/init.d/edb-icache <action>

Where action specifies:

• start to start the service.
• stop to stop the service
• restart to stop and then start the service.
• status to return the status of the service.

Dynamically Modifying Infinite Cache Server Nodes

You can dynamically modify the Infinite Cache server nodes; to change the Infinite Cache server configuration, use the edb_icache_servers parameter in the postgresql.conf file to:

• specify additional cache information to add a server/s.
• delete server information to remove a server/s.
• specify additional server information and delete existing server information to both add and delete servers during the same reload operation.

After updating the edb_icache_servers parameter in the postgresql.conf file, you must reload the configuration parameters for the changes to take effect. You can use the menu-driven reload options (navigate through the EDB Postgres menu to the Advanced Server 10 menu; continue to the Expert Configuration menu, and select the Reload Configuration option). If prompted, enter your password to reload the configuration parameters.

Alternatively, you can use the pg_ctl reload command to update the server's configuration parameters at the command line:

pg_ctl reload -D <data_directory>

Where data_directory specifies the complete path to the data directory.

Please Note: If the server detects a problem with the value specified for the edb_icache_servers parameter during a server reload, it will ignore changes to the parameter and use the last valid parameter value. If you are performing a server restart, and the parameter contains an invalid value, the server will return an error

Controlling the edb-icache Daemons

edb-icache is a high-performance memory caching daemon that distributes and stores data in shared buffers. The server transparently interacts with edb-icache daemon to store and retrieve data.

Before starting the database server, the edb-icache daemon must be running on each server node. Log into each server and start the edb-icache server (on that host) by issuing the following command:

# edb-icache -u enterprisedb -d -m 1024

Where:

-u

-u specifies the user name.

-m

-m specifies the amount of memory to be used by edb-icache. The default is 64MB.

-d

-d designates that the service should run in the background.

To gracefully kill an edb-icache daemon (close any in-use files, flush buffers, and exit), execute the command:

# killall -TERM edb-icache

If the edb-icache daemon refuses to die, you may need to use the following command:

# killall -KILL edb-icache

Command Line Options

To view the command line options for the edb-icache daemon, use the following command:

# /opt/edb/icache/bin/edb-icache -h

The command line options are:

edb-icache-tool

edb-icache-tool provides a command line interface that queries the edb-icache daemon to retrieve statistical information about a specific cache node. The syntax is:

edb-icache-tool <host[:port]> stats

host specifies the address of the host that you are querying. port specifies the port that the daemon is listening on.

edb-icache-tool retrieves the statistics described in the following table:

In the following example, edb-icache-tool retrieves statistical information about an Infinite Cache server located at the address, 192.168.23.85 and listening on port 11213:

     # edb-icache-tool 192.168.23.85:11213 stats
Field                      Value
accepting_conns            1
auth_cmds                  0
auth_errors                0
bytes                      52901223
bytes_read                 188383848
bytes_written              60510385
cas_badval                 0
cas_hits                   0
cas_misses                 0
cmd_flush                  1
cmd_get                    53139
cmd_set                    229120
conn_yields                0
connection_structures      34
curr_connections           13
curr_items                 54953
decr_hits                  0
decr_misses                0
delete_hits                0
delete_misses              0
evictions                  0
get_hits                   52784
get_misses                 355
incr_hits                  0
incr_misses                0
limit_maxbytes             314572800
listen_disabled_num        0
pid                        7226
pointer_size               32
reclaimed                  0
rusage_system              10.676667
rusage_user                3.068191
threads                    4
time                       1320919080
total_connections          111
total_items                229120
uptime                     7649
version                    1.4.5

Warming the edb-icache Servers

When the server starts, the primary and secondary caches are empty. When Advanced Server processes a client request, the server reads the required data from disk and stores a copy in each cache. You can improve server performance by warming (or pre-loading) the data into the memory cache before a client asks for it.

There are two advantages to warming the cache. Advanced Server will find data in the cache the first time it is requested by a client application, instead of waiting for it to be read from disk. Also, manually warming the cache with the data that your applications are most likely to need saves time by avoiding future random disk reads. If you don't warm the cache at startup, Advanced Server performance may not reach full speed until the client applications happen to load commonly used data into the cache.

There are several ways to load pages to warm the Infinite Cache server nodes. You can:

• Use the edb_icache_warm utility to warm the caches from the command line.
• Use the edb_icache_warm() function from within edb-psql.
• Use the edb_icache_warm() function via scripts to warm the cache.

While it is not necessary to re-warm the cache after making changes to an existing cache configuration, re-warming the cache can improve performance by bringing the new configuration of cache servers up-to-date.

The edb_icache_warm() Function

The edb_icache_warm() function comes in two variations; the first variation warms not only the table, but any indexes associated with the table. If you use the second variation, you must make additional calls to warm any associated indexes.

The first form of the edb_icache_warm() function warms the given table and any associated indexes into the cache. The signature is:

edb_icache_warm(table_name)

You may specify table_name as a table name, OID, or regclass value.

# edb-psql edb -c "select edb_icache_warm('accounts')"

When you call the first form of edb_icache_warm(), Advanced Server reads each page in the given table, compresses the page (if configured to do so), and then sends the compressed data to an Infinite Cache server. edb_icache_warm() also reads, compresses, and caches each page in each index defined for the given table.

The second form of the edb_icache_warm() function warms the pages that contain the specified range of bytes into the cache. The signature of the second form is:

edb_icache_warm(table-spec, startbyte, endbyte):

You must make subsequent calls to specify indexes separately when using this form of the edb_icache_warm() function.

# edb-psql edb -c "select edb_icache_warm('accounts', 1, 10000)"

The edb_icache_warm() function is typically called by a utility program (such as the edb_icache_warm utility) to spread the warming process among multiple processes that operate in parallel.

Using the edb_icache_warm Utility

You can use the edb_icache_warm command-line utility to load the cache servers with specified tables, allowing fast access to relevant data from the cache.

The syntax for edb_icache_warm is:

# edb_icache_warm –d database –t tablename

The only required parameter is tablename. tablename can be specified with or without the -t option. All other parameters are optional; if omitted, default values are inferred from Advanced Server environment variables.

The options for edb_icache_warm are:

Retrieving Statistics from Infinite Cache

Using edb_icache_stats()

You can view Infinite Cache statistics by using the edb_icache_stats() function at the edb-psql command line (or any other query tool). The edb_icache_stats() function returns a result set that reflects the state of an Infinite Cache node or nodes and the related usage statistics. The result set includes:

You can use SQL queries to view Infinite Cache statistics. To view the server status of all Infinite Cache nodes:

SELECT hostname, port, state FROM edb_icache_stats()

 hostname       | port  | state
----------------+-------+--------
 192.168.23.85  | 11211 | UNHEALTHY
 192.168.23.85  | 11212 | ACTIVE
(2 rows)

Use the following command to view complete statistics (shown here using edb-psql's expanded display mode, \x) for a specified node:

SELECT * FROM edb_icache_stats() WHERE hostname = '192.168.23.85:11211'

-[RECORD 1]-----------+--------------
hostname              | 192.168.23.85
port                  | 11211
state                 | ACTIVE
write_failures        | 0
bytes                 | 225029460
bytes_read            | 225728252
bytes_written         | 192806774
cmd_get               | 23313
cmd_set               | 27088
connection_structures | 53
curr_connections      | 3
curr_items            | 27088
evictions             | 0
get_hits              | 23266
get_misses            | 47
limit_maxbytes        | 805306368
pid                   | 4240
pointer_size          | 32
rusage_user           | 0.481926
rusage_system         | 1.583759
threads               | 1
total_time            | 1242199782
total_connections     | 66
total_items           | 27088
uptime                | 714
version               | 1.2.6

edb_icache_server_list

The edb_icache_server_list view exposes information about the status and health of all Infinite Cache servers listed in the edb_icache_servers GUC. The edb_icache_server_list view is created using the edb_icache stats() API. The view exposes the following information for each server:

The state column will contain one of the following four values, reflecting the health of the given server:

Use the following SELECT statement to return the health of each node in the Infinite Cache server farm:

SELECT hostname, port, state FROM edb_icache_server_list

 hostname      | port  | state
---------------+-------+-------
 192.168.23.85 | 11211 | ACTIVE
 192.168.23.85 | 11212 | ACTIVE
(2 rows)

Use the following command to view complete details about a specific Infinite Cache node (shown here using edb-psql's \x expanded-view option):

SELECT * FROM edb_icache_server_list WHERE hostname = '192.168.23.85:11211'

-[RECORD 1]-----------+--------------
hostname              | 192.168.23.85
port                  | 11211
state                 | ACTIVE
write_failures        | 0
total_memory          | 805306368
memory_used           | 225029460
memory_free           | 580276908
hit_ratio             | 99.79

Retrieving Table Statistics

Advanced Server provides six system views that contain statistical information on a per-table basis. The views are:

• pg_statio_all_tables
• pg_statio_sys_tables
• pg_statio_user_tables
• pg_statio_all_indexes
• pg_statio_sys_indexes
• pg_statio_user_indexes

You can use standard SQL queries to view and compare the information stored in the views. The views contain information that will allow you to observe the effectiveness of the Advanced Server buffer cache and the icache servers.

pg_statio_all_tables

The pg_statio_all_tables view contains one row for each table in the database. The view contains the following information:

You can execute a simple query to view performance statistics for a specific table:

SELECT * FROM pg_statio_all_tables WHERE relname=’jobhist’;

-[ RECORD 1 ]---------+---------
relid                 | 16402
schemaname            | public
relname               | jobhist
heap_blks_read        | 1
heap_blks_hit         | 51
heap_blks_icache_hit  | 0
idx_blks_read         | 2
idx_blks_hit          | 17
idx_blks_icache_hit   | 0
toast_blks_read       |
toast_blks_hit        |
toast_blks_icache_hit |
tidx_blks_read        |
tidx_blks_hit         |
tidx_blks_icache_hit  |

Or, you can view the statistics by activity level. The following example displays the statistics for the ten tables that have the greatest heap_blks_icache_hit activity:

SELECT * FROM pg_statio_all_tables ORDER BY heap_blks_icache_hit DESC LIMIT 10;

relid       schemaname                         relname
   heap_blks_read    heap_blks_hit             heap_blks_icache_hit
  idx_blks_read      idx_blks_hit              idx_blks_icache_hit
  toast_blks_read    toast_blks_hit            toast_blks_icache_hit
  tidx_blks_read     tidx_blks_hit             tidx_blks_icache_hit
-----------------------------------------------------------------------------
16390      public                              pgbench_accounts
   264105            71150                     81498
   13171             282541                    18053

1259       pg_catalog                          pg_class
   22                2904                      18
   14                3449                      11

1249       pg_catalog                          pg_attribute
   49                1619                      16
   17                2841                      13
1255       pg_catalog                          pg_proc
   38                276                       11
   33                682                       16
   0                 0                         0
   0                 0                         0
2619       pg_catalog                          pg_statistic
   20                295                       8
   4                 436                       4
   0                 0                         0
   0                 0                         0
2617       pg_catalog                          pg_operator
   20                293                       8
   19                791                       10
2602       pg_catalog                          pg_amop
   10                721                       6
   13                1154                      13
2610       pg_catalog                          pg_index
   10                633                       6
   8                 719                       8
1247       pg_catalog                          pg_type
   17                235                       5 
   12                433                       4
2615       pg_catalog                          pg_namespace
   4                 260                       4
   6                 330                       4
   0                 0                         0
   0                 0                         0
 (10 rows)

pg_statio_sys_tables

The pg_statio_sys_tables view contains one row for each table in a system-defined schema. The statistical information included in this view is the same as for pg_statio_all_tables.

pg_statio_user_tables

The pg_statio_user_tables view contains one row for each table in a user-defined schema. The statistical information in this view is the same as for pg_statio_all_tables.

pg_statio_all_indexes

The pg_statio_all_indexes view contains one row for each index in the current database. The view contains the following information:

You can execute a simple query to view performance statistics for the indexes on a specific table:

SELECT * FROM pg_statio_all_indexes WHERE relname=’pg_attribute’;

-[ RECORD 1 ]---------+---------
relid                 | 1249
indexrelid            | 2658
schemaname            | pg_catalog
relname               | pg_attribute
indexrelname          | pg_attribute_relid_attnam_index
idx_blks_read         | 10
idx_blks_hit          | 1200
idx_blks_icache_hit   | 0
-[ RECORD 2 ]---------+---------
relid                 | 1249
indexrelid            | 2659
schemaname            | pg_catalog
relname               | pg_attribute
indexrelname          | pg_attribute_relid_attnum_index
idx_blks_read         | 12
idx_blks_hit          | 3917
idx_blks_icache_hit   | 0

The result set from the query includes the statistical information for two indexes; the pg_attribute table has two indexes.

You can also view the statistics by activity level. The following example displays the statistics for the ten indexes that have the greatest idx_blks_icache_hit activity:

SELECT * FROM pg_statio_all_indexes ORDER BY idx_blks_icache_hit DESC LIMIT 10;

relid   indexrelid   schemaname   relname
indexrelname                  idx_blks_read   idx_blks_hit   idx_blks_icache_hit
---------------------------------------------------------------------------------
16390   16401        public       pgbench_accounts
pgbench_accounts_pkey         13171           282541         18053
1249    2659         pg_catalog   pg_attribute
pg_attr_relid_attnum_index    14              2749           13
1255    2690         pg_catalog   proc
pg_proc_oid_index             16              580            12
1259    2663         pg_catalog   pg_class
pg_class_relname_nsp_index    10              2019           7
2602    2654         pg_catalog   pg_amop
pg_amop_opr_fam_index         7               453            7
2603    2655         pg_catalog   pg_amproc
pg_amproc_fam_proc_index      6               605            6
2617    2688         pg_catalog   pg_operator
pg_operator_oid_index         7               452            6
2602    2653         pg_catalog   pg_amop
pg_amop_fam_strat_index       6               701            6
2615    2684         pg_catalog   pg_namespace
pg_namespace_nspname_index    4               328            4
1262    2672         pg_catalog   pg_database
pg_database_oid_index         4               254            4

pg_statio_sys_indexes

The pg_statio_sys_indexes view contains one row for each index on the system tables. The statistical information in this view is the same as in pg_statio_all_indexes.

pg_statio_user_indexes

The pg_statio_user_indexes view contains one row for each index on a table that resides in a user-defined schema. The statistical information in this view is the same as in pg_statio_all_indexes.

edb_icache_server_enable()

You can use the edb_icache_server_enable() function to take the Infinite Cache server offline for maintenance or other planned downtime. The syntax is:

void edb_icache_server_enable(host TEXT, port INTEGER, online BOOL)

host specifies the host that you want to disable. The host name may be specified by name or numeric address.

port specifies the port number that the Infinite Cache server is listening on.

online specifies the state of the Infinite Cache server. The value of online must be true or false.

To take a server offline, specify the host that you want to disable, the port number that the Infinite Cache server is listening on, and false. To bring the Infinite Cache server back online, specify the host name and port number, and pass a value of true.

The state of a server taken offline with the edb_icache_server_enable() function is MANUAL OFFLINE. Advanced Server will not automatically reconnect to an Infinite Cache server that you have taken offline with edb_icache_server_enable(..., false); you must bring the server back online by calling edb_icache_server_enable(..., true).

Infinite Cache Log Entries

When you start Advanced Server, a message that includes Infinite Cache status, cache node count and cache node size is written to the server log. The following example shows the server log for an active Infinite Cache installation with two 750 MB cache servers:

** EnterpriseDB Dynamic Tuning Agent**************************************
*       System Utilization: 66 %                                         *
*       Autovacuum Naptime: 60 Seconds                                   *
*       Infinite Cache: on                                               *
*       Infinite Cache Servers: 2                                        *
*       Infinite Cache Size: 1.500 GB                                    *
**************************************************************************

Allocating Memory to the Cache Servers

As mentioned earlier in this document, each computer imposes a limit on the amount of physical memory that you can install. However, modern operating systems typically simulate a larger address space so that programs can transparently access more memory than is actually installed. This "virtual memory" allows a computer to run multiple programs that may simultaneously require more memory than is physically available. For example, you may run an e-mail client, a web browser, and a database server which each require 1GB of memory on a machine that contains only 2GB of physical RAM. When the operating system runs out of physical memory, it starts swapping bits and pieces of the currently running programs to disk to make room to satisfy your current demand for memory.

This can bring your system to a grinding halt.

Since the primary goal of Infinite Cache is to improve performance by limiting disk I/O, you should avoid dedicating so much memory to Infinite Cache that the operating system must start swapping data to disk. If the operating system begins to swap to disk, you lose the benefits offered by Infinite Cache.

The overall demand for physical memory can vary throughout the day; if the server is frequently idle, you may never encounter swapping. If you have dedicated a large portion of physical memory to the cache, and system usage increases, the operating system may start swapping. To get the best performance and avoid disk swapping, dedicate a server node to Infinite Cache so other applications on that computer will not compete for physical memory.

• On this page
• Dynatune
• Infinite Cache