Tuesday, January 31, 2012

Locking statistics of tables

Having accurate statistics of database objects is mandatory in order to get the best execution path, and usually you can collect statistics using the DBMS_STATS package with no problem at all. But sometimes you have special tables that needs special treatment, like "temporary" tables (normal tables that are populated and its data modified very fast) that can change a lot the execution path of a SQL sentence and drive you crazy trying to pinpoint the root cause of that execution path changes.

As a demonstration of this behavior we will create two tables, one that will change a lot (test1) and other that is more or less static (test2):

SQL> create table test1 (id number primary key, id_char varchar2(15));

Table created.

SQL> declare
mynum number := 1;
begin
while mynum < 10 loop
insert into test1 values(mynum,to_char(mynum));
mynum := mynum + 1;
end loop;
end;
/

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> create table test2 (id number primary key, id_char varchar2(15));

Table created.

SQL> declare
mynum number := 1;
begin
while mynum < 100000 loop
insert into test2 values(mynum,to_char(mynum));
mynum := mynum + 1;
end loop;
end;
/

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

At the beginning test1 table has 9 records, and we want to join test1 and test2 tables to get a few records, but first we will gather statistics of test1:

SQL> BEGIN DBMS_STATS.GATHER_TABLE_STATS( OWNNAME => 'MYUSER', TABNAME => 'TEST1'); END;
2 /

PL/SQL procedure successfully completed.

SQL> select a.ID, b.ID_CHAR from test1 a join test2 b on (a.ID=b.ID) where a.ID between 4 and 7;

ID ID_CHAR
---------- ---------------
4 4
5 5
6 6
7 7

Next we will get the current execution plan for this small query:

SQL> EXPLAIN PLAN FOR
select a.ID, b.ID_CHAR from test1 a join test2 b on (a.ID=b.ID) where a.ID between 4 and 7;

Explained.

SQL> @$ORACLE_HOME/rdbms/admin/utlxpls.sql

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3677731951

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 56 | 4 (0) | 00:00:01 |
| 1 | NESTED LOOPS | | 4 | 56 | 4 (0) | 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| TEST2 | 5 | 55 | 4 (0) | 00:00:01 |
|* 3 | INDEX RANGE SCAN | SYS_C004858 | 5 | | 3 (0) | 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | SYS_C004857 | 1 | 3 | 0 (0) | 00:00:01 |
--------------------------------------------------------------------------------------------

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

3 - access("B"."ID">=4 AND "B"."ID"<=7)
4 - access("A"."ID"="B"."ID")
filter("A"."ID">=4 AND "A"."ID"<=7)

18 rows selected.

This execution plan looks good and really doesn't matter a lot because we got just a few records, but wait! Nine records is not the typical size of test1 table; now we will create more records:

SQL> delete from test1;

9 rows deleted.

SQL> declare
mynum number := 1;
begin
while mynum < 10000 loop
insert into test1 values(mynum,to_char(mynum));
mynum := mynum + 1;
end loop;
end;
/

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

You might think that there is not a lot of difference having ten or ten thousand records in test1, but Oracle's optimizer does not think the same way and this time the execution plan is different:

SQL> EXPLAIN PLAN FOR
2 select a.ID, b.ID_CHAR from test1 a join test2 b on (a.ID=b.ID) where a.ID between 4000 and 7000;

Explained.

SQL> @$ORACLE_HOME/rdbms/admin/utlxpls.sql

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2418654178

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 | 3 (0) | 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 1 | 14 | 3 (0) | 00:00:01 |
|* 3 | INDEX RANGE SCAN | SYS_C004857 | 1 | 3 | 1 (0) | 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | SYS_C004858 | 1 | | 1 (0) | 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| TEST2 | 1 | 11 | 2 (0) | 00:00:01 |
--------------------------------------------------------------------------------------------

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

3 - access("A"."ID">=4000 AND "A"."ID"<=7000)
4 - access("A"."ID"="B"."ID")
filter("B"."ID"<=7000 AND "B"."ID">=4000)

