Patch to 2.2.9 for named checkpoints

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Patch to 2.2.9 for named checkpoints

Rob David

Hi,

 

I have posted a patch on the tracker for a change against 2.2.9 to allow for named checkpoints. This was a much needed feature that we are currently using in our organisation for automated Web UI testing. Our UI test cases typically unavoidably commit transactions, and we are using this feature to quickly wind the database back to a known state between each test case in order to isolate them from each other. We’re currently finding that we are now able to do this with something like sub-second performance, which is a massive improvement over the only previous solution we had under Oracle of using scripted backup and restore, which was something more like a minute. We think this is very useful addition for our use case, which must be a fairly typical one, for which alternative solutions appear to be very hard to find (or are very expensive).

 

The extensions work like this:

 

The checkpoint command has been extended:

 

CHECKPOINT [ ‘name’ ] [ DEFRAG ]

 

and two new commands added:

 

RESTORE [FROM] CHECKPOINT ‘name’;

DROP CHECKPOINT ‘name’;

 

Each named checkpoint exists as a saved copy of the database files stored in a <database>.checkpoints directory stored in the same directory as the main database. The implementation of the extended checkpoint command is fairly straightforward. Once the data is flushed to database files, and the files are closed, a suitably named copy is made to the checkpoint directory before re-opening the database again.

 

The drop command is likewise not much more than a glorified file delete.

 

The hard part is the implementation of RESTORE, which must shutdown the database engine, restore the database files from the checkpoint backup and re-open them all again, whilst keeping existing database sessions open. It does this by trying to lock out the session manager, and all the individual sessions, rolling back any transactions that may be in progress as it goes. It then reloads the database from the checkpoint, and then refreshes the user information in the sessions from the restored user database, and kills off any session for any users that don’t now exist in the database.

 

I’m not sure if my solution is 100% sound. Implementing the above required having to reorganise some of the session locking, which is always a risky proposition. However, It  has been running completely reliably (or so I’m told) in our test system for the last few months, so I’m led to believe there’s at least some merit to it. Therefore I’m submitting the patch in the hope it will be useful to someone.

 

Regards

 

Rob D

 

 


Rob David

Sword Apak
www.sword-apak.com

t +44 1454 871000
f +44 1454 871199
e [hidden email]

Apak House
Badminton Court
Station Road
Yate, Bristol
BS37 5HZ.  United Kingdom

www.sword-group.com 
 

Apak Group Limited, Registered in England No. 01451033.
Registered Office: 1000 Great West Road, Brentford, Middlesex, TW8 9DW.

The information contained in this message or any of its attachments may be privileged and confidential and intended for the exclusive use of the addressee.  The views expressed may not be Apak policy but the personal views of the originator.  If you have received this message in error, please advise the sender immediately by reply e-mail and delete this message and any attachments without retaining a copy.  This footnote also confirms that this email has been swept for the presence of known computer viruses before being sent.



------------------------------------------------------------------------------
Free Next-Gen Firewall Hardware Offer
Buy your Sophos next-gen firewall before the end March 2013
and get the hardware for free! Learn more.
http://p.sf.net/sfu/sophos-d2d-feb
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|

Re: Patch to 2.2.9 for named checkpoints

Fred Toussi-2
Thanks Rob,
 
This feature is certainly useful for large tests.
 
As you say, the restore is the difficult part. You seem to have paid attention to session states.
 
I haven't looked at the patch yet and may or may not use your code, But I will review it and include the required functionality for the next version, which also extends the ORA compatibility mode.
 
Regards
 
Fred Toussi
 
On Fri, Feb 8, 2013, at 17:18, Rob David wrote:

Hi,

 

I have posted a patch on the tracker for a change against 2.2.9 to allow for named checkpoints. This was a much needed feature that we are currently using in our organisation for automated Web UI testing. Our UI test cases typically unavoidably commit transactions, and we are using this feature to quickly wind the database back to a known state between each test case in order to isolate them from each other. We’re currently finding that we are now able to do this with something like sub-second performance, which is a massive improvement over the only previous solution we had under Oracle of using scripted backup and restore, which was something more like a minute. We think this is very useful addition for our use case, which must be a fairly typical one, for which alternative solutions appear to be very hard to find (or are very expensive).

 

