Quantcast

Firebird Transaction ID limit solution

classic Classic list List threaded Threaded
88 messages Options
12345
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Firebird Transaction ID limit solution

Yi Lu
In the past week, we have a few databases from several customers run out of the 2^31-1 transaction ID limit. After some research I found this problem to have been around since five years ago. Some suggestions were given such as this thread,
http://firebird.1100200.n4.nabble.com/discussion-about-real-fix-for-CORE-2348-td3478013.html
but they requires off-time.

While we've made our efforts to reduce number of transactions from application level, we still need a thorough solution.

1.I wonder if anyone now has a proper solution to this issue, without requireing shutting down the system.

2. If we have no option other than modifying Firebird source code, can anyone shed some light on how complicated this change will be and how wide the change would affect? Any advice on where to make the code change will be appreciated.

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Firebird Transaction ID limit solution

Dimitry Sibiryakov-3
24.12.2011 1:37, Yi Lu wrote:
> 1.I wonder if anyone now has a proper solution to this issue, without
> requireing shutting down the system.

   Set up cluster. While one node is down for maintenance, users can work with other(s).

--
   SY, SD.

------------------------------------------------------------------------------
Write once. Port to many.
Get the SDK and tools to simplify cross-platform app development. Create
new or port existing apps to sell to consumers worldwide. Explore the
Intel AppUpSM program developer opportunity. appdeveloper.intel.com/join
http://p.sf.net/sfu/intel-appdev
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Firebird Transaction ID limit solution

Dmitry Kuzmenko-3
In reply to this post by Yi Lu
Hello, Yi!

Saturday, December 24, 2011, 4:37:08 AM, you wrote:

YL> In the past week, we have a few databases from several customers run out of
YL> the 2^31-1 transaction ID limit. After some research I found this problem to
YL> have been around since five years ago. Some suggestions were given such as
YL> this thread,
YL> http://firebird.1100200.n4.nabble.com/discussion-about-real-fix-for-CORE-2348-td3478013.html
YL> but they requires off-time.

YL> While we've made our efforts to reduce number of transactions from
YL> application level, we still need a thorough solution.

How long database "live" until it reach transaction limit?
How many users work with databases? How long during the day they
work with database?
How many transactions you have each day?
Can you give gstat -h information ?

YL> 1.I wonder if anyone now has a proper solution to this issue, without
YL> requireing shutting down the system.

you can't reset transaction numbers without doing backup/restore.

YL> 2. If we have no option other than modifying Firebird source code, can
YL> anyone shed some light on how complicated this change will be and how wide
YL> the change would affect? Any advice on where to make the code change will be
YL> appreciated.

What Firebird version do you use? Extending transaction numbers from
32 to 64 bit is not only change of the sources, but also change of
ODS.
So, this will not be done in any existing versions, including 2.5.

Extending transaction numbers can be done in Firebird 3.0, but
I haven't found this in it's latest roadmap.

--
Dmitry Kuzmenko, www.ibase.ru, (495) 953-13-34


------------------------------------------------------------------------------
Write once. Port to many.
Get the SDK and tools to simplify cross-platform app development. Create
new or port existing apps to sell to consumers worldwide. Explore the
Intel AppUpSM program developer opportunity. appdeveloper.intel.com/join
http://p.sf.net/sfu/intel-appdev
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Firebird Transaction ID limit solution - Email found in subject

Leyne, Sean
In reply to this post by Dimitry Sibiryakov-3
Dimitry,

A cluster won't help, since all databases would see the same number of transactions, the number of which is the problem.


Sean

________________________________________
From: Dimitry Sibiryakov [[hidden email]]
Sent: Saturday, December 24, 2011 4:29 AM
To: For discussion among Firebird Developers
Subject: Re: [Firebird-devel] Firebird Transaction ID limit solution - Email found in subject

