Yann Neuhaus

Subscribe to Yann Neuhaus feed
dbi services technical blog
Updated: 10 hours 59 min ago

Running a Cassandra cluster in a single server

Thu, 2016-10-06 02:25

This blog post is about the configuration of Apache Cassandra for running a cluster of 3 instances on a single host. Basically C* is not really made to run it in a multi instances environment, but for many cases and reasons you might run a C* cluster on a single server. The principal reason is for tests cases.

Context

The objective is to have a Cassandra ring of 3 instances on 1 host. For that, we will use:

  • apache-cassandra-3.0.9 (community version)
  • Separated disks for a segregation between binaries, data and logs: binaries, data and logs will be in separated mount points
  • Multiple IPs interfaces for each instance (virtual interfaces in our case)
  • Instances name: csd1, csd2, csd3
  • Cluster name: dbitest
Binary installation

Download the binaries from the Apache website, http://www.apache.org/dyn/closer.lua/cassandra/3.0.9/apache-cassandra-3.0.9-bin.tar.gz and install it. In this post we are going to install the 3.0.9 release.

wget http://www.pirbot.com/mirrors/apache/cassandra/3.0.9/apache-cassandra-3.0.9-bin.tar.gz

Extract it:

tar -zxvf apache-cassandra-3.0.9-bin.tar.gz

Place the software into the product directory:

cp –r apache-cassandra-3.0.9 /u00/app/cassandra/product/

 

Instances configuration

First, we will create the admin directory for each instance. The admin directory contains the following sub folders:

  • /u00/app/cassandra/admin/csd$i
    • /backup: soft link to the backup mount point
    • /dump: soft link to the dump mount point
    • /etc: Cassandra configuration files, such as Cassandra.yaml, logback.xml and Cassandra-env.sh.
    • /log: soft link to the log file from the log mount point
    • /pid: contain the pid of the C* process

To create these directories, use the following loop.

for i in 1 2 3 ;
do mkdir -p /u00/app/cassandra/admin/csd$i &&
mkdir /u00/app/cassandra/admin/csd$i/backup &&
mkdir /u00/app/cassandra/admin/csd$i/dump &&
mkdir /u00/app/cassandra/admin/csd$i/etc &&
mkdir /u00/app/cassandra/admin/csd$i/log &&
mkdir /u00/app/cassandra/admin/csd$i/pid &&
mkdir /u00/app/cassandra/admin/csd$i/product;
done

Next copy the C* configuration files into each admin/csd$i/etc directory. Every instance has its own configuration files.

