Skip navigation.

Feed aggregator

Variations on 1M rows insert(2): commit write

Yann Neuhaus - Mon, 2015-05-04 10:39

In the previous blog post I measured the time it takes to insert 1 million rows. About 45 seconds when done row-by-row which goes down to 7 seconds when inserted in bulk. In both case, the commit was done only at the end. But in some cases you need to commit at each row, in order to make it visible to other sessions as soon as it is inserted (think of several sessions inserting concurrently). And commit makes it also durable - available even after an instance crashes - and that supposes that the change is written to disk. What's the cost for that?

Test case

Of course, I cannot do bulk insert for that as I want to commit for each row. I take the sane test as in the previous post. A table with a primary key (so only one index):

create table DEMO ("id" number , "text" varchar2(15), "number" number) ;

And the same PL/SQL except that I've added a COMMIT in the loop:

declare
 type people_array is varray(12) of varchar(15);
 people people_array := people_array( 'Marc', 'Bill', 'George', 'Eliot', 'Matt', 'Trey', 'Tracy','Greg', 'Steve', 'Kristina', 'Katie', 'Jeff' );
 people_count number :=people.COUNT;
 n number;
begin
 for i in 0..1e6 loop
  n:=trunc(dbms_random.value(0,10000));
  insert into DEMO values( i , people( dbms_random.value(1,people_count) ) , n );
  commit;
 end loop;
 commit;
end;
/

My test environment is still the Oracle Developer Day 11g VM with 1 CPU allocated from by VirtualBox.

 

simple commit in PL/SQL

Yes, this is a special case. I'm running that in a PL/SQL statement and PL/SQL has a special optimization here because the 'commit successful' message is given to the user only at the end. Then the intermediate commits are lighter than commits that return to client. However, look at that 1 million insert + commit execution:

PL/SQL procedure successfully completed.
Elapsed: 00:02:16.34

We are far from the 45 seconds of row-by-row inserts that were committed only at the end. Commit has a big overhead.

 

commit write wait immediate

If you run the inserts from a client, doing a user call for each row, the special optimization is not there. In that case the commit waits for log writer and returns only when log writer has acknowledged the write to disk (and to SYNC standby database if you are in DataGuard). We can do the same from PL/SQL if we issue a COMMIT WRITE WAIT IMMEDIATE instead of a simple commit. So that's a good way to compare the time without introducing additional roundtrip latency.

 for i in 0..1e6 loop
  n:=trunc(dbms_random.value(0,10000));
  insert into DEMO values( i , people( dbms_random.value(1,people_count) ) , n );
  commit write wait immediate;
 end loop;

When executing my procedure with the loop above, here is the time it takes:

PL/SQL procedure successfully completed.
Elapsed: 00:05:06.57

Time has doubled. This is the most expensive insert you can do. But it's also the only way if you want to:

  • Give commit feedback to the user at each commit
  • Ensure that the commit is done as quick as possible (this is the IMMEDIATE write) favoring response time over throughput.
  • Ensure that commit is durable (the D in ACID) meaning that we wait that the changes are on persistent storage (this is the WAIT write)

As we can see here, those requirements have a cost.

 

commit write wait batch

We can favor the throughput with the BATCH commit logging instead of IMMEDIATE.

 for i in 0..1e6 loop
  n:=trunc(dbms_random.value(0,10000));
  insert into DEMO values( i , people( dbms_random.value(1,people_count) ) , n );
  commit write wait batch;
 end loop;

The optimization is not very high here where I've only one session doing all those inserts:

PL/SQL procedure successfully completed.
Elapsed: 00:04:28.97

but the redo size is reduced. I plan to show more statistics and wait events about it in a future post. The time spend here in the WAIT commits is mostly in that orange 'log file sync' waits we don't like to see on enterprise manager screen. The only point where session process is waiting for work that is expected to be done asynchronously in background. At commit, the session waits for the redo to be persisted.

 

commit write nowait immediate

If you are ok to loose a transaction even when the recent commit was successful, then you don't have to wait. That seems to be heretic for ACID aficionados, but is totally acceptable for supporters of 'eventual consistency'. I prefer to think about it according to business requirements.

When something - external to the database - occurs when the commit is successful, then you must trust in ACID and you have to wait. When your customer withdraw money at the ATM then once they have their money you must be sure that the operation is commited in the database. A crash of the database should not give back the money on his account because he will not give back the bills.

However, when you insert events coming from sensors (you can think Big Data) you probably want to keep them for a while in the database but you can accept to loose a few of them in the rare case of server crash. Especially if that improves a lot the performance and the scalability of the process. Maybe you can accept to loose some transactions. Or you are able to load them back again after a crash.
Think of it like the NOLOGGING operations. It's not what you want by default, but if you are sure about the business requirements, and the recover procedure, then you have new was to improve performance and scalability.

 for i in 0..1e6 loop
  n:=trunc(dbms_random.value(0,10000));
  insert into DEMO values( i , people( dbms_random.value(1,people_count) ) , n );
  commit write nowait immediate;
 end loop;

And the improvement is obvious:

PL/SQL procedure successfully completed.
Elapsed: 00:02:08.75

When you don't wait, obviously, you improve the response time and the scalability.

 

commit write nowait batch

And we can batch the redo record, as we did above, in addition to nowait:

 for i in 0..1e6 loop
  n:=trunc(dbms_random.value(0,10000));
  insert into DEMO values( i , people( dbms_random.value(1,people_count) ) , n );
  commit write nowait batch;
 end loop;

And the improvement is obvious:

PL/SQL procedure successfully completed.
Elapsed: 00:01:45.33

Let the log writer do its job in batch, without waiting for its acknowledgement, and the row-by-row commit overhead is minimized. And it's ok as long as you know what you are doing:

  • You can manage the loss of your transaction in case of server crash. Either by re-executing them or because the missing changes have no consequence.
  • You do a WAIT commit when you return to the user a commit status, because he can do something you don't manage and which depend on that commit status.
statement level, session level, instance level

I show only the statement level way to manage commit wait and commit logging because it's a decision that must be done by developers. You can choose the same behavior with the commit_wait and commit_logging session parameters but that's probably not the right option. As I said before, the commit behavior probably depends on where it is in the code. The final commit before returning to the user is probably managed differently.
For the same reason, you probably don't change those parameters at PDB or instance level. But there are always exceptions. If you are doing an application migration that is using the row-by-row API to insert data into the new system, then maybe you can do all that in NOWAIT BATCH by setting instance parameters. If it fails, you restart from the beginning (flashback database helps here).

This kind of optimization exists also in SQL Server. David Bararin has blogged about MSSQL delayed durability previously. Want to meet in one day our experts from all technologies? Come to our Event In-Memory: boost your IT performance! where we talk about SQL Server, Oracle and SAP HANA.

And as we are talking about cross technology, stay tuned to see 1M row insert variations on other database systems...

EMC World 2015 - Day 1 at Momentum

Yann Neuhaus - Mon, 2015-05-04 08:07

The first day of my first EMC World conferences and specially the ones from Momentum wich covers the Enterprise Content Division (ECD) products/solutions/strategies aso. The start was great, being in Las Vegas where you have the feeling you are on another planet, I had the same feel during the General Session or the ECD Keynote; each time good explanations coupled with good shows.

The information I have got was interresting and some questions came in my mind. Questions that I hope can be answered in the next days.

InfoArchive

Before attending the General Session I went to another one which was about EMC InfoArchive. Today I work mainly with the Documentum Content Server and products around it like xPlore, ADTS, D2 aso.

To be prepared for new futur customer requests and challenges I wanted to see what is behind InfoArchive. Let's give some points:

- One main goal of using InfoArchive is to reduce the cost of the storage and to keep the assets.

- Once legacy applications are shut down, you can archive their data into InfoArchive. You can also use it to archive data from active applications where you can build some rules to define which data will be moved to InfoArchive. And this can be done for flat, complex as well as, of course, for document records.

- When the data are saved into InfoArchive, you can use xQuery, xForm to retrieve the data and display them in a way the user wants to see it.

That's on the general overview. On a technical point of view here some information:

- The Archive Service is build using a Data Service (xDB data server) and/or a Content Server. In case you have to archive only metadata the xDB service is sufficient.

- The storage to be used is obviously the EMC storages but other ones can also be used meaning this solution can be implemented in more type of infrastructures.

- To the question what is archived, the answer is SIP (Submission Information Package). You have a SIP descriptor  and SIP Data (metadata or/and Content)

- LWSO objects are stored to use less storage

- The search is done first against the AIP (Archive Info Packages) and once the object is found, against the AIU (Archive Info Unit).There is no fulltext available on the InfoArchive layer, the reason is that an archive system does not use it in general.

- RPS can be used to manage the retention.

Open questions

So that for the "facts", now there are some other open points which could be raised in case InforArchive will be used. You can save you data in normal XML formats but you can also define how the data are saved and how you want to search them. In this case who will manage that, the Record&Archive team or do you need first a business analyste? Can the defined model easily be changed for the current archived information? There are technical questions but I think the organization has first to be defined to have a successfull implementation of InfoArchive

Again, some questions are coming in my mind. And again, let's see if I can have some answers in ... the next days.

Get the main Administration Information from SQL Server with PowerShell

Yann Neuhaus - Mon, 2015-05-04 07:48

In my previous blog Automate SQL Server Administration with PowerShell – How to retrieve SQL Server services?, I presented you the first step of the SQL Server administration through an automated process.

This blog is a follow-up of the previous one, and it will focus on retrieving information of a SQL Server instance with PowerShell

 

Disclaimer: I am not a developer but a SQL Server dba. If you find errors or some ways of improvement, I will be glad to read your comments!

 

List all SQL Server instances

To be able to proceed for all the instances, you can easily get all your instance names with this function:

Get-SQLInstances_function_20150504-135411_1.png

 

Retrieve SQL Server instance information

In my example, I execute my script on the machine hosting my SQL Server instance.

I use SMO objects to access to the instance information . But you need the instance full name , as follows:

full_instance_name.png

I only give the instance name as parameter because I execute my script on a local server, otherwise I need to give the server name as parameter.

 

First I initialize my SMO object of my instance like this:

instance_smo_object.png

 

This SMO object contains SQL Server instance main information. To list all properties and the object methods, proceed as follows:

service_properties_and_methods_20150504-141841_1.png

 

To list the general information of  the instance, you can proceed like this:

instance_general_information.png

To list the directory paths related to the instance, here is an example:

instance_directories.png

To list important instance configuration, here is an example:

instance_configuration.png

 

By formating the information you retrieve in the instance SMO object, you can generate reports, audit your environment or whatever!

The following capture is an existing dashboard from our Database Management Kit (DMK).

dmk_instance_information.png

 

Next steps

The SMO object for the SQL Server instance has a limit number of properties and methods. Sometimes, you need information which are not present in the object. In this case, you must use the "sqlcmd" command and retrieve your information by using T-SQL.

Here is the way to proceed:

invoke_sqlcmd_command.png

To retrieve any of SQL Server instance information, the "sqlcmd" command would always work. You can also use it to modify the instance configuration.

 

I hope this blog will help you in your work. In my next blog, I will show you how to access to your database information with PowerShell.

Get SQL Server services with PowerShell

Yann Neuhaus - Mon, 2015-05-04 02:13
 

SQL Server Configuration Manager and SQL Server Management Studio are the main tools to administrate the components of SQL Server. They are very convenient to use and pretty complete.
But as soon as you wish an automated process, these tools have their limitations. Nevertheless, there is still the solution: PowerShell!

This blog introduces a first step towards an automation process of SQL Server administration. I will retrieve all SQL Server services related to a specific instance name.

The process will always be similar by using the SMO WMI server PowerShell object.

 

Disclaimer: I am not a developer but a SQL Server dba. If you find errors or some ways of improvement, I will be glad to read your comments!

 

SQL Engine

To retrieve the SQL Engine service for a specific instance name:

Get-SQLEngine_function.png

  SQL Agent

To retrieve the SQL Agent service for a specific instance name:

Get-SQLAgent_function.png

  SQL Full-text Filter

To retrieve the SQL Full-text Filter service for a specific instance name:

Get-SQLFullTextFilter_function.png

  SQL Browser

To retrieve the SQL Browser service:

Get-SQLBrowser_function.png

  SQL Analysis

To retrieve the SQL Analysis service for a specific instance name:

Get-SQLAnalysis_functionpng.png

  SQL Reporting

To retrieve the SQL Reporting service for a specific instance name:

Get-SQLReporting_function.png

  SQL Integration

To retrieve the SQL Integration service:

Get-SQLIntegration_function.png

  Service Object

Each function returns an object with the following properties and methods:

service_properties_and_methods.png

You are able to start, restart or stop your service. But you can also retrieve specific information such as the Service Account, the Start Mode or the Service Account.

  Next Step

If you do not want to proceed just for a specific instance, but for all instances, you can list all instance names in that way:

Get-SQLInstances_function.png

Then, with your list of instance names, you loop by calling each function. Do not forget to test if the service returned exists (by testing if it is null).

 

To finish my article, all these functions are part of our Database Management Kit (DMK) developed by our team. We use it to access faster to common and standards information, but also to automate processes.

For example, the DMK is able (in just one command!) to make a security audit of your SQL Server environment, by following the best practices from Microsoft and from our experts. A report is generated at the end of the audit to list all the security points to review.

getting started with postgres plus advanced server (3) - setting up a hot standby server

Yann Neuhaus - Sat, 2015-05-02 02:42

So, we have a ppas 94 database up and running and we have a backup server for backing up and restoring the database. Now it is time to additionally protect the database by setting up a hot standby database. This database could even be used to offload reporting functionality from the primary database as the standby database will be open in read only mode. Again, I'll use another system for that so that the system overview looks like this:

server ip address purpose ppas 192.168.56.243 ppas database cluster ppasbart 192.168.56.245 backup and recovery server ppasstandby 192.168.56.244 ppas hot standby database


As the standby database will need the ppas binaries just follow the first post for setting this up again. Once the binaries are installed and the database is up and running I'll completely destroy it but keep the data directory:

[root@oel7 tmp]# service ppas-9.4 stop
Stopping Postgres Plus Advanced Server 9.4: 
waiting for server to shut down.... done
server stopped
[root@oel7 tmp]# rm -rf /opt/PostgresPlus/9.4AS/data/*
[root@oel7 tmp]# 

Ready to go. It is amazingly easy to setup a hot standby server with postgres. In a nutshell, everything that needs to be done is to create a replication user in the database, do a base backup of the primary database, copy that to the standby server, create a recovery.conf file and startup the standby database. Lets start by creating the user which will be used for the recovery in the primary database:

[root@ppas ~]# su - enterprisedb
-bash-4.2$ . ./pgplus_env.sh 
-bash-4.2$ psql
psql.bin (9.4.1.3)
Type "help" for help.

edb=# edb=# create role standby LOGIN REPLICATION UNENCRYPTED PASSWORD 'standby';
CREATE ROLE
edb=# commit;
COMMIT
edb'# 

... and adjust the pg_hba.conf file (the second entry is for the base backup later):

-bash-4.2$ tail -1 data/pg_hba.conf
host    replication     standby         192.168.56.244/24          md5
local   replication     standby                                              md5

... and adjust the wal-level in postgresql.conf

-bash-4.2$ grep wal_level data/postgresql.conf 
wal_level = hot_standby			# minimal, archive, hot_standby, or logical

For the settings in pg_hba.conf and postgresql.conf to take effect either a reload of the main server process or a complete restart is required:

-bash-4.2$ pg_ctl -D data/ restart
waiting for server to shut down..... done
server stopped
server starting

Now it is a good time to test if we can connect to the primary database from the standby node:

[root@oel7 tmp]# /opt/PostgresPlus/9.4AS/bin/psql -h 192.168.56.243 -U standby edb
Password for user standby: 
psql.bin (9.4.1.3)
Type "help" for help.

edb=> 

Ready for the basebackup of the primary database?

mkdir /var/tmp/primary_base_backup/
-bash-4.2$ pg_basebackup -D /var/tmp/primary_base_backup/ -U standby -F t -R -x -z -l for_standby -P
Password: 
56517/56517 kB (100%), 1/1 tablespace
-bash-4.2$ 

Especially notice the "-R" switch of pg_basebackup as this creates a minimal recovery.conf for us which we can use as a template for our standby database. Transfer and extract the file written to the standby server (I again prepared passwordless ssh authentication between the primary and the standby server. check the second post on how to do that).

