Jan 05

What is pg_extractor ?

Posted on by

In my recent blog post, I wrote about PostgreSQL DBA Handyman toolset. In the list of tools, getddl is one of them. If you are using getddl to get DDL schema and track the daily changes in SVN for production databases, you should consider moving that process to use pg_extractor instead. pg_extractor is the more advance and robust tool for extracting schema as well data using pg_dump. Keith Fiske, an author of the tool, described tool in detail in his blog post. Thanks to Keith for making the schema extraction tool more robust and taking it to next level !

Hopefully, it will help you to have more control over your database in smarter way!

Dec 06

“Shrink” MS SQL Server transaction logfile

Posted on by
          Microsoft SQL Server transactions log file “too large” is common problem. We need to manually shrink the transaction log file as one time operation and after that setting up proper recovery policy and/or keeping mirror server available all the time , we could  avoid  infinite growth of transactions log in future.
          Transaction log file growth is dependent on one of the following Recovery method used for the instance:
 1. Simple
 2. Full
 3. Bulk-Logged

          If your database is in the Simple recovery mode, then the transaction log is truncated of inactive transaction after the checkpoint process occurs.  The checkpoint process writes all modified data pages from memory to disk.  When the checkpoint is performed, the inactive portion of the transaction log is marked as reusable.
          SQL Server 2005 databases are set to the Full recovery model by default.  With the Full or Bulk-Logged recovery mode, inactive transactions remain in the transaction log file until after a Checkpoint is processed and a transaction log backup is made.  It’s important to note that a full backup does not remove inactive transactions from the transaction log.  If  database recovery model is set to Full or Bulk-Logged, then it is absolutely IMPORTANT that we make transaction log backups to go along with  full backups because full backup does not remove inactive transactions from the transaction log. The transaction log backup performs a truncation of the inactive portion of the transaction log and allow them to used for future transactions. Note that truncation of log file does not  shrink already bloated log file that’s the operation we have to do manually one time  and if we set proper procedure in future, we could avoid unnecessary growth of log file. Other factors affecting transaction log to grow are:

  1. Replicated mirrored server falls behind master server
  2. Long running transactions

To solve #1:
          Database mirroring is uses transaction logs for replication in that it requires that the transactions remain in the log until the record has been written to disk on the mirror server. If the mirror server  falls behind the master server , the amount of active log space will grow. In this case, you might need to stop database mirroring, take a log backup that truncates the log, apply that log backup to the mirror database and restart mirroring.

To solve #2: ( it’s non-issue for most of the OLTP servers) :
          If there are open transactions, DBCC OPENTRAN will provide a session_id or SPID of the connection that has the transaction open.  You can pass this session_id to sp_who2 to determine which user has the connection open.

              Following queries can be ued to detemine recovery model and transaction log truncation interval:

SELECT name, recovery_model_desc  FROM sys.databases;

master SIMPLE
tempdb SIMPLE
model FULL
msdb SIMPLE
ReportServer SIMPLE
ReportServerTempDB SIMPLE
PRODUCTION_DB FULL

SELECT name, log_reuse_wait_desc FROM sys.databases;

master ACTIVE_TRANSACTION
tempdb ACTIVE_TRANSACTION
model NOTHING
msdb NOTHING
ReportServer NOTHING
ReportServerTempDB NOTHING
PRODUCTION_DB LOG_BACKUP

          Results from above queries reveals that PRODUCTION_DB database is setup for FULL database backup and transaction log will not be truncated until LOG_BACKUP.  Make sure that you take full backup followed by log backup that will make transactions log file’s inactive blocks to be available for re-write and prevent from growing further. Keep in mind that it will not shrink transaction log file . To shrink the transaction log file you can follow steps described in official docs  OR following is the simple example of identifying and shrinking log file after taking log file backup:

                SELECT name FROM sys.database_files WHERE type_desc = ‘LOG’

         Once I have log file name, I can use the DBCC command to shrink the file. In the following command I try to shrink my log file down to 1GB.
                 DBCC SHRINKFILE (’PRODUCTION_DB_log.log’, 1000)     
       Run the shrink command couple of times during the off-peak hours. Also, please  make sure that your databases are NOT set to auto-shrink. Auto-shrink database might encounter performance problems.

          Hopefully, it will help you next time you come across transaction log file “too large” issue on MS SQL server installation.

Dec 06

Faster & Better VACUUM FULL