The extensions work like this:

 

The checkpoint command has been extended:

 

CHECKPOINT [ ‘name’ ] [ DEFRAG ]

 

and two new commands added:

 

RESTORE [FROM] CHECKPOINT ‘name’;

DROP CHECKPOINT ‘name’;

 

Each named checkpoint exists as a saved copy of the database files stored in a <database>.checkpoints directory stored in the same directory as the main database. The implementation of the extended checkpoint command is fairly straightforward. Once the data is flushed to database files, and the files are closed, a suitably named copy is made to the checkpoint directory before re-opening the database again.

 

The drop command is likewise not much more than a glorified file delete.

 

The hard part is the implementation of RESTORE, which must shutdown the database engine, restore the database files from the checkpoint backup and re-open them all again, whilst keeping existing database sessions open. It does this by trying to lock out the session manager, and all the individual sessions, rolling back any transactions that may be in progress as it goes. It then reloads the database from the checkpoint, and then refreshes the user information in the sessions from the restored user database, and kills off any session for any users that don’t now exist in the database.

 

I’m not sure if my solution is 100% sound. Implementing the above required having to reorganise some of the session locking, which is always a risky proposition. However, It  has been running completely reliably (or so I’m told) in our test system for the last few months, so I’m led to believe there’s at least some merit to it. Therefore I’m submitting the patch in the hope it will be useful to someone.

 

Regards

 

Rob D

 

 


Rob David
Sword Apak

www.sword-apak.com


t   +44 1454 871000
f   +44 1454 871199
e   [hidden email]

Apak House
Badminton Court
Station Road
Yate, Bristol
BS37 5HZ.  United Kingdom

www.sword-group.com 
 

Apak Group Limited, Registered in England No. 01451033.
Registered Office: 1000 Great West Road, Brentford, Middlesex, TW8 9DW.


The information contained in this message or any of its attachments may be privileged and confidential and intended for the exclusive use of the addressee.  The views expressed may not be Apak policy but the personal views of the originator.  If you have received this message in error, please advise the sender immediately by reply e-mail and delete this message and any attachments without retaining a copy.  This footnote also confirms that this email has been swept for the presence of known computer viruses before being sent.


------------------------------------------------------------------------------
Free Next-Gen Firewall Hardware Offer
Buy your Sophos next-gen firewall before the end March 2013
and get the hardware for free! Learn more.
_______________________________________________
Hsqldb-user mailing list
 

------------------------------------------------------------------------------
Free Next-Gen Firewall Hardware Offer
Buy your Sophos next-gen firewall before the end March 2013
and get the hardware for free! Learn more.
http://p.sf.net/sfu/sophos-d2d-feb
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|

Re: Patch to 2.2.9 for named checkpoints

Rob David

Hi Fred,

 

Thanks for the quick response. Very pleased to hear you would like to adopt the features for the next release. In my opinion it would be a very cool USP for HSQL. Hope the code will be useful.

 

Rob

 


Rob David

Sword Apak
www.sword-apak.com

t +44 1454 871000
f +44 1454 871199
e [hidden email]

Apak House
Badminton Court
Station Road
Yate, Bristol
BS37 5HZ.  United Kingdom

www.sword-group.com 
 

From: Fred Toussi [mailto:[hidden email]]
Sent: 08 February 2013 18:49
To: User discussions about HyperSQL Database Engine.
Subject: Re: [Hsqldb-user] Patch to 2.2.9 for named checkpoints

 

Thanks Rob,

 

This feature is certainly useful for large tests.

 

As you say, the restore is the difficult part. You seem to have paid attention to session states.

 

I haven't looked at the patch yet and may or may not use your code, But I will review it and include the required functionality for the next version, which also extends the ORA compatibility mode.

 

Regards

 

Fred Toussi

 

On Fri, Feb 8, 2013, at 17:18, Rob David wrote:

Hi,

 

