{"id":1653,"date":"2018-08-14T08:54:03","date_gmt":"2018-08-14T08:54:03","guid":{"rendered":"http:\/\/dbtut.com\/?p=1653"},"modified":"2018-11-09T23:00:18","modified_gmt":"2018-11-09T23:00:18","slug":"pg-tuning-statistics-and-indexes","status":"publish","type":"post","link":"https:\/\/dbtut.com\/index.php\/2018\/08\/14\/pg-tuning-statistics-and-indexes\/","title":{"rendered":"PG Tuning, Statistics and Indexes"},"content":{"rendered":"

Database Tuning<\/strong><\/span><\/p>\n

Fundamentally, database performance tuning is done for two basic reasons, to reduce response time and to reduce resource\u00a0usage, both of which can apply for any given situation. Julian Stuhler looks at database performance tuning, and why it remains\u00a0one of the most important topics for any DBA, developer or systems administrator.<\/p>\n

Why Bother with Database Performance?<\/strong><\/p>\n

So, what\u2019s the big deal with database performance? Fundamentally, database performance tuning is done\u00a0for two basic reasons, and either or both can apply for any given situation:<\/p>\n

Reduce response time. With today\u2019s internet-enabled, customer facing applications, providing sub-second\u00a0response times for OLTP applications has never been more critical. A few years ago, a skilled telephone\u00a0customer support operative could hide longer response times for account queries by chatting with the
\ncaller, but now that the customer has direct access to those same systems, any delays become much more\u00a0obvious. Many studies have demonstrated the link between response time and customer satisfaction for\u00a0online B2C applications, and when your nearest competitor is only a click away, keeping customers happy is
\nparamount. Although database performance is just one component of the overall end-to-end response\u00a0time experienced by the customer, it is often the most critical and variable one.<\/p>\n

Reduce resource usage. Running any sort of query against a database generates a load on the database\u00a0server, and the less efficiently that query runs the more CPU and I\/O it will require. These are finite\u00a0resources for a given server configuration, so the transaction throughput can be limited at peak times
\n(potentially leading to a response time issue as described above). Tuning a system to reduce the CPU\u00a0and\/or I\/O resources required can have a significant impact on the transaction throughput for a given\u00a0server, potentially allowing expensive upgrades to be avoided or deferred. Reducing resource usage can be
\neven more important in a mainframe environment, where most customers are on some form of Monthly\u00a0Licence Charge (MLC) model. With MLC, the amount a System z customer pays depends on the amount of\u00a0processor resource consumed during the previous period, measured in MSUs (Million Service Units). So, the
\nmonthly cost paid by most customers for their IBM software is directly related to the amount of work their\u00a0CPs process: the higher the workload, the higher the monthly cost. Tuning the application to reduce CPU\u00a0costs can generate immediate savings in software licence fee charges.<\/p>\n

Database Statistics<\/strong><\/span><\/span><\/p>\n