PostgreSQL Error – WARNING: canceling wait for synchronous replication due to user request

PostgreSQL Error – WARNING: canceling wait for synchronous replication due to user request

I needed to create index on my PostgreSQL cluster today. It was 1 master performing synchronous streaming replication to 1 slave. I did the following:

1. Created a new tablespace on master to store all the indexes.
#pgsql create tablespace tablespace_index location ‘/var/lib/pgsql/9.2/data/tablespace';

2. Create the actual index itself.
CREATE INDEX CONCURRENTLY certificatedata_idx2 ON CertificateData (username) tablespace tablespace_index;

The index creation hangs. I checked the pg_stat_activity and saw the the query was still active. It took me about 2 hours before I decide to stop the index creation. When I did a Ctrl-C, I saw the following:

WARNING: canceling wait for synchronous replication due to user request
 DETAIL: The transaction has already committed locally, but might not have been replicated to the standby.
 ^CCancel request sent
 ERROR: canceling statement due to user request

The error speaks for itself. It means you have an issue with the replication to the slave database. Now, this is weird. I decided to look at the log files. And I found out that the replication has stopped because I did not create the tablespace directory (/var/lib/pgsql/9.2/data/tablespace) on the slave! Thus, the replication process could not create an tablespace and the slave node crashes. After creating the directory, the index creation took only less than 20 minutes on a 7GB table!

Thankfully, all these was done in the staging environment.

Regards,
Wei Shan

Leave a comment

What to do when you join a company as a new DBA

What to do when you join a company as a new DBA

When we take up a new job and join a company as a new DBA, what should we do immediately? Unless your manager has specific task or expectations for you, this is what I recommend. Start documenting or looking through all the database you will be managing. This should include important database information like the following:

1. Database architecture diagram (Is this RAC, DataGuard or GoldenGate?)

2. Database parameters (AMM or ASMM, Archive logging, DB block size, DBID?)

3. Understand the application using the database (OLTP, DWH, Hybrid?)

4. Read and understand existing documentations available. (DR, Failover, Backup procedures)

The reason is simple, understand the environment you will be managing throughly. Every database environment is different. By documenting the environment, you will be able to understand it fully. It will help when you are troubleshooting issues.

Regards,
Wei Shan

Leave a comment

PostgreSQL – How to kill database sessions

PostgreSQL – How to kill database sessions

From session #1

[root@postgresql ~]# su - weishan
[weishan@postgresql ~]$ psql -d testdb
psql (9.3.6)
Type "help" for help.
testdb=# SELECT datname as database, pid as pid, usename as username, application_name as application, client_addr as client_address, query FROM pg_stat_activity;
database | pid | username | application | client_address | query 
----------+-------+----------+-------------+----------------+-----------------------------------------
 testdb | 11834 | weishan | psql | | SELECT datname as database, +
 | | | | | pid as pid, +
 | | | | | usename as username, +
 | | | | | application_name as application,+
 | | | | | client_addr as client_address, +
 | | | | | query +
 | | | | | FROM pg_stat_activity;
(1 row)

We can see the username logged in is “weishan” and the SQL statement being executed as well.

From session #2

[root@postgresql ~]# su - postgres
-bash-4.1$ psql -d testdb
psql (9.3.6)
Type "help" for help.
testdb=# SELECT datname as database, pid as pid, usename as username, application_name as application, client_addr as client_address, query FROM pg_stat_activity;
database | pid | username | application | client_address | query 
----------+-------+----------+-------------+----------------+--------------------------------------------------------------
 testdb | 11834 | weishan | psql | | SELECT datname as database, +
 | | | | | pid as pid, +
 | | | | | usename as username, +
 | | | | | application_name as application, +
 | | | | | client_addr as client_address, +
 | | | | | query +
 | | | | | FROM pg_stat_activity;
 testdb | 12067 | postgres | psql | | SELECT datname as database, pid as pid, usename as username,+
 | | | | | application_name as application, +
 | | | | | client_addr as client_address, query +
 | | | | | FROM pg_stat_activity;
(2 rows)

We can now see 2 sessions in the database, session #1 and session #2.

To kill the session, use PG_TERMINATE_BACKEND function.

testdb=# select pg_terminate_backend(pid) from pg_stat_activity where usename='weishan';
 pg_terminate_backend 
----------------------
 t
(1 row)

From session #1