24.12.2011 1:37, Yi Lu wrote:
> 1.I wonder if anyone now has a proper solution to this issue, without
> requireing shutting down the system.

   Set up cluster. While one node is down for maintenance, users can work with other(s).

--
   SY, SD.

------------------------------------------------------------------------------
Write once. Port to many.
Get the SDK and tools to simplify cross-platform app development. Create
new or port existing apps to sell to consumers worldwide. Explore the
Intel AppUpSM program developer opportunity. appdeveloper.intel.com/join
http://p.sf.net/sfu/intel-appdev
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel

------------------------------------------------------------------------------
Write once. Port to many.
Get the SDK and tools to simplify cross-platform app development. Create
new or port existing apps to sell to consumers worldwide. Explore the
Intel AppUpSM program developer opportunity. appdeveloper.intel.com/join
http://p.sf.net/sfu/intel-appdev
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Firebird Transaction ID limit solution - Email found in subject

Dimitry Sibiryakov-3
25.12.2011 1:00, Leyne, Sean wrote:
> A cluster won't help, since all databases would see the same number of transactions, the number of which is the problem.

   No, if several transactions during transfer between nodes are merged into one.
   Besides, there is no need for all nodes to start from the same counter value.

--
   SY, SD.

------------------------------------------------------------------------------
Write once. Port to many.
Get the SDK and tools to simplify cross-platform app development. Create
new or port existing apps to sell to consumers worldwide. Explore the
Intel AppUpSM program developer opportunity. appdeveloper.intel.com/join
http://p.sf.net/sfu/intel-appdev
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Firebird Transaction ID limit solution - Email found in subject

Leyne, Sean
In reply to this post by Leyne, Sean
Dimitry,

> 27.12.2011 19:17, Leyne, Sean wrote:
> > That type of solution is not what I would define as a cluster.
>
>    As you wish. But the rest of world consider this kind of system to be called
> "a shared-nothing cluster".

You are correct!  "a shared-nothing cluster" is a type of cluster, one that is used for a number of database cluster solutions.

I should have said:

"That type of solution is not what immediately comes to mind for me, since I see a shared disk solution (using redundant SAN storage) to be much easier to implement for FB."


Sean


------------------------------------------------------------------------------
Write once. Port to many.
Get the SDK and tools to simplify cross-platform app development. Create
new or port existing apps to sell to consumers worldwide. Explore the
Intel AppUpSM program developer opportunity. appdeveloper.intel.com/join
http://p.sf.net/sfu/intel-appdev
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Firebird Transaction ID limit solution

Dimitry Sibiryakov-3
27.12.2011 22:17, Leyne, Sean wrote:
> I should have said:
>
> "That type of solution is not what immediately comes to mind for me, since I see a shared disk solution (using redundant SAN storage) to be much easier to implement for FB."

   Unfortunately, shared-storage cluster doesn't solve transaction limit problem.
   BTW, distributed lock manager also isn't a trivial thing. And is quite slow.

--
   SY, SD.

------------------------------------------------------------------------------
Write once. Port to many.
Get the SDK and tools to simplify cross-platform app development. Create
new or port existing apps to sell to consumers worldwide. Explore the
Intel AppUpSM program developer opportunity. appdeveloper.intel.com/join
http://p.sf.net/sfu/intel-appdev
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Firebird Transaction ID limit solution

Yi Lu
In reply to this post by Dmitry Kuzmenko-3
One of our busy sites has more than 120 TPS and it cannot last longer than 6 month. The database is online 24-7 and number of simultaneous users ranging from 0 to 50. Currently, its transaction number is at 2,089,589,687

By calculation, an average of 39 transactions per second (TPS) corresponds to 2 year of life.  39 TPS is not unrealistically intensive in modern age.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Firebird Transaction ID limit solution

Yi Lu
It is feasible to roll over the transaction ID without putting the database offline? i.e. when ID is close to limit, reset it to 0 from the code?
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Firebird Transaction ID limit solution