Posted on by
                In presentation, I discussed in detail about Bloat issue in PostgreSQL and methods to remove Bloat from the tables/indexes. Now a days, PostgreSQL9.0 is common and  the widely used version for the production use and it’s vital to remind about changes in most important bloat removal tool called “VACUUM FULL”. Until PostgreSQL 9.0, VACUUM FULL was tardy and DBA always stayed away from it and used CLUSTER instead. (Checkout presentation for difference between CLUSTER vs VACUUM FULL)
                The VACUUM FULL statement recovers free space from a table to reduce its size from bloated tables, mostly when VACUUM itself hasn’t been run frequently enough. Before PostgreSQL 9.0 , it was tardy and slow because of the way it was executed: records were read and moved one by one from their source block to a block closer to the beginning of the table. Once the end of the table was emptied, this empty part was removed. This method was very inefficient: moving records one by one creates a lot of random IO.  Additionally, during this reorganization, indexes had to be maintained, making everything even more costly, and fragmenting indexes. It was therefore advised to reindex a table just after a VACUUM FULL.
                Now, the VACUUM FULL statement, as of PostgreSQL 9.0, creates a new table from the current one, copying all the records sequentially. Once all records are copied, index are created back, and the old table is destroyed and replaced. This has the advantage of being much faster. VACUUM FULL still needs an EXCLUSIVE LOCK  during entire operation. The only drawback of this method compared to the old one, is that VACUUM FULL can use as much as two times the size of the table and indexes on disk, as it is creating a new versions of it.

Let’s compare run-time of VACUUM FULL on PostgreSQL 8.4 vs PostgreSQL 9.0

postgres=# create table vacuumtest(id int primary key);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index “vacuumtest_pkey” for table “vacuumtest”
CREATE TABLE
postgres=# insert into vacuumtest select generate_series(1,10000000);
INSERT 0 10000000
postgres=# delete from vacuumtest where id%4=0;
DELETE 2500000
postgres=# vacuum vacuumtest;
VACUUM

On 8.4:
postgres=# vacuum full vacuumtest ;
VACUUM
Time: 61418.197 ms
postgres=# reindex table vacuumtest;
REINDEX
Time: 12212.815 ms

On 9.0:
postgres=# vacuum full vacuumtest ;
VACUUM
Time: 32640.714 ms

                Above results show that VACCUM FULL on PostgreSQL 9.0 is way faster than previous versions. Moreover, VACUUM FULL has couple of advantages over CLUSTER :  it’s faster than CLUSTER because it doesn’t have to build new table using ORDER by clause & you can run VACUUM FULL on tables on which there isn’t any index.
                If you are running any bloat removal tool on the production database, i would recommend you to revisit vacuum parameters and tighten them up a little that makes regular vacuum to run more frequent so it will help to reduce frequency of running more intrusive bloat removal tools!!

Nov 11

PostgreSQL Handyman Toolset

Posted on by
     A PostgreSQL handyman is a person skilled at a wide range of maintenance and repairs, typically around the PostgreSQL database system.  If you are working with PostgreSQL and you have right tools for  the job, you can easily accomplish that job efficiently and most importantly on time. In real world, if you want to dig into land and you don’t have tools then you are failed at job before you start the job!  Having tools for any job is most important factor for job success. While working at OmniTI, I tested and used variety of tools to make my work life easy and efficient.

      PostgreSQL community is thriving by people, those helps to build new tools, give it back to community for re-use and providing helping hands to test the tools. Here are some of my suggested list of tools which could help  to finish your job efficiently and on time!!

 1.  Tasty Treats for PostgreSQL project includes plenty of PostgreSQL management tools , which is one of the projects of OmniTI’s Lab repository.

Some of the tools includes:

  • zbackup
  • getddl
  • pgsniff
  • table growth report
  • quickstats
  • pg_log monitor
  • bloat reports
  • pgtruss
  • system monitoring
  • compact table
  • blocked queries
  • Curo

2.  OMNIpitr Advanced WAL File Management Tools for PostgreSQL. This tool helps to handle HA processes efficiently to manage wal shipping, restore & removal , online backup on slave and/or master.

3.  bucardo.org also includes mission critial tools:

  •  Bucardo  
    • Asynchronous PostgreSQL Replication System
  • check_postgres  
    • Nagios-Friendly PostgreSQL Database Checking Script
  • pgsi 
    • Wiki-Ready Query Reports
  • tail_n_mail 
    • Monitor log files
  • boxinfo 
    • Gather system information

4.  repmgr: it allows you to monitor and manage  high availability part of  PostgreSQL databases (9.0+) installation.

5. pg_reorg: it allows you to rebuild Bloated tables online. Review these slides to understand how it works.
 
6.  RubyRep : Components of this tool set can help you compare and sync two tables or even two databases.

I would love to know  tools used by you for managing PostgreSQL database server which are missing from my PostgreSQL handyman too-lset !!

Hopefully, these tools will make your next task easy!! If you find bug, don’t hesitate to report to send out email to relevant tool maintainer with error or suggestion or patch with bug fix.

DISCLAIMER: use any of listed tool on production at your own risk.

Sep 14

The Scalability & Performance Conference : Surge 2011