testdb=# SELECT datname as database, pid as pid, usename as username, application_name as application, client_addr as client_address, query FROM pg_stat_activity;
server closed the connection unexpectedly
 This probably means the server terminated abnormally
 before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.

We can see that session #1 has been disconnect and auto-connection was being made.

Cheers,
Wei Shan

Leave a comment

How did I became an (Oracle) Database Administrator

How did I became an (Oracle) Database Administrator

Every now and then, I get questions asking me how did I get into the field of database administration. Getting a role as a DBA is tricky. If you look at the requirements for DBA, they usually require more than 3 years of experience. There are very few companies looking for fresh graduates for DBA role.

After completing my 2 years of mandatory National Service (NS), I began to look for an IT job. 2 years of NS reduced my IT knowledge to non-existence. I was out of touch with the IT industry. I couldn’t even tell you what version was .NET framework at that time. I attended an interview (System Engineer) with a web-hosting company in Singapore. It was a horrible experience.

Interviewer: So, why do you want to join the IT industry?
Me: Because I love technology and I think it’s rather interesting.

He started asking me technical questions about the position and I could barely answer his questions. At the end of the interview, he made this remark.

I’m going to be frank with you because I do not want to waste your time. I will not hire you. You have no talent in IT. Since you mentioned that you are interested in technology, why don’t you go and try to sell handphones. It will probably suit you better.

I was devastated. I felt so insulted and shameful of myself. I was never insulted like this before. I went home and cried my lungs out. I didn’t had to mood to attend any interviews after that. I was seriously contemplating about what to do with my career. Then, my brother introduced me a 2 month free Oracle database course (MDeC KDI programme) in Malaysia, KL.

I worked my ass off for 2 months straight and got my first Oracle certification (OCA 11G DBA). When I got back to Singapore, I applied for many Oracle DBA positions and finally I got an interview from Seagate. It took me about 3 months of job hunting. The interview went like this.

He asked me 2 technical questions.

1. Purpose of Redo log
2. Purpose of Undo log

Interview: Why do you want to join us?
Me: Because I want to learn. The job scope is exactly what I am looking for. The salary does not matter to me, you can pay me $800 and I will still want the job.

And yes, I got hired in the end. :)

Summary
Today, I am still very grateful to my hiring manager at that time (I still drink coffee with him once in a while). He told me, the only reason why he hired me was because of my attitude, he knew that I was eager to prove myself and that was why I will be willing to put in everything I got to make it work. My advice to all fellow IT professionals, keep that learning attitude and your chance will come. Don’t give up and don’t get discouraged by the nay-sayers.

We have a saying in Chinese.

机会是留给有准备的人.

Chance is for those who are prepared.

2 Comments

Preparing ASM disks for Oracle RAC Installation (Solaris SPARC)

Preparing ASM disks for Oracle RAC Installation (Solaris SPARC)

Environment:
1. Solaris SPARC 64bit 11.2
2. Oracle RAC 12.1.0.2
3. Storage using HDS VSP

While I was doing my first (Solaris SPARC) RAC installation for a customer, I was slightly stumped. My typical installations were on Linux, with or without ASMLib. It was easy and very well documented. However, I was not so experienced in RAC installation on a SPARC!

I came on-site and the Solaris engineer told me that the LUNs (27 of them!) have been zoned to the system. When I do a “ls /dev/rdsk/”, I see plenty of disks!

c1d0s0 c1d11s2 c1d13s5 c1d16s0 c1d18s3 c1d1s6 c1d22s1 c1d24s4 c1d26s7 c1d3s3 c1d5s6 c1d8s1
c1d0s1 c1d11s3 c1d13s6 c1d16s1 c1d18s4 c1d2 c1d22s2 c1d24s5 c1d27s0 c1d3s4 c1d5s7 c1d8s2

I have formatted the above for readability.

Apparently, Solaris disks have 8 slices, from s0 to s7.

According to MOS[Doc ID 368840.1]:

Partition # 2 is a standard partition and it SHOULD NOT be changed as it reflects the total capacity of the disk. All other partitions are available for the configuration. All this information for the partitions and other data is contained in a special structure stored within the first 512 bytes of the disk, on the first cylinders. This structure is called VTOC (Volume Table of Contents). Actions like re-writting the VTOC will cause loosing the disk. Thus, it would be best to modify partition 0, starting at cylinder.

So, how do I actually format the disk correctly?

#format
Searching for disks...done