for i in 1 2 3;
do cp -r /u00/app/cassandra/admin/csd$i/product/apache-cassandra-3.0.9/conf/* /u00/app/cassandra/admin/csd$i/etc/;
done

Now, create the three data directories for each instance.

for i in 1 2 3;
do
sudo mkdir -p /u01/cassandradata/csd$i &&
sudo chown -R cassandra:cassandra /u01/cassandradata/csd$i;
done

Configure virtual network interfaces

You must create additional virtual network interfaces for each C* instance, to use a different listen_address and rpc_address for each instance.

sudo ifconfig enp0s3:0 192.168.56.105
sudo ifconfig enp0s3:1 192.168.56.106

Then, you have 3 interfaces (default + 2 virtuals), for your C* instances.

enp0s3: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 192.168.56.104  …

enp0s3:0: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 192.168.56.105  …

enp0s3:1: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 192.168.56.106  …

enp0s8: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        …

lo: flags=73<UP,LOOPBACK,RUNNING>  mtu 65536
…

As all the required files are in place, we can now change the 3 main configuration files, to set up the three instances (csd1, csd2 and csd3).

cassandra-env.sh:

Use a different “JMX_PORT” for each node. For instance: 7199 for csd1, 7299 for csd2 and 7399 for csd3.

JMX will bind to the local host IP by default, you can use the default.

cassandra.yaml

The central configuration file of Apache Cassandra. Change the following parameters:

  • cluster_name: ‘dbitest’ All instances must have the same cluster name
  • commitlog_directory: /u01/cassandradata/csd$i/commitlog
  • data_file_directories:/u01/cassandradata/csd$i/data
  • saved_caches_directory: /u01/cassandradata/csd$i/saved_caches
  • listen_address: 192.168.56.104
  • rpc_address: 192.168.56.104
  • seeds: specify the list of IP of the seed nodes

logback.xml

The configuration file for C* logs. Make the following changes:

<file>${cassandra.logdir}/system.log</file> to <file>/u00/app/cassandra/admin/csd$i/log/system.log</file>

<fileNamePattern>${cassandra.logdir}/system.log.%i.zip</fileNamePattern> to 
<fileNamePattern>/u00/app/cassandra/admin/csd$i/log/system.log.%i.zip</fileNamePattern>

<file>${cassandra.logdir}/debug.log</file> to <file>/u00/app/cassandra/admin/csd$i/debug.log</file>

<fileNamePattern>${cassandra.logdir}/debug.log.%i.zip</fileNamePattern> to
<fileNamePattern>/u00/app/cassandra/admin/csd1/log/debug.log.%i.zip</fileNamePattern>

Make the change for the three instances.

Starting the nodes

Before starting each node, you must dynamically set the environment variables for each of them.

$CASSANDRA_HOME=/u00/app/cassandra/product/apache-cassandra-3.0.9
$CASSANDRA_CONF=/u00/app/cassandra/admin/csd$i/etc/

After, setting the variables for each node you can start Apache Cassandra by command-line:

./$CASSANDRA_HOME/bin/cassandra –f

Execute the same command in a separated shell, for each node. Do not forget to set the environment variables $CASSANDRA_HOME and $CASSANDRA_CONF.

Cluster status

Verify the cluster status with nodetool utility. Check if all nodes are up and running.

[cassandra@test bin]$ ./nodetool status
 Datacenter: datacenter1
 =======================
 Status=Up/Down|/ State=Normal/Leaving/Joining/Moving
 --  Address         Load       Tokens       Owns (effective)  Host ID                               Rack
 UN  192.168.56.104  263.42 KB  256          64.5%             cddee7f4-c0d5-4cba-9ddc-b773a08a9245  rack1
 UN  192.168.56.105  212.19 KB  256          67.5%             19cd17d2-1aeb-48e5-9299-7a0282c2e92e  rack1
 UN  192.168.56.106  147.54 KB  256          68.0%             f65d5b92-ed3b-4f68-b93d-72e6162eafca  rack1

 

Enjoy ;-)

 

Cet article Running a Cassandra cluster in a single server est apparu en premier sur Blog dbi services.

How to patch Postgres Plus Advanced Server

Wed, 2016-10-05 09:43

As with any other software there comes the time when you need to patch your Postgres Plus Advanced Server instances. Is that different from patching Community PostgreSQL? Yes and no :) The difference is that you need a subscription to get access to the EDB Customer Portal for being able to download the patch. This is pretty much the same as with My Oracle Support where you need a customer support identifier mapped to your account for being able to download patches, to have access to the knowledge base and for being able to open cases in case you run into troubles which you are not able to solve yourself.

Assuming you have access to the EDB customer portal and you downloaded the patch for your base release the procedure is pretty simple. For this little demo I am running the 9.5.0.5 base release of Postgres Plus Advanced Server:

postgres@centos7:/home/postgres/ [PG2] psql
psql.bin (9.5.0.5)
Type "help" for help.

postgres=# select version();
                                                   version                                                    
--------------------------------------------------------------------------------------------------------------
 EnterpriseDB 9.5.0.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-55), 64-bit
(1 row)

postgres=# 

This is the release you can download from the EDB website for testing. The patch I downloaded from the portal is this one:

postgres@centos7:/home/postgres/ [PG2] cd /u01/app/postgres/software/
postgres@centos7:/u01/app/postgres/software/ [PG2] ls -la
total 27216
drwxrwxr-x. 2 postgres postgres       51 Oct  5 10:16 .
drwxrwxr-x. 5 postgres postgres       47 Jun 15 13:10 ..
-rw-rw-r--. 1 postgres postgres 27868299 Oct  5 10:16 postgresplusas-9.5.4.9-1-linux-x64.run
postgres@centos7:/u01/app/postgres/software/ [PG2] chmod +x postgresplusas-9.5.4.9-1-linux-x64.run 

This should patch my base release to currently latest release which is 9.5.4.9-1. How does it work? Lets execute the binary and see what happens:

postgres@centos7:/u01/app/postgres/software/ [PG2] ./postgresplusas-9.5.4.9-1-linux-x64.run 
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

Error: There has been an error.
This installer requires root privileges. Please become superuser before 
executing the installer
Press [Enter] to continue:

A no go for most of the cases. Running installers as root is not a good practice and should be avoided whenever possible. But, luckily, as with the base release installer the patch itself can be installed in “extract only” mode:

postgres@centos7:/u01/app/postgres/software/ [PG2] ./postgresplusas-9.5.4.9-1-linux-x64.run --extract-only yes --prefix /u01/app/postgres/product/95edb/db_5/9.5AS/
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 specify the directory where Postgres Plus Advanced Server will be 
installed.

Installation Directory [/u01/app/postgres/product/95edb/db_5/9.5AS]: 

----------------------------------------------------------------------------
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 Database Server
 0% ______________ 50% ______________ 100%
 #########################################

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

postgres@centos7:/u01/app/postgres/software/ [PG2] 

Looks good, but you never should do this when your PostgreSQL is running, otherwise you’ll get this:

2016-10-05 10:22:43 CEST LOG:  server process (PID 4359) was terminated by signal 11: Segmentation fault
2016-10-05 10:22:43 CEST LOG:  terminating any other active server processes
2016-10-05 10:22:43 CEST WARNING:  terminating connection because of crash of another server process
2016-10-05 10:22:43 CEST DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2016-10-05 10:22:43 CEST HINT:  In a moment you should be able to reconnect to the database and repeat your command.
2016-10-05 10:22:43 CEST LOG:  statistics collector process (PID 3324) was terminated by signal 11: Segmentation fault
2016-10-05 10:22:43 CEST LOG:  all server processes terminated; reinitializing

Always shutdown, before you begin to patch. In my case I just started the instance again and I am on the current release:

postgres@centos7:/u01/app/postgres/software/ [PG2] sqh
psql.bin (9.5.4.9)
Type "help" for help.

postgres=# select version();
                                                   version                                                    
--------------------------------------------------------------------------------------------------------------
 EnterpriseDB 9.5.4.9 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-55), 64-bit
(1 row)

postgres=# 

Simple and fast. If you prepare this very well your downtime will be around one minute. I can already hear the question: Can I switchover to my standby, apply the patch on the master, switchback and then proceed on the standby for reducing the downtime even more? This will be a topic for another post.

PS: Of course you can also prepare a brand new home for the patched binaries and then shutdown your instance, switch to the new binaries and start again from there.

PS2: Just in case you are not aware of: As we have established a partnership with EnterpriseDB in the past you can obtain (an) EDB subscription(s) easily from us. Of course we would do a review of what you really need before. It is not always required to go for the Postgres Plus version. Community PostgreSQL works very well in the most cases and can be backed by a EDB subscription as well, if required.

 

Cet article How to patch Postgres Plus Advanced Server est apparu en premier sur Blog dbi services.

Apache Cassandra overview

Tue, 2016-10-04 08:20

Apache Cassandra is one of the most popular NoSQL database http://db-engines.com/en/ranking. It is used by many big company as Facebook, Netflix and many others. Initially the project was initiated by Facebook. In combination of Google BigTable and Amazon DynamoDB, they developed Cassandra. Actually, Cassandra is developed and maintained by the Apache foundation for the community version and by DataStax for the enterprise and commercial version.

Cassandra_history

Architecture

Cassandra has a master-less architecture. A cluster is represented as a ring. It’s a peer-to-peer cluster, with no single point of failure.

Cassandra key features:

  • Distributed
  • Decentralized
  • Replicated
  • Scalable
  • Fault-tolerant
  • Tuneable consistency level
  • No Single Point of Failure
  • High Available
  • Multi data center

Cassandra_RIng2

Any server nodes can accept write or read queries from clients. Every server is equal.

Cassandra use the Gossip protocol for internode communication within the cluster. The Gossip allows the exchanges of information between cluster nodes, as the status. Each cluster nodes send it status every second to three other node in the ring. A gossip message contain a version and in case of conflict the older version is overwritten.

A Partitioned Row Store

Because C* is a NoSQL database, and in the “NoSQL world” no strict standards are defined, there is a lot of misunderstanding around the Cassandra data model. Currently, C* is defined as a column-store oriented DBMS, but there is a big confusion on this definition.

To define properly, the Cassandra data model you have to decompose it. The picture below will help you to understand the data objects composition.

thinkmap

Cassandra data model is organized into rows of multiple columns/values pairs. Each row is uniquely identifiable by a key, the Row Key. Rows group columns and super columns. Column is the most basic unit of the data model. All columns are sorted by column key name, and all row key are sorted by row key.

Each row key, are stored into tables or column-family and both are encapsulate into Keyspaces. Below a representation of the complete Cassandra data model.

Cassandra_dataModel

Finally, Apache C* can be defined as a Partitioned row store database.

 

How data is distributed

Cassandra use a partitioner (internal component) to distribute data across cluster nodes. A partitioner determines where each piece of data have to be stored. The partitioning process is completely automatic and transparent.

Basically the partitioner is a hash function and compute a token for each partition key (Partition Key = Row Key). Depending on the partitioning strategy, each node of the cluster is responsible of a token. Below a picture of the partitioning process.

Cassandra_Partitioning

 

Three partitioning strategy are available:

  • Murmur3Partitioner
  • RandomPartitioner
  • ByteOrderedPartitioner

 

Replication

Cassandra can stores multiple copy of data on multiple nodes in order to ensure high availability and fault tolerance. Two important concepts are used for C* replication.

Replication strategy: determines in which node data is placed

Replication factor: determines the number of nodes where data is placed. A replication factor of 1 means that the data is replicated one time in one node.

The client connect to any node in the ring. This node became the coordinator, then the coordinator determines where the data must be stored. Finally, the data is replicated from one node to another in the sense of a clock hand.

Cassandra_replication

 

 

A quick overview of Cassandra main concepts has been done in this post in order to understand more precisely how Apache Cassandra works. Of course Cassandra concepts are more complex and will be described in future blog posts.

 

Cet article Apache Cassandra overview est apparu en premier sur Blog dbi services.

Oracle 12c and RMAN switch datafile to copy, is it really so easy?

Tue, 2016-10-04 06:29

Oracle incrementally updating backups are used quite often because they are easy to setup and restoring a datafile is very fast. It is very fast because you are not really restoring a datafile, you are switching to the copy, in case something happens. But how do I switch back to the original destination with minimal downtime and with minimal impact on the system?

A quite common scenario is that we have 3 diskgroups, +DATA, +FRA and +REDO with different performance characteristics, like the following:

  • +DATA Diskgoup is on fast storage (10k rpm)
  • +FRA Diskgroup is on medium storage (7200 rpm)
  • +REDO Diskgroup is on very fast storage (15k rpm)

Loosing a 8T datafile

 

 

 

 

 

 

 

 

In case we loose now a bigfile with 8TB on the +DATA diskgroup, what options do we  have to restore it. Ok. Lets ask the Oracle Data Recovery Advisor first.  Oracle came up with the following script.

oracle@oel001:/home/oracle/rman/ [OCM121] cat /u00/app/oracle/diag/rdbms/ocm121/OCM121/hm/reco_3905236091.hm
   # restore and recover datafile
   restore ( datafile 9 );
   recover datafile 9;
   sql 'alter database datafile 9 online';

The script does his job, no question about it, but it means, that Oracle would copy 8TB from the +FRA to +DATA and afterwards maybe applying an inc1 and some archivelogs. If we do run this script, we wait for 4h. (suppose that we are copying with 600MB per second, which is very good)

In case your Database has a Standby in a DataGuard configuration, Oracle comes up with the following suggestion.

oracle@oel001:/home/oracle/rman/ [OCM121] cat /u00/app/oracle/diag/rdbms/ocm121/OCM121/hm/reco_4177443733.hm
   # restore from standby and recover datafile
   restore ( datafile 9 from service "sty121" );
   recover datafile 9;
   sql 'alter database datafile 9 online';

Again, it will work, but now Oracle tries to get the 8TB datafile over the network from the standby, which makes it even slower. Unfortunately, the “switch datafile to copy” was not build in, into the Recovery Advisor.

Ok. Lets do it manually and switch to the datafile copy. That takes only a few minutes, and this is the reason why we have incrementally updating backups. To make it as fast as possible.

RMAN> switch datafile 9 to copy;

datafile 9 switched to datafile copy "+FRA/OCM121/DATAFILE/monster.346.919594959"

RMAN> recover datafile 9;

RMAN> sql 'alter database datafile 9 online';

 

Now we restored and recoverd the 8TB datafile, and users can start working again on that bigfile tablespace. But due to the fact that the +FRA has only medium storage, your application might run slower than before.

Be careful, another issue might pop up after you are already on your datafile copy in the +FRA. If now a backup kicks it (scheduled by cron or something else), then Oracle has to create another 8TB in the +FRA as a new base for his incrementally updating backups, which makes your application even slower and even worse, you might run out of space.

An easy restore might end up now in a quite complex scenario. So what do we do now. First of all, we have to make sure that backups are not scheduled during our restore/recovery, and then we can manually create a new datafile copy in +DATA (of course, after the situation was corrected which lead to the datafile loss). In case you are running 12c, you can use the new feature “Multisection Backup for Datafile Copies”.

RMAN> backup section size 1T as copy datafile 9 format ='+DATA' tag clonefile9;

 

Now, a small downtime kicks in, when we have to take the datafile offline, switch to our new one in +DATA, recover it, and take it online again.

RMAN> sql 'alter database datafile 9 offline';

RMAN> switch datafile 9 to copy;

RMAN> recover datafile 9;

RMAN> sql 'alter database datafile 9 online';

 

Uffff … we are ready now, and users can work with the application again which is on the fast storage in +DATA. But wait a second, if we start our RMAN backup again, then Oracle does not regognize  the datafile copy in +FRA anymore as a valid copy for incrementally updataing backups. So, Oracle has to create another 8TB in the +FRA.

Now comes the 1Million $ question. How can we avoid this? We need to tag the datafile copy in +FRA as a valid starting point for incrementally updating backups.

RMAN> catalog datafilecopy '+fra/ocm121/datafile/MONSTER.346.919594959' level 0 TAG 'incr_update';

cataloged datafile copy
datafile copy file name=+FRA/ocm121/datafile/monster.346.919594959 RECID=33 STAMP=919603336

Oracle has the very useful command “catalog” for situations like this. Take care, that you specify “level 0″ and the correct “tag”, otherwise the datafile copy will not be regognized.

Now we are really ready, and we can start the RMAN incremetally backups again, like we did beforehand.

To summarize it:

  • Take care of your backups during the restore, it might makes the situation even worse.
  • Make use of the new feature “Multisection Backup for Datafile Copies”. It can speed up the creation of your datafile copies quite heavily.
  • Use the “catalog” command to tag your datafile copy correctly. It avoids the creation of another 8TB.

Cheers,

William

 

Cet article Oracle 12c and RMAN switch datafile to copy, is it really so easy? est apparu en premier sur Blog dbi services.

Oracle 12cR2: DDL deferred invalidation

Sun, 2016-10-02 15:07

In a previous post I described the new V$SQL views about rolling invalidation. I did the example with dbms_stats which is able to do rolling invalidation since 11g. But more is coming with 12.2 as you can use rolling invalidation for some DDL.

When you do some DDL on an object, dependent cursors are invalidated. On a busy database it’s something to avoid. I’ve seen recently a peak of hanging sessions during 10 minutes and the root cause was an ALTER TABLE ADD COMMENT. This do not change anything in the execution plan, but the cursors are invalidated. And doing that on a central table can trigger a hard parse storm.

For the example, I’ll reduce the rolling invalidation window to 25 seconds as I don’t want to wait hours:
SQL> alter system set "_optimizer_invalidation_period"=25;
System altered.

Currently 12.2 is available only on Oracle Database Express Cloud Service, but please don’t ask me how I was able to set an underscore parameter there. However you can reproduce the same by waiting 5 hours instead of 25 seconds.

I create a simple table and prepare my query to check cursor information:

SQL> create table DEMO as select * from dual;
Table created.
 
SQL> select sql_text,invalidations,loads,parse_calls,executions,first_load_time,last_load_time,last_active_time,is_rolling_invalid from v$sql where sql_text like 'S%DEMO%';
no rows selected
 

I run a simple query and check the cursor:

SQL> SELECT * FROM DEMO;
 
D
-
X
 
SQL>
SQL> select sql_text,invalidations,loads,parse_calls,executions,first_load_time,last_load_time,last_active_time,is_rolling_invalid from v$sql where sql_text like 'S%DEMO%';
 
SQL_TEXT INVALIDATIONS LOADS PARSE_CALLS EXECUTIONS FIRST_LOAD_TIME LAST_LOAD_TIME LAST_ACTIVE IS_ROLLING_INVALID
------------------- ------------- ---------- ----------- ---------- ------------------- ------------------- ----------- --------------------
SELECT * FROM DEMO 0 1 1 1 2016-09-17/01:04:16 2016-09-17/01:04:16 01:04:16 N

1 parse call, cursor loaded, hard parsed and executed.

I create an index on the table with the DEFERRED INVALIDATION new syntax:


SQL> create index DEMO on DEMO(dummy) deferred invalidation;
Index created.

I see that it is flagged as rolling invalid:

SQL> select sql_text,invalidations,loads,parse_calls,executions,first_load_time,last_load_time,last_active_time,is_rolling_invalid from v$sql where sql_text like 'S%DEMO%';
 
SQL_TEXT INVALIDATIONS LOADS PARSE_CALLS EXECUTIONS FIRST_LOAD_TIME LAST_LOAD_TIME LAST_ACTIVE IS_ROLLING_INVALID
------------------- ------------- ---------- ----------- ---------- ------------------- ------------------- ----------- --------------------
SELECT * FROM DEMO 0 1 1 1 2016-09-17/01:04:16 2016-09-17/01:04:16 01:04:16 Y

When I run it, a timestamp is set within the rolling invalidation window (5 hours by default, but here 25 seconds):

SQL> SELECT * FROM DEMO;
 
D
-
X
 
SQL> select sql_text,invalidations,loads,parse_calls,executions,first_load_time,last_load_time,last_active_time,is_rolling_invalid from v$sql where sql_text like 'S%DEMO%';
 
SQL_TEXT INVALIDATIONS LOADS PARSE_CALLS EXECUTIONS FIRST_LOAD_TIME LAST_LOAD_TIME LAST_ACTIVE IS_ROLLING_INVALID
------------------- ------------- ---------- ----------- ---------- ------------------- ------------------- ----------- --------------------
SELECT * FROM DEMO 0 1 2 2 2016-09-17/01:04:16 2016-09-17/01:04:16 01:04:16 X

As you see, this Y/N flag has a third value to show that it has been executed after being rolling invalidated.

I wait 30 seconds:

SQL> host sleep 30

From that point, the invalidation timestamp has been reached so a new execution will create a new child cursor:

SQL> select sql_text,invalidations,loads,parse_calls,executions,first_load_time,last_load_time,last_active_time,is_rolling_invalid from v$sql where sql_text like 'S%DEMO%';
 
SQL_TEXT INVALIDATIONS LOADS PARSE_CALLS EXECUTIONS FIRST_LOAD_TIME LAST_LOAD_TIME LAST_ACTIVE IS_ROLLING_INVALID
------------------- ------------- ---------- ----------- ---------- ------------------- ------------------- ----------- --------------------
SELECT * FROM DEMO 0 1 2 2 2016-09-17/01:04:16 2016-09-17/01:04:16 01:04:16 X
 
SQL> SELECT * FROM DEMO;
 
D
-
X
 
SQL> select sql_text,invalidations,loads,parse_calls,executions,first_load_time,last_load_time,last_active_time,is_rolling_invalid from v$sql where sql_text like 'S%DEMO%';
 
SQL_TEXT INVALIDATIONS LOADS PARSE_CALLS EXECUTIONS FIRST_LOAD_TIME LAST_LOAD_TIME LAST_ACTIVE IS_ROLLING_INVALID
------------------- ------------- ---------- ----------- ---------- ------------------- ------------------- ----------- --------------------
SELECT * FROM DEMO 0 1 2 2 2016-09-17/01:04:16 2016-09-17/01:04:16 01:04:16 X
SELECT * FROM DEMO 0 1 1 1 2016-09-17/01:04:16 2016-09-17/01:04:45 01:04:45 N

You must be careful here. If you used to check the INVALIDATIONS column, then you may miss the rolling ones. INVALIDATIONS is for parent cursors. IS_ROLLING_INVALID is for invalidated child cursors.

Note that, of course, until the invalidation, the new index will not be used by those cursors. So if you create the index to solve an performance issue you may prefer to invalidate the cursors.

Same can be done with index rebuild:


SQL> alter index DEMO rebuild deferred invalidation;
Index altered.
 
SQL> select sql_text,invalidations,loads,parse_calls,executions,first_load_time,last_load_time,last_active_time,is_rolling_invalid from v$sql where sql_text like 'S%DEMO%';
 
SQL_TEXT INVALIDATIONS LOADS PARSE_CALLS EXECUTIONS FIRST_LOAD_TIME LAST_LOAD_TIME LAST_ACTIVE IS_ROLLING_INVALID
------------------- ------------- ---------- ----------- ---------- ------------------- ------------------- ----------- --------------------
SELECT * FROM DEMO 0 1 2 2 2016-09-17/01:04:16 2016-09-17/01:04:16 01:04:16 X
SELECT * FROM DEMO 0 1 1 1 2016-09-17/01:04:16 2016-09-17/01:04:45 01:04:45 Y
 
SQL> SELECT * FROM DEMO;
 
D
-
X
 
SQL> select sql_text,invalidations,loads,parse_calls,executions,first_load_time,last_load_time,last_active_time,is_rolling_invalid from v$sql where sql_text like 'S%DEMO%';
 
SQL_TEXT INVALIDATIONS LOADS PARSE_CALLS EXECUTIONS FIRST_LOAD_TIME LAST_LOAD_TIME LAST_ACTIVE IS_ROLLING_INVALID
------------------- ------------- ---------- ----------- ---------- ------------------- ------------------- ----------- --------------------
SELECT * FROM DEMO 0 1 2 2 2016-09-17/01:04:16 2016-09-17/01:04:16 01:04:16 X
SELECT * FROM DEMO 0 1 2 2 2016-09-17/01:04:16 2016-09-17/01:04:45 01:04:46 X

Of course, rolling invalidation can happen only for the cursors that do not use the index.

With the same restriction, you can do it when you set an index unusable


SQL> alter index DEMO unusable deferred invalidation;
 
Index altered.
 
SQL> select sql_text,invalidations,loads,parse_calls,executions,first_load_time,last_load_time,last_active_time,is_rolling_invalid from v$sql where sql_text like 'S%DEMO%';
 
SQL_TEXT INVALIDATIONS LOADS PARSE_CALLS EXECUTIONS FIRST_LOAD_TIME LAST_LOAD_TIME LAST_ACTIVE IS_ROLLING_INVALID
------------------- ------------- ---------- ----------- ---------- ------------------- ------------------- ----------- --------------------
SELECT * FROM DEMO 0 1 2 2 2016-09-17/01:04:16 2016-09-17/01:04:16 01:04:16 X
SELECT * FROM DEMO 0 1 2 2 2016-09-17/01:04:16 2016-09-17/01:04:45 01:04:46 X
 
SQL> SELECT * FROM DEMO;
 
D
-
X
 
SQL> select sql_text,invalidations,loads,parse_calls,executions,first_load_time,last_load_time,last_active_time,is_rolling_invalid from v$sql where sql_text like 'S%DEMO%';
 
SQL_TEXT INVALIDATIONS LOADS PARSE_CALLS EXECUTIONS FIRST_LOAD_TIME LAST_LOAD_TIME LAST_ACTIVE IS_ROLLING_INVALID
------------------- ------------- ---------- ----------- ---------- ------------------- ------------------- ----------- --------------------
SELECT * FROM DEMO 0 1 2 2 2016-09-17/01:04:16 2016-09-17/01:04:16 01:04:16 X
SELECT * FROM DEMO 0 1 3 3 2016-09-17/01:04:16 2016-09-17/01:04:45 01:04:46 X

You get the same behavior if you drop the index: cursors that do not use it are no invalidated immediately.

From my tests, you can add DEFERRED INVALIDATION when you MOVE TABLE, but invalidation is immediate. Only when moving partitions, the rolling invalidation occurs.

An alternative to specify DEFERRED INVALIDATION in the DDL is to set it as the default:


SQL> show parameter cursor_invalidation
 
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_invalidation string IMMEDIATE
 
SQL> alter session set cursor_invalidation=deferred;
 
Session altered.
 

But remember, not all DDL will do rolling invalidation, even when the syntax is accepted.

 

Cet article Oracle 12cR2: DDL deferred invalidation est apparu en premier sur Blog dbi services.

Running PostgreSQL on ZFS on Linux – Compression

Sat, 2016-10-01 08:18

In the last posts in this little series we looked at how to get a ZFS file system up and running on a CentOS 7 host and how snapshots and clones can be used to simply processes such as testing and cloning PostgreSQL instances. In this post we’ll look at another feature of zfs: Compression.

The current status of my ZFS file systems is:

[root@centos7 ~] zfs list
NAME            USED  AVAIL  REFER  MOUNTPOINT
pgpool          170M  9.46G  20.5K  /pgpool
pgpool/pgdata   169M  9.46G   169M  /pgpool/pgdata

To check if compression is enabled:

[root@centos7 ~] zfs get compression pgpool/pgdata
NAME           PROPERTY     VALUE     SOURCE
pgpool/pgdata  compression  off       default

Lets create another file system and enable compression for it:

[root@centos7 ~] zfs create pgpool/pgdatacompressed
[root@centos7 ~] zfs list
NAME                      USED  AVAIL  REFER  MOUNTPOINT
pgpool                    170M  9.46G  20.5K  /pgpool
pgpool/pgdata             169M  9.46G   169M  /pgpool/pgdata
pgpool/pgdatacompressed    19K  9.46G    19K  /pgpool/pgdatacompressed
[root@centos7 ~] zfs get compression pgpool/pgdatacompressed
NAME                     PROPERTY     VALUE     SOURCE
pgpool/pgdatacompressed  compression  off       default
[root@centos7 ~] zfs set compression=on pgpool/pgdatacompressed
[root@centos7 ~] zfs get compression pgpool/pgdatacompressed
NAME                     PROPERTY     VALUE     SOURCE
pgpool/pgdatacompressed  compression  on        local

You can ask zfs to report the compression ratio for a file system:

[root@centos7 ~] zfs get compressratio pgpool/pgdatacompressed
NAME                     PROPERTY       VALUE  SOURCE
pgpool/pgdatacompressed  compressratio  1.00x  -
[root@centos7 ~] chown postgres:postgres /pgpool/pgdatacompressed/

The ratio is 1 which is because we do not have any data yet. Lets copy the PostgreSQL cluster from the uncompressed file system into our new compressed file system:

postgres@centos7:/home/postgres/ [PG1] cp -pr /pgpool/pgdata/* /pgpool/pgdatacompressed/
postgres@centos7:/home/postgres/ [PG1] ls -l /pgpool/pgdatacompressed/
total 30
drwx------. 6 postgres postgres     6 Sep 29 14:00 base
drwx------. 2 postgres postgres    54 Sep 29 14:27 global
drwx------. 2 postgres postgres     3 Sep 28 15:11 pg_clog
drwx------. 2 postgres postgres     2 Sep 28 15:11 pg_commit_ts
drwx------. 2 postgres postgres     2 Sep 28 15:11 pg_dynshmem
-rw-------. 1 postgres postgres  4468 Sep 28 15:11 pg_hba.conf
-rw-------. 1 postgres postgres  1636 Sep 28 15:11 pg_ident.conf
drwxr-xr-x. 2 postgres postgres     2 Sep 28 15:11 pg_log
drwx------. 4 postgres postgres     4 Sep 28 15:11 pg_logical
drwx------. 4 postgres postgres     4 Sep 28 15:11 pg_multixact
drwx------. 2 postgres postgres     3 Sep 29 14:27 pg_notify
drwx------. 2 postgres postgres     2 Sep 28 15:11 pg_replslot
drwx------. 2 postgres postgres     2 Sep 28 15:11 pg_serial
drwx------. 2 postgres postgres     2 Sep 28 15:11 pg_snapshots
drwx------. 2 postgres postgres     5 Sep 29 14:46 pg_stat
drwx------. 2 postgres postgres     2 Sep 29 14:46 pg_stat_tmp
drwx------. 2 postgres postgres     3 Sep 28 15:11 pg_subtrans
drwx------. 2 postgres postgres     2 Sep 28 15:11 pg_tblspc
drwx------. 2 postgres postgres     2 Sep 28 15:11 pg_twophase
-rw-------. 1 postgres postgres     4 Sep 28 15:11 PG_VERSION
drwx------. 3 postgres postgres     8 Sep 29 14:26 pg_xlog
-rw-------. 1 postgres postgres    88 Sep 28 15:11 postgresql.auto.conf
-rw-------. 1 postgres postgres 21270 Sep 28 15:11 postgresql.conf
-rw-------. 1 postgres postgres    69 Sep 29 14:27 postmaster.opts

We already should see a difference, shouldn’t we?

postgres@centos7:/home/postgres/ [PG1] df -h | grep pgdata
pgpool/pgdata            9.6G  170M  9.4G   2% /pgpool/pgdata
pgpool/pgdatacompressed  9.5G   82M  9.4G   1% /pgpool/pgdatacompressed

Not bad, less than half of the size. We should see another compression ratio than 1 now:

[root@centos7 ~] zfs get compressratio pgpool/pgdatacompressed
NAME                     PROPERTY       VALUE  SOURCE
pgpool/pgdatacompressed  compressratio  1.93x  -

Lets generate some data in our two PostgreSQL instances and check the time it takes as well as the size of the file systems afterwards. As in the last post the second instance just gets a different port, everything else is identical:

postgres@centos7:/home/postgres/ [PG1] pg_ctl start -D /pgpool/pgdata
postgres@centos7:/home/postgres/ [PG1] sed -i 's/#port = 5432/port=5433/g' /pgpool/pgdatacompressed/postgresql.conf
postgres@centos7:/home/postgres/ [PG1] FATAL:  data directory "/pgpool/pgdatacompressed" has group or world access
postgres@centos7:/home/postgres/ [PG1] chmod o-rwx,g-rwx /pgpool/pgdatacompressed/
postgres@centos7:/home/postgres/ [PG1] pg_ctl start -D /pgpool/pgdatacompressed/

This is the script to generate some data:

\timing
\c postgres
drop database if exists dataload;
create database dataload;
\c dataload
create table dataload ( a bigint
                      , b varchar(100)
                      , c timestamp
                      );
with 
  data_generator_num as
     ( select *
         from generate_series ( 1
                              , 1000000 ) nums
     ) 
insert into dataload
select data_generator_num.nums
     , md5(data_generator_num.nums::varchar)
     , current_date+data_generator_num.nums
 from data_generator_num;

I will run the script two times on each instance. For the instance on the uncompressed file system:

-- FIRST RUN
postgres=# \i generate_data.sql
Timing is on.
You are now connected to database "postgres" as user "postgres".
DROP DATABASE
Time: 720.626 ms
CREATE DATABASE
Time: 4631.212 ms
You are now connected to database "dataload" as user "postgres".
CREATE TABLE
Time: 6.517 ms
INSERT 0 1000000
Time: 28668.343 ms
-- SECOND RUN
dataload=# \i generate_data.sql
Timing is on.
You are now connected to database "postgres" as user "postgres".
DROP DATABASE
Time: 774.061 ms
CREATE DATABASE
Time: 2721.169 ms
You are now connected to database "dataload" as user "postgres".
CREATE TABLE
Time: 7.374 ms
INSERT 0 1000000
Time: 32168.043 ms
dataload=# 

For the instance on the compressed file system:

-- FIRST RUN
postgres=# \i generate_data.sql
Timing is on.
You are now connected to database "postgres" as user "postgres".
psql:generate_data.sql:3: NOTICE:  database "dataload" does not exist, skipping
DROP DATABASE
Time: 0.850 ms
CREATE DATABASE
Time: 4281.965 ms
You are now connected to database "dataload" as user "postgres".
CREATE TABLE
Time: 5.120 ms
INSERT 0 1000000
Time: 30606.966 ms
-- SECOND RUN
dataload=# \i generate_data.sql
Timing is on.
You are now connected to database "postgres" as user "postgres".
DROP DATABASE
Time: 2359.120 ms
CREATE DATABASE
Time: 3267.151 ms
You are now connected to database "dataload" as user "postgres".
CREATE TABLE
Time: 8.665 ms
INSERT 0 1000000
Time: 23474.290 ms
dataload=# 

Despite that the numbers are quite bad (5 seconds to create an empty table) the fastest load was the second one on the compressed file system. So at least it is not slower. I have to admit that I did not do any tuning on the file systems and my VM does not have much memory (512m) which is far too less if you work with ZFS (ZFS needs much memory, at least 1gb).

So, what about the size of the data. First lets check what PostgreSQL is telling us:

-- instance on the uncompressed file system
dataload=# select * from pg_size_pretty ( pg_relation_size ( 'dataload' ));
 pg_size_pretty 
----------------
 81 MB
(1 row)
-- instance on the compressed file system
dataload=# select * from pg_size_pretty ( pg_relation_size ( 'dataload' ));
 pg_size_pretty 
----------------
 81 MB
(1 row)

Exactly the same, which is not surprising as PostgreSQL sees the files as if they would be uncompressed (please be aware that the my_app_table from the last post is still there which is why the file system usage in total is larger than you might expect). It is quite funny on how the size is reported on the compressed file system depending on how you ask.

You can use oid2name to map the file name to a table name:

postgres@centos7:/pgpool/pgdatacompressed/base/24580/ [PG1] oid2name -d dataload -p 5433 -f 24581
From database "dataload":
  Filenode  Table Name
----------------------
     24581    dataload

File 24581 is the table we generated. When you ask for the size by using “du” you get:

postgres@centos7:/pgpool/pgdatacompressed/base/24580/ [PG1] du -h 24581
48M	24581

This is the compressed size. When you use “ls” you get the uncompressed size:

postgres@centos7:/pgpool/pgdatacompressed/base/24580/ [PG1] ls -lh 24581
-rw-------. 1 postgres postgres 81M Sep 30 10:43 24581

What does “df” tell us:

postgres@centos7:/home/postgres/ [PG1] df -h | grep pgdata
pgpool/pgdata            9.5G  437M  9.1G   5% /pgpool/pgdata
pgpool/pgdatacompressed  9.2G  165M  9.1G   2% /pgpool/pgdatacompressed

Not bad, 437M of uncompressed data which is 165M compressed. So, if you are short on space this really can be an option.

 

Cet article Running PostgreSQL on ZFS on Linux – Compression est apparu en premier sur Blog dbi services.

Oracle 12c and RMAN automatic datafile creation

Fri, 2016-09-30 09:13

A lot of new features popped up with RMAN 12c, like  Expansion of Multi-section support, or Simplified cross-platform migration and many many more. However, in this post I would like to highlight a small, but very helpful new feature which I demonstrated during my session at the SOUG day.

The automatic datafile creation

In earlier versions of Oracle, you might run into situations where you create a new tablespace, and some objects into it, and then, all of a sudden, the DB crashes or the datafile is lost, without ever being backed up.

This is where the follwing command comes into play:

alter database create datafile <NAME>;

However, in Oracle 12c, this is done automatically for you, in case you use RMAN.

Lets create a tablespace and afterwards resize the datafile.

SQL> create tablespace dbi datafile '/home/oracle/rman/dbi01.dbf' size 16M;

Tablespace created.

SQL> alter database datafile '/home/oracle/rman/dbi01.dbf' resize 32M;

Database altered.

Now lets create a table in the new tablespace.

SQL> create table t_dbi tablespace dbi as select * from dba_objects;

Table created.

SQL> select count(*) from t_dbi;

  COUNT(*)
----------
    120130

Afterwards, we simulate an error and then check what Oracle says.

$ echo destroy > /home/oracle/rman/dbi01.dbf
SQL> alter system flush buffer_cache; 

System altered. 

SQL> select count(*) from t_dbi; 
select count(*) from t_dbi 
* 
ERROR at line 1: 
ORA-03135: connection lost contact Process ID: 25538 
Session ID: 387 Serial number: 55345

From the alert.log

Errors in file /u00/app/oracle/diag/rdbms/ocm121/OCM121/trace/OCM121_ora_25904.trc:
ORA-01157: cannot identify/lock data file 11 - see DBWR trace file
ORA-01110: data file 11: '/home/oracle/rman/dbi01.dbf'

Ok. Datafile is gone, and we have no backup. Lets do a preview to see, how Oracle would restore the datafile.

RMAN> restore ( datafile 11 ) preview;

Starting restore at 30-SEP-2016 15:46:27
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=355 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=126 device type=DISK

datafile 11 will be created automatically during restore operation
using channel ORA_DISK_1
using channel ORA_DISK_2

List of Archived Log Copies for database with db_unique_name OCM121
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - --------------------
519     1    529     A 30-SEP-2016 12:23:00
        Name: +FRA/OCM121/ARCHIVELOG/2016_09_30/thread_1_seq_529.454.923931563

recovery will be done up to SCN 7346834
Media recovery start SCN is 7346834
Recovery must be done beyond SCN 7346834 to clear datafile fuzziness
Finished restore at 30-SEP-2016 15:46:28

As you can see in the preview output, Oracle will create the datafile automatically for us. Ok. Lets try it.

RMAN> restore ( datafile 11 );

Starting restore at 30-SEP-2016 15:48:29
using channel ORA_DISK_1
using channel ORA_DISK_2

creating datafile file number=11 name=/home/oracle/rman/dbi01.dbf
restore not done; all files read only, offline, or already restored
Finished restore at 30-SEP-2016 15:48:31

RMAN> recover datafile 11;

Starting recover at 30-SEP-2016 15:49:17
using channel ORA_DISK_1
using channel ORA_DISK_2

starting media recovery
media recovery complete, elapsed time: 00:00:02

Finished recover at 30-SEP-2016 15:49:20

RMAN> alter database datafile 11 online;

Statement processed

Ready. We can now access our table again without running the “alter database create datafile” command during the restore. And the resize to 32M was also done for us. However, the resize came during the recovery part.

$ ls -l dbi01.dbf
-rw-r----- 1 oracle asmadmin 33562624 Sep 30 15:52 dbi01.dbf

From my point of view, a small, but quite helpful feature.

One last remark. I have done my demo with Oracle 12cR1 with July 2016 PSU. In that version, the RMAN PREVIEW command has a bug, which says that you cannot recover into a consistent state, even if you can.  After applying the following OnOff patch on top of July PSU 2016, the RMAN PREVIEW command works as expected.

Patch 20315311: RMAN-5119: RECOVERY CAN NOT BE DONE TO A CONSISTENT STATE

Cheers,

William

 

 

 

 

Cet article Oracle 12c and RMAN automatic datafile creation est apparu en premier sur Blog dbi services.

Running PostgreSQL on ZFS on Linux – Fun with snapshots and clones

Fri, 2016-09-30 02:24

In the last post we looked at how to get a ZFS file system up and running on a CentOS 7 host and how to enable the auto mount of the ZFS file systems. In this post we’ll look at two of the features ZFS provides: Snapshots and clones.

A ZFS snapshot is a read only copy of a file system. How can we benefit from that when it comes to PostgreSQL. There are several scenarios where this can be useful. Imagine you are developing an application and you want to test the deployment of a new release on top of a previous release. What you probably want to have is a production like PostgreSQL instance with lots of data for being able to test the upgrade path. In addition it would be great if you can revert in seconds and start from scratch just in case you run into troubles or you missed one important point in the upgrade scripts. Using ZFS snapshots you can have all of this. Lets see.

Currently my PostgreSQL instance from the last post does not contain any user data, so lets generate some:

postgres= create table my_app_table ( a int, b varchar(50) );
CREATE TABLE
postgres=# with aa as 
postgres-# ( select * 
postgres(#     from generate_series (1,1000000) a
postgres(# )
postgres-# insert into my_app_table
postgres-# select aa.a, md5(aa.a::varchar)
postgres-#   from aa;
INSERT 0 1000000

This is the release we want to test our upgrade scripts from so lets create a snapshot of the current state of our instance:

[root@centos7 ~] zfs snapshot pgpool/pgdata@baserelease
[root@centos7 ~] zfs list -t snapshot
NAME                        USED  AVAIL  REFER  MOUNTPOINT
pgpool/pgdata@baserelease  16.6M      -   202M  -

The “@baserelease” is the name of the snapshot or to be correct everything after the “@” is the name of the snapshot.

Are you worried about consistency? This should not be an issue as PostgreSQL fsyncs the WAL so the instance should just start, apply all the wal records which are missing from the data files and you’re fine. Anyway, this is a scenario for testing: So as long as you have a consistent starting point you are fine.

A simple upgrade script could be:

postgres=# alter table my_app_table add column c date;
ALTER TABLE
postgres=# update my_app_table set c = now();
UPDATE 1000000

What happened to the snapshot?

[root@centos7 ~] zfs list -t snapshot
NAME                        USED  AVAIL  REFER  MOUNTPOINT
pgpool/pgdata@baserelease  78.3M      -   202M  -

As soon as you modify data the snapshot will grow, no surprise.

So you did run your tests and discovered some things you could improve and once you improved what you wanted you want to start from the same point again. When having a snapshot this is quite easy, just revert to the snapshot. Of course you’ll need to stop your PostgreSQL instance first:

postgres@centos7:/home/postgres/ [PG1] pg_ctl stop -D /pgpool/pgdata/ -m fast
waiting for server to shut down....LOG:  received fast shutdown request
LOG:  aborting any active transactions
LOG:  autovacuum launcher shutting down
LOG:  shutting down
 done
server stopped

As soon as the instance is down the snapshot can be reverted:

[root@centos7 ~] zfs rollback pgpool/pgdata@baserelease
[root@centos7 ~] zfs list -t snapshot
NAME                        USED  AVAIL  REFER  MOUNTPOINT
pgpool/pgdata@baserelease     1K      -   202M  -

When you check the data after you started the instance again it is exactly as it was before:

postgres@centos7:/home/postgres/ [PG1] pg_ctl start -D /pgpool/pgdata/
postgres@centos7:/home/postgres/ [PG1] LOG:  database system was not properly shut down; automatic recovery in progress
postgres@centos7:/home/postgres/ [PG1] psql postgres
psql (9.5.4 dbi services build)
Type "help" for help.

postgres= \d my_app_table
        Table "public.my_app_table"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 a      | integer               | 
 b      | character varying(50) | 

Notice the message about the automatic recovery, that is when the wal is replayed. Now you can just start your upgrade script again, revert in case of issues, start again, revert again, and so on.

Another use case: Rapid cloning of PostgreSQL instances (clones are writable, snapshots not). How does that work? This is where clones come into the game. For being able to clone you need a snapshot as clones depend on snapshots. Another thing to keep in mind is that you can not delete a snapshot when you have a clone still sitting on top of it. Lets see how it works:

As said we need a snapshot:

[root@centos7 ~] zfs snapshot pgpool/pgdata@clonebase

On top of this snapshot we can now create a clone:

[root@centos7 ~] zfs create pgpool/clones
[root@centos7 ~] zfs clone pgpool/pgdata@clonebase pgpool/clones/1
[root@centos7 ~] zfs list
NAME              USED  AVAIL  REFER  MOUNTPOINT
pgpool            170M  9.46G    21K  /pgpool
pgpool/clones    20.5K  9.46G  19.5K  /pgpool/clones
pgpool/clones/1     1K  9.46G   169M  /pgpool/clones/1
pgpool/pgdata     170M  9.46G   169M  /pgpool/pgdata

Using the new clone we bring up another PostgreSQL instance in seconds, containing the exact data from the source of the clone:

postgres@centos7:/home/postgres/ [PG1] rm /pgpool/clones/1/*.pid
postgres@centos7:/home/postgres/ [PG1] sed -i 's/#port = 5432/port=5433/g' /pgpool/clones/1/postgresql.conf
postgres@centos7:/home/postgres/ [PG1] pg_ctl start -D /pgpool/clones/1/
postgres@centos7:/home/postgres/ [PG1] psql -p 5433 postgres
psql (9.5.4 dbi services build)
Type "help" for help.

postgres=

Quite cool and easy.

Conclusion: I am not sure if I’d use ZFS for production databases on Linux because I have not tested enough. But for development and testing purposes there are quite a few benefits such as snapshots and cloning. This can simply your processes a lot. You could even use snapshots and clones as a basis for your backups although I’d prefer barman or bart.

PS: To clean up:

[root@centos7 ~] zfs destroy pgpool/clones/1
[root@centos7 ~] zfs destroy pgpool/clones
 

Cet article Running PostgreSQL on ZFS on Linux – Fun with snapshots and clones est apparu en premier sur Blog dbi services.

SQLSaturday #546 Porto – Troubleshooting scenarios with availability groups

Fri, 2016-09-30 01:48

sqlsat546_header

J-1 before the next SQL Saturday 2016 in Portugal.

I usually promote this event in France but this time this is for SQL Server Portuguese community. I will have the chance to talk about SQL Server AlwaysOn and troubleshooting scenarios. It will be also a good opportunity to meet portuguese people like Niko Neugebauer (), André Batista () and many others.

Moreover, Porto is definitely a beautiful city and a good way to finish the summer vacations.

Hope to see you there!

 

Cet article SQLSaturday #546 Porto – Troubleshooting scenarios with availability groups est apparu en premier sur Blog dbi services.

Running PostgreSQL on ZFS on Linux

Wed, 2016-09-28 09:43

ZFS for Solaris is around for several years now (since 2015). But there is also a project called OpenZFS which makes ZFS available on other operating systems. For Linux the announcement for ZFS being production ready was back in 2013. So why not run PostgreSQL on it? ZFS provides many cool features including compression, snapshots and build in volume management. Lets give it a try and do an initial setup. More details will follow in separate posts.

As usual I am running a CentOS 7 VM for my tests:

[root@centos7 ~] lsb_release -a
LSB Version:	:core-4.1-amd64:core-4.1-noarch:cxx-4.1-amd64:cxx-4.1-noarch:desktop-4.1-amd64:desktop-4.1-noarch:languages-4.1-amd64:languages-4.1-noarch:printing-4.1-amd64:printing-4.1-noarch
Distributor ID:	CentOS
Description:	CentOS Linux release 7.2.1511 (Core) 
Release:	7.2.1511
Codename:	Core

There is a dedicated website for ZFS on Linux where you can find the instructions on how to install it for various distributions. The instruction for CentOS/RHEL are quite easy. Download the repo files:

[root@centos7 ~] yum install http://download.zfsonlinux.org/epel/zfs-release$(rpm -E %dist).noarch.rpm
Loaded plugins: fastestmirror
zfs-release.el7.centos.noarch.rpm                                                                    | 5.0 kB  00:00:00     
Examining /var/tmp/yum-root-Uv79vc/zfs-release.el7.centos.noarch.rpm: zfs-release-1-3.el7.centos.noarch
Marking /var/tmp/yum-root-Uv79vc/zfs-release.el7.centos.noarch.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package zfs-release.noarch 0:1-3.el7.centos will be installed
--> Finished Dependency Resolution

Dependencies Resolved

============================================================================================================================
 Package                  Arch                Version                     Repository                                   Size
============================================================================================================================
Installing:
 zfs-release              noarch              1-3.el7.centos              /zfs-release.el7.centos.noarch              2.9 k

Transaction Summary
============================================================================================================================
Install  1 Package

Total size: 2.9 k
Installed size: 2.9 k
Is this ok [y/d/N]: y
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : zfs-release-1-3.el7.centos.noarch                                                                        1/1 
  Verifying  : zfs-release-1-3.el7.centos.noarch                                                                        1/1 

Installed:
  zfs-release.noarch 0:1-3.el7.centos                                                                                       

Complete!

[root@centos7 ~] gpg --quiet --with-fingerprint /etc/pki/rpm-gpg/RPM-GPG-KEY-zfsonlinux
gpg: new configuration file `/root/.gnupg/gpg.conf' created
gpg: WARNING: options in `/root/.gnupg/gpg.conf' are not yet active during this run
pub  2048R/F14AB620 2013-03-21 ZFS on Linux 
      Key fingerprint = C93A FFFD 9F3F 7B03 C310  CEB6 A9D5 A1C0 F14A B620
sub  2048R/99685629 2013-03-21

For the next step it depends if you want to go with DKMS or kABI-tracking kmod. I’ll go with kABI-tracking kmod and therefore will disable the DKMS repository and enable the kmod repository:

[root@centos7 ~] cat /etc/yum.repos.d/zfs.repo 
[zfs]
name=ZFS on Linux for EL7 - dkms
baseurl=http://download.zfsonlinux.org/epel/7/$basearch/
enabled=0
metadata_expire=7d
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-zfsonlinux

[zfs-kmod]
name=ZFS on Linux for EL7 - kmod
baseurl=http://download.zfsonlinux.org/epel/7/kmod/$basearch/
enabled=1
metadata_expire=7d
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-zfsonlinux

[zfs-source]
name=ZFS on Linux for EL7 - Source
baseurl=http://download.zfsonlinux.org/epel/7/SRPMS/
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-zfsonlinux

[zfs-testing]
name=ZFS on Linux for EL7 - dkms - Testing
baseurl=http://download.zfsonlinux.org/epel-testing/7/$basearch/
enabled=0
metadata_expire=7d
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-zfsonlinux

[zfs-testing-kmod]
name=ZFS on Linux for EL7 - kmod - Testing
baseurl=http://download.zfsonlinux.org/epel-testing/7/kmod/$basearch/
enabled=0
metadata_expire=7d
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-zfsonlinux

[zfs-testing-source]
name=ZFS on Linux for EL7 - Testing Source
baseurl=http://download.zfsonlinux.org/epel-testing/7/SRPMS/
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-zfsonlinux
[root@centos7 ~] 

Installing ZFS from here on is just a matter of using yum:

[root@centos7 ~] yum install zfs
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
 * base: mirror.spreitzer.ch
 * extras: mirror.spreitzer.ch
 * updates: mirror.de.leaseweb.net
zfs-kmod/x86_64/primary_db                                                                           | 231 kB  00:00:01     
Resolving Dependencies
--> Running transaction check
---> Package zfs.x86_64 0:0.6.5.8-1.el7.centos will be installed
--> Processing Dependency: zfs-kmod = 0.6.5.8 for package: zfs-0.6.5.8-1.el7.centos.x86_64
--> Processing Dependency: spl = 0.6.5.8 for package: zfs-0.6.5.8-1.el7.centos.x86_64
--> Processing Dependency: libzpool2 = 0.6.5.8 for package: zfs-0.6.5.8-1.el7.centos.x86_64
--> Processing Dependency: libzfs2 = 0.6.5.8 for package: zfs-0.6.5.8-1.el7.centos.x86_64
--> Processing Dependency: libuutil1 = 0.6.5.8 for package: zfs-0.6.5.8-1.el7.centos.x86_64
--> Processing Dependency: libnvpair1 = 0.6.5.8 for package: zfs-0.6.5.8-1.el7.centos.x86_64
--> Processing Dependency: libzpool.so.2()(64bit) for package: zfs-0.6.5.8-1.el7.centos.x86_64
--> Processing Dependency: libzfs_core.so.1()(64bit) for package: zfs-0.6.5.8-1.el7.centos.x86_64
--> Processing Dependency: libzfs.so.2()(64bit) for package: zfs-0.6.5.8-1.el7.centos.x86_64
--> Processing Dependency: libuutil.so.1()(64bit) for package: zfs-0.6.5.8-1.el7.centos.x86_64
--> Processing Dependency: libnvpair.so.1()(64bit) for package: zfs-0.6.5.8-1.el7.centos.x86_64
--> Running transaction check
---> Package kmod-zfs.x86_64 0:0.6.5.8-1.el7.centos will be installed
--> Processing Dependency: spl-kmod for package: kmod-zfs-0.6.5.8-1.el7.centos.x86_64
---> Package libnvpair1.x86_64 0:0.6.5.8-1.el7.centos will be installed
---> Package libuutil1.x86_64 0:0.6.5.8-1.el7.centos will be installed
---> Package libzfs2.x86_64 0:0.6.5.8-1.el7.centos will be installed
---> Package libzpool2.x86_64 0:0.6.5.8-1.el7.centos will be installed
---> Package spl.x86_64 0:0.6.5.8-1.el7.centos will be installed
--> Running transaction check
---> Package kmod-spl.x86_64 0:0.6.5.8-1.el7.centos will be installed
--> Finished Dependency Resolution

Dependencies Resolved

============================================================================================================================
 Package                     Arch                    Version                                Repository                 Size
============================================================================================================================
Installing:
 zfs                         x86_64                  0.6.5.8-1.el7.centos                   zfs-kmod                  334 k
Installing for dependencies:
 kmod-spl                    x86_64                  0.6.5.8-1.el7.centos                   zfs-kmod                  110 k
 kmod-zfs                    x86_64                  0.6.5.8-1.el7.centos                   zfs-kmod                  665 k
 libnvpair1                  x86_64                  0.6.5.8-1.el7.centos                   zfs-kmod                   35 k
 libuutil1                   x86_64                  0.6.5.8-1.el7.centos                   zfs-kmod                   41 k
 libzfs2                     x86_64                  0.6.5.8-1.el7.centos                   zfs-kmod                  123 k
 libzpool2                   x86_64                  0.6.5.8-1.el7.centos                   zfs-kmod                  423 k
 spl                         x86_64                  0.6.5.8-1.el7.centos                   zfs-kmod                   29 k

Transaction Summary
============================================================================================================================
Install  1 Package (+7 Dependent packages)

Total download size: 1.7 M
Installed size: 5.7 M
Is this ok [y/d/N]: y
Downloading packages:
(1/8): kmod-spl-0.6.5.8-1.el7.centos.x86_64.rpm                                                      | 110 kB  00:00:01     
(2/8): libnvpair1-0.6.5.8-1.el7.centos.x86_64.rpm                                                    |  35 kB  00:00:00     
(3/8): libuutil1-0.6.5.8-1.el7.centos.x86_64.rpm                                                     |  41 kB  00:00:00     
(4/8): kmod-zfs-0.6.5.8-1.el7.centos.x86_64.rpm                                                      | 665 kB  00:00:02     
(5/8): libzfs2-0.6.5.8-1.el7.centos.x86_64.rpm                                                       | 123 kB  00:00:00     
(6/8): libzpool2-0.6.5.8-1.el7.centos.x86_64.rpm                                                     | 423 kB  00:00:00     
(7/8): spl-0.6.5.8-1.el7.centos.x86_64.rpm                                                           |  29 kB  00:00:00     
(8/8): zfs-0.6.5.8-1.el7.centos.x86_64.rpm                                                           | 334 kB  00:00:00     
----------------------------------------------------------------------------------------------------------------------------
Total                                                                                       513 kB/s | 1.7 MB  00:00:03     
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : libuutil1-0.6.5.8-1.el7.centos.x86_64                                                                    1/8 
  Installing : libnvpair1-0.6.5.8-1.el7.centos.x86_64                                                                   2/8 
  Installing : libzpool2-0.6.5.8-1.el7.centos.x86_64                                                                    3/8 
  Installing : kmod-spl-0.6.5.8-1.el7.centos.x86_64                                                                     4/8 
  Installing : spl-0.6.5.8-1.el7.centos.x86_64                                                                          5/8 
  Installing : libzfs2-0.6.5.8-1.el7.centos.x86_64                                                                      6/8 
  Installing : kmod-zfs-0.6.5.8-1.el7.centos.x86_64                                                                     7/8 
  Installing : zfs-0.6.5.8-1.el7.centos.x86_64                                                                          8/8 
  Verifying  : libnvpair1-0.6.5.8-1.el7.centos.x86_64                                                                   1/8 
  Verifying  : libzfs2-0.6.5.8-1.el7.centos.x86_64                                                                      2/8 
  Verifying  : zfs-0.6.5.8-1.el7.centos.x86_64                                                                          3/8 
  Verifying  : spl-0.6.5.8-1.el7.centos.x86_64                                                                          4/8 
  Verifying  : kmod-zfs-0.6.5.8-1.el7.centos.x86_64                                                                     5/8 
  Verifying  : libzpool2-0.6.5.8-1.el7.centos.x86_64                                                                    6/8 
  Verifying  : libuutil1-0.6.5.8-1.el7.centos.x86_64                                                                    7/8 
  Verifying  : kmod-spl-0.6.5.8-1.el7.centos.x86_64                                                                     8/8 

Installed:
  zfs.x86_64 0:0.6.5.8-1.el7.centos                                                                                         

Dependency Installed:
  kmod-spl.x86_64 0:0.6.5.8-1.el7.centos   kmod-zfs.x86_64 0:0.6.5.8-1.el7.centos  libnvpair1.x86_64 0:0.6.5.8-1.el7.centos 
  libuutil1.x86_64 0:0.6.5.8-1.el7.centos  libzfs2.x86_64 0:0.6.5.8-1.el7.centos   libzpool2.x86_64 0:0.6.5.8-1.el7.centos  
  spl.x86_64 0:0.6.5.8-1.el7.centos       

Complete!
[root@centos7 ~]

Be aware that the kernel modules are not loaded by default, so you have to do this on your own:

[root@centos7 ~] /sbin/modprobe zfs
Last login: Wed Sep 28 11:04:21 2016 from 192.168.22.1
[postgres@centos7 ~]$ lsmod | grep zfs
zfs                  2713912  0 
zunicode              331170  1 zfs
zavl                   15236  1 zfs
zcommon                55411  1 zfs
znvpair                93227  2 zfs,zcommon
spl                    92223  3 zfs,zcommon,znvpair
[root@centos7 ~] zfs list
no datasets available

For loading the modules automatically create a file under /etc/modules-load.d:

[root@centos7 ~] echo "zfs" > /etc/modules-load.d/zfs.conf
[root@centos7 ~] cat /etc/modules-load.d/zfs.conf
zfs

So far so good. Lets create a ZFS file system. I have two disks available for playing with ZFS (sdb and sdc):

[root@centos7 ~] ls -la /dev/sd*
brw-rw----. 1 root disk 8,  0 Sep 28 11:14 /dev/sda
brw-rw----. 1 root disk 8,  1 Sep 28 11:14 /dev/sda1
brw-rw----. 1 root disk 8,  2 Sep 28 11:14 /dev/sda2
brw-rw----. 1 root disk 8, 16 Sep 28 11:14 /dev/sdb
brw-rw----. 1 root disk 8, 32 Sep 28 11:14 /dev/sdc

The first thing you have to do is to create a new zfs pool (I don’t care about the warnings, that is why I use the “-f” option below):

[root@centos7 ~] zpool create pgpool mirror /dev/sdb /dev/sdc
invalid vdev specification
use '-f' to override the following errors:
/dev/sdb does not contain an EFI label but it may contain partition information in the MBR.
/dev/sdc does not contain an EFI label but it may contain partition information in the MBR.
[root@centos7 ~] zpool create pgpool mirror /dev/sdb /dev/sdc -f
[root@centos7 ~] zpool list
NAME     SIZE  ALLOC   FREE  EXPANDSZ   FRAG    CAP  DEDUP  HEALTH  ALTROOT
pgpool  9.94G    65K  9.94G         -     0%     0%  1.00x  ONLINE  -
[root@centos7 ~] zpool status pgpool
  pool: pgpool
 state: ONLINE
  scan: none requested
config:

	NAME        STATE     READ WRITE CKSUM
	pgpool      ONLINE       0     0     0
	  mirror-0  ONLINE       0     0     0
	    sdb     ONLINE       0     0     0
	    sdc     ONLINE       0     0     0

errors: No known data errors

[root@centos7 ~] df -h
Filesystem               Size  Used Avail Use% Mounted on
/dev/mapper/centos-root   49G  1.7G   47G   4% /
devtmpfs                 235M     0  235M   0% /dev
tmpfs                    245M     0  245M   0% /dev/shm
tmpfs                    245M  4.3M  241M   2% /run
tmpfs                    245M     0  245M   0% /sys/fs/cgroup
/dev/sda1                497M  291M  206M  59% /boot
tmpfs                     49M     0   49M   0% /run/user/1000
pgpool                   9.7G     0  9.7G   0% /pgpool

What I did here is to create a mirrored pool over my two disks. The open zfs wiki has some performance tips for running PostgreSQL on ZFS as well as for other topics. Lets go with the recommendations:

[root@centos7 ~] zfs create pgpool/pgdata -o recordsize=8192
[root@centos7 ~] zfs set logbias=throughput pgpool/pgdata
[root@centos7 ~] zfs set primarycache=all pgpool/pgdata
[root@centos7 ~] zfs list
NAME            USED  AVAIL  REFER  MOUNTPOINT
pgpool           82K  9.63G  19.5K  /pgpool
pgpool/pgdata    19K  9.63G    19K  /pgpool/pgdata

My new ZFS file system is ready and already mounted, cool. Lets change the permissions and list all the properties:

[root@centos7 ~] chown postgres:postgres /pgpool/pgdata
[root@centos7 ~] zfs get all /pgpool/pgdata
NAME           PROPERTY              VALUE                  SOURCE
pgpool/pgdata  type                  filesystem             -
pgpool/pgdata  creation              Wed Sep 28 11:31 2016  -
pgpool/pgdata  used                  19K                    -
pgpool/pgdata  available             9.63G                  -
pgpool/pgdata  referenced            19K                    -
pgpool/pgdata  compressratio         1.00x                  -
pgpool/pgdata  mounted               yes                    -
pgpool/pgdata  quota                 none                   default
pgpool/pgdata  reservation           none                   default
pgpool/pgdata  recordsize            8K                     local
pgpool/pgdata  mountpoint            /pgpool/pgdata         default
pgpool/pgdata  sharenfs              off                    default
pgpool/pgdata  checksum              on                     default
pgpool/pgdata  compression           off                    default
pgpool/pgdata  atime                 on                     default
pgpool/pgdata  devices               on                     default
pgpool/pgdata  exec                  on                     default
pgpool/pgdata  setuid                on                     default
pgpool/pgdata  readonly              off                    default
pgpool/pgdata  zoned                 off                    default
pgpool/pgdata  snapdir               hidden                 default
pgpool/pgdata  aclinherit            restricted             default
pgpool/pgdata  canmount              on                     default
pgpool/pgdata  xattr                 on                     default
pgpool/pgdata  copies                1                      default
pgpool/pgdata  version               5                      -
pgpool/pgdata  utf8only              off                    -
pgpool/pgdata  normalization         none                   -
pgpool/pgdata  casesensitivity       sensitive              -
pgpool/pgdata  vscan                 off                    default
pgpool/pgdata  nbmand                off                    default
pgpool/pgdata  sharesmb              off                    default
pgpool/pgdata  refquota              none                   default
pgpool/pgdata  refreservation        none                   default
pgpool/pgdata  primarycache          all                    default
pgpool/pgdata  secondarycache        all                    default
pgpool/pgdata  usedbysnapshots       0                      -
pgpool/pgdata  usedbydataset         19K                    -
pgpool/pgdata  usedbychildren        0                      -
pgpool/pgdata  usedbyrefreservation  0                      -
pgpool/pgdata  logbias               throughput             local
pgpool/pgdata  dedup                 off                    default
pgpool/pgdata  mlslabel              none                   default
pgpool/pgdata  sync                  standard               default
pgpool/pgdata  refcompressratio      1.00x                  -
pgpool/pgdata  written               19K                    -
pgpool/pgdata  logicalused           9.50K                  -
pgpool/pgdata  logicalreferenced     9.50K                  -
pgpool/pgdata  filesystem_limit      none                   default
pgpool/pgdata  snapshot_limit        none                   default
pgpool/pgdata  filesystem_count      none                   default
pgpool/pgdata  snapshot_count        none                   default
pgpool/pgdata  snapdev               hidden                 default
pgpool/pgdata  acltype               off                    default
pgpool/pgdata  context               none                   default
pgpool/pgdata  fscontext             none                   default
pgpool/pgdata  defcontext            none                   default
pgpool/pgdata  rootcontext           none                   default
pgpool/pgdata  relatime              on                     temporary
pgpool/pgdata  redundant_metadata    all                    default
pgpool/pgdata  overlay               off                    default

Ready to deploy a PostgreSQL instance on it:

postgres@centos7:/home/postgres/ [pg954] initdb -D /pgpool/pgdata/
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locales
  COLLATE:  en_US.UTF-8
  CTYPE:    en_US.UTF-8
  MESSAGES: en_US.UTF-8
  MONETARY: de_CH.UTF-8
  NUMERIC:  de_CH.UTF-8
  TIME:     en_US.UTF-8
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /pgpool/pgdata ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
creating template1 database in /pgpool/pgdata/base/1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects' descriptions ... ok
creating collations ... ok
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
loading PL/pgSQL server-side language ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok
syncing data to disk ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    pg_ctl -D /pgpool/pgdata/ -l logfile start

Startup:

postgres@centos7:/home/postgres/ [pg954] mkdir /pgpool/pgdata/pg_log
postgres@centos7:/home/postgres/ [pg954] sed -i 's/logging_collector = off/logging_collector = on/g' /pgpool/pgdata/postgresql.conf
postgres@centos7:/home/postgres/ [pg954] pg_ctl -D /pgpool/pgdata/ start
postgres@centos7:/home/postgres/ [pg954] psql postgres
psql (9.5.4 dbi services build)
Type "help" for help.

postgres=

Ready. Lets reboot and check if the ZFS file system is mounted automatically:

postgres@centos7:/home/postgres/ [pg954] df -h
Filesystem               Size  Used Avail Use% Mounted on
/dev/mapper/centos-root   49G  1.8G   47G   4% /
devtmpfs                 235M     0  235M   0% /dev
tmpfs                    245M     0  245M   0% /dev/shm
tmpfs                    245M  4.3M  241M   2% /run
tmpfs                    245M     0  245M   0% /sys/fs/cgroup
/dev/sda1                497M  291M  206M  59% /boot
tmpfs                     49M     0   49M   0% /run/user/1000
postgres@centos7:/home/postgres/ [pg954] lsmod | grep zfs
zfs                  2713912  0 
zunicode              331170  1 zfs
zavl                   15236  1 zfs
zcommon                55411  1 zfs
znvpair                93227  2 zfs,zcommon
spl                    92223  3 zfs,zcommon,znvpair

Gone. The kernel modules are loaded but the file system was not mounted. What to do?

[root@centos7 ~] zpool list
no pools available
[root@centos7 ~] zpool import pgpool
[root@centos7 ~] zpool list
NAME     SIZE  ALLOC   FREE  EXPANDSZ   FRAG    CAP  DEDUP  HEALTH  ALTROOT
pgpool  9.94G  39.3M  9.90G         -     0%     0%  1.00x  ONLINE  -
[root@centos7 ~]# df -h
Filesystem               Size  Used Avail Use% Mounted on
/dev/mapper/centos-root   49G  1.8G   47G   4% /
devtmpfs                 235M     0  235M   0% /dev
tmpfs                    245M     0  245M   0% /dev/shm
tmpfs                    245M  4.3M  241M   2% /run
tmpfs                    245M     0  245M   0% /sys/fs/cgroup
/dev/sda1                497M  291M  206M  59% /boot
tmpfs                     49M     0   49M   0% /run/user/1000
pgpool                   9.6G     0  9.6G   0% /pgpool
pgpool/pgdata            9.7G   39M  9.6G   1% /pgpool/pgdata

Ok, how to auto mount?

[root@centos7 ~] systemctl enable zfs-mount
[root@centos7 ~] systemctl enable zfs-import-cache
[root@centos7 ~] reboot

I am not sure why this is necessary, should happen automatically.

PS: There is an interesting discussion about PostgreSQL on ZFS on the PostgreSQL performance mailing list currently.

 

Cet article Running PostgreSQL on ZFS on Linux est apparu en premier sur Blog dbi services.

Connecting your PostgreSQL instance to an Oracle database – Debian version

Tue, 2016-09-27 08:03

Some time ago I blogged about attaching your PostgreSQL instance to an Oracle database by using the oracle_fdw foreign data wrapper. This resulted in a comment which is the reason for this post: Doing the same with a Debian system where you can not use the rpm versions of the Oracle Instant Client (at least not directly). Lets go …

What I did to start with is to download the Debian 8 netinstall ISO and started from there with a minimal installation (see the end of this post for the screen shots of the installation if you are not sure on how to do it).

As I will compile PostgreSQL from source I’ll need to install the required packages:

root@debianpg:~ apt-get install libldap2-dev libpython-dev libreadline-dev libssl-dev bison flex libghc-zlib-dev libcrypto++-dev libxml2-dev libxslt1-dev tcl tclcl-dev bzip2 wget screen ksh git unzip

Create the directory structure for my PostgreSQL binaries and instance:

root@debianpg:~ mkdir -p /u01/app/postgres/product
root@debianpg:~ chown -R postgres:postgres /u01/app 
root@debianpg:~ mkdir -p /u02/pgdata
root@debianpg:~ mkdir -p /u03/pgdata
root@debianpg:~ chown -R postgres:postgres /u0*/pgdata 