Dimitry Sibiryakov-3
28.12.2011 17:34, Yi Lu wrote:
> It is feasible to roll over the transaction ID without putting the database
> offline? i.e. when ID is close to limit, reset it to 0 from the code?

   It will result in comple data loss. "DROP DATABASE" has the same result, but easier to do.

--
   SY, SD.

------------------------------------------------------------------------------
Ridiculously easy VDI. With Citrix VDI-in-a-Box, you don't need a complex
infrastructure or vast IT resources to deliver seamless, secure access to
virtual desktops. With this all-in-one solution, easily deploy virtual
desktops for less than the cost of PCs and save 60% on VDI infrastructure
costs. Try it free! http://p.sf.net/sfu/Citrix-VDIinabox
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Firebird Transaction ID limit solution

Pierre Y.
In reply to this post by Yi Lu
On Wed, Dec 28, 2011 at 5:34 PM, Yi Lu <[hidden email]> wrote:
> It is feasible to roll over the transaction ID without putting the database
> offline? i.e. when ID is close to limit, reset it to 0 from the code?

Seems that PostgreSQL AutoVacuum daemon do that for Postgresql databases :

http://www.postgresql.org/docs/8.3/static/routine-vacuuming.html

"PostgreSQL's VACUUM command has to run on a regular basis for several reasons:
...
 To protect against loss of very old data due to transaction ID wraparound"

------------------------------------------------------------------------------
Ridiculously easy VDI. With Citrix VDI-in-a-Box, you don't need a complex
infrastructure or vast IT resources to deliver seamless, secure access to
virtual desktops. With this all-in-one solution, easily deploy virtual
desktops for less than the cost of PCs and save 60% on VDI infrastructure
costs. Try it free! http://p.sf.net/sfu/Citrix-VDIinabox
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Firebird Transaction ID limit solution - Email found in subject

Leyne, Sean
In reply to this post by Leyne, Sean

> It is feasible to roll over the transaction ID without putting the database
> offline? i.e. when ID is close to limit, reset it to 0 from the code?

In theory there is a "simple" code change which would provide you another 6 months breathing room.

But there is no permanent solution which is currently available, and none that will be available in 6 months.  You will need to perform a backup/restore at some point.

The "simple" solution is to change the datatype of Transaction related variables from SLONG* to ULONG. The reality of this solution is, unfortunately, far uglier given the testing required to confirm that all references have been changed.


Sean


* for the life of me I don't understand why signed types where used for variables which could only contain positive values -- this is a common problem which is throughout the codebase.


------------------------------------------------------------------------------
Ridiculously easy VDI. With Citrix VDI-in-a-Box, you don't need a complex
infrastructure or vast IT resources to deliver seamless, secure access to
virtual desktops. With this all-in-one solution, easily deploy virtual
desktops for less than the cost of PCs and save 60% on VDI infrastructure
costs. Try it free! http://p.sf.net/sfu/Citrix-VDIinabox
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Firebird Transaction ID limit solution - Email found in subject

Alex Peshkoff
 On 12/28/11 22:51, Leyne, Sean wrote:

>> It is feasible to roll over the transaction ID without putting the database
>> offline? i.e. when ID is close to limit, reset it to 0 from the code?
> In theory there is a "simple" code change which would provide you another 6 months breathing room.
>
> But there is no permanent solution which is currently available, and none that will be available in 6 months.  You will need to perform a backup/restore at some point.
>
> The "simple" solution is to change the datatype of Transaction related variables from SLONG* to ULONG. The reality of this solution is, unfortunately, far uglier given the testing required to confirm that all references have been changed.
>
>
> Sean
>
>
> * for the life of me I don't understand why signed types where used for variables which could only contain positive values -- this is a common problem which is throughout the codebase.

This change was done in trunk. The reason for signed type here was (at
least visible reason) very simple - into some functions using same
parameter might be passed transaction number (positive value) and
something else (negative value). I.e. negative sign meant 'this is not
transaction' and function behaved according to it.