19 rows selected.

You might think that retrieving four records is different of retrieving four thousand and that's right, but the optimizer choose the execution path based in wrong data; look the amount of rows Oracle thinks it will work to.

You will see it clearly refreshing the test1 table statistics and getting the new execution path:

SQL> BEGIN DBMS_STATS.GATHER_TABLE_STATS( OWNNAME => 'SYS', TABNAME => 'TEST1'); END;
2 /

PL/SQL procedure successfully completed.

SQL> EXPLAIN PLAN FOR
select a.ID, b.ID_CHAR from test1 a join test2 b on (a.ID=b.ID) where a.ID between 4000 and 7000;

Explained.

SQL> @$ORACLE_HOME/rdbms/admin/utlxpls.sql

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3677731951

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3001 | 45015 | 17 (12) | 00:00:01 |
| 1 | NESTED LOOPS | | 3001 | 45015 | 17 (12) | 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| TEST2 | 3002 | 33022 | 15 (0) | 00:00:01 |
|* 3 | INDEX RANGE SCAN | SYS_C004858 | 3002 | | 8 (0) | 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | SYS_C004857 | 1 | 4 | 0 (0) | 00:00:01 |
--------------------------------------------------------------------------------------------

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

3 - access("B"."ID">=4000 AND "B"."ID"<=7000)
4 - access("A"."ID"="B"."ID")
filter("A"."ID">=4000 AND "A"."ID"<=7000)

18 rows selected.

Can you see the problem? Having to deal with this kind of tables you may: use hints to force the optimal execution of the SQL sentence, refresh statistics every time you change a table like this, or lock the statistics when you have a typical table size or optimal execution path.

We will consider that at this point the test1 table has a typical size and its statistics help the optimizer to chose an optimal execution plan, therefore we will lock its statistics this way:

SQL> exec DBMS_STATS.LOCK_TABLE_STATS ('MYUSER', 'TEST1');

PL/SQL procedure successfully completed.

Now we will repopulate again the test1 table with nine records:

SQL> delete from TEST1;

9999 rows deleted.

SQL> declare
mynum number := 1;
begin
while mynum < 10 loop
insert into test1 values(mynum,to_char(mynum));
mynum := mynum + 1;
end loop;
end;
/

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> BEGIN DBMS_STATS.GATHER_TABLE_STATS( OWNNAME => 'MYUSER', TABNAME => 'TEST1'); END;
2 /
BEGIN DBMS_STATS.GATHER_TABLE_STATS( OWNNAME => 'MYUSER', TABNAME => 'TEST1'); END;
*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 23829
ORA-06512: at "SYS.DBMS_STATS", line 23880
ORA-06512: at line 1

As long as the statistics of test1 table were locked you cannot gather new statistics unless you unlock the statistics of the table. Anyway, the point of doing this is to help the optimizer to chose a good execution plan:

SQL> EXPLAIN PLAN FOR
select a.ID, b.ID_CHAR from test1 a join test2 b on (a.ID=b.ID) where a.ID between 4 and 7;

Explained.

SQL> @$ORACLE_HOME/rdbms/admin/utlxpls.sql

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3677731951

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 60 | 4 (0) | 00:00:01 |
| 1 | NESTED LOOPS | | 4 | 60 | 4 (0) | 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| TEST2 | 5 | 55 | 4 (0) | 00:00:01 |
|* 3 | INDEX RANGE SCAN | SYS_C004858 | 5 | | 3 (0) | 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | SYS_C004857 | 1 | 4 | 0 (0) | 00:00:01 |
--------------------------------------------------------------------------------------------

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

3 - access("B"."ID">=4 AND "B"."ID"<=7)
4 - access("A"."ID"="B"."ID")
filter("A"."ID"<=7 AND "A"."ID">=4)

18 rows selected.

SQL> delete from TEST1;

9 rows deleted.