I have posted a patch on the tracker for a change against 2.2.9 to allow for named checkpoints. This was a much needed feature that we are currently using in our organisation for automated Web UI testing. Our UI test cases typically unavoidably commit transactions, and we are using this feature to quickly wind the database back to a known state between each test case in order to isolate them from each other. We’re currently finding that we are now able to do this with something like sub-second performance, which is a massive improvement over the only previous solution we had under Oracle of using scripted backup and restore, which was something more like a minute. We think this is very useful addition for our use case, which must be a fairly typical one, for which alternative solutions appear to be very hard to find (or are very expensive).

 

The extensions work like this:

 

The checkpoint command has been extended:

 

CHECKPOINT [ ‘name’ ] [ DEFRAG ]

 

and two new commands added:

 

RESTORE [FROM] CHECKPOINT ‘name’;

DROP CHECKPOINT ‘name’;

 

Each named checkpoint exists as a saved copy of the database files stored in a <database>.checkpoints directory stored in the same directory as the main database. The implementation of the extended checkpoint command is fairly straightforward. Once the data is flushed to database files, and the files are closed, a suitably named copy is made to the checkpoint directory before re-opening the database again.

 

The drop command is likewise not much more than a glorified file delete.

 

The hard part is the implementation of RESTORE, which must shutdown the database engine, restore the database files from the checkpoint backup and re-open them all again, whilst keeping existing database sessions open. It does this by trying to lock out the session manager, and all the individual sessions, rolling back any transactions that may be in progress as it goes. It then reloads the database from the checkpoint, and then refreshes the user information in the sessions from the restored user database, and kills off any session for any users that don’t now exist in the database.

 

I’m not sure if my solution is 100% sound. Implementing the above required having to reorganise some of the session locking, which is always a risky proposition. However, It  has been running completely reliably (or so I’m told) in our test system for the last few months, so I’m led to believe there’s at least some merit to it. Therefore I’m submitting the patch in the hope it will be useful to someone.

 

Regards

 

Rob D

 

 


Rob David
Sword Apak
www.sword-apak.com

t

 

+44 1454 871000

f

 

+44 1454 871199

e

 

[hidden email]


Apak House
Badminton Court
Station Road
Yate, Bristol
BS37 5HZ.  United Kingdom
www.sword-group.com 
 

Apak Group Limited, Registered in England No. 01451033.
Registered Office: 1000 Great West Road, Brentford, Middlesex, TW8 9DW.


The information contained in this message or any of its attachments may be privileged and confidential and intended for the exclusive use of the addressee.  The views expressed may not be Apak policy but the personal views of the originator.  If you have received this message in error, please advise the sender immediately by reply e-mail and delete this message and any attachments without retaining a copy.  This footnote also confirms that this email has been swept for the presence of known computer viruses before being sent.

------------------------------------------------------------------------------

Free Next-Gen Firewall Hardware Offer

Buy your Sophos next-gen firewall before the end March 2013

and get the hardware for free! Learn more.

_______________________________________________

Hsqldb-user mailing list

 


------------------------------------------------------------------------------
Free Next-Gen Firewall Hardware Offer
Buy your Sophos next-gen firewall before the end March 2013
and get the hardware for free! Learn more.
http://p.sf.net/sfu/sophos-d2d-feb
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|

Re: Patch to 2.2.9 for named checkpoints

Skörd, Peter
In reply to this post by Fred Toussi-2

Hi Fred,

I read the conversation about named checkpoints, this is a feature that we would be very useful for our acceptance tests, do you know if this will be included in the 2.3 release or will it be 3.0?

Is it included in the snapshot released today 20130226?

BR, Peter

 

From: Fred Toussi [mailto:[hidden email]]
Sent: den 8 februari 2013 19:49
To: User discussions about HyperSQL Database Engine.
Subject: Re: [Hsqldb-user] Patch to 2.2.9 for named checkpoints

 

Thanks Rob,

 

This feature is certainly useful for large tests.

 

As you say, the restore is the difficult part. You seem to have paid attention to session states.

 

I haven't looked at the patch yet and may or may not use your code, But I will review it and include the required functionality for the next version, which also extends the ORA compatibility mode.

 

Regards

 

Fred Toussi

 