Please do not treat it as an advice to use trunk in production!!!


------------------------------------------------------------------------------
Ridiculously easy VDI. With Citrix VDI-in-a-Box, you don't need a complex
infrastructure or vast IT resources to deliver seamless, secure access to
virtual desktops. With this all-in-one solution, easily deploy virtual
desktops for less than the cost of PCs and save 60% on VDI infrastructure
costs. Try it free! http://p.sf.net/sfu/Citrix-VDIinabox
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Firebird Transaction ID limit solution - Email found in subject

Leyne, Sean
Alex,

> > The "simple" solution is to change the datatype of Transaction related
> variables from SLONG* to ULONG. The reality of this solution is,
> unfortunately, far uglier given the testing required to confirm that all
> references have been changed.
> >
> >
> > Sean
> >
> >
> > * for the life of me I don't understand why signed types where used for
> variables which could only contain positive values -- this is a common
> problem which is throughout the codebase.
>
> This change was done in trunk.

To be clear, you have a code-branch that uses ULONG for transaction ID?


> The reason for signed type here was (at least visible reason) very simple
>  - into some functions using same parameter might
> be passed transaction number (positive value) and something else (negative
> value). I.e. negative sign meant 'this is not transaction' and function behaved
> according to it.

And some people have complained about some of my suggestions as being "hacks"!!!


Sean


------------------------------------------------------------------------------
Ridiculously easy VDI. With Citrix VDI-in-a-Box, you don't need a complex
infrastructure or vast IT resources to deliver seamless, secure access to
virtual desktops. With this all-in-one solution, easily deploy virtual
desktops for less than the cost of PCs and save 60% on VDI infrastructure
costs. Try it free! http://p.sf.net/sfu/Citrix-VDIinabox
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Firebird Transaction ID limit solution - Email found in subject

Jesús García
In reply to this post by Alex Peshkoff

> This change was done in trunk. The reason for signed type here was (at
> least visible reason) very simple - into some functions using same
> parameter might be passed transaction number (positive value) and
> something else (negative value). I.e. negative sign meant 'this is not
> transaction' and function behaved according to it.
>
Would not be better, instead of that, If transaction id is equal To 0, no transaction, else transaction.

As now there is a problem with transactionid and heavy loaded systems, that could solve in a little the problem.

Jesus
------------------------------------------------------------------------------
Ridiculously easy VDI. With Citrix VDI-in-a-Box, you don't need a complex
infrastructure or vast IT resources to deliver seamless, secure access to
virtual desktops. With this all-in-one solution, easily deploy virtual
desktops for less than the cost of PCs and save 60% on VDI infrastructure
costs. Try it free! http://p.sf.net/sfu/Citrix-VDIinabox
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Firebird Transaction ID limit solution - Email found in subject

Dimitry Sibiryakov-3
29.12.2011 20:41, Jesus Garcia wrote:
> Would not be better, instead of that, If transaction id is equal To 0, no transaction, else transaction.

   There is transaction number zero.

> As now there is a problem with transactionid and heavy loaded systems, that could solve in a little the problem.

   Heavy loaded systems should use clusters. That's all. While one node is on maintenance,
others do all work. These systems need clusters anyway for high availability and/or load
balancing. One can't be serious running critical systems on a single server.

--
   SY, SD.

------------------------------------------------------------------------------
Ridiculously easy VDI. With Citrix VDI-in-a-Box, you don't need a complex
infrastructure or vast IT resources to deliver seamless, secure access to
virtual desktops. With this all-in-one solution, easily deploy virtual
desktops for less than the cost of PCs and save 60% on VDI infrastructure
costs. Try it free! http://p.sf.net/sfu/Citrix-VDIinabox
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Firebird Transaction ID limit solution - Email found in subject