Compile and install PostgreSQL from source:

postgres@debianpg:~$ PGHOME=/u01/app/postgres/product/95/db_4
postgres@debianpg:~$ SEGSIZE=2
postgres@debianpg:~$ BLOCKSIZE=8
postgres@debianpg:~$ WALSEGSIZE=16
postgres@debianpg:~$ wget https://ftp.postgresql.org/pub/source/v9.5.4/postgresql-9.5.4.tar.bz2
postgres@debianpg:~$ tar -axf postgresql-9.5.4.tar.bz2
postgres@debianpg:~$ cd postgresql-9.5.4/
./configure --prefix=${PGHOME} \
            --exec-prefix=${PGHOME} \
            --bindir=${PGHOME}/bin \
            --libdir=${PGHOME}/lib \
            --sysconfdir=${PGHOME}/etc \
            --includedir=${PGHOME}/include \
            --datarootdir=${PGHOME}/share \
            --datadir=${PGHOME}/share \
            --with-pgport=5432 \
            --with-perl \
            --with-python \
            --with-openssl \
            --with-ldap \
            --with-libxml \
            --with-libxslt \
            --with-segsize=${SEGSIZE} \
            --with-blocksize=${BLOCKSIZE} \
            --with-wal-segsize=${WALSEGSIZE}  \
            --with-extra-version=" dbi services build"