On Fri, Feb 8, 2013, at 17:18, Rob David wrote:

Hi,

 

I have posted a patch on the tracker for a change against 2.2.9 to allow for named checkpoints. This was a much needed feature that we are currently using in our organisation for automated Web UI testing. Our UI test cases typically unavoidably commit transactions, and we are using this feature to quickly wind the database back to a known state between each test case in order to isolate them from each other. We’re currently finding that we are now able to do this with something like sub-second performance, which is a massive improvement over the only previous solution we had under Oracle of using scripted backup and restore, which was something more like a minute. We think this is very useful addition for our use case, which must be a fairly typical one, for which alternative solutions appear to be very hard to find (or are very expensive).

 

The extensions work like this:

 

The checkpoint command has been extended:

 

CHECKPOINT [ ‘name’ ] [ DEFRAG ]

 

and two new commands added:

 

RESTORE [FROM] CHECKPOINT ‘name’;

DROP CHECKPOINT ‘name’;

 

Each named checkpoint exists as a saved copy of the database files stored in a <database>.checkpoints directory stored in the same directory as the main database. The implementation of the extended checkpoint command is fairly straightforward. Once the data is flushed to database files, and the files are closed, a suitably named copy is made to the checkpoint directory before re-opening the database again.

 

The drop command is likewise not much more than a glorified file delete.

 

The hard part is the implementation of RESTORE, which must shutdown the database engine, restore the database files from the checkpoint backup and re-open them all again, whilst keeping existing database sessions open. It does this by trying to lock out the session manager, and all the individual sessions, rolling back any transactions that may be in progress as it goes. It then reloads the database from the checkpoint, and then refreshes the user information in the sessions from the restored user database, and kills off any session for any users that don’t now exist in the database.

 

I’m not sure if my solution is 100% sound. Implementing the above required having to reorganise some of the session locking, which is always a risky proposition. However, It  has been running completely reliably (or so I’m told) in our test system for the last few months, so I’m led to believe there’s at least some merit to it. Therefore I’m submitting the patch in the hope it will be useful to someone.

 

Regards

 

Rob D

 

 


Rob David
Sword Apak
www.sword-apak.com

t

 

+44 1454 871000

f

 

+44 1454 871199

e

 

[hidden email]


Apak House
Badminton Court
Station Road
Yate, Bristol
BS37 5HZ.  United Kingdom
www.sword-group.com 
 

Apak Group Limited, Registered in England No. 01451033.
Registered Office: 1000 Great West Road, Brentford, Middlesex, TW8 9DW.


The information contained in this message or any of its attachments may be privileged and confidential and intended for the exclusive use of the addressee.  The views expressed may not be Apak policy but the personal views of the originator.  If you have received this message in error, please advise the sender immediately by reply e-mail and delete this message and any attachments without retaining a copy.  This footnote also confirms that this email has been swept for the presence of known computer viruses before being sent.

------------------------------------------------------------------------------

Free Next-Gen Firewall Hardware Offer

Buy your Sophos next-gen firewall before the end March 2013

and get the hardware for free! Learn more.

_______________________________________________

Hsqldb-user mailing list

 

 

Click here to report this email as spam.


------------------------------------------------------------------------------
Everyone hates slow websites. So do we.
Make your web apps faster with AppDynamics
Download AppDynamics Lite for free today:
http://p.sf.net/sfu/appdyn_d2d_feb
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|

Re: Patch to 2.2.9 for named checkpoints

Fred Toussi-2
Hi Peter,
 
This won't go into 2.3.0, which is due soon. The likely target is 2.3.1 in a few months time.
 
I have looked at the code. It shouldn't bee too difficult to insert the functionality into 2.3.0 code, as the classes have not changed a lot between 2.2.9 and 2.3.0.
 
Fred
 
 
On Tue, Feb 26, 2013, at 9:46, Skörd, Peter wrote:

Hi Fred,

I read the conversation about named checkpoints, this is a feature that we would be very useful for our acceptance tests, do you know if this will be included in the 2.3 release or will it be 3.0?

Is it included in the snapshot released today 20130226?

BR, Peter

 