SQL> declare
mynum number := 1;
begin
while mynum < 10000 loop
insert into test1 values(mynum,to_char(mynum));
mynum := mynum + 1;
end loop;
end;
/

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> EXPLAIN PLAN FOR
select a.ID, b.ID_CHAR from test1 a join test2 b on (a.ID=b.ID) where a.ID between 4000 and 7000;

Explained.

SQL> @$ORACLE_HOME/rdbms/admin/utlxpls.sql

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3677731951

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3001 | 45015 | 17 (12) | 00:00:01 |
| 1 | NESTED LOOPS | | 3001 | 45015 | 17 (12) | 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| TEST2 | 3002 | 33022 | 15 (0) | 00:00:01 |
|* 3 | INDEX RANGE SCAN | SYS_C004858 | 3002 | | 8 (0) | 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | SYS_C004857 | 1 | 4 | 0 (0) | 00:00:01 |
--------------------------------------------------------------------------------------------

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

3 - access("B"."ID">=4000 AND "B"."ID"<=7000)
4 - access("A"."ID"="B"."ID")
filter("A"."ID">=4000 AND "A"."ID"<=7000)

18 rows selected.

As you can see, this time the execution plan was the same for ten or ten thousand records in test1 table and the number of rows managed in the execution plans were more or less accurate.

By the way, if you want to unlock the statistics of a table you can do it this way:

SQL> exec DBMS_STATS.UNLOCK_TABLE_STATS ('MYUSER', 'TEST1');

PL/SQL procedure successfully completed.

SQL> BEGIN DBMS_STATS.GATHER_TABLE_STATS( OWNNAME => 'MYUSER', TABNAME => 'TEST1'); END;
2 /

PL/SQL procedure successfully completed.


More information:

Locking or Unlocking Statistics
Preserving Statistics using DBMS_STATS.LOCK_TABLE_STATS

Monday, January 30, 2012

Oracle and OCFS2 not releasing space used

Sometimes you have to drop a tablespace to recover storage space and use it to create other tablespace, like when you have to resize an undo tablespace. And usually is not a big issue unless you have OCFS2 file systems:

oracle@myserver$ df -m|egrep '(Filesystem)|(/mydb/undo)'
Filesystem 1M-blocks Used Available Use% Mounted on
24576 24572 4 100% /mydb/undo

oracle@myserver$ sqlplus '/ as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Jan 26 22:57:35 2012

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> drop tablespace myundo including contents and datafiles;

Tablespace dropped.

SQL> create undo tablespace mynewundo datafile '/mydb/undo/undo.dbf' size 10g;
create undo tablespace mynewundo datafile '/mydb/undo/undo.dbf' size 10g
*
ERROR at line 1:
ORA-01119: error in creating database file '/mydb/undo/undo.dbf'
ORA-27044: unable to write the header block of file
Linux-ia64 Error: 28: No space left on device
Additional information: 4

SQL> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

oracle@myserver$ df -m|egrep '(Filesystem)|(/mydb/undo)'
Filesystem 1M-blocks Used Available Use% Mounted on
24576 24572 4 100% /mydb/undo

oracle@myserver$ ls -la /mydb/undo
total 16
drwxr-xr-x 4 root root 4096 Jan 20 2010 .
drwxr-xr-x 4 root root 4096 Dec 17 2009 ..
drwxr-xr-x 2 root root 4096 Dec 17 2009 lost+found

As you might notice there is no space recovered from dropping that undo tablespace, and since we need to create a new undo tablespace in the same file system we have to do something.

I don't have a lot of dabatases placed in OCFS2 file systems so I didn't care about finding the root cause of this problem, and since I had this database with no users connected I just created a small undo tablespace somewhere else, shut down the instance, and after doing it I got that space back:

oracle@myserver$ df -m|egrep '(Filesystem)|(/mydb/undo)'
Filesystem 1M-blocks Used Available Use% Mounted on
24576 359 24217 2% /mydb/undo