postgres@debianpg:~$ make world
postgres@debianpg:~$ make install
postgres@debianpg:~$ cd contrib
postgres@debianpg:~$ make install
postgres@debianpg:~$ cd ../..
postgres@debianpg:~$ rm -rf postgres*

Initialize a new cluster:

postgres@debianpg:~$ /u01/app/postgres/product/95/db_4/bin/initdb -D /u02/pgdata/PG1 -X /u02/pgdata/PG1 --locale=en_US.UTF-8
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

creating directory /u02/pgdata/PG1 ... ok
fixing permissions on existing directory /u02/pgdata/PG1 ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
creating template1 database in /u02/pgdata/PG1/base/1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects' descriptions ... ok
creating collations ... ok
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
loading PL/pgSQL server-side language ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok
syncing data to disk ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    /u01/app/postgres/product/95/db_4/bin/pg_ctl -D /u02/pgdata/PG1 -l logfile start

Adjust the logging_collector parameter and startup the instance:

postgres@debianpg:~$ sed -i 's/#logging_collector = off/logging_collector = on/g' /u02/pgdata/PG1/postgresql.conf
postgres@debianpg:~$ mkdir /u02/pgdata/PG1/pg_log
postgres@debianpg:~$ /u01/app/postgres/product/95/db_4/bin/pg_ctl start -D /u02/pgdata/PG1/
postgres@debianpg:~$ /u01/app/postgres/product/95/db_4/bin/psql
psql (9.5.4 dbi services build)
Type "help" for help.