From: Fred Toussi [mailto:[hidden email]]
Sent: den 8 februari 2013 19:49
To: User discussions about HyperSQL Database Engine.
Subject: Re: [Hsqldb-user] Patch to 2.2.9 for named checkpoints

 

Thanks Rob,

 

This feature is certainly useful for large tests.

 

As you say, the restore is the difficult part. You seem to have paid attention to session states.

 

I haven't looked at the patch yet and may or may not use your code, But I will review it and include the required functionality for the next version, which also extends the ORA compatibility mode.

 

Regards

 

Fred Toussi

 

On Fri, Feb 8, 2013, at 17:18, Rob David wrote:

Hi,

 

I have posted a patch on the tracker for a change against 2.2.9 to allow for named checkpoints. This was a much needed feature that we are currently using in our organisation for automated Web UI testing. Our UI test cases typically unavoidably commit transactions, and we are using this feature to quickly wind the database back to a known state between each test case in order to isolate them from each other. We’re currently finding that we are now able to do this with something like sub-second performance, which is a massive improvement over the only previous solution we had under Oracle of using scripted backup and restore, which was something more like a minute. We think this is very useful addition for our use case, which must be a fairly typical one, for which alternative solutions appear to be very hard to find (or are very expensive).

 

The extensions work like this:

 

The checkpoint command has been extended:

 

CHECKPOINT [ ‘name’ ] [ DEFRAG ]

 

and two new commands added:

 

RESTORE [FROM] CHECKPOINT ‘name’;

DROP CHECKPOINT ‘name’;

 

Each named checkpoint exists as a saved copy of the database files stored in a <database>.checkpoints directory stored in the same directory as the main database. The implementation of the extended checkpoint command is fairly straightforward. Once the data is flushed to database files, and the files are closed, a suitably named copy is made to the checkpoint directory before re-opening the database again.

 

The drop command is likewise not much more than a glorified file delete.

 

The hard part is the implementation of RESTORE, which must shutdown the database engine, restore the database files from the checkpoint backup and re-open them all again, whilst keeping existing database sessions open. It does this by trying to lock out the session manager, and all the individual sessions, rolling back any transactions that may be in progress as it goes. It then reloads the database from the checkpoint, and then refreshes the user information in the sessions from the restored user database, and kills off any session for any users that don’t now exist in the database.

 

I’m not sure if my solution is 100% sound. Implementing the above required having to reorganise some of the session locking, which is always a risky proposition. However, It  has been running completely reliably (or so I’m told) in our test system for the last few months, so I’m led to believe there’s at least some merit to it. Therefore I’m submitting the patch in the hope it will be useful to someone.

 

Regards

 

Rob D

 

 


Rob David
Sword Apak
www.sword-apak.com

t

 

+44 1454 871000

f

 

+44 1454 871199

e

 

[hidden email]


Apak House
Badminton Court
Station Road
Yate, Bristol
BS37 5HZ.  United Kingdom

www.sword-group.com 
 

Apak Group Limited, Registered in England No. 01451033.
Registered Office: 1000 Great West Road, Brentford, Middlesex, TW8 9DW.


The information contained in this message or any of its attachments may be privileged and confidential and intended for the exclusive use of the addressee.  The views expressed may not be Apak policy but the personal views of the originator.  If you have received this message in error, please advise the sender immediately by reply e-mail and delete this message and any attachments without retaining a copy.  This footnote also confirms that this email has been swept for the presence of known computer viruses before being sent.

------------------------------------------------------------------------------

Free Next-Gen Firewall Hardware Offer

Buy your Sophos next-gen firewall before the end March 2013

and get the hardware for free! Learn more.

_______________________________________________

Hsqldb-user mailing list

 

 

Click here to report this email as spam.

------------------------------------------------------------------------------
Everyone hates slow websites. So do we.
Make your web apps faster with AppDynamics
Download AppDynamics Lite for free today:
_______________________________________________
Hsqldb-user mailing list
 

------------------------------------------------------------------------------
Everyone hates slow websites. So do we.
Make your web apps faster with AppDynamics
Download AppDynamics Lite for free today:
http://p.sf.net/sfu/appdyn_d2d_feb
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user