bash-4.2$ pwd
/opt/PostgresPlus/9.4AS/data
bash-4.2$ scp 192.168.56.243:/var/tmp/primary_base_backup/* .
base.tar.gz                                                                                                  100% 5864KB   5.7MB/s   00:00    
-bash-4.2$ 
-bash-4.2$ tar -axf base.tar.gz 
-bash-4.2$ ls
backup_label  dbms_pipe  pg_dynshmem    pg_log        pg_notify    pg_snapshots  pg_subtrans  PG_VERSION            postgresql.conf
base          global     pg_hba.conf    pg_logical    pg_replslot  pg_stat       pg_tblspc    pg_xlog               recovery.conf
base.tar.gz   pg_clog    pg_ident.conf  pg_multixact  pg_serial    pg_stat_tmp   pg_twophase  postgresql.auto.conf
-bash-4.2$ 

Almost ready. Now we need to adjust the recovery.conf file:

standby_mode = 'on'
primary_conninfo = 'host=192.168.56.243 port=5444 user=standby password=standby'
restore_command = 'scp bart@192.168.56.245:/opt/backup/ppas94/archived_wals/%f %p'

... and enable hot standby mode in the postgresql.conf file on the standby server and adjust the listen address:

-bash-4.2$ grep hot postgresql.conf 
wal_level = hot_standby			# minimal, archive, hot_standby, or logical
hot_standby = on			# "on" allows queries during recovery
#hot_standby_feedback = off		# send info from standby to prevent
-bash-4.2$ grep listen data/postgresql.conf
listen_addresses = '192.168.56.244'		# what IP address(es) to listen on;

Startup the standby database and if everything is fine messages similar to this should be reported in the postgresql log file (/opt/PostgresPlus/9.4AS/data/pg_log/):

2015-04-29 14:03:36 CEST LOG:  entering standby mode
scp: /opt/backup/ppas94/archived_wals/000000010000000000000017: No such file or directory
2015-04-29 14:03:36 CEST LOG:  consistent recovery state reached at 0/17000090
2015-04-29 14:03:36 CEST LOG:  redo starts at 0/17000090
2015-04-29 14:03:36 CEST LOG:  record with zero length at 0/170000C8
2015-04-29 14:03:36 CEST LOG:  database system is ready to accept read only connections
2015-04-29 14:03:36 CEST LOG:  started streaming WAL from primary at 0/17000000 on timeline 1

To further prove the setup lets create a simple table in the primary database and add some rows to it:

edb=# create table standby_test ( a int ); 
CREATE TABLE
edb=# insert into standby_test values (1);
INSERT 0 1
edb=# insert into standby_test values (2);
INSERT 0 1
edb=# commit;
COMMIT
edb=# \! hostname
ppas.local
edb=# 

Lets see if we can query the table on the standby:

-bash-4.2$ psql
psql.bin (9.4.1.3)
Type "help" for help.

edb=# select * from standby_test;
 a 
---
 1
 2
(2 rows)

edb=# \! hostname
ppasstandby.local
edb=# 

Cool. Minimal effort for getting a hot standby database up and running. Make yourself familiar with the various settings that influence the behavior of the standby database. I'll write another post on how to do failovers in near future.

Variations on 1M rows insert (1): bulk insert

Yann Neuhaus - Fri, 2015-05-01 13:30

This blog is the first one of a series about inserting 1 million rows. Do you have an idea about the time it takes? The idea came from another project, about NoSQL databases, but I'll focus on Oracle in those posts. There are a lot of different ways to insert 1 million rows in Oracle: row-by-row insert or bulk, row-by-row commit or not, different ways to commit, In-Memory Database, Database In-Memory Option, etc.

Test case

Here is the table I create:

create table DEMO ("id" number , "text" varchar2(15), "number" number) ;

Here is the first PL/SQL I'll run:

declare
 type people_array is varray(12) of varchar(15);
 people people_array := people_array( 'Marc', 'Bill', 'George', 'Eliot', 'Matt', 'Trey', 'Tracy','Greg', 'Steve', 'Kristina', 'Katie', 'Jeff' );
 people_count number :=people.COUNT;
 n number;
begin
 for i in 0..1e6 loop
  n:=trunc(dbms_random.value(0,10000));
  insert into DEMO values( i , people( dbms_random.value(1,people_count) ) , n );
 end loop;
 commit;
end;
/

You may wonder why I chose this testcase. As I said, this comes from another project and you can google to find where the the test case comes from. The performance will be mainly about inserts but the idea is also to implement some procedural processing to show that it's possible in different database system, here in PL/SQL on Oracle and in a future post in TimesTen.

 

Test environment

All the test will be done on a VM with only one CPU activated. This is important because some activity will involve background processes.

$ grep "model name" /proc/cpuinfo
model name      : Intel(R) Core(TM) i7-3630QM CPU @ 2.40GHz

 

Oracle 11g

My first tests are done on the first Oracle Developer Day VM that was available for download. It's a 11g version. We will see 12c later.

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production

And it's 32-bit:

SQL> select addr,4*length(addr) from v$process where rownum=1;

ADDR     4*LENGTH(ADDR)
-------- --------------
3B3C9F80             32

 

With and without index

As you have seen above, I've created the table with a primary key, so I have an index on it. How long does it take to run the statement above to insert 1 million rows?

PL/SQL procedure successfully completed.
Elapsed: 00:00:46.80

On one CPU it takes 46 seconds to insert one million rows, row-by-row, from a simple PL/SQL loop. We will take that as the base for future comparisons. Of course I've done several runs and I keep them as long as there is less than 5% difference.

 

Just to show that the index has an important cost when inserting, let's run the same testcase without the PRIMARY KEY:

PL/SQL procedure successfully completed.
Elapsed: 00:00:35.10

I'll keep the testcase with the primary key index as a point of comparison. There is no point to insert one million rows if we can't retreive one easily. And the table with primary key pattern is the most relevant one when comparing different databases - SQL or NoSQL.

 

Bulk insert

The previous inserts are quite optimal: directly done from PL/SQL in the database, so no roundtrips. Commit only at the end and we know that in Oracle it's better not to commit too often. I will cover the need of row-by-row commits in a future post. But this is still row-by-row insert and this is not optimal. When we have all rows to insert from one session, then we can insert in bulk (one execution of the insert statement inserts several rows).

There are several ways to do that depending on the client language. Here we are in PL/SQL, so it is easy: put all rows into a collection and call the insert with that collection:

declare
 type people_array is varray(12) of varchar(15);
 people people_array := people_array( 'Marc', 'Bill', 'George', 'Eliot', 'Matt', 'Trey', 'Tracy','Greg', 'Steve', 'Kristina', 'Katie', 'Jeff' );
 people_count number :=people.COUNT;
 -- declare the collection
 type DEMO_record_type is record ("user_id" number , "name" varchar2(15), "number" number) ;
 type DEMO_collection_type is table of DEMO_record_type index by binary_integer;
 DEMO_collection DEMO_collection_type;
begin
 -- fill the collection
 for i in 1..1e6 loop
  DEMO_collection(i)."user_id":=i;
  DEMO_collection(i)."name":=people( dbms_random.value(1,people.count) );
  DEMO_collection(i)."number":=trunc(dbms_random.value(0,10000));
  null;
 end loop;
 -- call the insert
 forall i in 1..DEMO_collection.count insert into DEMO values DEMO_collection(i);
 commit;
end;
/

The result is obvious:

PL/SQL procedure successfully completed.
Elapsed: 00:00:06.55

And for the testcase without the primary key index:

PL/SQL procedure successfully completed.
Elapsed: 00:00:03.80

So bulk inserts here are 8x to 10x faster.

 

conclusion

The immediate conclusion is that when you have lot of rows to insert and you don't need to commit at each row, then you should do it in bulk. Here I made it from PL/SQL which run directly in the database. If you are inserting from an application server or a remote client, then the elimination of roundtrips will be a lot more important.

 

In future posts we will see what happens when you need to commit for each row. And I will also run it with In-Memory database and with Database In-Memory. Don't forget that if you are in Switzerland in June, our experts from Oracle, Microsoft, and SAP technologies will talk about In-Memory as implemented by SQL Server, Oracle and SAP HANA. All the same day. It's free and you can register now: Event In-Memory: boost your IT performance!

Using Windows 2012 R2 & dynamic witness feature with minimal configurations - part II

Yann Neuhaus - Thu, 2015-04-30 11:53

I wrote a blog post some time ago about using a file share witness with a minimal windows failover cluster configuration that consists of two cluster nodes. In this blog post, I told I was reluctant to use a witness in this case because it introduces a weakness in the availability process. Indeed, the system is not able to adjust node weight in this configuration but it does mean that we don’t need a witness in this case and this is what I want to clarify here. I admit myself I was wrong on this subject during for some time.

Let’s set the scene with a pretty simple Windows failover cluster architecture that includes two nodes and with dynamic quorum but without a configured witness. The node vote configuration is as follows:

 

blog_38_-_1_-_cluster_nodes_state

 

At this point the system will affect randomly a node weight to the current available nodes. For instance, in my context the vote is affected to the SQL143 node but there is a weakness in this configuration. Let’s first say the node SQL141 goes down in an unplanned scenario. In this case the cluster stays functioning because the node SQL143 has the vote (last man standing). Now, let’s say this time the node SQL143 goes down in an unplanned scenario. In this case the cluster will lost the quorum because the node SQL141 doesn’t have the vote to survive. You will find related entries in the cluster event log as shown to the next picture with two specific event ids (1135 and 1177).

 

blog_38_-_2_-_event_viewer

 

However in the event of the node SQL143 is gracefully shutdown, the cluster will able to remove the vote of the node SQL143 and give it to the node SQL141. But you know, I’m a follower of the murphy law: anything that can go wrong, will go wrong and it is particularly true in IT world.

So we don’t have the choice here. To protect from unplanned failure with two nodes, we should add a witness and at this point you may use either a disk or a file share witness. My preference is to promote first the disk quorum type but it is often not suitable with customers especially for geo cluster configuration. In this case using file share witness is very useful but it might introduce some important considerations about quorum resiliency. First of all, I want to exclude scenarios where the cluster resides on one datacenter. There are no really considerations here because the loose of the datacenter implies the unavailability of the entire cluster (and surely other components).  

Let’s talk about geo location clusters often used with SQL Server availability groups and where important considerations must be made about the file share witness localization. Indeed, most of my customers are dealing only with two datacenters and in this case the 100$ question is where to place it? Most of time, we will place the witness in the location of what we can call the primary datacenter. If the connectivity is lost between the two datacenters the service stays functioning in the primary datacenter. However a manual activation will be required in the event of full primary data center failure.

 

blog_38_-_3_-_geo_clust_primary_without_change

blog_38_-_3_-_geo_clust_primary_

 

 

 

Another scenario consists in placing the witness on the secondary datacenter. Unlike our first scenario, a network failure between the two datacenters will trigger an automatic failover of the resources to the secondary datacenter but if in the event of a complete failure of the secondary datacenter, the cluster will lost the quorum (as a reminder the remaining node is not able to survive).

 

blog_38_-_4_-_geo_clust_secondary_

        blog_38_-_4_-_geo_clust_secondary_failover

 

 

As you can see, each of aforementioned scenario have their advantages and drawbacks. A better situation would be to have a third datacenter to host the witness. Indeed, in the event of network failure between the two datacenters that host the cluster nodes, the vote will be assigned to the node which will first successfully lock the file share witness this time.

Keep in mind that even in this third case, losing the witness because either of a network failure between the two main datacenters and the third datacenter or the file share used by the witness deleted accidently by an administrator, can compromise the entire of the cluster availability in case of a node failure (one who has the vote). So be aware to monitor correctly this critical resource.

So, I would finish by a personal think. I always wondered why in the case of a minimal configuration (only 2 cluster nodes and a FSW), the cluster was not able to perform weight adjustment. Until now, I didn’t get the response from Microsoft but after some time, I think this weird behavior is quite normal. Let’s image the scenario where your file share witness resource is in failed state and the cluster is able to perform weight adjustment. Which of the nodes it may choose? The primary or the secondary? In fact it doesn’t matter because in the both cases, the next failure of the node which has the vote will also shutdown the cluster. Finally it is just delaying an inevitable situation …

Happy clustering !

Windows Server Next, AKA Windows Server 2016!

Yann Neuhaus - Thu, 2015-04-30 02:10

The first technical preview of the future version of Windows Server was, since last October, available (here) and a second one with more new features should be available in May.
The final version which should be normally released in 2015 has been recently postponed to 2016. It will be the first time that the client and server releases will be decoupled.

This new version of Windows Server will include:

  • new and changed functionalities for Hyper-V
  • improvements for Remote Desktop Services
  • new and updated functionalities for Failover Clustering
  • significant new features with PowerShell 5.0
  • directory services, Web application proxy and other features
  • ...

According to Microsoft employee, Jeffrey Snover, the next version of Windows Server has been deeply refactoring to really build a Cloud-optimized server! A server which is deeply refactored for a cloud scenario.

b2ap3_thumbnail_WinowsServer2016_Roadmap.jpg

The goal is to scope out my needs in order to use only the required components.

On top of this Cloud-Optimized server, the server will be build, the same server that we have for the moment, compatible with that we have but with two application profiles:

  • the first application profile which will target the existing set of APIs server
  • the second will be a subset of APIs which will be cloud-optimized

Microsoft works also to further clarify the difference between Server and Client to avoid making a mix between client APIs and server APIs for example.

Microsoft will also introduce Docker containers to his new Windows Server 2016! Container is a compute environment also called compute container.

We will have two flavors of compute containers:

  • one for application compatibility (server running in a container)
  • a second optimized for the cloud (cloud-optimized server)


Docker container

 The goal of Docker is to embed an application into a virtual container. Application via the container will be able to be executed without any problem on Windows or Linux servers. This technology will facilitate the deployment of application and is offered as Open Source under apache license by an American company called Docker.

b2ap3_thumbnail_WinowsServer2016_Docker1.jpgb2ap3_thumbnail_WinowsServer2016_Docker2.jpg

A container is very lightweight as it does not contain its own operation system. In fact, it will use the host machine in order to achieve all of the system calls.

Migration of Docker containers will be easier as their weight are small.

The bigger clouds providers like Amazon on AWS, Microsoft on Azure, Google on Google Compute, have already integrated this new technology... Dealing with Docker containers give the possibility to migrate from one cloud to another one easily.


Nano server

In addition, the Docker container technology which will come with Windows Server 2016 will be part of a set of application deployment services, called Nano Server.

According to an internal presentation of WZor published by Microsoft, Nano Server is presented as “The future of Windows Server”.

Nano Server will be a zero-footprint model, server roles and optional features will reside outside of it. No binaries or metadata in the image, it will be just standalone packages.

Hyper-V, Clustering, Storage, Core CLR, ASP.NET V.Next, PaaS v2, containers will be part of the new roles and features.

The goal will be also to change the mentality of servers management. Tend towards remote management and process automation via Core PowerShell and WMI. In order to facilitate remote management, local tools like Task manager, Registry editor, Event viewer... will be replaced by web-based tools and accessible via a remote connection.

This new solution will be integrated also in Visual Studio.

 

In conclusion, WZor summarized Nano Server as “a nucleus of next-gen cloud infrastructure and applications”. This shows the direction that Microsoft wants to give to Windows Server 2016: even better integration to the cloud, optimization for new distributed applications and management facilitation.

getting started with postgres plus advanced server (2) – setting up a backup and recovery server

Yann Neuhaus - Wed, 2015-04-29 23:50

The first post in this series explained how to get ppas installed on a linux system. Now that the database cluster is up and running we should take care immediately about backup and recovery. For this I'll use another system where I'll install and configure bart. So, the system overview for now is:

server ip address purpose ppas 192.168.56.243 ppas database cluster ppasbart 192.168.56.245 backup and recovery server


As bart requires the postgres binaries I'll just repeat the ppas installation on the bart server. Check the first post on how to do that.

tip: there is a "--extract-only" switch which only extracts the binaries without bringing up a database cluster.

After that just install the bart rpm:

yum localinstall edb-bart-1.0.2-1.rhel6.x86_64.rpm

All the files will be installed under:

ls -la /usr/edb-bart-1.0/
total 20
drwxr-xr-x.  4 root root    44 Apr 23 13:41 .
drwxr-xr-x. 14 root root  4096 Apr 23 13:41 ..
drwxr-xr-x.  2 root root    17 Apr 23 13:41 bin
drwxr-xr-x.  2 root root    21 Apr 23 13:41 etc
-rw-r--r--.  1 root root 15225 Jan 27 15:24 license.txt

Having a dedicated user for bart is a good idea:

# groupadd bart
# useradd -g bart bart
# passwd bart
Changing password for user bart.
New password: 
Retype new password: 
$passwd: all authentication tokens updated successfully.

As backups need some space a top level directory for all the bart backups needs to be created:

# mkdir /opt/backup
chown bart:bart /opt/backup
chmod 700 /opt/backup
mkdir -p /opt/backup/ppas94/archived_wals

Now everything is in place to start the bart configuration. A minimal configuration file would look like this:

cat /usr/edb-bart-1.0/etc/bart.cfg
[BART]
bart-host = bart@192.168.56.245
backup_path = /opt/backup
pg_basebackup_path = /opt/PostgresPlus/9.4AS/bin/pg_basebackup
logfile = /var/tmp/bart.log
xlog-method = fetch

[PPAS94]
host = 192.168.56.243
port = 5444
user = enterprisedb
description = "PPAS 94 server"

The BART section is the global section while the next sections are specific to the database clusters to backup and restore. As bart requires passwordless ssh authentication between the bart host and the database host to be backup up lets setup this. On the bart bart host ( ppasbart ):

su - bart
ssh-keygen -t rsa

On the host where database runs ( ppas ):

su -
cd /opt/PostgresPlus/9.4AS
mkdir .ssh
chown enterprisedb:enterprisedb .ssh/
chmod 700 .ssh/
su - enterprisedb
ssh-keygen -t rsa

As the public keys are now available we'll need to make them available on each host. On the ppas host:

cat .ssh/id_rsa.pub > .ssh/authorized_keys
chmod 600 .ssh/authorized_keys

Add the public key from the barthost to the authorized keys file above. Example: get the public key from the bart host:

[bart@ppasbart ~]$ id
uid=1001(bart) gid=1001(bart) groups=1001(bart) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023
[bart@ppasbart ~]$ cat .ssh/id_rsa.pub 
ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQCn+DN//ia+BocR6kTfHkPoXfx3/HRU5KM1Bqy1nDeGnUn98CSl3kbRkUkiyumDfj4XOIoxOxnVJw6Invyi2VjzeQ12XMMILBFRBAoePDpy4kOQWY+SaS215G72DKzNYY8nGPUwjaQdFpFt3eQhwLP4D5uqomPIi9Dmv7Gp8ZHU0DBgJfrDaqrg8oF3GrzF50ZRjZTAkF3pDxJnrzIEEme+QQFKVxBnSU2ClS5XHdjMBWg+oSx3XSEBHZefP9NgX22ru52lTWmvTscUQbIbDo8SaWucIZC7uhvljteN4AuAdMv+OUblOm9ZUtO2Y8vX8hNMJvqRBlYh9RGl+m6wUZLN document.write(['bart','ppasbart.loca'].join('@'))l

Copy/paste this key into the authorized_keys file for the enterprisedb user on the database host, so that the file looks similar to this:

cat .ssh/id_rsa.pub 
ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQCn+DN//ia+BocR6kTfHkPoXfx3/HRU5KM1Bqy1nDeGnUn98CSl3kbRkUkiyumDfj4XOIoxOxnVJw6Invyi2VjzeQ12XMMILBFRBAoePDpy4kOQWY+SaS215G72DKzNYY8nGPUwjaQdFpFt3eQhwLP4D5uqomPIi9Dmv7Gp8ZHU0DBgJfrDaqrg8oF3GrzF50ZRjZTAkF3pDxJnrzIEEme+QQFKVxBnSU2ClS5XHdjMBWg+oSx3XSEBHZefP9NgX22ru52lTWmvTscUQbIbDo8SaWucIZC7uhvljteN4AuAdMv+OUblOm9ZUtO2Y8vX8hNMJvqRBlYh9RGl+m6wUZLN l
[bart@ppasbart ~]$ cat .ssh/authorized_keys
ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQDAQZWeegLpqVB20c3cIN0Bc7pN6OjFM5pBsunDbO6SQ0+UYxZGScwjnX9FSOlmYzqrlz62jxV2dOJBHgaJj/mbFs5XbmvFw6Z4Zj224aBOXAfej4nHqVnn1Tpuum4HIrbsau3rI+jLCNP+MKnumwM7JiG06dsoG4PeUOghCLyFrItq2/uCIDHWoeQCqqnLD/lLG5y1YXQCSR4VkiQm62tU0aTUBQdZWnvtgskKkHWyVRERfLOmlz2puvmmc5YxmQ5XBVMN5dIcIZntTfx3JC3imjrUl10L3hkiPkV0eAt3KtC1M0n9DDao3SfHFfKfEfp5p69vvpZM2uGFbcpkQrtN l
ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQCn+DN//ia+BocR6kTfHkPoXfx3/HRU5KM1Bqy1nDeGnUn98CSl3kbRkUkiyumDfj4XOIoxOxnVJw6Invyi2VjzeQ12XMMILBFRBAoePDpy4kOQWY+SaS215G72DKzNYY8nGPUwjaQdFpFt3eQhwLP4D5uqomPIi9Dmv7Gp8ZHU0DBgJfrDaqrg8oF3GrzF50ZRjZTAkF3pDxJnrzIEEme+QQFKVxBnSU2ClS5XHdjMBWg+oSx3XSEBHZefP9NgX22ru52lTWmvTscUQbIbDo8SaWucIZC7uhvljteN4AuAdMv+OUblOm9ZUtO2Y8vX8hNMJvqRBlYh9RGl+m6wUZLN

Make the file the same on the bart host and test if you can connect without passwords:

[bart@ppasbart ~]$ hostname
ppasbart.local
[bart@ppasbart ~]$ ssh bart@ppasbart
Last login: Thu Apr 23 14:24:39 2015 from ppas
[bart@ppasbart ~]$ logout
Connection to ppasbart closed.
[bart@ppasbart ~]$ ssh enterprisedb@ppas
Last login: Thu Apr 23 14:24:47 2015 from ppas
-bash-4.2$ logout
Connection to ppas closed.

Do the same test on the ppas host:

bash-4.2$ hostname
ppas.local
-bash-4.2$ ssh bart@ppasbart
Last login: Thu Apr 23 14:22:07 2015 from ppasbart
[bart@ppasbart ~]$ logout
Connection to ppasbart closed.
-bash-4.2$ ssh enterprisedb@ppas
Last login: Thu Apr 23 14:22:18 2015 from ppasbart
-bash-4.2$ logout
Connection to ppas closed.
-bash-4.2$ 

Once this works we need to setup a replication user in the database being backed up. So create the user in the database which runs on the ppas host (I'll do that with enterprise user instead of the postgres user as we'll need to adjust pg_hba.conf file right after creating the user):

[root@ppas 9.4AS]# su - enterprisedb
Last login: Thu Apr 23 14:25:50 CEST 2015 from ppasbart on pts/1
-bash-4.2$ . pgplus_env.sh
-bash-4.2$ psql -U enterprisedb
psql.bin (9.4.1.3)
Type "help" for help.

edb=# CREATE ROLE bart WITH LOGIN REPLICATION PASSWORD 'bart';       
CREATE ROLE
edb=# exit
-bash-4.2$ echo "host    all     bart         192.168.56.245/32          md5" >> data/pg_hba.conf

Make sure that the IP matches your bart host. Then adjust the bart.cfg file on the bart host to match your configuration:

cat /usr/edb-bart-1.0/etc/bart.cfg
[BART]
bart-host = bart@192.168.56.245
backup_path = /opt/backup
pg_basebackup_path = /opt/PostgresPlus/9.4AS/bin/pg_basebackup
logfile = /var/tmp/bart.log
xlog-method = fetch

[PPAS94]
host = 192.168.56.243
port = 5444
user = bart
remote-host = enterprisedb@192.168.56.243
description = "PPAS 94 remote server"

Another requirement is that the bart database user must be able to connect to the database without prompting for a password. Thus we create the .pgpass file on the bart host which is used for reading the password:

[bart@ppasbart ~]$ cat .pgpass 
192.168.56.243:5444:*:bart:bart
[bart@ppasbart ~]$ chmod 600 .pgpass

As a last step we need to enable wal archiving on the database that should be backed up. The following parameters need to be set in the postgresql.conf file:

wal_level = archive  # or higher
archive_mode = on
archive_command = 'scp %p bart@192.168.56.245:/opt/backup/ppas94/archived_wals/%f'
max_wal_senders = 1  # or higher

Once done restart the database cluster:

su -
service ppas-9.4 restart

Lets see if bart can see anything on the bart server:

[bart@ppasbart ~]$ /usr/edb-bart-1.0/bin/bart -c /usr/edb-bart-1.0/etc/bart.cfg SHOW-SERVERS -s PPAS94 
Server name         : ppas94
Host name           : 192.168.56.243
User name           : bart
Port                : 5444
Remote host         : enterprisedb@192.168.56.243
Archive path        : /opt/backup/ppas94/archived_wals
WARNING: xlog-method is empty, defaulting to global policy
Xlog Method         : fetch
Tablespace path(s)  : 
Description         : "PPAS 94 remote server"

Looks fine. So lets do a backup:

[bart@ppasbart ~]$ /usr/edb-bart-1.0/bin/bart -c /usr/edb-bart-1.0/etc/bart.cfg BACKUP -s PPAS94

INFO:  creating backup for server 'ppas94'
INFO:  backup identifier: '1429795268774'
WARNING: xlog-method is empty, defaulting to global policy
56357/56357 kB (100%), 1/1 tablespace

INFO:  backup checksum: 6e614f981902c99326a7625a9c262d98
INFO:  backup completed successfully

Cool. Lets see what is in the backup catalog:

[root@ppasbart tmp]# ls -la /opt/backup/
total 0
drwx------. 3 bart bart 19 Apr 23 15:02 .
drwxr-xr-x. 4 root root 38 Apr 23 13:49 ..
drwx------. 4 bart bart 46 Apr 23 15:21 ppas94
[root@ppasbart tmp]# ls -la /opt/backup/ppas94/
total 4
drwx------. 4 bart bart   46 Apr 23 15:21 .
drwx------. 3 bart bart   19 Apr 23 15:02 ..
drwx------. 2 bart bart   36 Apr 23 15:21 1429795268774
drwx------. 2 bart bart 4096 Apr 23 15:21 archived_wals
[root@ppasbart tmp]# ls -la /opt/backup/ppas94/1429795268774/
total 56364
drwx------. 2 bart bart       36 Apr 23 15:21 .
drwx------. 4 bart bart       46 Apr 23 15:21 ..
-rw-rw-r--. 1 bart bart       33 Apr 23 15:21 base.md5
-rw-rw-r--. 1 bart bart 57710592 Apr 23 15:21 base.tar
[root@ppasbart tmp]# ls -la /opt/backup/ppas94/archived_wals/
total 81928
drwx------. 2 bart bart     4096 Apr 23 15:21 .
drwx------. 4 bart bart       46 Apr 23 15:21 ..
-rw-------. 1 bart bart 16777216 Apr 23 15:10 000000010000000000000002
-rw-------. 1 bart bart 16777216 Apr 23 15:13 000000010000000000000003
-rw-------. 1 bart bart 16777216 Apr 23 15:20 000000010000000000000004
-rw-------. 1 bart bart 16777216 Apr 23 15:21 000000010000000000000005
-rw-------. 1 bart bart 16777216 Apr 23 15:21 000000010000000000000006
-rw-------. 1 bart bart      304 Apr 23 15:21 000000010000000000000006.00000028.backup

Use the SHOW-BACKUPS switch to get on overview of the backups available:

[bart@ppasbart ~]$ /usr/edb-bart-1.0/bin/bart -c /usr/edb-bart-1.0/etc/bart.cfg SHOW-BACKUPS 
 Server Name   Backup ID       Backup Time           Backup Size  
                                                                  
 ppas94        1429795268774   2015-04-23 15:21:23   55.0371 MB   
 ppas94        1429795515326   2015-04-23 15:25:18   5.72567 MB   
 ppas94        1429795614916   2015-04-23 15:26:58   5.72567 MB   
                                                                  

A backup without a restore proves nothing so lets try to restore one of the backups to the ppas server to a different directory:

[root@ppas 9.4AS]# mkdir /opt/PostgresPlus/9.4AS/data2
[root@ppas 9.4AS]# chown enterprisedb:enterprisedb /opt/PostgresPlus/9.4AS/data2

On the ppasbart host do the restore:

[bart@ppasbart ~]$ /usr/edb-bart-1.0/bin/bart -c /usr/edb-bart-1.0/etc/bart.cfg RESTORE -s PPAS94 -i 1429795614916 -r enterprisedb@ppas -p /opt/PostgresPlus/9.4AS/data2
INFO:  restoring backup '1429795614916' of server 'ppas94'
INFO:  restoring backup to enterprisedb@ppas:/opt/PostgresPlus/9.4AS/data2
INFO:  base backup restored
INFO:  archiving is disabled
INFO:  backup restored successfully at enterprisedb@ppas:/opt/PostgresPlus/9.4AS/data2

Looks good. Lets see what is in the data2 directory on the ppas host:

[root@ppas 9.4AS]# ls /opt/PostgresPlus/9.4AS/data2
backup_label  dbms_pipe  pg_clog      pg_hba.conf    pg_log      pg_multixact  pg_replslot  pg_snapshots  pg_stat_tmp  pg_tblspc    PG_VERSION  postgresql.auto.conf
base          global     pg_dynshmem  pg_ident.conf  pg_logical  pg_notify     pg_serial    pg_stat       pg_subtrans  pg_twophase  pg_xlog     postgresql.conf
[root@ppas 9.4AS]# ls /opt/PostgresPlus/9.4AS/data2/pg_xlog
000000010000000000000008  archive_status

Looks good, too. As this is all on the same server we need to change the port before bringing up the database:

-bash-4.2$ grep port postgresql.conf  | head  -1
port = 5445				# (change requires restart)
-bash-4.2$ pg_ctl start -D data2/
server starting
-bash-4.2$ 2015-04-23 16:01:30 CEST FATAL:  data directory "/opt/PostgresPlus/9.4AS/data2" has group or world access
2015-04-23 16:01:30 CEST DETAIL:  Permissions should be u=rwx (0700).

Ok, fine. Change it:

-bash-4.2$ chmod 700 /opt/PostgresPlus/9.4AS/data2
-bash-4.2$ pg_ctl start -D data2/
server starting
-bash-4.2$ 2015-04-23 16:02:00 CEST LOG:  redirecting log output to logging collector process
2015-04-23 16:02:00 CEST HINT:  Future log output will appear in directory "pg_log".

Seems ok, lets connect:

-bash-4.2$ psql -p 5445 -U bart
Password for user bart: 
psql.bin (9.4.1.3)
Type "help" for help.

edb=> l
                                           List of databases
   Name    |    Owner     | Encoding |   Collate   |    Ctype    | ICU |       Access privileges       
-----------+--------------+----------+-------------+-------------+-----+-------------------------------
 edb       | enterprisedb | UTF8     | en_US.UTF-8 | en_US.UTF-8 |     | 
 postgres  | enterprisedb | UTF8     | en_US.UTF-8 | en_US.UTF-8 |     | 
 template0 | enterprisedb | UTF8     | en_US.UTF-8 | en_US.UTF-8 |     | =c/enterprisedb              +
           |              |          |             |             |     | enterprisedb=CTc/enterprisedb
 template1 | enterprisedb | UTF8     | en_US.UTF-8 | en_US.UTF-8 |     | =c/enterprisedb              +
           |              |          |             |             |     | enterprisedb=CTc/enterprisedb
(4 rows)

Cool. Works. But: archiving is disabled and you'll need to enable it again. This is the default behavior of bart as it adds "archive_mode=off" to the end of the postgressql.conf. But take care that you adjust the archive_command parameter as all archived wals will be scp'ed to the same directory on the ppasbart server as the original database did. Can we do a point in time recovery? Let's try (I'll destroy the restored database cluster and will use the same data2 directory ):

-bash-4.2$ pg_ctl -D data2 stop -m fast
waiting for server to shut down.... done
server stopped
-bash-4.2$ rm -rf data2/*
-bash-4.2$ 

Lets try the restore to a specific point in time:

[bart@ppasbart ~]$ /usr/edb-bart-1.0/bin/bart -c /usr/edb-bart-1.0/etc/bart.cfg RESTORE -s PPAS94 -i 1429795614916 -r enterprisedb@ppas -p /opt/PostgresPlus/9.4AS/data2 -g '2015-04-03 15:23:00'
INFO:  restoring backup '1429795614916' of server 'ppas94'
INFO:  restoring backup to enterprisedb@ppas:/opt/PostgresPlus/9.4AS/data2
INFO:  base backup restored
INFO:  creating recovery.conf file
INFO:  archiving is disabled
INFO:  backup restored successfully at enterprisedb@ppas:/opt/PostgresPlus/9.4AS/data2

Seems ok, but what is the difference? When specifying a point in time a recovery.conf file will be created for the restored database cluster:

-bash-4.2$ cat data2/recovery.conf
restore_command = 'scp -o BatchMode=yes -o PasswordAuthentication=no bart@192.168.56.245:/opt/backup/ppas94/archived_wals/%f %p'
recovery_target_time = '2015-04-03 15:23:00'

Lets start the database (after changing the port again in postgresql.conf):

-bash-4.2$ pg_ctl -D data2 start
server starting
-bash-4.2$ 2015-04-23 16:16:12 CEST LOG:  redirecting log output to logging collector process
2015-04-23 16:16:12 CEST HINT:  Future log output will appear in directory "pg_log".

Are we able to connect?

-bash-4.2$ psql -U bart -p 5445 
Password for user bart: 
psql.bin (9.4.1.3)
Type "help" for help.

edb=>

Works, too. So now we have a central backup server for our postgresql infrastructure from which backups and restores can be executed. Combine this with a backup software (like netbackup, etc) which picks up the backups from the bartserver and you should be fine. in the next post we'll setup a hot standby database server.

A migration pitfall with ALL COLUMN SIZE AUTO

Yann Neuhaus - Wed, 2015-04-29 13:05

When you migrate, you should be prepared to face some execution plan changing. That's not new. But here I'll show you a case where you have several bad execution plans because lot of histograms are missing. The version is the same. The system is the same. You've migrated with DataPump importing all statistics. You have the same automatic job to gather statistics with all default options. You have repeated the migration several times on a system where you constantly reproduce the load. Have done a lot of regression tests. Everything was ok.

SQL Server Tips: How to know if In-Memory Feature is supported by your server?

Yann Neuhaus - Wed, 2015-04-29 00:21


A customer asks me, how to know if In-Memory Feature is supported by my SQL Server server?

An easy way is to check the edition, version etc. but now, you have directly a property for that.

 

On msdn here, you find all property that you can search with the T-SQL Command: SERVERPROPERTY

 

But if you try to run through all your servers with CMS (Central Management Server), for all SQL Server below than SQL Server 2014, you have a NULL value.


 InMemory_Property01.png

 

I write rapidly this script to have no NULL value and have an usable info:

SELECT CASE CAST( ServerProperty('IsXTPSupported') AS INT)  WHEN 0 THEN 'Not Supported'  WHEN 1 THEN 'Supported'  ELSE 'Not Available Version (must be SQL2014 or Higher)'ENDAS In_Memory_Supported

 


InMemory_Property02.png


I hope this script can help you and if you want to know more on SQL Server In-Memory Technology come to our Event in June 2015: inscription & details here Cool




getting started with postgres plus advanced server (1) - setting up ppas

Yann Neuhaus - Tue, 2015-04-28 10:35

I did several posts around postgresql and postgres plus advanced server in the past. What is missing is a beginners guide on how to get postgres plus advanced server up and running including a solution for backup and recovery, high availability and monitoring. So I thought I'd write a guide on how to do that, consisting of:

  1. setting up postgres plus advanced server
  2. setting up a backup and recovery server
  3. setting up a hot standby database
  4. setting up monitoring

As this is the first post of the series this is about getting ppas installed and creating the first database cluster.

Obviously the first thing to do is to install an operating system. Several of these are supported, just choose the one you like. An example setup can be found here. So, once ppas was downloaded and transferred to the system where it is supposed to be installed we can start. There are several ways to get ppas installed on the system but before you begin java should be installed. For yum based distributions this is done by:

yum install java
Using the standalone installer in interactive mode

Starting the installation is just a matter of extracting the file and executing it:

[root@oel7 tmp]# ls
ppasmeta-9.4.1.3-linux-x64.tar.gz
[root@oel7 tmp]# tar -axf ppasmeta-9.4.1.3-linux-x64.tar.gz 
[root@oel7 tmp]# ls
ppasmeta-9.4.1.3-linux-x64  ppasmeta-9.4.1.3-linux-x64.tar.gz
[root@oel7 tmp]# ppasmeta-9.4.1.3-linux-x64/ppasmeta-9.4.1.3-linux-x64.run 

alt alt alt provide the username and password you used for downloading the product: alt alt alt alt alt alt alt alt alt alt alt alt alt alt altdone.

Using the standalone installer in interactive text mode

If you do not want to use the graphical user interface you can launch the installer in interactive text mode:

# ppasmeta-9.4.1.3-linux-x64/ppasmeta-9.4.1.3-linux-x64.run --mode text

Either go with the default options or adjust what you like. The questions should be self explaining:

Language Selection

Please select the installation language
[1] English - English
[2] Japanese - 日本語
[3] Simplified Chinese - 简体中文
[4] Traditional Chinese - 繁体中文
[5] Korean - 한국어
Please choose an option [1] : 1
----------------------------------------------------------------------------
Welcome to the Postgres Plus Advanced Server Setup Wizard.

----------------------------------------------------------------------------
Please read the following License Agreement. You must accept the terms of this 
agreement before continuing with the installation.

Press [Enter] to continue:
.....
.....
Press [Enter] to continue:

Do you accept this license? [y/n]: y

----------------------------------------------------------------------------
User Authentication

This installation requires a registration with EnterpriseDB.com. Please enter 
your credentials below. If you do not have an account, Please create one now on 
https://www.enterprisedb.com/user-login-registration

Email []: 

Password : xxxxx

----------------------------------------------------------------------------
Please specify the directory where Postgres Plus Advanced Server will be 
installed.

Installation Directory [/opt/PostgresPlus]: 

----------------------------------------------------------------------------
Select the components you want to install.

Database Server [Y/n] :y

Connectors [Y/n] :y

Infinite Cache [Y/n] :y

Migration Toolkit [Y/n] :y

Postgres Enterprise Manager Client [Y/n] :y

pgpool-II [Y/n] :y

pgpool-II Extensions [Y/n] :y

EDB*Plus [Y/n] :y

Slony Replication [Y/n] :y

PgBouncer [Y/n] :y

Is the selection above correct? [Y/n]: y

----------------------------------------------------------------------------
Additional Directories

Please select a directory under which to store your data.

Data Directory [/opt/PostgresPlus/9.4AS/data]: 

Please select a directory under which to store your Write-Ahead Logs.

Write-Ahead Log (WAL) Directory [/opt/PostgresPlus/9.4AS/data/pg_xlog]: 

----------------------------------------------------------------------------
Configuration Mode

Postgres Plus Advanced Server always installs with Oracle(R) compatibility features and maintains full PostgreSQL compliance. Select your style preference for installation defaults and samples.

The Oracle configuration will cause the use of certain objects  (e.g. DATE data types, string operations, etc.) to produce Oracle compatible results, create the same Oracle sample tables, and have the database match Oracle examples used in the documentation.

Configuration Mode

[1] Oracle Compatible
[2] PostgreSQL Compatible
Please choose an option [1] : 1

----------------------------------------------------------------------------
Please provide a password for the database superuser (enterprisedb). A locked 
Unix user account (enterprisedb) will be created if not present.

Password :
Retype Password :
----------------------------------------------------------------------------
Additional Configuration

Please select the port number the server should listen on.

Port [5444]: 

Select the locale to be used by the new database cluster.

Locale

[1] [Default locale]
......
Please choose an option [1] : 1

Install sample tables and procedures. [Y/n]: Y

----------------------------------------------------------------------------
Dynatune Dynamic Tuning:
Server Utilization

Please select the type of server to determine the amount of system resources 
that may be utilized:

[1] Development (e.g. a developer's laptop)
[2] General Purpose (e.g. a web or application server)
[3] Dedicated (a server running only Postgres Plus)
Please choose an option [2] : 2

----------------------------------------------------------------------------
Dynatune Dynamic Tuning:
Workload Profile

Please select the type of workload this server will be used for:

[1] Transaction Processing (OLTP systems)
[2] General Purpose (OLTP and reporting workloads)
[3] Reporting (Complex queries or OLAP workloads)
Please choose an option [1] : 2

----------------------------------------------------------------------------
Advanced Configuration

----------------------------------------------------------------------------
PgBouncer Listening Port [6432]: 

----------------------------------------------------------------------------
Service Configuration

Autostart PgBouncer Service [Y/n]: n

Autostart pgAgent Service [Y/n]: n

Update Notification Service [Y/n]: n

The Update Notification Service informs, downloads and installs whenever 
security patches and other updates are available for your Postgres Plus Advanced 
Server installation.

----------------------------------------------------------------------------
Pre Installation Summary

Following settings will be used for installation:

Installation Directory: /opt/PostgresPlus
Data Directory: /opt/PostgresPlus/9.4AS/data
WAL Directory: /opt/PostgresPlus/9.4AS/data/pg_xlog
Database Port: 5444
Database Superuser: enterprisedb
Operating System Account: enterprisedb
Database Service: ppas-9.4
PgBouncer Listening Port: 6432

Press [Enter] to continue:

----------------------------------------------------------------------------
Setup is now ready to begin installing Postgres Plus Advanced Server on your 
computer.

Do you want to continue? [Y/n]: Y

----------------------------------------------------------------------------
Please wait while Setup installs Postgres Plus Advanced Server on your computer.

 Installing Postgres Plus Advanced Server
 0% ______________ 50% ______________ 100%
 ########################################
 Installing Database Server ...
 Installing pgAgent ...
 Installing Connectors ...
 Installing Migration Toolkit ...
 Installing EDB*Plus ...
 Installing Infinite Cache ...
 Installing Postgres Enterprise Manager Client ...
 Installing Slony Replication ...
 Installing pgpool-II ...
 Installing pgpool-II Extensions ...
 Installing PgBouncer ...
 Installing StackBuilder Plus ...
 #

----------------------------------------------------------------------------
Setup has finished installing Postgres Plus Advanced Server on your computer.

done.

Using the standalone installer in unattended mode

Another option is to use the unattended mode by providing all the parameters on the command line or by creating a configuration file. This is an example for providing the parameters on the command line. Most of the parameters can be skipped and the default is applied:

ppasmeta-9.4.1.3-linux-x64/ppasmeta-9.4.1.3-linux-x64.run --mode unattended 
   --enable-components dbserver,connectors,infinitecache,edbmtk,pem_client,
                       pgpool,pgpoolextension,edbplus,replication,pgbouncer 
   --installer-language en --superaccount enterprisedb 
   --servicename ppas-9.4 --serviceaccount enterprisedb 
   --prefix /opt/PostgresPlus --datadir /opt/PostgresPlus/9.4AS/data 
   --xlogdir /opt/PostgresPlus/9.4AS/data/pg_xlog 
   --databasemode oracle --superpassword enterprisedb 
   --webusername document.write(['xx.xx','xx.xxx'].join('@')) --webpassword xxxxx

 Installing Database Server ...
 Installing pgAgent ...
 Installing Connectors ...
 Installing Migration Toolkit ...
 Installing EDB*Plus ...
 Installing Infinite Cache ...
 Installing Postgres Enterprise Manager Client ...
 Installing Slony Replication ...
 Installing pgpool-II ...
 Installing pgpool-II Extensions ...
 Installing PgBouncer ...
 Installing StackBuilder Plus ...X11 connection rejected because of wrong authentication.

Done. No matter which installation method was chosen the result is that ppas is installed and the database cluster is initialized. You might check the processes:

# ps -ef | grep postgres
enterpr+ 12759     1  0 12:03 ?        00:00:00 /opt/PostgresPlus/9.4AS/bin/edb-postgres -D /opt/PostgresPlus/9.4AS/data
enterpr+ 12760 12759  0 12:03 ?        00:00:00 postgres: logger process   
enterpr+ 12762 12759  0 12:03 ?        00:00:00 postgres: checkpointer process   
enterpr+ 12763 12759  0 12:03 ?        00:00:00 postgres: writer process   
enterpr+ 12764 12759  0 12:03 ?        00:00:00 postgres: wal writer process   
enterpr+ 12765 12759  0 12:03 ?        00:00:00 postgres: autovacuum launcher process   
enterpr+ 12766 12759  0 12:03 ?        00:00:00 postgres: stats collector process   
enterpr+ 12882 12759  0 12:03 ?        00:00:00 postgres: enterprisedb edb ::1[45984] idle
root     13866  2619  0 12:15 pts/0    00:00:00 grep --color=auto postgres

Or the services that got created:

# chkconfig --list | grep ppas

Note: This output shows SysV services only and does not include native
      systemd services. SysV configuration data might be overridden by native
      systemd configuration.

      If you want to list systemd services use 'systemctl list-unit-files'.
      To see services enabled on particular target use
      'systemctl list-dependencies [target]'.

ppas-9.4       	0:off	1:off	2:on	3:on	4:on	5:on	6:off
ppas-agent-9.4 	0:off	1:off	2:on	3:on	4:on	5:on	6:off
ppas-infinitecache	0:off	1:off	2:off	3:off	4:off	5:off	6:off
ppas-pgpool    	0:off	1:off	2:off	3:off	4:off	5:off	6:off
ppas-replication-9.4	0:off	1:off	2:off	3:off	4:off	5:off	6:off
# ls -la /etc/init.d/ppas*
-rwxr-xr-x. 1 root root 3663 Apr 23 12:03 /etc/init.d/ppas-9.4
-rwxr-xr-x. 1 root root 2630 Apr 23 12:03 /etc/init.d/ppas-agent-9.4
-rwxr-xr-x. 1 root root 1924 Apr 23 12:04 /etc/init.d/ppas-infinitecache
-rwxr-xr-x. 1 root root 3035 Apr 23 12:04 /etc/init.d/ppas-pgpool
-rwxr-xr-x. 1 root root 3083 Apr 23 12:04 /etc/init.d/ppas-replication-9.4

As the account which installed the software should not be used to work with the database lets create an os account for doing the connections to the database:

# groupadd postgres
# useradd -g postgres postgres
# passwd postgres
Changing password for user postgres.
New password: 
Retype new password: 
passwd: all authentication tokens updated successfully.

ppas brings an environment file for setting all the environment variables. Lets source that so it will be available for future logins:

su - postgres
echo ". /opt/PostgresPlus/9.4AS/pgplus_env.sh" >> .bash_profile

Once you login to the postgres account the environment is there:

$ env | grep PG
PGPORT=5444
PGDATABASE=edb
PGLOCALEDIR=/opt/PostgresPlus/9.4AS/share/locale
PGDATA=/opt/PostgresPlus/9.4AS/data
$ env | grep EDB
EDBHOME=/opt/PostgresPlus/9.4AS

Now we are ready to login to the database:

$ psql -U enterprisedb
Password for user enterprisedb: 
psql.bin (9.4.1.3)
Type "help" for help.

edb=# l
                                           List of databases
   Name    |    Owner     | Encoding |   Collate   |    Ctype    | ICU |       Access privileges       
-----------+--------------+----------+-------------+-------------+-----+-------------------------------
 edb       | enterprisedb | UTF8     | en_US.UTF-8 | en_US.UTF-8 |     | 
 postgres  | enterprisedb | UTF8     | en_US.UTF-8 | en_US.UTF-8 |     | 
 template0 | enterprisedb | UTF8     | en_US.UTF-8 | en_US.UTF-8 |     | =c/enterprisedb              +
           |              |          |             |             |     | enterprisedb=CTc/enterprisedb
 template1 | enterprisedb | UTF8     | en_US.UTF-8 | en_US.UTF-8 |     | =c/enterprisedb              +
           |              |          |             |             |     | enterprisedb=CTc/enterprisedb
(4 rows)

Mission completed. The next post will setup a backup and recovery server for backing up and restoring the ppas database cluster.

Azure DocumentDB: Microsoft goes to NoSQL

Yann Neuhaus - Mon, 2015-04-27 02:26

Azure DocumentDB is a documentary database service and as like its name suggests, it is a Microsoft Azure Cloud service.

b2ap3_thumbnail_Blog_DocumentDB01.jpg

Since the last summer, developers have access to this new service, and now it is available for all.
The Redmond Company offers for the first time a "NoSQL" database.

List listeners and services from the instance

Yann Neuhaus - Fri, 2015-04-24 11:35

Want to know all your listeners - including scan listeners, and the services it listens for? It is possible from the instance, with the - undocumented - view V$LISTENER_NETWORK which is there since 11.2

ALTER TABLE INMEMORY

Yann Neuhaus - Thu, 2015-04-23 06:52

In-Memory Column Store is amazing. It brings very good performance to full table scans. I't easy: just 'flip a switch' and you accelerate all reporting queries on your table, without thinking about what to index and how. But in this post, I would like to warn you about the consequences when you just flip that switch. The new full table scan plan will replace the old ones... even before the table is populated in memory...
I'm not sure that it is the expected behaviour. In my opinion the CBO should consider INMEMORY plans only once the population is done. But here is the exemple.

Test case

Here is the testcase. I have a table DEMO with bitmap indexes on its columns:

12:04:54 SQL> create table DEMO compress as
12:04:54   2  with M as (select substr(dbms_random.string('U',1),1,1) U from dual connect by 10>=level)
12:04:54   3  select M1.U U1, M2.U U2, M3.U U3, M4.U U4 from M M1,M M2, M M3, M M4, (select * from dual connect by 1000>=level)
12:04:54   4  /
Table created.

12:05:00 SQL> create bitmap index DEMO_U1 on DEMO(U1);
Index created.
12:05:01 SQL> create bitmap index DEMO_U2 on DEMO(U2);
Index created.
12:05:03 SQL> create bitmap index DEMO_U3 on DEMO(U3);
Index created.
12:05:04 SQL> create bitmap index DEMO_U4 on DEMO(U4);
Index created.
And my test query on those columns:
12:05:05 SQL> alter session set statistics_level=all;
Session altered.
12:05:05 SQL> select distinct * from DEMO where U1='A' and U2>'X' and U3 in ('A','E') and U4='B';
no rows selected
with its execution plan:
12:05:06 SQL> select * from table(dbms_xplan.display_cursor(format=>'iostats last'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
SQL_ID  64skw45ghn5a0, child number 0
-------------------------------------
select distinct * from DEMO where U1='A' and U2>'X' and U3 in ('A','E')
and U4='B'

Plan hash value: 3881032911

---------------------------------------------------------------------------------------
| Id  | Operation                      | Name    | Starts | E-Rows | A-Rows | Buffers |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |         |      1 |        |      0 |       2 |
|   1 |  HASH UNIQUE                   |         |      1 |      2 |      0 |       2 |
|   2 |   TABLE ACCESS BY INDEX ROWID  | DEMO    |      1 |   4070 |      0 |       2 |
|   3 |    BITMAP CONVERSION TO ROWIDS |         |      1 |        |      0 |       2 |
|   4 |     BITMAP AND                 |         |      1 |        |      0 |       2 |
|   5 |      BITMAP MERGE              |         |      1 |        |      0 |       2 |
|*  6 |       BITMAP INDEX RANGE SCAN  | DEMO_U2 |      1 |        |      0 |       2 |
|*  7 |      BITMAP INDEX SINGLE VALUE | DEMO_U1 |      1 |        |      0 |       0 |
|*  8 |      BITMAP INDEX SINGLE VALUE | DEMO_U4 |      1 |        |      0 |       0 |
|   9 |      BITMAP OR                 |         |      1 |        |      0 |       0 |
|* 10 |       BITMAP INDEX SINGLE VALUE| DEMO_U3 |      1 |        |      0 |       0 |
|* 11 |       BITMAP INDEX SINGLE VALUE| DEMO_U3 |      1 |        |      0 |       0 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - access("U2">'X')
       filter("U2">'X')
   7 - access("U1"='A')
   8 - access("U4"='B')
  10 - access("U3"='A')
  11 - access("U3"='E')


34 rows selected.
Good. I'm happy with that plan. But I've In-Memory option so probably I can get rid of those bitmap indexes.

alter table INMEMORY

Let's put that query in memory:

12:05:06 SQL> alter table DEMO inmemory priority none memcompress for query high;
Table altered.
and run that query again
12:05:06 SQL> select distinct * from DEMO where U1='A' and U2>'X' and U3 in ('A','E') and U4='B';
no rows selected

12:05:07 SQL> select * from table(dbms_xplan.display_cursor(format=>'iostats last'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
SQL_ID  64skw45ghn5a0, child number 0
-------------------------------------
select distinct * from DEMO where U1='A' and U2>'X' and U3 in ('A','E')
and U4='B'

Plan hash value: 51067428

------------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Starts | E-Rows | A-Rows | Buffers | Reads  |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |      1 |        |      0 |   13740 |  13736 |
|   1 |  HASH UNIQUE                |      |      1 |      2 |      0 |   13740 |  13736 |
|*  2 |   TABLE ACCESS INMEMORY FULL| DEMO |      1 |   4070 |      0 |   13740 |  13736 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - inmemory(("U2">'X' AND "U1"='A' AND "U4"='B' AND INTERNAL_FUNCTION("U3")))
       filter(("U2">'X' AND "U1"='A' AND "U4"='B' AND INTERNAL_FUNCTION("U3")))
Here is my problem. Now that I have defined the table to be populated into the In-Memory Column Store, then the CBO choose an In-Memory plan for my query.

This is a FULL TABLE SCAN because you can only do full table scans from the In-Memory Column Store. But I have a problem. The column store is not yet populated:

12:05:07 SQL> select segment_name,inmemory_size,bytes_not_populated from v$im_segments;
no rows selected
So the FULL TABLE SCAN occured on the row store. Look at the statistics above: 1370 logical reads from the buffer cache. And 13736 physical reads because that table is not in the buffer cache. I always used index access for it before, so the table blocks are not in buffer cache. And the full table scan has good change to be done in direct-path.
I still have a very good access from the bitmap indexes - which are still there - but now I'm now doing a very expensive full table scan.

Population

Look at the same query two seconds later:

12:05:09 SQL> select distinct * from DEMO where U1='A' and U2>'X' and U3 in ('A','E') and U4='B';
no rows selected

12:05:09 SQL> select * from table(dbms_xplan.display_cursor(format=>'iostats last'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
SQL_ID  64skw45ghn5a0, child number 0
-------------------------------------
select distinct * from DEMO where U1='A' and U2>'X' and U3 in ('A','E')
and U4='B'

Plan hash value: 51067428

------------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Starts | E-Rows | A-Rows | Buffers | Reads  |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |      1 |        |      0 |   11120 |  11117 |
|   1 |  HASH UNIQUE                |      |      1 |      2 |      0 |   11120 |  11117 |
|*  2 |   TABLE ACCESS INMEMORY FULL| DEMO |      1 |   4070 |      0 |   11120 |  11117 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - inmemory(("U2">'X' AND "U1"='A' AND "U4"='B' AND INTERNAL_FUNCTION("U3")))
       filter(("U2">'X' AND "U1"='A' AND "U4"='B' AND INTERNAL_FUNCTION("U3")))
It is just a bit better: 11117 physical reads instead of 13736. This is because some In-Memory Compression Units are already there in the In-Memory Column Store:
12:05:10 SQL> select segment_name,inmemory_size,bytes_not_populated from v$im_segments;

SEGMENT_NA INMEMORY_SIZE      BYTES BYTES_NOT_POPULATED
---------- ------------- ---------- -------------------
DEMO             6815744  117440512            88973312
Among the 117440512 bytes (which is 14336 8k blocks) only 88973312 are not yet populated (10861 8k blocks). This is why a bit earlier the query still had to read 11120 blocks from buffer cache.

Let's wait 1 minute for population. Remember that during that time, the population uses a lot of CPU in order to read the row store blocs, put it in column, compress it and store it into the column store.

12:06:04 SQL> select distinct * from DEMO where U1='A' and U2>'X' and U3 in ('A','E') and U4='B';
no rows selected

12:06:04 SQL> select * from table(dbms_xplan.display_cursor(format=>'iostats last'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
SQL_ID  64skw45ghn5a0, child number 0
-------------------------------------
select distinct * from DEMO where U1='A' and U2>'X' and U3 in ('A','E')
and U4='B'

Plan hash value: 51067428

---------------------------------------------------------------------------------
| Id  | Operation                   | Name | Starts | E-Rows | A-Rows | Buffers |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |      1 |        |      0 |       3 |
|   1 |  HASH UNIQUE                |      |      1 |      2 |      0 |       3 |
|*  2 |   TABLE ACCESS INMEMORY FULL| DEMO |      1 |   2546 |      0 |       3 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - inmemory(("U1"='A' AND "U4"='B' AND "U2">'X' AND INTERNAL_FUNCTION("U3")))
       filter(("U1"='A' AND "U4"='B' AND "U2">'X' AND INTERNAL_FUNCTION("U3")))
Ok. not only 3 blocks were read from buffer cache. I have now good performance that I can compare with what I had with the bitmap indexes.

This is because population is completed:

12:06:15 SQL> select segment_name,inmemory_size,bytes,bytes_not_populated from v$im_segments;

SEGMENT_NA INMEMORY_SIZE      BYTES BYTES_NOT_POPULATED
---------- ------------- ---------- -------------------
DEMO            31195136  117440512                   0

Conclusion

My conclusion is that altering a table to populate it into the In-memory Column Store looks like an easy operation. But it is not. When you do that:

  • You change the plans to FULL TABLE SCAN which will not be optimal until the table is fully populated.
  • You trigger the population which will increase a lot your server CPU usage
  • you have the risk to get tables only partially populated in case you're in RAC, or if you don't have enough space in the inmemory_size
So this is something to plan and to monitor. And you will also need to think about what happens if your instance crashes and you have to restart it. How long will it take to get back to correct performance?
And that's even without asking yourself yet if you can drop those bitmap indexes that are superseeded by the In-Memory column store now.

Of course, there are solutions for any problem. if you are on Exadata, then SmartScan will come to the rescue until the IMCS is populated. Full table scan is offloaded to storage nodes. Database node CPU resources are available for quick population. In that way, they are complementary.

Successful launch dbi services Zürich

Yann Neuhaus - Wed, 2015-04-22 12:50

Yesterday evening dbi services (headquarters in Delémont) launched officially its third branch in Zürich (Beside Basel and Lausanne). Five years after its take off, the "Oracle Database Partner of Year 2014", is employing more than 40 consultants. I would like to use this opportunity to thank all the customers and partners who trust dbi services. A particular thanks goes to the customers and partners who helped us to enjoy a very pleasant inauguration party yesterday.

Thanks also to Mr Thomas Salzmann (KKG) who presented our successful collaboration and to Massimo Castelli (Oracle) who presented the challenges of recruitment in the IT branch. I was pleased to see that large players like Oracle do, sometimes, have the same challenges as mid-sized companies :-) .

All this adventure would not have been possible without our incredible teams, working hard every day to transform ideas and problems into projects and solutions. dbi services will continue to leverage the skills of its employees, to look for opportunities, in order to remain at top-level provider for the operating systems, database and middleware layers.

A final thanks goes to Kurt Meier who will lead the dbi services branch in Zürich and for the very good organisation of this party. After having won the first customers, Kurt proved that dbi services will succeed and manage this new challenge.

b2ap3_thumbnail_rsz_dsc_2404.jpg

 

b2ap3_thumbnail_rsz_dsc_2390.jpg

 

D2 xPlore search on apostrophes

Yann Neuhaus - Wed, 2015-04-22 03:32

When using the D2 searches you are likely to go into trouble regarding special characters such as apostrophes. The goal in this blog is to show you how to parameterize new special character searches.

In many documents you'll have text with apostrophes or special characters that you want to search on. But unfortunately by default the D2 search will return nothing when you enter apostrophes directly into the search bar. The xPlore will replace special characters by spaces and store the two words one after the other to match them more easily in this order.

In fact this is not a D2 issue. Your xPlore is likely to not be set for special characters handling. By default xPlore is set to recognize apostrophes but in Word for example you have different kind of apostrophes. These characters have a different ascii code, so xPlore doesn't recognize them.

To solve this issue you simply have to tell xPlore to handle Word's apostrophes (or whatever character you want to search on).

In order to do this, login to your xPlore server then edit the following file:

Path$DSEARCH_HOME/config/indexserverconfig.xml

Find the line with:

Line special-characters="@#$%^_~`&:.()-+='/\[]{}" context-characters="!,;?""

Then add your apostrophes or special characters as follow (copy and past directly from Word to the file):

New Line"@#$%^_~`&:.()-+='/\[]{}’‘„“"

And save the file.

Now, new indexed documents can be searched with apostrophes. But note that if you want the older documents to be searchable as well, you will need to re-index the whole repository.

Weblogic ThreadPool has stuck threads

Yann Neuhaus - Wed, 2015-04-22 02:49

In Weblogic it is common to have this warning: ThreadPool has stuck threads. Here we will see a way to determine which can be the cause.

When monitoring Weblogic you can notice that time to time your servers are going in Warning mode. And when clicking on warnings you see this screen:

Warning-reason.png

The reason is presented as "ThreadPool has stuck threads". So it doesn't help a lot. But we can have a deeper view and maybe a real cause.

Now click on the server name from your list, then go to Monitoring -> Threads.

The Hogging Thread Count column shows how many threads seems stuck. The Pending User Request Count column shows the number of requests not delivered to the users. If it is different than 0 your users are impacted.

In order to visualize the real state of threads click on Dump Thread Stacks:

Monitoring-threads-Edited.png

Some threads are marked as stuck whereas they aren't. If the process handled by the thread is too long to achieve then Weblogic will detect it as stuck. By default Weblogic will detect stuck threads after 600 seconds (10 minutes) of waiting (This parameter can be changed).

When the thread dump is displayed you can search for thread with STUCK status:

Thread-dump.png

Here you can see that the thread is stuck in java.lang.Object.wait() function. It means that the thread is waiting for a result or another process to end. In this particular case we can see that the function com.crystaldecisions.sdk.occa.managedreports.ras.internal.CECORBACommunicationAdapter.request() was executed just before waiting, so the thread is likely to wait for the result for this "request" function.

As what we thought the issue came from a reporting server that could not deliver the reports anymore. That's why we had some stuck threads.

Stuck threads are generally generated by the application itself or some other components which do not have to do with weblogic.

Last point, you can check if the thread is stuck in the previous view like this:

Threads-stuck-Edited.png

SQL Server - Change Management: list all updates

Yann Neuhaus - Mon, 2015-04-20 01:29

I am looking to have all SQL Server updates on a server including Service Packs, Cumulative Updates and other fixes like we can see in the uninstall panel from Windows.

installed-update.png

 

C15LV: Nothing to BLOG About - Think Again

Yann Neuhaus - Wed, 2015-04-15 19:58

Before going to the airport to come back in Europe, the las session I assisted at IOUG Collaborate is a panel: Nothing to BLOG About - Think Again

Some famous bloggers were there to help, motivate and inspire people that are afraid to blog. The idea is good. The session was good. The panelists have shared ideas, tips, experience. It was very good.

I'm already a blogger, but that inspired me anyway. In the same idea I'll expose how I started blogging, just in case it gives some ideas.

Note that tomorrow I speak about participating in the community (read, share, write, talk, blog, tweet, link,...) at our internal event dbi xChange. Thus, waiting at the airport, it's a good occasion to explain how I start blogging in case it gives some ideas to people that want to start.