postgres= select version();
                                                   version                                                   
-------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.5.4 dbi services build on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit
(1 row)

postgres=

Download the Oracle Instant Client zip file from here. You’ll need these:

  • instantclient-basic-linux.x64-12.1.0.2.0.zip
  • instantclient-sqlplus-linux.x64-12.1.0.2.0.zip
  • instantclient-sdk-linux.x64-12.1.0.2.0.zip

Extract to a location which fits your needs:

postgres@debianpg:~$ cd /u01/app/
postgres@debianpg:/u01/app$ unzip /home/postgres/instantclient-basic-linux.x64-12.1.0.2.0.zip
postgres@debianpg:/u01/app$ unzip /home/postgres/instantclient-sqlplus-linux.x64-12.1.0.2.0.zip
postgres@debianpg:/u01/app$ unzip /home/postgres/instantclient-sdk-linux.x64-12.1.0.2.0.zip
postgres@debianpg:/u01/app$ ls -l
total 8
drwxr-xr-x 3 postgres postgres 4096 Sep 27 12:04 instantclient_12_1
drwxr-xr-x 4 postgres postgres 4096 Sep 27 10:57 postgres

Do a connection test with sqlplus to be sure the instant client is working in general:

postgres@debianpg:/u01/app$ export ORACLE_HOME=/u01/app/instantclient_12_1
postgres@debianpg:/u01/app$ export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/u01/app/instantclient_12_1
postgres@debianpg:/u01/app$ export PATH=$PATH:$ORACLE_HOME
postgres@debianpg:/u01/app$ which sqlplus
/u01/app/instantclient_12_1/sqlplus
postgres@debianpg:/u01/app$ sqlplus sh/sh@192.168.22.242:1521/PROD
sqlplus: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory

Ups, easy to fix:

root@debianpg:~ apt-get install libaio1

Again:

postgres@debianpg:/u01/app$ sqlplus sh/sh@192.168.22.242:1521/PROD.local

SQL*Plus: Release 12.1.0.2.0 Production on Tue Sep 27 12:12:44 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Tue Sep 27 2016 12:09:05 +02:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> 

Perfect. Connections are working to the Oracle instance. Continue with the oracle_fdw setup:

postgres@debianpg:~$ wget https://github.com/laurenz/oracle_fdw/archive/master.zip
postgres@debianpg:~$ unzip master.zip 
postgres@debianpg:~$ cd oracle_fdw-master/
postgres@debianpg:~/oracle_fdw-master$ export PATH=/u01/app/postgres/product/95/db_4/bin/:$PATH
postgres@debianpg:~/oracle_fdw-master$ which pg_config 
/u01/app/postgres/product/95/db_4/bin//pg_config
postgres@debianpg:~/oracle_fdw-master$ make
...
/usr/bin/ld: cannot find -lclntsh
collect2: error: ld returned 1 exit status
/u01/app/postgres/product/95/db_4/lib/pgxs/src/makefiles/../../src/Makefile.shlib:311: recipe for target 'oracle_fdw.so' failed
make: *** [oracle_fdw.so] Error 1

This one was unexpected. After some digging this resolves the issue:

postgres@debianpg:/u01/app/instantclient_12_1$ cd /u01/app/instantclient_12_1
postgres@debianpg:/u01/app/instantclient_12_1$ ln -s libclntsh.so.12.1 libclntsh.so

Not sure if I missed something or this is a bug (you can follow the issue here).

Once the link is there you’ll be able to “make” and to “make install”. This is the result:

postgres@debianpg:~/oracle_fdw-master$ make
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fpic -I/u01/app/instantclient_12_1/sdk/include -I/u01/app/instantclient_12_1/oci/include -I/u01/app/instantclient_12_1/rdbms/public -I/usr/include/oracle/12.1/client -I/usr/include/oracle/12.1/client64 -I/usr/include/oracle/11.2/client -I/usr/include/oracle/11.2/client64 -I/usr/include/oracle/11.1/client -I/usr/include/oracle/11.1/client64 -I/usr/include/oracle/10.2.0.5/client -I/usr/include/oracle/10.2.0.5/client64 -I/usr/include/oracle/10.2.0.4/client -I/usr/include/oracle/10.2.0.4/client64 -I/usr/include/oracle/10.2.0.3/client -I/usr/include/oracle/10.2.0.3/client64 -I. -I./ -I/u01/app/postgres/product/95/db_4/include/server -I/u01/app/postgres/product/95/db_4/include/internal -D_GNU_SOURCE -I/usr/include/libxml2   -c -o oracle_fdw.o oracle_fdw.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fpic -I/u01/app/instantclient_12_1/sdk/include -I/u01/app/instantclient_12_1/oci/include -I/u01/app/instantclient_12_1/rdbms/public -I/usr/include/oracle/12.1/client -I/usr/include/oracle/12.1/client64 -I/usr/include/oracle/11.2/client -I/usr/include/oracle/11.2/client64 -I/usr/include/oracle/11.1/client -I/usr/include/oracle/11.1/client64 -I/usr/include/oracle/10.2.0.5/client -I/usr/include/oracle/10.2.0.5/client64 -I/usr/include/oracle/10.2.0.4/client -I/usr/include/oracle/10.2.0.4/client64 -I/usr/include/oracle/10.2.0.3/client -I/usr/include/oracle/10.2.0.3/client64 -I. -I./ -I/u01/app/postgres/product/95/db_4/include/server -I/u01/app/postgres/product/95/db_4/include/internal -D_GNU_SOURCE -I/usr/include/libxml2   -c -o oracle_utils.o oracle_utils.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fpic -I/u01/app/instantclient_12_1/sdk/include -I/u01/app/instantclient_12_1/oci/include -I/u01/app/instantclient_12_1/rdbms/public -I/usr/include/oracle/12.1/client -I/usr/include/oracle/12.1/client64 -I/usr/include/oracle/11.2/client -I/usr/include/oracle/11.2/client64 -I/usr/include/oracle/11.1/client -I/usr/include/oracle/11.1/client64 -I/usr/include/oracle/10.2.0.5/client -I/usr/include/oracle/10.2.0.5/client64 -I/usr/include/oracle/10.2.0.4/client -I/usr/include/oracle/10.2.0.4/client64 -I/usr/include/oracle/10.2.0.3/client -I/usr/include/oracle/10.2.0.3/client64 -I. -I./ -I/u01/app/postgres/product/95/db_4/include/server -I/u01/app/postgres/product/95/db_4/include/internal -D_GNU_SOURCE -I/usr/include/libxml2   -c -o oracle_gis.o oracle_gis.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fpic -shared -o oracle_fdw.so oracle_fdw.o oracle_utils.o oracle_gis.o -L/u01/app/postgres/product/95/db_4/lib -Wl,--as-needed -Wl,-rpath,'/u01/app/postgres/product/95/db_4/lib',--enable-new-dtags  -L/u01/app/instantclient_12_1 -L/u01/app/instantclient_12_1/bin -L/u01/app/instantclient_12_1/lib -L/u01/app/instantclient_12_1/sdk/include -lclntsh -L/usr/lib/oracle/12.1/client/lib -L/usr/lib/oracle/12.1/client64/lib -L/usr/lib/oracle/11.2/client/lib -L/usr/lib/oracle/11.2/client64/lib -L/usr/lib/oracle/11.1/client/lib -L/usr/lib/oracle/11.1/client64/lib -L/usr/lib/oracle/10.2.0.5/client/lib -L/usr/lib/oracle/10.2.0.5/client64/lib -L/usr/lib/oracle/10.2.0.4/client/lib -L/usr/lib/oracle/10.2.0.4/client64/lib -L/usr/lib/oracle/10.2.0.3/client/lib -L/usr/lib/oracle/10.2.0.3/client64/lib 
postgres@debianpg:~/oracle_fdw-master$ make install
/bin/mkdir -p '/u01/app/postgres/product/95/db_4/lib'
/bin/mkdir -p '/u01/app/postgres/product/95/db_4/share/extension'
/bin/mkdir -p '/u01/app/postgres/product/95/db_4/share/extension'
/bin/mkdir -p '/u01/app/postgres/product/95/db_4/share/doc/extension'
/usr/bin/install -c -m 755  oracle_fdw.so '/u01/app/postgres/product/95/db_4/lib/oracle_fdw.so'
/usr/bin/install -c -m 644 .//oracle_fdw.control '/u01/app/postgres/product/95/db_4/share/extension/'
/usr/bin/install -c -m 644 .//oracle_fdw--1.1.sql .//oracle_fdw--1.0--1.1.sql  '/u01/app/postgres/product/95/db_4/share/extension/'
/usr/bin/install -c -m 644 .//README.oracle_fdw '/u01/app/postgres/product/95/db_4/share/doc/extension/'

Remember that the PostgreSQL instance needs to find the Oracle libraries, so set the environment before restarting PostgreSQL:

postgres@debianpg:~$ echo $ORACLE_HOME
/u01/app/instantclient_12_1
postgres@debianpg:~$ echo $LD_LIBRARY_PATH
:/u01/app/instantclient_12_1:/u01/app/instantclient_12_1/sdk/include/
postgres@debianpg:~$ pg_ctl -D /u02/pgdata/PG1/ restart -m fast
postgres@debianpg:~$ psql
psql (9.5.4 dbi services build)
Type "help" for help.

postgres= create extension oracle_fdw;
CREATE EXTENSION
postgres= \dx
                        List of installed extensions
    Name    | Version |   Schema   |              Description               
------------+---------+------------+----------------------------------------
 oracle_fdw | 1.1     | public     | foreign data wrapper for Oracle access
 plpgsql    | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

All fine. Lets get the foreign data:

postgres= create schema oracle;
CREATE SCHEMA
postgres= create server oracle foreign data wrapper oracle_fdw options (dbserver '//192.168.22.242/PROD.local' );
CREATE SERVER
postgres= create user mapping for postgres server oracle options (user 'sh', password 'sh');
CREATE USER MAPPING
postgres= import foreign schema "SH" from server oracle into oracle;
IMPORT FOREIGN SCHEMA
postgres= set search_path='oracle';
SET
postgres= \d
                       List of relations
 Schema |            Name            |     Type      |  Owner   
--------+----------------------------+---------------+----------
 oracle | cal_month_sales_mv         | foreign table | postgres
 oracle | channels                   | foreign table | postgres
 oracle | costs                      | foreign table | postgres
 oracle | countries                  | foreign table | postgres
 oracle | currency                   | foreign table | postgres
 oracle | customers                  | foreign table | postgres
 oracle | dimension_exceptions       | foreign table | postgres
 oracle | fweek_pscat_sales_mv       | foreign table | postgres
 oracle | products                   | foreign table | postgres
 oracle | profits                    | foreign table | postgres
 oracle | promotions                 | foreign table | postgres
 oracle | sales                      | foreign table | postgres
 oracle | sales_transactions_ext     | foreign table | postgres
 oracle | supplementary_demographics | foreign table | postgres
 oracle | times                      | foreign table | postgres
(15 rows)

postgres= select count(*) from countries;
 count 
-------
    23
(1 row)

Perfect, works. Hope this helps.

Debian 8 installation screen shots:

Selection_014
Selection_015
Selection_016
Selection_017
Selection_018
Selection_020
Selection_021
Selection_022
Selection_023
Selection_024
Selection_025
Selection_026
Selection_027
Selection_028
Selection_029
Selection_030
Selection_031
Selection_032
Selection_033
Selection_034
Selection_036
Selection_037
Selection_038
Selection_039
Selection_040
Selection_041
Selection_042
Selection_043
Selection_044
Selection_045
Selection_046
Selection_047
Selection_048

 

Cet article Connecting your PostgreSQL instance to an Oracle database – Debian version est apparu en premier sur Blog dbi services.

When an index seek operator is not always your friend

Mon, 2016-09-26 12:40

Did you ever consider an index seek as an issue? . Well, let’s talk about a story with one of my customers where the context is pretty simple: a particular query that is out of the range of the application’s performance requirements (roughly 200ms of execution time in average). The execution plan of the query was similar to what you may see in the picture below:

 

blog 104 - 1 - query execution plan

At first glance, no obvious way to improve the performance of the above query right? The query was similar to the following one (with some exceptions with the real context but it does not matter in our case):

DECLARE @id VARCHAR(10) = 'LPKL';

SELECT 
	[Num_DUT],
	[Etat],
	[Description],
	Etape_Process
FROM 
	[dbo].[evenements]
WHERE 
	actif IS NULL 
	AND [date] >= '20160101'
	AND SUBSTRING(DM_Param, 12, 4) = @id

 

Here  the definition of the dbo.evenements table.

create table dbo.evenements
(
	[date] datetime,
	Actif BIT NULL,
	Etape_Process VARCHAR(50),
	DM_Param VARCHAR(50),
	Num_DUT INT,
	[Etat] VARCHAR(10),
	[Description] VARCHAR(50)
)

 

Let’s set quickly the context. This table is constantly filled up by information from different sensors. The question that came in my mind at this moment was why an index seek is used here regarding the query predicate? After all, we may noticed a parallel execution plan (cost threshold for parallelism is by defaut) that leads to ask questions about the index seek’s behavior. The index used in this query was as follows:

CREATE NONCLUSTERED INDEX [idx_dbi_Evenements_actif] ON [dbo].[evenements]
(
	[Actif] ASC,
	[date] ASC,
	[Etape_Process] ASC,
	[DM_Param] ASC
)
INCLUDE ( 	[Num_DUT],
	[Etat],
	[Description]) 
WHERE [date] >= '20160101'

Regarding the index definition, if we take a look closely at the WHERE clause of the query, we may assume that using a seek operator in the execution is a little bit tricky.

blog 104 - 2 - seek operator info

Indeed, predicting the selectivity of the SUBTRING part of the clause presents a big challenge for SQL Server in this case. The cardinality estimation seems to be wrong event after updating the dbo.evenements statistics.

blog 104 - 3 - cardinality estimate issue

So the next question that came in mind concerned the index seek operation itself. Is it really an index seek? I based my assumption upon the use of parallelism in the execution plan. Why using parallelism if I just have to get 2100 rows which represent only 0.2 % of all the data in my case? I remembered an old article written by Paul White in 2011 with the title When is a Seek not a Seek?

So referring to this article I moved directly on the output of the SET STATISTICS IO

Table ‘evenements’. Scan count 5, logical reads 10590, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Well, the situation is more obvious here if we take a look at the number of pages included to the nonclustered index.

blog 104 - 5 - index pages

In fact, each seek is a partial scan to search the corresponding values that satisfy SUBSTRING(DM_Param, 12, 4) predicate at the leaf level. Why 5 scans here? Well my assumption here is the SQL Engine is using 4 threads in parallel to perform a partial scan as shown below:

blog 104 - 51 - parallel threads

Let’s do some math here. Each thread fetches approximatively 2600 pages regardless all the details, so we are not so far from the total logical reads displayed by the SET STATISTISC IO previously (4 x 2600 = 10400).

That said, we identified the cause of the high query cost but we did not respond to the main question: Are we able to reduce it? Well, to answer the question, let’s come back to the query semantic. At this point, we get stuck by the current predicate. After some further discussions with the customer and the data semantic of the table dbo.evenements, we identified that the DM_Param column doesn’t meet to the first normal form because it does not contains atomic values (the sensor identifier + some additional parameters) making it non sargable.

Moreover, to introduce another difficultly the SUBSTRING() function arguments were purely dynamic in this and must be adjusted to extract the correct identifier value regarding the line record. Finally we decided to update the initial scheme to meet the normal form rules. Thus, we introduced an additional table to store sensors identifiers and we used a non-semantic primary key to join the dbo.evenements table as well. Of course the NUM_DT column values should be updated accordingly to the new scheme.

Better for performance? Let’s have a look at the new execution plan

blog 104 - 7 - new execution plan

The execution cost dropped under the threshold of parallelism value in such way that we were able to use a serializable plan.

What about IO statistics?

Table ‘evenements’. Scan count 1, logical reads 28, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table ‘identifier’. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

The above output is meaningful by itself. In the context of my customer we successfully reduced the execution time from 200 – 300s to 2-3ms on average. Of course a big improvement at the price of some changes from the application side that concern insert / update / delete operations. But once again we concluded after prototyping of new insert / update / delete operations that they were not really impacted in terms of performance.

Happy performance troubleshooting!

 

 

 

Cet article When an index seek operator is not always your friend est apparu en premier sur Blog dbi services.

Oracle 12cR2: IS_ROLLING_INVALID in V$SQL

Sun, 2016-09-25 11:51

In a previous post I published a test case to show when a cursor is not shared anymore after a rolling invalidation. Basically the dbms_stats marks the cursor as ‘rolling invalid’ and the next execution marks it as ‘rolling invalid executed’. Looking at 12cR2 there is a little enhancement in V$SQL with an additional column displays those states.

Note that 12cR2 full documentation is not yet available, but you can test this on the Exadata Express Cloud Service.

I set the invalidation period to 5 seconds instead of 5 hours to show the behavior without waiting

17:43:52 SQL> alter system set "_optimizer_invalidation_period"=5;
System altered.

I’ll run a statement with dbms_sql in order to separate parse and execute phases