Jesús García

  Heavy loaded systems should use clusters. That's all. While one node is on maintenance,
others do all work. These systems need clusters anyway for high availability and/or load
balancing. One can't be serious running critical systems on a single server.

I don't agree with you. You generalize, and is not allways good.

From your POV what is not serious is having an "enterprise database" with that serious and important restriction.

I have not readed until your posts that for heavy loaded systems is necessary one  cluster of firebird, and what is worse, who don't use them is not serious.

Replication is not easy with firebird, and is not native in the engine. There is so many problems with replication depending on the features of firebird used. Is different use firebird for store data in tables than programming business logic in database and use user restrictions, triggers, etc.

I do not use any other rdbms, but is there the same problem with postgre, Oracle, sqlserver, informix, db2, etc.?

Don't misinterpret my words, i love firebird.

Jesus


------------------------------------------------------------------------------
Ridiculously easy VDI. With Citrix VDI-in-a-Box, you don't need a complex
infrastructure or vast IT resources to deliver seamless, secure access to
virtual desktops. With this all-in-one solution, easily deploy virtual
desktops for less than the cost of PCs and save 60% on VDI infrastructure
costs. Try it free! http://p.sf.net/sfu/Citrix-VDIinabox
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Firebird Transaction ID limit solution

Leyne, Sean
In reply to this post by Yi Lu


>    There is transaction number zero.

Actually, all databases are initialized with transaction #1 as the starting value, so there should not be any transaction 0.


Sean


------------------------------------------------------------------------------
Ridiculously easy VDI. With Citrix VDI-in-a-Box, you don't need a complex
infrastructure or vast IT resources to deliver seamless, secure access to
virtual desktops. With this all-in-one solution, easily deploy virtual
desktops for less than the cost of PCs and save 60% on VDI infrastructure
costs. Try it free! http://p.sf.net/sfu/Citrix-VDIinabox
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Firebird Transaction ID limit solution - Email found in subject

Ann Harrison
In reply to this post by Leyne, Sean
Sean,

>  Alex wrote:

>>  - into some functions using same parameter might
>> be passed transaction number (positive value) and something else (negative
>> value). I.e. negative sign meant 'this is not transaction' and function behaved
>> according to it.
>
> And some people have complained about some of my suggestions as being "hacks"!!!
>

Actually, there is at least one other "special" value for transaction
ids.  Zero is always
the system transaction.  If you consider that a signed long is
"retirement proof", which
we did, using the other half for something else doesn't seem so bad.
Particularly if
you cut your programming teeth in a 64Kb address space.

Maybe it's time to look at all the small integers as well.

Cheers,

Ann

------------------------------------------------------------------------------
Ridiculously easy VDI. With Citrix VDI-in-a-Box, you don't need a complex
infrastructure or vast IT resources to deliver seamless, secure access to
virtual desktops. With this all-in-one solution, easily deploy virtual
desktops for less than the cost of PCs and save 60% on VDI infrastructure
costs. Try it free! http://p.sf.net/sfu/Citrix-VDIinabox
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Firebird Transaction ID limit solution - Email found in subject

Dmitry Yemanov-3
In reply to this post by Leyne, Sean
29.12.2011 22:05, Leyne, Sean wrote:
>
>> This change was done in trunk.
>
> To be clear, you have a code-branch that uses ULONG for transaction ID?

Trunk is the ongoing development branch (formerly known as HEAD in CVS),
i.e. transaction IDs are already unsigned long in FB 3.0.


Dmitry

------------------------------------------------------------------------------
Ridiculously easy VDI. With Citrix VDI-in-a-Box, you don't need a complex
infrastructure or vast IT resources to deliver seamless, secure access to
virtual desktops. With this all-in-one solution, easily deploy virtual
desktops for less than the cost of PCs and save 60% on VDI infrastructure
costs. Try it free! http://p.sf.net/sfu/Citrix-VDIinabox
Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
12345
Loading...