AVAILABLE DISK SELECTIONS:
0. c1d1 <HITACHI-OPEN-V -SUN-7303-100.00GB>
/virtual-devices@100/channel-devices@200/disk@1
1. c1d2 <HITACHI-OPEN-V -SUN-7303-50.00GB>
/virtual-devices@100/channel-devices@200/disk@2
2. c1d3 <HITACHI-OPEN-V -SUN-7303 cyl 1363 alt 2 hd 15 sec 512>
/virtual-devices@100/channel-devices@200/disk@3
3. c1d4 <HITACHI-OPEN-V -SUN-7303 cyl 1363 alt 2 hd 15 sec 512>
/virtual-devices@100/channel-devices@200/disk@4

Specify disk (enter its number): Specify disk (enter its number): 3

partition> 0
Part Tag Flag Cylinders Size Blocks
0 unassigned wm 0 - 1362 4.99GB (1363/0/0) 10467840

Enter partition id tag[unassigned]:
Enter partition permission flags[wm]:
Enter new starting cyl[0]: 3
Enter partition size[10444800b, 1360c, 1362e, 5100.00mb, 4.98gb]: $
partition> p
Current partition table (unnamed):
Total disk cylinders available: 1363 + 2 (reserved cylinders)

Part Tag Flag Cylinders Size Blocks
0 unassigned wm 3 - 1362 4.98GB (1360/0/0) 10444800
1 unassigned wu 0 0 (0/0/0) 0
2 backup wu 0 - 1362 4.99GB (1363/0/0) 10467840
3 unassigned wm 0 0 (0/0/0) 0
4 unassigned wm 0 0 (0/0/0) 0
5 unassigned wm 0 0 (0/0/0) 0
6 unassigned wm 0 0 (0/0/0) 0
7 unassigned wm 0 0 (0/0/0) 0

Ta-Da! Now, you should see c1d4s0 as /dev/rdsk/c1d4s0 and be able to view it as a candidate disk!

Regards,
Wei Shan

Leave a comment

PostgreSQL – PG_CTL Utility

PostgreSQL – PG_CTL Utility

pg_ctl is a utility for initializing, starting, stopping, or restarting the PostgreSQL database server or displaying the status of a running server.

#pg_ctl --help
pg_ctl is a utility to initialize, start, stop, or control a PostgreSQL server.

Usage:
pg_ctl init[db] [-D DATADIR] [-s] [-o "OPTIONS"]
pg_ctl start [-w] [-t SECS] [-D DATADIR] [-s] [-l FILENAME] [-o "OPTIONS"]
pg_ctl stop [-W] [-t SECS] [-D DATADIR] [-s] [-m SHUTDOWN-MODE]
pg_ctl restart [-w] [-t SECS] [-D DATADIR] [-s] [-m SHUTDOWN-MODE]
[-o "OPTIONS"]
pg_ctl reload [-D DATADIR] [-s]
pg_ctl status [-D DATADIR]
pg_ctl promote [-D DATADIR] [-s]
pg_ctl kill SIGNALNAME PID

Common options:
-D, --pgdata=DATADIR location of the database storage area
-s, --silent only print errors, no informational messages
-t, --timeout=SECS seconds to wait when using -w option
-V, --version output version information, then exit
-w wait until operation completes
-W do not wait until operation completes
-?, --help show this help, then exit
(The default is to wait for shutdown, but not for start or restart.)

If the -D option is omitted, the environment variable PGDATA is used.

Options for start or restart:
-c, --core-files allow postgres to produce core files
-l, --log=FILENAME write (or append) server log to FILENAME
-o OPTIONS command line options to pass to postgres
(PostgreSQL server executable) or initdb
-p PATH-TO-POSTGRES normally not necessary

Options for stop or restart:
-m, --mode=MODE MODE can be "smart", "fast", or "immediate"

Shutdown modes are:
smart quit after all clients have disconnected
fast quit directly, with proper shutdown
immediate quit without complete shutdown; will lead to recovery on restart

I wanted to test the difference in effect between “pg_ctl restart” and “pg_ctl reload”.

In session1, I run the following:

session1=# start transaction;
START TRANSACTION
session1=# update customer set title='Mrs' where customer_id='20';
UPDATE 1

PG_CTL RELOAD

Now, I execute “reload command” in session2.

session2$ pg_ctl reload
server signaled

Back to session1.

session1=# end;
COMMIT

It works. The connection was not dropped or affected.

PG_CTL RESTART

Now, I execute “restart command” in session2.