17:43:53 SQL> variable c number
17:43:53 SQL> exec :c := dbms_sql.open_cursor;
PL/SQL procedure successfully completed.
17:43:53 SQL> exec dbms_sql.parse(:c, 'select (cast(sys_extract_utc(current_timestamp) as date)-date''1970-01-01'')*24*3600 from DEMO' , dbms_sql.native );
PL/SQL procedure successfully completed.

Here is the cursor from V$SQL including the new IS_ROLLING_INVALID column:

17:43:53 SQL> select invalidations,loads,parse_calls,executions,first_load_time,last_load_time,last_active_time,is_rolling_invalid from v$sql where sql_id='61x2h0y9zv0r6';
 
INVALIDATIONS LOADS PARSE_CALLS EXECUTIONS FIRST_LOAD_TIME LAST_LOAD_TIME LAST_ACTIVE_TIME IS_ROLLING_INVALID
------------- ---------- ----------- ---------- ------------------- ------------------- ------------------ ------------------
0 1 1 0 2016-09-25/17:43:53 2016-09-25/17:43:53 25-SEP-16 17:43:52 N

Statement is parsed (one parse call + load) but IS_ROLLING_INVALID is N

Now I execute it:

17:43:53 SQL> exec dbms_output.put_line( dbms_sql.execute(:c) );
0
PL/SQL procedure successfully completed.
 
17:43:53 SQL> select invalidations,loads,parse_calls,executions,first_load_time,last_load_time,last_active_time,is_rolling_invalid from v$sql where sql_id='61x2h0y9zv0r6';
 
INVALIDATIONS LOADS PARSE_CALLS EXECUTIONS FIRST_LOAD_TIME LAST_LOAD_TIME LAST_ACTIVE_TIME IS_ROLLING_INVALID
------------- ---------- ----------- ---------- ------------------- ------------------- ------------------ ------------------
0 1 1 1 2016-09-25/17:43:53 2016-09-25/17:43:53 25-SEP-16 17:43:52 N

Statement has one execution.

I’m now gathering statistics with default rolling invalidation:

17:43:53 SQL> exec dbms_stats.gather_table_stats(user,'DEMO');
PL/SQL procedure successfully completed.
 
17:43:53 SQL> select invalidations,loads,parse_calls,executions,first_load_time,last_load_time,last_active_time,is_rolling_invalid from v$sql where sql_id='61x2h0y9zv0r6';
 
INVALIDATIONS LOADS PARSE_CALLS EXECUTIONS FIRST_LOAD_TIME LAST_LOAD_TIME LAST_ACTIVE_TIME IS_ROLLING_INVALID
------------- ---------- ----------- ---------- ------------------- ------------------- ------------------ ------------------
0 1 1 1 2016-09-25/17:43:53 2016-09-25/17:43:53 25-SEP-16 17:43:52 Y

The cursor is now marked as rolling invalid (IS_ROLLING_INVALID=”Y”) but wait, this is not a “Y”/”N” boolean, there’s another possible value.

I execute the statement again (no parse call, only execution):

17:43:53 SQL> exec dbms_output.put_line( dbms_sql.execute(:c) );
0
PL/SQL procedure successfully completed.
 
17:43:53 SQL> select invalidations,loads,parse_calls,executions,first_load_time,last_load_time,last_active_time,is_rolling_invalid from v$sql where sql_id='61x2h0y9zv0r6';
 
INVALIDATIONS LOADS PARSE_CALLS EXECUTIONS FIRST_LOAD_TIME LAST_LOAD_TIME LAST_ACTIVE_TIME IS_ROLLING_INVALID
------------- ---------- ----------- ---------- ------------------- ------------------- ------------------ ------------------
0 1 1 2 2016-09-25/17:43:53 2016-09-25/17:43:53 25-SEP-16 17:43:52 X

Cursor is now marked as rolling invalid executed (“X”) and this is where the rolling window starts (which I’ve set to 5 seconds instead of 5 hours)

I wait 5 seconds and the cursor has not changed:

17:43:58 SQL> select invalidations,loads,parse_calls,executions,first_load_time,last_load_time,last_active_time,is_rolling_invalid from v$sql where sql_id='61x2h0y9zv0r6';
 
INVALIDATIONS LOADS PARSE_CALLS EXECUTIONS FIRST_LOAD_TIME LAST_LOAD_TIME LAST_ACTIVE_TIME IS_ROLLING_INVALID
------------- ---------- ----------- ---------- ------------------- ------------------- ------------------ ------------------
0 1 1 2 2016-09-25/17:43:53 2016-09-25/17:43:53 25-SEP-16 17:43:52 X
 

I execute it again (no parse call, only re-execute the cursor):

17:43:58 SQL> exec dbms_output.put_line( dbms_sql.execute(:c) );
0
PL/SQL procedure successfully completed.

For this execution, a new child has been created:

17:43:58 SQL> select invalidations,loads,parse_calls,executions,first_load_time,last_load_time,last_active_time,is_rolling_invalid from v$sql where sql_id='61x2h0y9zv0r6';
 
INVALIDATIONS LOADS PARSE_CALLS EXECUTIONS FIRST_LOAD_TIME LAST_LOAD_TIME LAST_ACTIVE_TIME IS_ROLLING_INVALID
------------- ---------- ----------- ---------- ------------------- ------------------- ------------------ ------------------
0 1 1 2 2016-09-25/17:43:53 2016-09-25/17:43:53 25-SEP-16 17:43:52 X
0 1 0 1 2016-09-25/17:43:53 2016-09-25/17:43:57 25-SEP-16 17:43:57 N

So rolling invalidation do not require a parse call. Execution can start the rolling window and set the invalidation timestamp, and first execution after this timestamp creates a new child cursor.

I’ll now test what happens with parse calls only.

I set a longer rolling window (2 minutes) here:

17:43:58 SQL> exec dbms_stats.gather_table_stats(user,'DEMO');
PL/SQL procedure successfully completed.
 
17:43:58 SQL> alter system set "_optimizer_invalidation_period"=120;
System altered.

The last child has been marked as rolling invalid but not yet executed in this state:

17:43:58 SQL> select invalidations,loads,parse_calls,executions,first_load_time,last_load_time,last_active_time,is_rolling_invalid from v$sql where sql_id='61x2h0y9zv0r6';
 
INVALIDATIONS LOADS PARSE_CALLS EXECUTIONS FIRST_LOAD_TIME LAST_LOAD_TIME LAST_ACTIVE_TIME IS_ROLLING_INVALID
------------- ---------- ----------- ---------- ------------------- ------------------- ------------------ ------------------
0 1 1 2 2016-09-25/17:43:53 2016-09-25/17:43:53 25-SEP-16 17:43:52 X
0 1 0 1 2016-09-25/17:43:53 2016-09-25/17:43:57 25-SEP-16 17:43:57 Y

From a new session I open another cursor:

17:43:58 SQL> connect &_user./demo@&_connect_identifier
Connected.
17:43:58 SQL> exec :c := dbms_sql.open_cursor;
PL/SQL procedure successfully completed.

And run several parse calls without execute, one every 10 seconds:

17:43:58 SQL> exec for i in 1..12 loop dbms_sql.parse(:c, 'select (cast(sys_extract_utc(current_timestamp) as date)-date''1970-01-01'')*24*3600 from DEMO' , dbms_sql.native ); dbms_lock.sleep(10); end loop;
PL/SQL procedure successfully completed.

So two minutes later I see that I have a new child created during the rolling window:

17:45:58 SQL> select invalidations,loads,parse_calls,executions,first_load_time,last_load_time,last_active_time,is_rolling_invalid from v$sql where sql_id='61x2h0y9zv0r6';
 
INVALIDATIONS LOADS PARSE_CALLS EXECUTIONS FIRST_LOAD_TIME LAST_LOAD_TIME LAST_ACTI IS_ROLLING_INVALID
------------- ---------- ----------- ---------- ------------------- ------------------- --------- ------------------
0 1 1 2 2016-09-25/17:43:53 2016-09-25/17:43:53 25-SEP-16 X
0 1 3 1 2016-09-25/17:43:53 2016-09-25/17:43:57 25-SEP-16 Y
0 1 9 0 2016-09-25/17:43:53 2016-09-25/17:44:27 25-SEP-16 N

Here, at the third parse call (17:44:27) during the invalidation window, a new child cursor has been created. The old one is still marked as rolling invalid (“Y”), but not ‘rolling invalid executed’ (“X”) because it has not been executed.

So it seems that both parse or execute are triggering the rolling invalidation, and the IS_ROLLING_INVALID displays which one.

An execute will now execute the new cursor:

17:45:58 SQL> exec dbms_output.put_line( dbms_sql.execute(:c) );
 
PL/SQL procedure successfully completed.
 
17:45:58 SQL> select invalidations,loads,parse_calls,executions,first_load_time,last_load_time,last_active_time,is_rolling_invalid from v$sql where sql_id='61x2h0y9zv0r6';
 
INVALIDATIONS LOADS PARSE_CALLS EXECUTIONS FIRST_LOAD_TIME LAST_LOAD_TIME LAST_ACTI IS_ROLLING_INVALID
------------- ---------- ----------- ---------- ------------------- ------------------- --------- ------------------
0 1 1 2 2016-09-25/17:43:53 2016-09-25/17:43:53 25-SEP-16 X
0 1 3 1 2016-09-25/17:43:53 2016-09-25/17:43:57 25-SEP-16 Y
0 1 9 1 2016-09-25/17:43:53 2016-09-25/17:44:27 25-SEP-16 N

Of course, when new cursors have been created we can see the reason in V$SQL_SHARED_CURSOR:

17:45:58 SQL> select child_number,reason from v$sql_shared_cursor where sql_id='61x2h0y9zv0r6';
 
CHILD_NUMBER REASON
------------ --------------------------------------------------------------------------------
0 <ChildNode><ChildNumber>0</ChildNumber><ID>33</ID><reason>Rolling Invalidate Win
dow Exceeded(2)</reason><size>0x0</size><details>already_processed</details></Ch
ildNode><ChildNode><ChildNumber>0</ChildNumber><ID>33</ID><reason>Rolling Invali
date Window Exceeded(3)</reason><size>2x4</size><invalidation_window>1472658232<
/invalidation_window><ksugctm>1472658237</ksugctm></ChildNode>
 
1 <ChildNode><ChildNumber>1</ChildNumber><ID>33</ID><reason>Rolling Invalidate Win
dow Exceeded(2)</reason><size>0x0</size><details>already_processed</details></Ch
ildNode><ChildNode><ChildNumber>1</ChildNumber><ID>33</ID><reason>Rolling Invali
date Window Exceeded(3)</reason><size>2x4</size><invalidation_window>1472658266<
/invalidation_window><ksugctm>1472658268</ksugctm></ChildNode>
 
2

The last child cursor has been created at 5:44:28 (invalidation_window=1472658268) because invalidation timestamp (ksugctm=1472658266)

So what?

We love Oracle because it’s not a black box. And it’s good to see that they continue in this way by exposing in V$ views information that can be helpful for troubleshooting.

Rolling invalidation has been introduced for dbms_stats because we have to gather statistics and we don’t want hard parse storms after that.
But remember that invalidation can also occur with DDL such as create, alter, drop, comment, grant, revoke.

You should avoid running DDL when application is running. However, we may have to do some of those operations online. It would be nice to have the same rolling invalidation mechanisms and it seems that it will be possible:


SQL> show parameter invalid
 
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_invalidation string IMMEDIATE
 
SQL> alter session set cursor_invalidation=XXX;
ERROR:
ORA-00096: invalid value XXX for parameter cursor_invalidation, must be from among IMMEDIATE, DEFERRED

That’s interesting. I’ll explain which DDL can use that in a future blog post.

 

Cet article Oracle 12cR2: IS_ROLLING_INVALID in V$SQL est apparu en premier sur Blog dbi services.

Modern software architecture – what is a database?

Sat, 2016-09-24 17:03

This blog post is focused at developers and software architects. I’m probably not writing at the right place. You’re on an infrastructure experts blog and the author is an Oracle DBA. So what can you learn from someone working on that 30 years old technology talking about that old SQL language ? You run with modern languages, powerful frameworks, multi-layer architecture, micro-services, distributed database and of course all open-source. You hate your DBA because he is the major slow-down for your agile development. You don’t want SQL. You don’t want databases. You don’t want DBA.

How can I encourage you to read this blog post? I was not always an DBA. I started as a developer, more than 20 years ago. And believe me, it was not prehistory at all. Object-Oriented design, Rapid Application Development, Automatic programming (remember C.A.S.E.?), visual programming (have you ever seen an IDE like IBM Visual Age?), query generation (early days of Business-Objects). All these evolved with more and more languages, frameworks, layers, micro-services, XML, SOA, JSON, REST,… but only one technology remained: the critial persistent data is still in a relational database and accessed by SQL.

What is a database

Most of developers think that a database is there to store and retrieve data. I’m sorry but that’s wrong. That may have been right a long time ago, with key-value storage and hierarchical databases, but that’s too old for me. When I started to work, databases were already doing far more than that. Let me explain. With those prehistoric databases, you retrieved data in the same way you stored it. You insert with a key, you fetch with that key. It is easy to explain to modern developers because they “invented” it few years ago, calling it CRUD (Create Read Update Delete). First argument of those CRUD methods is a key value. And you store unformatted data as XML or JSON associated to that value. If this is the only feature that you need, then for sure you don’t want a database.

Relational database management systems (RDBMS) are doing a lot more than that. First, you can query data in a completely different way than you inserted it. And this is the real life-cycle of data. For example, You take orders, one by one, with customer and product information for each of them. Of course you can update and read it with the order ID that has been generated, but that’s only a small use case and probably not the most critical. Warehouse users will query (and update) orders by product. Delivery users will query (and update) orders by customer. Marketing users will query by channels and many other dimensions. Finance will join with accounting. With a persistence only system, you have to code all that. Of course if you declared the mapping of associations, you can navigate through them. But the user requirement is to get a set of orders, or a quantity of products in stock, or a subset of customers, which is different from navigating through orders one by one. With a database, you dont need to code anything. With a proper data model what you inserted can be manipulated without its key value. All data that you have inserted can be accessed from any different point of view. And you don’t have to code anything for that. Imagine a Data Access Object with ‘QueryBy methods covering any combination of columns and operators.

A database system does not only store data, it processes data and provide a service to manipulate data.

SQL

SQL is not a language to code how to get the information. SQL only describes what you want. It’s a question you ask to a data service. Same idea as Uber where you enter your destination and desired car and the service manages everything for you: the path, the communication, the paiement, the security. You may not like the SQL syntax, but it can be generated. I’m not talking about generating CRUD statements here, but generating SQL syntax from a SQL semantic expressed in Java or example. There’s a very good example for that: jOOQ (look at the exemples there).

I understand that you can hate SQL for it’s syntax. SQL was build for pre-compilers, not for execution time parsing of text, and I’ll come back on that later with static SQL. But you can’t say that SQL semantic is not modern. It’s a 4th generation language that saves all the procedural coding you have to do with 3rd generation languages. SQL is a declarative language build on a mathematics theory. It goes far beyond the for() loops and if()else.

In SQL you describe the result that you want. How to retrieve the data is done by the database system. The optimizer builds the procedural code (know as the execution plan) and the execution engine takes care of everything (concurrency, maintaining redundant structures for performance, caching, multithreading, monitoring, debugging, etc). Do you really want to code all that or do you prefer to rely on a data service that does everything for you?

You know why developers don’t like SQL? Because SQL has not been designed for programmers. It was for users. The goal was that a non-programmer can ask its question to the system (such as “give me the country of the top customers having bought a specific product in last 3 months”) without the need of a developer. There was no GUI at that time, only Command Line Interface, and SQL was the User Friendly Interface to the database. Today we have GUIs and we don’t need SQL. But it is there so programmers build tools or framework to generate SQL from a programming language. Sure it is ridiculous and it would be better to have a programming language that directly calls the SQL semantic without generating plain old English text. We need a Structured Query Language (SQL) we just don’t need it to be in English.

Set vs loops

So why do people prefer to code everything in procedural language (3GL)? Because this is only what they learned. If at school you learned only loops and comparisons, then you are going to access data in loops. If you learned to think about data as sets, then you don’t need loops. Unfortunately, the set concepts are teached in mathematics classes but not in IT.

Imagine you have to print “Hello World” 5 times. Which pseudo-code so you prefer?


print("Hello World\n")
print("Hello World\n")
print("Hello World\n")
print("Hello World\n")
print("Hello World\n")

or


print ( "Hello World\n" + "Hello World\n" + "Hello World\n" + "Hello World\n" + "Hello World\n" )

I’ve put that in pseudo-code. I don’t want to play with String and StringBuffer here. But the idea is only to explain that if you have to process a set of things it is more efficient to process them as a set rather than one-by-one. That works for everything. And this is where databases rocks: they process sets of rows. If you have to increment the column N by one in every row of your table T, you don’t need to start a loop and increment the column row-by-row. Just ask your RDBMS data service to do it: ‘/* PLEASE */ UPDATE T set N=N+1′. The “please” is in comment because everything that is not there to describe the result is not part of SQL. You have to use hints to force the way to do it, but they are written as comments because SQL do not allow any way to tell how to do it. This was a joke of course, the “please” is not mandatory because we are talking to a machine.

ACID

I’m not sure you get all the magic that is behind:

UPDATE T set N=N+1;

it’s not a simple loop as:

for each row in T
set N=N+1

The RDBMS does more than that. Imagine that there is a unique index on the column N. How many lines of code do you need to do that N=N+1 row by row and be sure that at any point you don’t have duplicates? Imagine that after updating half of the rows you encounter someone else currently updating the same row. You have to wait for his commit. But then, if he updated the value of N, do you increment the past value or the new one? You can’t increment the old one or his modification will be lost. But if you increment the new one, your previous incremented rows are inconsistent because they were based on a previous state of data.
I was talking about having an index. You have to maintain this index as well. You have to be sure that what is in cache is consistent with what is in disk. That modifications made in the cache will not be lost in case of server failure. And if you run in a cluster, those caches must be synchronized.

Coding the same as this “UPDATE T set N=N+1″ in a procedural language is not easy and can become very complex in a multi-user environment.

Maybe you have all the tools you need to generate that code. But if you code it you have to test it. Are your tests covering all concurrency cases (sessions updating or reading same rows, or different rows from same table,…). What is already coded within the database has already been tested. It’s a service and you just have to use it.

Static SQL

I claimed above that SQL is there to be pre-compiled. Yes, SQL is witten in plain text, like most of programming languages, and must be parsed, optimized, compiled. It’s not only for performance. The main reason is that you prefer to get errors at compile time than at runtime. If you put SQL in text strings in your code it will remain text until execution time when it will be prepared. And only then you will get errors. The second reason is that when the SQL is parsed, it is easy to find the dependencies. Want to see all SQL statements touching to a specific column? Do you prefer to do guess on some text search or to methodically follow dependencies?