oracle@myserver$ ls -la /mydb/undo
total 16
drwxr-xr-x 4 root root 4096 Jan 20 2010 .
drwxr-xr-x 4 root root 4096 Dec 17 2009 ..
drwxr-xr-x 2 root root 4096 Dec 17 2009 lost+found

oracle@myserver$ mount|grep /mydb/undo
/dev/mapper/myvg-undo on /mydb/undo type ocfs2 (rw,_netdev,datavolume,nointr,heartbeat=local)

oracle@myserver$ uname -a
Linux myserver.localdomain 2.6.9-67.EL #1 SMP Wed Nov 7 13:43:35 EST 2007 ia64 ia64 ia64 GNU/Linux

oracle@myserver$ /sbin/lsmod|grep ocf
ocfs2 744096 26
debugfs 27340 2 ocfs2
ocfs2_dlmfs 53416 1
ocfs2_dlm 424192 2 ocfs2,ocfs2_dlmfs
ocfs2_nodemanager 316791 32 ocfs2,ocfs2_dlmfs,ocfs2_dlm
configfs 65468 2 ocfs2_nodemanager
jbd 149272 2 ocfs2,ext3

oracle@myserver$ /sbin/modinfo ocfs2
filename: /lib/modules/2.6.9-67.EL/kernel/fs/ocfs2/ocfs2.ko
license: GPL
author: Oracle
version: 1.2.9 1B43458FD47258934A48F1C
description: OCFS2 1.2.9 Mon May 19 14:04:34 PDT 2008 (build a693806cb619dd7f225004092b675ede)
depends: ocfs2_nodemanager,ocfs2_dlm,jbd,debugfs
vermagic: 2.6.9-67.EL SMP ia64gcc-3.4

I know this is just a workaround and not a good one, but it works fine and you can move on the next thing.

Wednesday, January 25, 2012

How to configure a VPN PPTP connection

If you need to open a VPN PPTP connection from a Linux computer, you can use the pptp client to configure a PPP connection and launch the pppd daemon to make the connection. You need to know at least the VPN server, an authorized user to make the connection and its password, and we will work with the root account for practical purposes but it's not mandatory.

First, if you don't have the pptp client you have to install it or configure the PPP connection by hand or by other means, in Debian Squeeze you could install the pptp-linux package.

Next, this is the easy way to configure your VPN PPTP connection:

pptpsetup --create MYVPN --server myvpnserver --username myvpnuser --password mypassword

You have to substitute myvpnserver, myvpnuser and mypassword with the VPN server, the user and password respectively; MYVPN is the name of the PPP configuration you will create and is chosen by you.

After this you will have a new configuration file in /etc/ppp/peers and also an extra line in /etc/ppp/chap-secrets:

olimpo:~# cat /etc/ppp/peers/MYVPN
# written by pptpsetup
pty "pptp myvpnserver --nolaunchpppd"
lock
noauth
nobsdcomp
nodeflate
name myvpnuser
remotename MYVPN
ipparam MYVPN

olimpo:~# cat /etc/ppp/chap-secrets
# Secrets for authentication using CHAP
# client server secret IP addresses

# added by pptpsetup for MYVPN
myvpnuser MYVPN "mypassword" *

If you want to configure the pppd daemon by hand, you just have to create a file in /etc/ppp/peers and add the authentication information in /etc/ppp/chap-secrets and it must be the same.

At this time you can open the PPP connection launching the pppd daemon:

olimpo:~# pppd call MYVPN updetach
Using interface ppp0
Connect: ppp0 <--> /dev/pts/3
CHAP authentication succeeded
local IP address 10.5.15.222
remote IP address 10.5.15.127