session2$ pg_ctl restart -m immediate
waiting for server to shut down.... done
server stopped
server starting

Back to session1.

session1# end;
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.

Summary

pg_ctl restart will cause the a full restart of the postgres server while pg_ ctl reload will only allow postmaster to reread the configuration files and reload the settings. This effect is similar to Linux service reload.

Regards,
Wei Shan

Leave a comment

Difference between PostgreSQL and Postgres Plus Advanced Server

Difference between PostgreSQL and Postgres Plus Advanced Server

Recently, I took up a opportunity to start working with the not-so-mainstream databases like PostgreSQL and Redis. It’s a really big challenge for me as I have worked with Oracle database for more than 4 years now. I started to research more on PostgreSQL and found a HUGE bunch of information. While I start to pickup on PostgreSQL, I hope to document the knowledge here.

What is PostgreSQL?

PostgreSQL is an open source relation database management system (RDBMS) that is supported on majority of the operating systems, including Unix, Linux and Windows. It is extremely mature, having more than 15 years of active development and a proven architecture which is quite similar to Oracle database.

Taken from PostgreSQL website

An enterprise class database, PostgreSQL boasts sophisticated features such as Multi-Version Concurrency Control (MVCC), point in time recovery, tablespaces, asynchronous replication, nested transactions (savepoints), online/hot backups, a sophisticated query planner/optimizer, and write ahead logging for fault tolerance. It supports international character sets, multibyte character encodings, Unicode, and it is locale-aware for sorting, case-sensitivity, and formatting. It is highly scalable both in the sheer quantity of data it can manage and in the number of concurrent users it can accommodate. There are active PostgreSQL systems in production environments that manage in excess of 4 terabytes of data. Some general PostgreSQL limits are included in the table below.

What is Postgres Plus Advance Server?

Postgres Plus Advance Server is a distribution of PostgreSQL maintained and supported by  EnterpriseDB. It is usually used by companies who require maintenance support from a company.

Taken from Here.

EnterpriseDB adds additional functionality and capabilities in the following areas:

1. Database Feature Compatibility for Oracle
2. Performance Features
3. Productivity Tools – Postgres Enterprise Manager, xDB Replication Server and the Migration Toolkit
4. Indemnification (Read: SUPPORT)

Postgres Plus Advanced Server contains all the features and functionality of PostgreSQL and adds additional performance features, security, and database feature compatibility for Oracle. The performance features include: a SQL Profiler, Index Advisor, Query Hints, Infinite Cache, DynaTune, a Hi-speed bulk loader, Bulk Collect/Fetch/Binding, and Multi-threaded Replication. Security is improved with the addition of server side code obfuscation, fine grained audit logging and SQL injection attack prevention. Finally, Advanced Server provides database compatibility that includes: PL/SQL support, a PL/SQL Debugger, Oracle SQL Extensions, OCI support, User Defined Objects, popular Function Packages, Dictionary Views, Database Links, EDB*Plus, EDB*Wrap, and SQL wait statistics.

Summary

In my opinion, customers who are migrating from Oracle to PostgreSQL would go for the latter option as it provides plenty of compatibility support for Oracle databases. However, it’s always good to sign up for support contract for the open source version with them. You never know when you need the extra help!

Regards,
Wei Shan

Leave a comment

Working with Oracle Database Resource Manager

Working with Oracle Database Resource Manager

Database Consolidation is an extremely common practice among companies these days. There are several reasons why people choose to consolidate their databases. The most important reason is cost. Consolidation allows you to save cost, it could be electricity bills, DB licences or operation costs.

There are 3 types of database consolidation:

  1. Multiple databases within same Operating System (OS)
  2. Multiple schemas/workload within same database
  3. Multiple databases within same database (12c multi-tenant feature!)

Instance Caging allows you to set the amount of CPU a database can access to. Oracle Database Resource Manager(DBRM) allows you to slice the CPU cycles to share them among the different workload, schemas or databases. For engineered systems, IO Resource Manager(IORM) allows you to manage the IO resources as well. However, I will not be sharing about them.

Below are the types of resources that be managed by Oracle Database Resource Manager. I will be using CPU with my examples later.

  • CPU
  • Degree of Parallelism Limit
  • Parallel Target Percentage
  • Parallel Queue Timeout
  • Active Session Pool with Queuing
  • Automatic Consumer Group Switching
  • Canceling SQL and Terminating Sessions
  • Execution Time Limit
  • Undo Pool
  • Idle Time Limit