Yes, SQL is there to be static and not dynamic. That claim may look strange for an Oracle DBA because all statements are dynamic in Oracle. Even at the time of precompilers (such as Pro*C) the statements were parsed but were put as text in the binary. And at first execution, they are parsed again and optimized. If you want the execution plan to be defined at deployment time, you have to use Outlines or SQL Plan Baselines. There is no direct way to bind the execution plan at deployment time in Oracle. In my opinion the static SQL as it is known on DB2 for example is really missing in Oracle. OLTP Software Vendors would love to ship the optimized execution plans with their application. Imagine that all SQL statements in an OLTP application are parsed and optimized, compiled as bound procedures, similar to stored procedures, with procedural access (the execution plan) and you just have to call them. For reporting, DSS, BI you need the plans to adapt to the values and volume of data, but for OLTP you need stability. And from the application, you just call those static SQL like a data service.

Talking about procedural execution stored in the database, I’m coming to stored procedures and PL/SQL of course.

Stored Procedures

When you code in your 3GL language, do you have functions that update global variables (BASIC is the first language I learned and this was the idea) or do you define classes which encapsulate the function and the data definition? The revolution of Object Oriented concepts was to put data and logic at the same place. It’s better for code maintainability with direct dependency procedural code and data structures. It’s better for security because data is accessible only through provided methods. And it’s better for performance because procedural code access data at the same place.

Yes Object Oriented design rocks and this why you need to put business logic in the database. Putting the data on one component and running the code on another component of an information system is the worst you can do. Exactly as if in your Object Oriented application you store the object attributes on one node and run the methods on another one. And this is exactly what you do with the business logic outside of the database. Your DAO objects do not hold the data. The database does. Your objects can hold only a copy of the data, but the master copy where are managed concurrency management, high availability and persistance is in the database.

We will talk about the language later, this is only about the fact that the procedural code run in the same machine and the same processes than the data access.
There are a lot of myths about running business logic in the database. Most of them come from ignorance. Until last Monday I believed that one argument against running business logic in the database was unbeatable: You pay Oracle licences on the number of CPU, so you don’t want to use the database CPUs to run something that can run on a free server. I agreed with that without testing it, and this is where myths come from.

But Toon Koppelaars has tested it and he proved that you use more database CPU when you put the business logic outside of the database. I hope his presentation from Oak Table World 2016 will be available soon. He proved that by analyzing exactly what is running in the database, using linux perf and flame graphs: https://twitter.com/ChrisAntognini/status/778273744242352128

All those rountrips from remote compute server, all those row-by-row processing coming from that design have an higher footprint on the database CPUs that directly running the same on the database server.

PL/SQL

Running business logic on the database server can be done with any language. You can create stored procedures in Java. You can code external procedures in C. But those languages have not been designed for data manipulation. It is sufficient to call SQL statements but not when you need procedural data access. PL/SQL is a language made for data processing. It’s not only for stored procedure. But it’s the only language that is coupled with your data structure. As I said above, it’s better to think in sets with SQL. But it may be sometimes complex. With PL/SQL you have a procedural language which is intermediate between row-by-row and sets because it has some bulk processing capabilities.

In pseudo-code the Hello World above is something like that:


forall s in ["Hello World\n","Hello World\n","Hello World\n","Hello World\n","Hello World\n"] print(s)

It looks like a loop but it is not. The whole array is passed to the print() function and loop is done at lower level.

In PL/SQL you can also use pipeline functions where rows are processed with a procedural language but as a data stream (like SQL does) rather than loops and calls.

I’ll go to other advantages of PL/SQL stored procedures but here again there is one reason frequently raised to refuse PL/SQL. You can find more developers in .Net or Java than in PL/SQL. And because they are rare, they are more expensive. But there is a counter argument I heard this week at Oracle Open World (but I don’t remember who raised that point unfortunately). PL/SQL is easy to learn. Really easy. You have begin – exception – end blocks, you declare all variables, you can be modular with procedures and inline procedures, you separate signature and body, you have very good IDE, excellent debugger and easy profiler,… and you can run it on Oracle XE which is free. So, if you have a good Java developer he can write efficient PL/SQL in a few days. By good developer, I mean someone who understands multi-user concurrency problems, bulk processing, multi-threading, etc.

There are less PL/SQL developers than Java developers because you don’t use PL/SQL. It’s not the opposite. If you use PL/SQL you will find developers and there are many software vendors that code their application in PL/SQL. Of course PL/SQL is not free (except in Oracle XE) but it runs on all platforms and on all editions.

Continuous Integration and Deployment, dependency and versioning

I come back quickly to the advantages of using a language that is coupled with your data.

PL/SQL stored procedures are compiled and all dependencies are stored. With one query on DBA_DEPENDENCIES you can know which tables your procedure is using and which procedures use a specific table. If you change the data model, the procedures that have to be changed are immediately invalidated. I don’t know any other language that does that. You don’t want to break the continuous integration build every time you change something in a table structure? Then go to PL/SQL.

Let’s go beyond continuous integration. How do you manage database changes in continuous deployment? Do you know that with PL/SQL you can modify your data model online, with your application running and without breaking anything? I said above that procedures impacted by the change are invalidated and the must be adapted to be able to be compiled. But this is only for the new version. You can deploy a new version of those procedures while the previous version is running. You can test this new version and only when everything is ok you switch the application to the new version. The feature is called Edition Based Redefinition (EBR) it exists since 11g in all Oracle editions. It’s not known and used enough, but all people I know that use it are very happy with it.

In development environment and continuous integration, it is common to say that the database always cause problem. Yes it is true but it’s not inherent to the database but the data. Data is shared and durable and this is what makes it complex. The code can be deployed in different places, and can be re-deployed if lost. Data can be updated at only one place and visible to all users. Upgrading to a new version of application is easy: you stop the old version and start the new version. For data it is different: you cannot start from scratch and you must keep and upgrade the previous data.

Object-Relational impedance

I’m probably going too far in this blog post but the fact that data is shared and durable is the main reason why we cannot apply same concepts to data objects (business objects) and presentation objects (GUI components). Application objects are transient. When you restart the application, you create other objects. The identity of those objects is an address in memory: it’s different on other systems and it’s different once application is restarted. Business objects are different. When you manipulate a business entity, it must have the same identity for any users, and this identity do not change when application is restarted, not even when application is upgraded. All other points given as “object-relational impedance” are minor. But the sharing and durability of business object identity is the reason why you have to think differently.

Where to put business logic?

If you’re still there, you’ve probably understood that it makes sense to run the data logic in the database, with declarative SQL or procedural PL/SQL stored procedures, working in sets or at least in bulk, and with static SQL as much as possible, and versioned by EBR.

Where to put business logic then? Well, business logic is data logic for most of it. But you’ve always learned that business logic must be in the application tier. Rather than taking reasons given one by one and explain what’s wrong with them, let me tell you how came this idea of business logic outside of the database. The idea came from my generation: the awesome idea of client/server.

At first, data was processed on the servers and only the presentation layer was on the user side (for example ISAM was very similar with what we do with thin web pages). And this worked very well, but it was only green text terminals. Then came PCs and Windows 3.11 and we wanted graphical applications. So we built applications on our PCs. But that was so easy that we implemented all business logic there. Not because it’s a better architecture, but because anyone can build his application without asking to the sysops. This was heaven for developers and a nightmare for operations to deploy those applications on all the enterprise PCs.
But this is where offloading business logic started. Application written with nice IDEs (I did this with Borland Paradox and Delphi) connecting directly to the database with SQL. Because application was de-correlated from the database everything was possible. We even wanted to have applications agnostic of the database, running in any RDBMS. Using standard SQL and standard ODBC. Even better: full flexibility for the developer by using only one table with Entity-Value-Attribute.

Actually, the worst design anti-patterns have been invented at that time and we still see them in current applications – totally unscalable.

When finally the deployment of those client/server applications became a nightmare, and because internet was coming with http, html, java, etc. we went to 3-tier design. Unfortunately, the business logic remained offloaded in the application server instead of being part again of the database server.

I mentioned ODBC and it was another contributor to that confusion. ODBC looks like a logical separation of the application layer and the database layer. But that’s wrong. ODBC is not a protocol. ODBC is an API. ODBC do not offer a service: it is a driver running on both layers and that magically communicates through network: code to process data on one side and data begin on the other.

A real data service encapsulates many SQL statements and some procedural code. And it is exactly the purpose of stored procedures. This is how all data applications were designed before that client/server orgy and this is how they should be designed today when we focus on centralization and as micro-services applications.

So what?

This blog post is already too long. It comes from 20 years experience as developer, application DBA, and operation DBA. I decided to write this when coming back from the Oracle Open World where several people are still advocating for the right design, especially Toon Koppelaars about Thick Database at Oak Table World and the amazing panel about “Thinking clearly about application architecture” with Toon Koppelaars, Bryn Llewellyn, Gerald Venzl, Cary Millsap, Connor McDonald

The dream of every software architect should be to attend that panel w/ @ToonKoppelaars @BrynLite @GeraldVenzl @CaryMillsap @connor_mc_d pic.twitter.com/npLzpnktMK

— Franck Pachot (@FranckPachot) September 22, 2016

Beside the marketing stuff, I was really impressed by the technical content around the Oracle Database this year at OOW16.

 

Cet article Modern software architecture – what is a database? est apparu en premier sur Blog dbi services.

Oracle Open World 2016 – Day 4 and 5

Fri, 2016-09-23 03:01

At the end of Oracle Open World my last BLOG concerning OOW 2016 covering day 4 and 5:

Wednesday is the day of the Party: Oracle’s appreciation event, a concert with Gwen Stefani and Sting at the AT&T Park (Stadium of the San Francisco Baseball team, the Giants). It was a great event with awesome musicians.

Before the party I visited the session “Oracle Active Data Guard: Power, Speed, Ease and Protection” provided by Larry M. Carpenter, the grandfather of Data Guard. Here a couple of nice new features of (Active) Data Guard in 12gR2:

  • Multi-Instance Redo Apply in RAC: Up until now the managed recovery processes (MRP) could only run on a single node on the standby RAC site and hence limited redo apply to the CPU and IO power of that one node. In the new release a coordinator process can distribute the redo data to MPR-processes on all nodes of the RAC cluster. This is called Multi-Instance Apply and is configured as follows:
    Without broker: recover managed standby database disconnect using instances 4;
    With broker: Through the ‘ApplyInstances’ property.
    Caveats in the first 12gR2 release:
    Using Multi-Instance Redo Apply disallows the use of the new feature In-Memory Column Store on Active Data Guard.
    RMAN block change tracking file is disabled.

 

  • Data Guard Broker enhancement for Multitenant: As Redo is generated at Container (CDB) level, switchover and failover will also happen for the whole CDB. In 12gR2 there is a new command when using the Data Guard Broker to migrate or failover a Pluggable DB (PDB) to another CDB on the same server:

    MIGRATE PLUGGABLE DATABASE PDBx TO CONTAINER CDB2 USING PDBx.xml CONNECT AS sys/mypassword@CDB2;

    Depending on what role the CDB I’m connected to has determines if a PDB is migrated to another CDB or is failed over to another CDB. So if e.g. a PDB has a failure on the primary site then I can “failover” its standby-equivalent to another Primary CDB on the standby machine and hence make the “standby PDB” a “primary PDB”. This works best when having 2 CDBs in 2 sites and replicate in opposite directions: CDB1 at site A replicates to CDB1 at site B. CDB2 at site B replicates to CDB2 at site A. So let’s assume PDBx in Primary DB CDB1 fails at site A. You can then migrate PDBx at site B to Primary DB CDB2. PDBx at site A in CDB1 will be dropped automatically, but the CDB2 at site A needs to be manually updated with the new data files of PDBx.

 

  • Use In-Memory Column Store on an Active Data Guard DB: As mentioned in my previous BLOG, in 12cR2 In-Memory can be used on an Active Data Guard Instance.
    Restrictions for In-Memory on Active Data Guard:
    In-Memory expressions are captured based on queries executed on the primary only. I.e. the expression statistics store (ESS) is maintained on the primary only.
    Automatic Data Optimization (ADO) policies are triggered only on access recorded on the primary database.
    In-Memory Fast-Start and In-Memory Join-Groups are not supported in an Active Data Guard

 

  • Diagnostics and Tuning for Active Data Guard: The Diagnostics Pack (AWR), the Tuning Pack features and SQL Plan Analyzer are supported in the new release on Active Data Guard.
    AWR: In an AWR catalog database the Active Data Guard DB is registered. From there remote snapshots can be taken from the Active Data Guard instance and stored in the AWR catalog: dbms_workload_repository.create_remote_snapshot("TYPICAL", ADG-id);
    SQL Tuning Advisor: All SQL Tuning Advisor Tasks are executed on the Active Data Guard instance. Necessary write activity are done through a DB-Link on the primary DB.

 

  • Repair blocks from NOLOGGING-operations: Blocks from NOLOGGING operations on primary can now be validated and repaired on Standby with rman commands:

    validate/recover ... nonlogged blocks;

    I.e. the primary DB does not necessarily need to be in FORCE LOGGING mode anymore. If NOLOGGING operations are necessary then they can be repaired on the Standby-DB. Previously complete datafiles had to be restored to repair NOLOGGING operations.

On Thursday I visited the panel discussion with the subject “Thinking clearly about Database Application Architecture”. Toon Koppelaars, Connor Mcdonald, Cary Milsap and Gerald Venzl discussed about the correct Application architecture when accessing data in an Oracle Database. The discussion was moderated by Bryn Llewellyn. Toon Koppelaars from the Real World Performance team at Oracle explained why the ThickDB approach by writing business logic (which need data processing) in PLSQL through set or bulk processing is the best method to have a well performing application (see also here). However, today the approach to process the data in layers outside the DB is being preferred (“data to processing” instead of “processing to data”). Unfortunately that results in row by row processing with lots of network roundtrips and higher CPU-usage on the DB-server due to the many times the whole stack on the DB-server has to be traversed.
It was clear and agreed in the audience that the ThickDB approach (“processing to data”) is correct, but why do developers not change their behavior since many years? The opinions on that differed, but also critical statements were expressed that “we as DBAs and DB-Consultants are part of the problem”, because there is no effort to change something in the base education of students to better understand the inner workings of a relational database system and the importance of “processing at the data”.

I’ll leave it to the reader to think about that and end my BLOGs about the Oracle Openworld 2016.

 

Cet article Oracle Open World 2016 – Day 4 and 5 est apparu en premier sur Blog dbi services.

OOW 2016: nouveautés base de donnée

Thu, 2016-09-22 14:20

Voici quelques infos sur ce qui a été annoncé ou présenté à l’Oracle Open World. L’info est relayée un peu partout principalement en anglais, donc voici un résumé à l’attention des francophones

Oracle Database 12c Release 2

Soyons clair, la base de donnée n’est pas le sujet principal de l’Open World. Comme prévu, c’est une sortie ‘Cloud First’ mais la version rendu publique lundi est une version limitée.
Si vous avez utilisé le ‘Schema as a Service’ c’est un peu la même idée sauf qu’il s’agit de ‘PDB as a Service’ ici. En multitenant, la consolidation par Schema est remplacée par la consolidation par PDB qui a l’avantage de présenter virtuellement une base complète, avec ses objects publics, ses multiples schemas, etc.
Donc pas d’accès d’administration: c’est un service “managed” – administré par Oracle.
Le multitenant permet de donner des droits DBA sur une PDB tout en empêchant d’interagir avec le reste du système. Ce sont des nouvelles fonctionnalités de la 12.2, entre autres les “lockdown profiles” qui ont été développées dans ce but.
Le service s’appelle “Exadata Express Cloud Service” car il tourne sur Exadata (donc compression HCC et bientôt SmartScan). La plupart des options sont d’ailleurs disponibles (In-Memory, Advanced Compression,…)
“Express” est pour la facilité et rapidité de provisonning: quelques minutes. Le but est qu’un développeur puisse en 5 minutes créer un service base de donnée facilement accessible (par SQL*Net encrypté). L’idée c’est qu’il soit aussi facile pour un développeur de créer une base Oracle que de créer des bases Postgres, Cassandra, MongoDB,…
Et bien sûr si on met toutes les option, le développeur va les utiliser et elles deviendront nécessaires en production.

Il y aura bientôt un Data Center en Europe. Pour le moment, c’est seulement aux USA. Le prix est attractif (CHF 170 par mois) mais la base est assez limitée en terme de CPU, stockage et mémoire. C’est principalement pour du développement et du bac à sable.

Donc la 12c Release 2 pour le moment n’est disponible que sous la forme de PDBaaS sur Exadata Express Cloud Service:

EXCS

Avant la fin de l’année, on devrait avoir la 12.2 en DBaaS (non-managed que l’on connait actuellement sir le PaaS Oracle) et la version General Availability viendra ensuite, probablement en 2017

 

Cet article OOW 2016: nouveautés base de donnée est apparu en premier sur Blog dbi services.

MySQL 8.0.0 DMR – First impression

Thu, 2016-09-22 07:23

Last week (09/12/2016) was announced the new MySQL 8.0.0 DMR (Development Milestone Release)
It is now available for download at dev.mysql.com
I downloaded and installed it
Following are my first impression on some new features
First of all, when you remember the size of earlier versions, it is more & more gluttonous, see below
mysql-5.0.96-linux-x86_64-glibc23.tar.gz    121.2M
mysql-5.1.72-linux-x86_64-glibc23.tar.gz    127.8M
mysql-5.5.52-linux2.6-x86_64.tar.gz            177.2M
mysql-5.6.33-linux-glibc2.5-x86_64.tar.gz    299.7M
mysql-5.7.15-linux-glibc2.5-x86_64.tar.gz   611.8M
mysql-8.0.0-dmr-linux-glibc2.12-x86_64.tar.gz is around 1Gb and uncompressed 3.6Gb (Oups…)

At first sight , it seems that nothing has changed, same schemas and almost the same tables
– 2 new tables related to roles in the mysql schema : default_roles, role_edges
– 6 new tables in the information_schema & 6 also in the performance_schema

When you scan your Data directory (datadir), you will find new “.SDI” files (Serialized Dictionary Information) for every schema except for MySQL
mysql@MYSQL:/u01/mysqldata/mysqld9/ [mysqld9] ll *.SDI
-rw-r-----. 1 mysql mysql 215 Sep 13 21:34 employees_4.SDI
-rw-r-----. 1 mysql mysql 225 Sep 13 21:37 performance_sche_7.SDI
-rw-r-----. 1 mysql mysql 209 Sep 13 21:34 sys_6.SDI
-rw-r-----. 1 mysql mysql 209 Sep 13 21:34 TBS_3.SDI