olimpo:~# tail --lines=16 /var/log/syslog
Jan 25 13:04:12 olimpo pppd[29036]: pppd 2.4.5 started by root, uid 0
Jan 25 13:04:12 olimpo pppd[29036]: Using interface ppp0
Jan 25 13:04:12 olimpo pppd[29036]: Connect: ppp0 <--> /dev/pts/3
Jan 25 13:04:13 olimpo pptp[29037]: anon log[main:pptp.c:314]: The synchronous pptp option is NOT activated
Jan 25 13:04:13 olimpo pptp[29041]: anon log[ctrlp_rep:pptp_ctrl.c:251]: Sent control packet type is 1 'Start-Control-Connection-Request'
Jan 25 13:04:13 olimpo pptp[29041]: anon log[ctrlp_disp:pptp_ctrl.c:739]: Received Start Control Connection Reply
Jan 25 13:04:13 olimpo pptp[29041]: anon log[ctrlp_disp:pptp_ctrl.c:773]: Client connection established.
Jan 25 13:04:14 olimpo NetworkManager[1919]: SCPlugin-Ifupdown: devices added (path: /sys/devices/virtual/net/ppp0, iface: ppp0)
Jan 25 13:04:14 olimpo NetworkManager[1919]: SCPlugin-Ifupdown: device added (path: /sys/devices/virtual/net/ppp0, iface: ppp0): no ifupdown configuration found.
Jan 25 13:04:14 olimpo pptp[29041]: anon log[ctrlp_rep:pptp_ctrl.c:251]: Sent control packet type is 7 'Outgoing-Call-Request'
Jan 25 13:04:14 olimpo pptp[29041]: anon log[ctrlp_disp:pptp_ctrl.c:858]: Received Outgoing Call Reply.
Jan 25 13:04:14 olimpo pptp[29041]: anon log[ctrlp_disp:pptp_ctrl.c:897]: Outgoing call established (call ID 0, peer's call ID 8918).
Jan 25 13:04:14 olimpo modem-manager: (net/ppp0): could not get port's parent device
Jan 25 13:04:17 olimpo pppd[29036]: CHAP authentication succeeded
Jan 25 13:04:17 olimpo pppd[29036]: local IP address 10.5.15.222
Jan 25 13:04:17 olimpo pppd[29036]: remote IP address 10.5.15.127

olimpo:~# ifconfig ppp0
ppp0 Link encap:Point-to-Point Protocol
inet addr:10.5.15.222 P-t-P:10.5.15.127 Mask:255.255.255.255
UP POINTOPOINT RUNNING NOARP MULTICAST MTU:1500 Metric:1
RX packets:7 errors:0 dropped:0 overruns:0 frame:0
TX packets:7 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:3
RX bytes:99 (99.0 B) TX bytes:93 (93.0 B)

olimpo:~# route
Kernel IP routing table
Destination Gateway Genmask Flags Metric Ref Use Iface
10.5.15.127 * 255.255.255.255 UH 0 0 0 ppp0
static.customer 10.123.46.1 255.255.255.255 UGH 0 0 0 br0
10.123.46.0 * 255.255.255.0 U 0 0 0 br0
default 10.123.46.1 0.0.0.0 UG 0 0 0 br0

That's it, you have a VPN connection open to you VPN server! Or it's almost done, because we have a routing problem.

olimpo:~# ping 10.5.15.123
PING 10.5.15.123 (10.5.15.123) 56(84) bytes of data.
^C
--- 10.5.15.123 ping statistics ---
7 packets transmitted, 0 received, 100% packet loss, time 6047ms

olimpo:~# traceroute 10.5.15.123
traceroute to 10.5.15.123 (10.5.15.123), 30 hops max, 60 byte packets
1 10.123.81.4 (10.123.81.4) 0.407 ms 0.429 ms 0.492 ms
2 10.123.148.1 (10.123.148.1) 0.313 ms 0.352 ms 0.345 ms
3 * * *
4 * * *
5 * * *
6 *^C

As you might notice in the route information, there is no route to the VPN network from your Linux computer. You could append defaultroute to the pppd command, but this way you will have connectivity ONLY to the VPN network; to put it simple: is like if you were attached directly to the other network. If you had internet access and there is no way to access internet in the VPN network then you'll have no internet access, or if you had access to other networks after using pppd with defaultroute you will no longer have access to them.

And in fact that's the way a VPN is supposed to work. For example, if you got a trojan in your computer and that program is tailored specifically for your organization, and you connect your computer to the VPN network and still have access to the internet, then that trojan might steal information and send it to someone else easily. Or you might thing that leaving your VPN connection open and your computer accessible from the internet would be handy to share your VPN connection; this way you're creating a security hole in the VPN network so be careful and do it if you really need to.

Therefore, if you need to keep connectivity to other networks and don't want to append defaultroute to the pppd command, you have to create the route by hand. In this case, we want to access the VPN network 10.5.15.0/24, therefore we will issue this route command:

olimpo:~# route add -net 10.5.15.0 netmask 255.255.255.0 dev ppp0

As you can see in the route and traceroute information, this time you can reach the example server (10.5.15.123) through your PPP gateway (10.5.15.127):

olimpo:~# route
Kernel IP routing table
Destination Gateway Genmask Flags Metric Ref Use Iface
10.5.15.127 * 255.255.255.255 UH 0 0 0 ppp0
static.customer 10.123.46.1 255.255.255.255 UGH 0 0 0 br0
10.123.46.0 * 255.255.255.0 U 0 0 0 br0
10.5.15.0 * 255.255.255.0 U 0 0 0 ppp0
default 10.123.46.1 0.0.0.0 UG 0 0 0 br0

olimpo:~# traceroute 10.5.15.123
traceroute to 10.5.15.123 (10.5.15.123), 30 hops max, 60 byte packets
1 10.5.15.127 (10.5.15.127) 163.061 ms 163.027 ms 163.010 ms
2 10.5.15.123 (10.5.15.123) 174.981 ms 174.967 ms 174.954 ms

Finally, in order to end your PPP connection you have to end the pppd daemon (gracefully); you might do this getting the pppd daemon PID and issuing a kill command:

olimpo:~# ps ax|grep pppd
10094 pts/1 S+ 0:00 grep pppd
29037 pts/1 S 0:00 pptp myvpnserver --nolaunchpppd
29041 pts/1 S 0:00 pptp myvpnserver --nolaunchpppd
29044 pts/1 S 0:00 pppd call MYVPN updetach

olimpo:~# cat /var/run/ppp0.pid
29044

olimpo:~/bin# kill -TERM 29044

olimpo:~/bin# tail --lines=11 /var/log/syslog
Jan 25 15:06:43 olimpo pppd[29044]: Terminating on signal 15
Jan 25 15:06:43 olimpo pppd[29044]: Modem hangup
Jan 25 15:06:43 olimpo pppd[29044]: Connect time 122.5 minutes.
Jan 25 15:06:43 olimpo pppd[29044]: Sent 1140 bytes, received 1056 bytes.
Jan 25 15:06:43 olimpo pptp[29041]: anon log[callmgr_main:pptp_callmgr.c:258]: Closing connection (shutdown)
Jan 25 15:06:43 olimpo pptp[29041]: anon log[ctrlp_rep:pptp_ctrl.c:251]: Sent control packet type is 12 'Call-Clear-Request'
Jan 25 15:06:43 olimpo pptp[29041]: anon log[call_callback:pptp_callmgr.c:79]: Closing connection (call state)
Jan 25 15:06:44 olimpo pppd[29044]: Connection terminated.
Jan 25 15:06:44 olimpo avahi-daemon[1488]: Withdrawing workstation service for ppp0.
Jan 25 15:06:44 olimpo NetworkManager[1919]: SCPlugin-Ifupdown: devices removed (path: /sys/devices/virtual/net/ppp0, iface: ppp0)
Jan 25 15:06:49 olimpo pppd[29044]: Exit.

olimpo:~# ps ax|grep pppd
11587 pts/1 S+ 0:00 grep pppd

More information:

The Point-to-Point Protocol