Multiple Databases within the same OS – Instance Caging

If you are consolidating multiple databases within the same OS, instance caging will be the feature to use! It’s extremely easy to configure.

SQL> alter system set cpu_count = 4;
SQL> alter system set resource_manager_plan = 'default_plan' sid='*';

Note 1: CPU_COUNT is a dynamic parameter
Note 2: RESOURCE_MANAGER_PLAN is a dynamic parameter
Note 3: CPU_CORE refers to the CPU threads, not CPU cores.
Note 4: It’s a good idea to oversubscribe the CPU_COUNT to fully utilise the CPU cores

Multiple schemas/workload within same database – Resource Manager

The steps below will create a very basic resource plan to manage the workload within the database. Session using the service ‘ONLINE_OLTP’ will 40% of the CPU cycles if the CPU are utilised at 100%.

SQL> exec dbms_resource_manager.create_pending_area();
SQL> exec dbms_resource_manager.create_consumer_group('ONLINE_APP','Online OTLP Application');
SQL> exec dbms_resource_manager.set_consumer_group_mapping (attribute => dbms_resource_manager.service_name,value => 'ONLINE_OLTP',consumer_group => 'ONLINE_APP'); SQL> exec dbms_resource_manager_privs.grant_switch_consumer_group(grantee_name => 'public',consumer_group => 'ONLINE_OLTP',grant_option => FALSE);
SQL> exec dbms_resource_manager.create_plan('DB_CONSOLIDATION','Plan for Consolidation');
SQL> exec dbms_resource_manager.create_plan_directive('DB_CONSOLIDATION','ONLINE_APP','Shares',mgmt_p1=> 40);
SQL> exec dbms_resource_manager.create_plan_directive('DB_CONSOLIDATION','OTHER_GROUPS',mgmt_p1=>10)
SQL> alter system set resource_manager_plan='DB_CONSOLIDATION';

The OTHER_GROUPS is to catch-all CONSUMER_GROUP for any workload not specified by any CONSUMER_GROUP. If the resource plan directive does not contain OTHER_GROUPS, you will hit the following error.

ORA-29377: consumer group OTHER_GROUPS is not part of top-plan DB_CONSOLIDATION
 ORA-06512: at "SYS.DBMS_RMIN_SYS", line 3640
 ORA-06512: at "SYS.DBMS_RMIN_SYS", line 3691
 ORA-06512: at "SYS.DBMS_RMIN_SYS", line 4364
 ORA-06512: at "SYS.DBMS_RESOURCE_MANAGER", line 756
 ORA-06512: at line 1

To check what are the consumer groups within the DB_CONSOLIDATION plan.

SQL> select group_or_subplan from dba_rsrc_plan_directives where plan='DB_CONSOLIDATION';
GROUP_OR_SUBPLAN
 --------------------------------------------------------------------------------
 ONLINE_APP
 OTHER_GROUPS

To check what is the currently active resource plan directives in the database.

SQL> select name, cpu_managed from v$rsrc_plan where is_top_plan = 'TRUE';
NAME CPU
 -------------------------------- ---
 DB_CONSOLIDATION ON

Note 1: Switching of resource plan is a dynamic operations.
Note 2: Remember to create the necessary privilege for the session to switch into the resource plan. Else, the mapping rule will be ignored.
Note 3: If the session is being throttled, you will see “resmgr:cpu quantum” wait events in your AWR report.

Multiple databases within same database (12c multi-tenant feature!) – Resource Manager

Do the following within the root container:

SQL> exec dbms_resource_manager.create_pending_area();
SQL> exec dbms_resource_manager.create_cdb_plan(plan => 'CDB_PLAN','CDB Resource Plan)
SQL> exec dbms_resource_manager.create_cdb_plan_directive(plan=> 'CDB_PLAN','pluggable_database => 'pdb1',shares => 3)
SQL> exec dbms_resource_manager.validate_pending_area();
SQL> SQL> exec dbms_resource_manager.submit_pending_area();
SQL> alter system set resource_manager_plan='CDB_PLAN';

So what happens when we use all 3 methods to manage our resources?

Screen Shot 2015-02-04 at 16.27.05

Assuming the system has 24 Core threads, the question is, how many CPU threads will OLTP get?

Answer: 0.5*0.5*8 = 2 :))

Cheers!
Wei Shan

Leave a comment

Follow

Get every new post delivered to your Inbox.