If you have a look in one of these files, you will see that they all provide data dictionary information in a serialized form (JSON formatted text)
mysql@MYSQL:/u01/mysqldata/mysqld9/ [mysqld9] cat employees_4.SDI
{
"sdi_version": 1,
"dd_version": 1,
"dd_object_type": "Schema",
"dd_object": {
"name": "employees",
"default_collation_id": 8,
"created": 0,
"last_altered": 0
}

Old “.frm” files containing metadata  have been removed, the same for “.par” file (Partition definition), “.trg file” (Trigger parameter) and “.isl” file (InnoDB Symbolic Link)
When you create a new INNODB table, metadata are stored now in the InnoDB file (.ibd)
For all the other storage engine (memory, CSV,Blackhole, etc…), we get now a new “.SDI” file but more complex
mysql@MYSQL:/u01/mysqldata/mysqld9/Newfeatures/ [mysqld9] cat t3_617.SDI
{
"sdi_version": 1,
"dd_version": 1,
"dd_object_type": "Table",
"dd_object": {
"name": "t3",
"mysql_version_id": 80000,
"created": 20160914075838,
"last_altered": 20160914075838,
"options": "avg_row_length=0;key_block_size=0;keys_disabled=0;pack_record=0;stats_ auto_recalc=0;stats_sample_pages=0;",
"columns": [
{
"name": "c1",
"type": 29,
"is_nullable": true,
"is_zerofill": false,
"is_unsigned": false,
"is_auto_increment": false,
"is_virtual": false,
"hidden": false,
"ordinal_position": 1,
"char_length": 20,
"numeric_precision": 0,
"numeric_scale": 0,
"datetime_precision": 0,
"has_no_default": false,
"default_value_null": true,
"default_value": "",
"default_option": "",
"update_option": "",
"comment": "",
"generation_expression": "",
"generation_expression_utf8": "",
"options": "interval_count=0;",
"se_private_data": "",
"column_key": 1,
"column_type_utf8": "char(20)",
"elements": [],
"collation_id": 8
}
],
"schema_ref": "Newfeatures",
"hidden": false,
"se_private_id": 18446744073709551615,
"engine": "MEMORY",
"comment": "",
"se_private_data": "",
"row_format": 1,
"partition_type": 0,
"partition_expression": "",
"default_partitioning": 0,
"subpartition_type": 0,
"subpartition_expression": "",
"default_subpartitioning": 0,
"indexes": [],
"foreign_keys": [],
"partitions": [],
"collation_id": 8
}

It is possible now to create roles, something we were waiting for years, that means you can define a set of privileges as a role and assign it to a user
mysqld9-(root@localhost) [Newfeatures]>create role tstrole;
mysqld9-(root@localhost) [Newfeatures]>grant all on employees.* to tstrole;
mysqld9-(root@localhost) [Newfeatures]>select host, user from mysql.user;
+---------------+-------------+
| host | user |
+---------------+-------------+
| % | tstrole |

As you can see, roles are stored in the mysql.user table meaning that you can assign a user to another user
mysqld9-(root@localhost) [Newfeatures]>show grants for tstrole;
+----------------------------------------------------------+
| Grants for tstrole@% |
+----------------------------------------------------------+
| GRANT USAGE ON *.* TO `tstrole`@`%` |
| GRANT ALL PRIVILEGES ON `employees`.* TO `tstrole`@`%` |
+----------------------------------------------------------+
mysqld9-(root@localhost) [Newfeatures]>grant tstrole to 'sme'@'localhost';
mysqld9-(root@localhost) [Newfeatures]>show grants for 'sme'@'localhost' using tstrole;
+----------------------------------------------------------------------------------+
| Grants for sme@localhost |
+----------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `sme`@`localhost` |
| GRANT ALL PRIVILEGES ON `employees`.* TO `sme`@`localhost` |
| GRANT `tstrole`@`%` TO `sme`@`localhost` |
+----------------------------------------------------------------------------------+

Now we connect as user “sme”
mysql -usme -p
mysql> use mysql
ERROR 1044 (42000): Access denied for user 'sme'@'localhost' to database 'mysql'

It seems to work, as user “sme” has only access to the employees schema

Data dictionary has been improved, almost all the system tables have been moved from MyISAM to the transactional InnoDB storage engine to increase reliability
Data dictionary tables are invisible,  they do not appear in the output of SHOW TABLES and cannot be accessed directly
but in most cases there are corresponding INFORMATION_SCHEMA tables that can be queried instead.

The new feature “Invisible Indexes” is really great.
You can toggle  now the visibility of an index as VISIBLE or INVISIBLE
We know that because of unused indexes, performance of modifications (insert, updates) are reduced
As they are also for the optimizer because they are taken in account for the plan selection
So first check for unused indexes
mysqld9-(root@localhost) [information_schema]>SELECT * FROM sys.schema_unused_indexes;
+------------------+-------------------+---------------+
| object_schema | object_name | index_name |
+------------------+---- --------------+---------------+
| employees | departments | dept_name |
| employees | dept_manager | dept_no |
+------------------+-------------------+---------------+

then
mysqld9-(root@localhost) [employees]>alter table departments alter index dept_name INVISIBLE;

Check in the data dictionary
mysqld9-(root@localhost) [employees]>SELECT * FROM information_schema.statistics WHERE is_visible='NO'\G
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: employees
TABLE_NAME: departments
NON_UNIQUE: 0
INDEX_SCHEMA: employees
INDEX_NAME: dept_name
SEQ_IN_INDEX: 1
COLUMN_NAME: dept_name
COLLATION: A
CARDINALITY: NULL
SUB_PART: NULL
PACKED: NULL
NULLABLE:
INDEX_TYPE: BTREE
COMMENT:
INDEX_COMMENT:
IS_VISIBLE: NO

You can make now persistant global variables
select @@max_connections;
+--------------------------+
| @@max_connections |
+--------------------------+
| 151 |
+--------------------------+
SET GLOBAL max_connections=200;

If you restart your Instance, such setting is lost. So now with
SET PERSIST max_connections=200;
This setting now will remain after an instance restart
select @@max_connections;
+--------------------------+
| @@max_connections |
+--------------------------+
| 200 |
+--------------------------+

Last but not least, be carefull when using MySQL 8.0.0.0 in a multi instance environment,
in my case 9 instances with different versions
I found a bug in mysqld_multi when you want to restart your instance 8.0.0.0
First I corrected the still not fixed Bug #77946 (https://bugs.mysql.com/bug.php?id=77946)
in order to be able to stop my instance properly with mysqld_multi
I stopped it and then tried several times to restart it
No way
2016-09-20T23:42:41.466540Z 1 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2016-09-20T23:42:41.466562Z 1 [ERROR] InnoDB: The error means the system cannot find the path specified.
2016-09-20T23:42:41.466568Z 1 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2016-09-20T23:42:41.466574Z 1 [ERROR] InnoDB: File ./ibdata1: 'open' returned OS error 71. Cannot continue operation
2016-09-20T23:42:41.466582Z 1 [ERROR] InnoDB: Cannot continue operation.

As a temporary workaround, you can either start it manually or use the mysqld_multi from a previous version
I reported this bug  to MySQL (https://bugs.mysql.com/83081)

 

Cet article MySQL 8.0.0 DMR – First impression est apparu en premier sur Blog dbi services.

Oracle Open World 2016 – Day 3

Wed, 2016-09-21 02:39

On day 3 I visited various sessions about In-Memory and its many new features in 12gR2. Yesterday I already listed some new features of In-Memory. In this post I want to explain the new features more in detail:

 

In-Memory Join Groups:

Columns are specified, which are used to join tables:

CREATE INMEMORY JOIN GROUP V_name_jg (VEHICLES(NAME), SALES(NAME));

Those columns share the compression dictionary. My understanding on how this works is as follows: The joins occur on dictionary values rather than data. E.g. a dictionary with distinct values of car brands (column NAME in VEHICLES and SALES) may look as follows:

BMW 1
VW 2
Mercedes 3
Ford 4
Fiat 5
Dodge 6

The dictionary values are stored in the compression units instead of the real data:

1
1
2
3
3
3
4
5
5
6

Doing that for the 2 columns allows the joins to happen on the dictionary values rather than real values. Oracle talks about a speedup of a factor 2-3 when using Join Groups.

 

In-Memory expressions:

SQL expression results (NET in the example below) can now be stored as additional in-memory columns:

CREATE TABLE SALES (
PRICE NUMBER, TAX NUMBER, ...
NET AS (PRICE + PRICE * TAX )
)
INMEMORY;

All in-memory optimizations (e.g. vector processing or storage indexes) apply to the expression columns as well. When a SQL contains the expression then it can be taken from the column store without computing it:

SELCT SUM(NET) FROM SALES WHERE NET > 50;

Different types of expressions are possible:

  • Arithmetic expression
  • Logical expression (e.g. DECODE)
  • Type conversion (e.g. UPPER, TO_CHAR)
  • PL/SQL expressions

Two modes to define the expressions to populate (Manual and Auto) can be used:

Manual: Declare virtual columns for the desired expression (see example above). The parameter INMEMORY_VIRTUAL_COLUMNS defines if virtual columns are considered for inmemory:
INMEMORY_VIRTUAL_COLUMNS
= ENABLE: All user-defined virtual columns on the table or partition enabled for in-memory will be populated
= MANUAL: User-defined virtual columns must explicitly marked for INMEMORY (default)
= DISABLE: No user-defined column will ever be populated

Auto: Oracle detects frequent expressions automatically. I.e. the optimizer stores “hot” expressions (based on frequency and cost) in the expression statistics store (ESS). With the procedures IME_CAPTURE and IME_POPULATE in the package DBMS_INMEMORY the expressions are captured and declared as hidden in-memory virtual columns on the appropriate table. Expressions in the ESS can be checked by querying ALL|DBA|USER_EXPRESSION_STATISTICS.

 

In-Memory JSON

JSON columns and expressions on JSON columns can now be populated in-memory.

 

In-Memory on Active Data Guard

According Oracle, In-Memory on Active Data Guard was the most wanted feature from customers for In-Memory for 12gR2. I.e. the In-Memory column store can now be used on the Active Data Guard instance as well. The standby database can have different data (columns) stored in-memory than the primary. The decision on what columns to populate in-memory on the different instances is based on a service. I.e. on primary the following DDL may be used:

ALTER TABLE SALES INMEMORY DISTRIBUTE FOR SERVICE A;
ALTER TABLE SHIPMENTS INMEMORY DISTRIBUTE FOR SERVICE B;

Table SALES will be populated In-Memory on that instance, which has service A enabled and SHIPMENTS will be populated on the instance, which has service B enabled.

 

In-Memory Columnar Flash

On Exadata the In-Memory format can be used on the Flash Cache. I.e. an In-Flash Column store can be created. The advantage is that there is usually much more Flash than RAM and hence more data can be cached for In-Memory on Flash scans. To do that the CELLMEMORY segment attribute is available:

ALTER TABLE sales CELLMEMORY;
CREATE TABLE trades (...) CELLMEMORY MEMCOMPRESS FOR QUERY;

Restrictions in comparison to normal In-Memory:

  • The MEMCOMPRESS sub-clause only supports “FOR QUERY LOW” and “FOR CAPACITY LOW”.
  • There is no PRIORITY sub-clause.

 

Automatic Data Optimization (ADO) extended to In-Memory

The heatmap can now be used to define how long data should remain In-Memory or when In-Memory compression should be changed. I.e. in the maintenance window or on manual request (using the procedure DBMS_ILM.EXECUTE_ILM) Oracle checks if a policy has been met to e.g. flush data from the In-Memory column store.

ALTER TABLE sales ILM ADD POLICY ... NO INMEMORY AFTER 10 DAYS OF NO ACCESS;

That allows sliding windows of column store content based on access-time or time of creation. A customized policy function can also be created in PLSQL.

 

In-Memory Fast-Start

The In-Memory column store content can now be checkpointed to Securefile Lobs. When the DB restarts the population is faster (between 2-5x) as the data can immediately be loaded in memory without CPU-intensive compression activity. To activate In-Memory Fast-Start the procedure DBMS_INMEMORY_ADMIN.FASTSTART_ENABLE with a tablespace-name as parameter has to be executed. The tablespace should be 2 times the size of the in-memory column store.

 

Cet article Oracle Open World 2016 – Day 3 est apparu en premier sur Blog dbi services.

Ravello

Tue, 2016-09-20 18:46

I’m at Oracle Open World and I’m rather focused on database rather than all those Cloud topics, but one product caught my attention at the Oracle ACED Briefing. Oracle bought Ravallo systems at the beginning of the year. What is Ravello? You have virtualized your applications on VMware and want to move them to the Cloud… without any changes.

Move your application

An application is not a single VM. It’s an ecosystem of VMs working together, linked by one or multiple network interfaces. Moving the virtual machines is easy, thanks to virtualization. But do you change the IP addresses everywhere? This is where you need another layer of virtualization.

Let’s see how it works. With Ravallo you can import your VMs easily. It can connect to your VSphere or you just import an ova. Once they are imported into Ravallo, you can start to build your application with a simple drag and drop of the concerned VMs.

The magic follows. Ravallo parses the VMs metadata and data to discover the ecosystem. Here his the result after having importing a database, two weblogic server servers, and a windows client:

2016-09-20 10.15.04

Not only the VMs are there. there is a network here with switches, routers, DNS, DHCP, a fenced network private to my application, all virtualized.

And there’s more: all IP addresses are the same in that private network than on my on-premises VMWare farm. That’s the point. Changing network interfaces, IP addresses, DNS, etc is a nightmare and you don’t have to do it.

This is only the design of the application. Once that application is defined, you can publish it to the Cloud: Oracle Public Cloud, Google one, or AWS.

And this is easy. You can choose that resources are optimized for cost or performance, and you even have an idea of the price:

2016-09-20 10.13.31 2016-09-20 10.13.13

Time to run our application. As it runs in an isolate environment, you can even run multiple instances of it. But how do you connect to it?

First, you can access the console to each VM from Ravallo though VNC.
For external access you create services that set NAT rules and allocate public IP addresses.

Ravallo is nested virtualization: two levels of hypervisors.

 

Cet article Ravello est apparu en premier sur Blog dbi services.

Oracle 12cR2 Optimizer Adaptive Statistics

Tue, 2016-09-20 10:13

When 12.1 came out, the major issue we encountered after migration was related to the new adaptive features of the optimizer: automatic reoptimization, SQL Plan Directives and the resulting dynamic sampling. Of course, Oracle product managers listen to feedbacks, ensure to provide workarounds or fixes and make things better for next release. Let’s see what has been announce on this topic for 12.2

Fixing vs. workarounds

Unfortunately, in most case, when a problem is encountered people put priority on it only until the issue appears as “solved”, and then close the problem. However for stability and reliability, this is not enough. There are two phases in problem resolution:

  1. Make broken things working again as soon as possible
  2. Ensure that the solution addresses the root cause and is in the same scope as the problem

If you stop after the first point, you don’t have a solution. You have a workaround, and two things will happen soon or later:

  • The problem will come back again
  • New problems will appear as side effects
12.1

So, when you upgrade to 12c from 11.2.0.4 for example, the easiest way to fix a regression is to set optimizer_features_enable=’11.2.0.4′. But when you do that, you did only the first step. Nothing is fixed. Actually, when doing that you didn’t even finish your upgrade job.
I’ve already blogged about how to fix an adaptive statistics issue and keep the fix in the same scope as the problem by:

so there are many combinations that depend on your context.

One parameter do not fit all

It’s easy to disable all new adaptive features and claim that the 12c optimizer is full of bugs. However there are two things that I’m sure:

  • The developers of the optimizer know their stuff at least 100x better than I do
  • They want to bring nice features rather than trying to break things

And they do something very nice: each individual feature can be enabled or disabled by a parameter. So there are lot of parameters. Some of them are undocumented just because at release time they don’t think they should have a different value other than default, except special situations guided by the support. But one set of default value cannot fit all environments. Are you doing OLTP or BI? OLTP likes stability, BI likes adaptive optimization. And probably your database has both OLTP and reporting workloads, and maybe at the same time. This is the first reason why one set of parameter cannot fit all. There’s another one you should think about before blaming the optimizer. Maybe they bring features that helps to make good applications even better. Maybe the set of default value is not chosen to fit the worst application design…
Let’s come back to the OLTP vs. BI example. Adaptive features are enabled by default for BI. You may spend more time on parsing in order to get the optimal execution plan. But then you complain that your OLTP spends more time on parsing… But you are not supposed to parse on OLTP! The overhead of adaptive features should not be a blocking problem if you parse your queries once and then execute them.

I tend to blog on encountered issues rather that on thinks that do not raise any problem. Because my job is to solve problems rather that stay looking at what works well. I’ve encountered a lot of issues with those adaptive features. But I have seen lot of application that had no problem at all when upgraded to 12c. When you disable the adaptive features, do you workaround an optimizer problem, or your application design problem?

12.2

In 12.1 only optimizer_adaptive_features is documented but it disables too many features. You may want to disable SQL Plan Directive and their consequences. But you probably want to keep adaptive plans as they are awesome and less prone of bad side effects. So in 12.2 this parameter has been split into two parameters: OPTIMIZER_ADAPTIVE_PLANS and OPTIMIZER_ADAPTIVE_STATISTICS

In addition to that, only OPTIMIZER_ADAPTIVE_PLANS is set to true by default. OPTIMIZER_ADAPTIVE_STATISTICS is false so by default you will not have the following 12c features: SQL Plan Directives, Statistics feedback, Performance feedback, Adaptive dynamic sampling for parallel query.

Here are the features enabled by OPTIMIZER_ADAPTIVE_PLANS:

optimizer_adaptive_plans TRUE FALSE _optimizer_nlj_hj_adaptive_join TRUE FALSE _px_adaptive_dist_method CHOOSE OFF _optimizer_strans_adaptive_pruning TRUE FALSE

If you want more information about them, I’ve written articles about adaptive join, adaptive PX distribution and adaptive star transformation bitmap pruning

Here are the features enabled by OPTIMIZER_ADAPTIVE_STATISTICS:

optimizer_adaptive_statistics FALSE TRUE _optimizer_dsdir_usage_control 0 126 _optimizer_use_feedback_for_join FALSE TRUE _optimizer_ads_for_pq FALSE TRUE

As you can see there is no “_optimizer_gather_feedback” here so the cardinality feedback coming from 11g is still there when you disable adaptive statistics. You may like it or not, and maybe want to disable cardinality feedback as well if you don’t want plans that change.

What if you already have some SPDs? as “_optimizer_dsdir_usage_control” is 0 they will not be used. And they will be dropped automatically after 53 weeks of no usage.

 

Cet article Oracle 12cR2 Optimizer Adaptive Statistics est apparu en premier sur Blog dbi services.

Pages