Posted on by
         The Scalability and Performance covers most of the web scale issues, innovations and  evolutions in technology. As per my knowledge, Surge is the only conference  that gives the coverage to the issues and solutions concerning Scalability & Performance without any bias on technology that we often notice at most other conferences. For example , if you are looking from Database perspective, it covers topic ranges from RDBMS to NoSQL and talks about problems solved by the technology and new possible problems introduced by new technology.

      I attended Surge 2010 and listed attended sessions on my last year  post. I am going to attend Surge again. Surge 2010 was the best conference attendance experience for me and others. By looking at Sponsor list  (Google is one of them!), this year (Sept 28-30 , 2011) conference is going to be far bigger and better than last year  and don’t forget to checkout industry leader Speakers list & Hack Day/Training Schedule !!  If you haven’t registered , you still have chance to do so.

Surge 2011 will be surge of the knowledge ! See you soon !

Jul 27

Monitoring Riak using Circonus

Posted on by
       One of the core responsibilities that all engineering disciplines share at OmniTi is assessing the appropriateness of the technologies at hand. Recently, I have been assessing “NoSQL” database technology called Riak. As a DBA, I would like to learn a way to monitor the database server without wasting lots of time and energy! At OmniTi, we use Circonus to monitor, trend and alert on checks for the production servers . It turns out you can plug all the critical Riak Stats metrics into Circonus with no effort and very little time.Let me introduce both the technologies before I walk you through steps to plug the checks into the Circonus monitoring system.

What is Riak?

       Riak is a key/value store “NoSQL” distributed web scale database and written primarily in Erlang.
What is Circonus?
    Circonus , a SAAS offering,  gives a cohesive view of how IT affects business. Translating monitoring and trending into business sense—this is where Circonus shines. Circonus brings network monitoring back to a business intelligence system that is capable of graphing, trending, reporting and fault detection. 

    I am not going into details for installing and setting up Riak database server but it’s pretty easy to follow instruction on the Basho Wiki Page.  In my example, I have ec2 instance fired up and running riak database already and it is connected to 2 other nodes in the ring.  It’s easy to get Riak stats  from HTTP API  access. In this example, I am going to plug in some of the important metrics from  riak status  output provided by http stats command. 
Let’s assume that we want to monitor riak running on 10.19.143.96:8098 and circonus agent can access following url to get stats from the server :
http://10.19.143.96:8098/stats
1.  If you don’t have Circonus Account you can Sign Up using easy sign up process by clicking on “Sign Up” button on the top right corner on http://circonus.com/
2.  You will get the password in your email and use it for changing your account password and login to circonus.
3. Under “Checks/Metrics” and Click on “+” Sign to add new checks. It will pop-up new screen to select check type.
4.  Web -> General -> JSON and Select the Circonus Agent to use.
5.  Set  hostname=10.19.143.96 , URI=/stats and Port=8098 and click on Test check

 

6.  Next screen will give you all the metrics rerurned by the stats output and you can select the import ones and hit the “Finish”
7. Choose and enable required metrics. Out of 70 metrics provided by Stats output , I “enabled” only 12 important metrics for now. Though I will be looking for advice from you guys to add more metrics for monitoring, trending and alerting purposes.
8. Once they are getting tracked , you can generate graphs like:

 

      Yay! you have all the important metrics are in the circonus and you can generate graphs whenever you want and create worksheet per server or data center!!
I could add all the required checks for Riak Database server  under 5 minutes into Circonus!!  Share your experience and let me know if you find difficulty working with Circonus.
May 05

When was my database started ?

Posted on by

In the database world, it’s fair to ask question to database that when have you started? In Oracle world, it can be easily find out by querying system level view V$INSTANCE. In PostgreSQL world, until today, I wasn’t able to answer that question without checking database log files. Today, I asked this question to OmniTi’s database operations irc channel and got answer in 20 minutes. I regret that I should have asked it earlier but anyways, now I know that in PostgreSQL it’s possible using this query. 


omniti=# SELECT (pg_stat_file( 'postmaster.pid')).modification;
      modification      
------------------------
 2010-08-13 15:37:14-04

If someone complains that database was restarted , it would be easy as DBA to query and verify it. I hope this query will be helpful to you during day-to-day operations!

Mar 14

Extreme Training Session at PgEast: P90X your Database!

Posted on by

Over-allocated space in tables and indexes is a very common problem in PostgreSQL database where explosive data growth and changes occurs. This over allocated space is called “bloat”. Bloated tables and indexes waste resources of the system and cause large performance anomaly to the applications. Vacuum process helps to avoid bloat but some cases where even vacuum can not able to recover space for reuse. To shrink tables in such cases, DBA needs special attention and tools to remove bloat from the tables and indexes.

At PgEast 2011, I will discuss PostgreSQL MVCC design (compare with Oracle and MySQL) , ways to avoid bloat and online & offline methods to remove bloat from the tables and indexes. P90X your database talk will help you to keep databases skinnier and faster. If you attending this conference, looking forward to hear your experiences and tools that helps to avoid and remove bloat.