Board index » delphi » Re: IB2007 stability

Re: IB2007 stability


2007-06-27 02:35:18 PM
delphi237
Regarding mismatched clients between IB7.1 and IB2007, is it a problem? All
my clients are still running gds32.dll ver 7.1.
Regards
Brian
"Brian Ellul" <XXXX@XXXXX.COM>writes
Quote
Windows 2003 SP2, IB2007. Freshly installed server.

This morning as the the user started logging in, I was called and found
out that no-one could get in the database. I looked in the log and found
this error.

JERRY (Server) Tue Jun 26 08:12:41 2007
Database: D:\ACCOUNTS\DATABASE\EC.GDB
internal gds software consistency check (exceeded index level (206), file:
btr.c line: 3248)

After a gfix, more errors came up......

JERRY (Server) Tue Jun 26 08:24:03 2007
Database: D:\ACCOUNTS\DATABASE\EC.GDB
Index RDB$PRIMARY161 is corrupt (missing entries) in table STUDENTBOOKINGS
(205)
JERRY (Server) Tue Jun 26 08:24:03 2007
Database: D:\ACCOUNTS\DATABASE\EC.GDB
Index STUDENTBOOKINGS_INDX7 is corrupt on page 111145 in table
STUDENTBOOKINGS (205)
JERRY (Server) Tue Jun 26 08:24:07 2007
Database: D:\ACCOUNTS\DATABASE\EC.GDB
Index STUDENTBOOKINGS_INDX15 is corrupt (missing entries) in table
STUDENTBOOKINGS (205)
JERRY (Server) Tue Jun 26 08:24:07 2007
Database: D:\ACCOUNTS\DATABASE\EC.GDB
Index STUDENTBOOKINGS_INDX16 is corrupt (missing entries) in table
STUDENTBOOKINGS (205)
JERRY (Server) Tue Jun 26 08:24:08 2007
Database: D:\ACCOUNTS\DATABASE\EC.GDB
Index STUDENTBOOKINGS_INDX3 is corrupt (missing entries) in table
STUDENTBOOKINGS (205)
JERRY (Server) Tue Jun 26 08:24:19 2007
Database: D:\ACCOUNTS\DATABASE\EC.GDB
Index TEMP_AGENTPERFORMANCE_INDX2 has orphan child page at page 124738 in
table TEMP_AGENTPERFORMANCE (216)
JERRY (Server) Tue Jun 26 08:24:49 2007
Database: D:\ACCOUNTS\DATABASE\EC.GDB
Page 119055 is an orphan

and a lot of more of these orphan pages errors...

OMG!!!! I haven't had a corruption like this since I was using IB5.6!
IB7.1 was quite stable for me.

Well I did a gfix -mend followed by a backup and restore and the database
was up and running. Now something else just happened. I found out that in
why of my tables I have duplicates Primary Keys... Yes, hard to believe
but I found 8 duplicates records, obviously this screwing up my
application. Unfortunately, I can not check the DB right now but I assume
that I have again a corruption.

Is there anywhere I can start looking to solve these severe problems? The
duplicates Primary Key is the one which is troubling me most (I remember
this happening on IB5.6, but then it seems it got fixed). I read Craig's
article on DB corruption and how to prevent but none seem to effect me.

Regards
Brian




 
 

Re: IB2007 stability

Hi
People following this newsgroup might remember that a few months ago we also
experienced problems after enabling group commit. The problems appeared with
IB 2007 and IB 7.5.1.
Remembering Bill Todds presentation at DevCon 2005 in SF and reading other
related articles I had the notion that enabling group commits should be the
"least dangerous way" to achieve higher performance. Disabling forced writes
is at the other end the most dangerous thing to do.
After using Interbase for 10+ years the current state for us is that we have
a number of customer sites running with forced writes disabled (experiencing
blazing IB-speed) and we haven't had a single problem with these sites. On
the other hand at the sites where we enabled group commit we more or less
immediately ran into severe problems. I'd appreciate to hear some
comment from CodeGear on this. All the dangers related to tweaking
performance settings in Interbase should cause data corruption ONLY when you
experience hardware problems of some kind. It is horrendous that a
performance setting should be able to corrupt a database when everything
else (hardware etc.) is working fine.
Journaling in IB2007 is of course also a safe way to increase performance
but it is a little bit more complicated to set up than to just
enable/disable a setting in the database itself.
Regards,
Kjell Ljungqvist
"Brian Ellul" <XXXX@XXXXX.COM>writes
Quote
Hi Dan

Great, this maybe was the reason for this severe corruption. I am running
six servers and enabled Group Commits on just one, the one which got
currupted (well, this server is the most heavily used also), so maybe by
disabling this option I will minimise the risk of corruption.

Thanks

"Dan Palley" <XXXX@XXXXX.COM>writes
news:46816f02$XXXX@XXXXX.COM...
>Brian, in my 10 or so years of using Interbase heavily, I have never had
>corruption until I enabled Group Commits. Fortunately, group commit
>seems to be deprecated or at least de-emphasized in IB 2007, replaced by
>journaling.
>
>Dan
>
>"Brian Ellul" <XXXX@XXXXX.COM>writes
>news:46815490$XXXX@XXXXX.COM...
>>Hi Dan
>>
>>Forced Writes = Off
>>But
>>Group Commit = Yes. I will switch this off immediately.
>>
>>Regards
>>Brian
>>
>>"Dan Palley" <XXXX@XXXXX.COM>writes
>>news:XXXX@XXXXX.COM...
>>>
>>>"Brian Ellul" <XXXX@XXXXX.COM>writes
>>>news:XXXX@XXXXX.COM...
>>>>Windows 2003 SP2, IB2007. Freshly installed server.
>>>>
>>>>This morning as the the user started logging in, I was called and
>>>>found out that no-one could get in the database. I looked in the log
>>>>and found this error.
>>>>
>>>>OMG!!!! I haven't had a corruption like this since I was using IB5.6!
>>>>IB7.1 was quite stable for me.
>>>>
>>>>Is there anywhere I can start looking to solve these severe problems?
>>>>The duplicates Primary Key is the one which is troubling me most (I
>>>>remember this happening on IB5.6, but then it seems it got fixed). I
>>>>read Craig's article on DB corruption and how to prevent but none seem
>>>>to effect me.
>>>
>>>By any chance do you have Forced Writes disabled or Group Commit
>>>enabled? I had a lot of problems with Group Commit in IB 7.5.x.
>>>
>>>Dan
>>>
>>
>>
>
>


 

Re: IB2007 stability

Brian Ellul writes:
Quote
Records in this table: 459132
Page Size = 8192

Index RDB$PRIMARY161 (0)
Depth: 2, leaf buckets: 406, nodes: 459132
Average data length: 1.00, total dup: 0, max dup: 0
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 13
60 - 79% = 2
80 - 99% = 391

Index STUDENTBOOKINGS_INDX7 (1)
Depth: 2, leaf buckets: 389, nodes: 459132
Average data length: 0.00, total dup: 452666, max dup: 2958
Fill distribution:
0 - 19% = 1
20 - 39% = 2
40 - 59% = 90
60 - 79% = 3
80 - 99% = 293

Index STUDENTBOOKINGS_INDX3 (8)
Depth: 2, leaf buckets: 389, nodes: 459132
Average data length: 0.00, total dup: 139434, max dup: 5
Fill distribution:
0 - 19% = 0
20 - 39% = 1
40 - 59% = 19
60 - 79% = 1
80 - 99% = 368

Index STUDENTBOOKINGS_INDX15 (5)
Depth: 2, leaf buckets: 377, nodes: 459132
Average data length: 0.00, total dup: 363562, max dup: 23
Fill distribution:
0 - 19% = 0
20 - 39% = 1
40 - 59% = 49
60 - 79% = 2
80 - 99% = 325

Index STUDENTBOOKINGS_INDX16 (6)
Depth: 2, leaf buckets: 406, nodes: 459132
Average data length: 1.00, total dup: 0, max dup: 0
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 13
60 - 79% = 2
80 - 99% = 391

"Quinn Wildman (CodeGear Developer Support)"
<XXXX@XXXXX.COM>writes
news:46813e39$XXXX@XXXXX.COM...
>If you run gstat, what is the index depth for the index in
>question? Approximately how records does the studentbookings table
>have? What is your database page size?
>
>Brian Ellul writes:
>>Windows 2003 SP2, IB2007. Freshly installed server.
>>
>>This morning as the the user started logging in, I was called and
>>found out that no-one could get in the database. I looked in the
>>log and found this error.
>>
>>JERRY (Server) Tue Jun 26 08:12:41 2007
>>Database: D:\ACCOUNTS\DATABASE\EC.GDB
>>internal gds software consistency check (exceeded index level
>>(206), file: btr.c line: 3248)
>>
>>After a gfix, more errors came up......
>>
>>JERRY (Server) Tue Jun 26 08:24:03 2007
>>Database: D:\ACCOUNTS\DATABASE\EC.GDB
>>Index RDB$PRIMARY161 is corrupt (missing entries) in table
>>STUDENTBOOKINGS (205) JERRY (Server) Tue Jun 26 08:24:03 2007
>>Database: D:\ACCOUNTS\DATABASE\EC.GDB
>>Index STUDENTBOOKINGS_INDX7 is corrupt on page 111145 in table
>>STUDENTBOOKINGS (205) JERRY (Server) Tue Jun 26 08:24:07 2007
>>Database: D:\ACCOUNTS\DATABASE\EC.GDB
>>Index STUDENTBOOKINGS_INDX15 is corrupt (missing entries) in
>>table STUDENTBOOKINGS (205) JERRY (Server) Tue Jun 26 08:24:07
>>2007
>>Database: D:\ACCOUNTS\DATABASE\EC.GDB
>>Index STUDENTBOOKINGS_INDX16 is corrupt (missing entries) in
>>table STUDENTBOOKINGS (205) JERRY (Server) Tue Jun 26 08:24:08
>>2007
>>Database: D:\ACCOUNTS\DATABASE\EC.GDB
>>Index STUDENTBOOKINGS_INDX3 is corrupt (missing entries) in table
>>STUDENTBOOKINGS (205) JERRY (Server) Tue Jun 26 08:24:19 2007
>>Database: D:\ACCOUNTS\DATABASE\EC.GDB
>>Index TEMP_AGENTPERFORMANCE_INDX2 has orphan child page at page
>>124738 in table TEMP_AGENTPERFORMANCE (216) JERRY (Server) Tue
>>Jun 26 08:24:49 2007
>>Database: D:\ACCOUNTS\DATABASE\EC.GDB
>>Page 119055 is an orphan
>>
>>and a lot of more of these orphan pages errors...
>>
>>OMG!!!! I haven't had a corruption like this since I was using
>>IB5.6! IB7.1 was quite stable for me.
>>
>>Well I did a gfix -mend followed by a backup and restore and the
>>database was up and running. Now something else just happened. I
>>found out that in why of my tables I have duplicates Primary
>>Keys... Yes, hard to believe but I found 8 duplicates records,
>>obviously this screwing up my application. Unfortunately, I
>>can't check the DB right now but I assume that I have again a
>>corruption.
>>
>>Is there anywhere I can start looking to solve these severe
>>problems? The duplicates Primary Key is the one which is
>>troubling me most (I remember this happening on IB5.6, but then
>>it seems it got fixed). I read Craig's article on DB corruption
>>and how to prevent but none seem to effect me.
>>
>>Regards
>>Brian
>>
>>
>>
I would guess that :
RDB$PRIMARY161
and
STUDENTBOOKINGS_INDX16
are the same indexes...If they are get rid of STUDENTBOOKINGS_INDX16.
--
Rob Schieck (TeamB)
 

Re: IB2007 stability

After you backup, do what ever you have to, to get all of your stored
procedures and triggers to re-compile. Drop them and re-add them if
required.
A backup and restore does not recompile your SPs and Triggers so you
have to.
hth
--
Rob Schieck (TeamB)
 

Re: IB2007 stability

"Robert Schieck (TeamB)" <XXXX@XXXXX.COM>writes
Quote
Brian Ellul writes:

>Records in this table: 459132
>Page Size = 8192
>
>Index RDB$PRIMARY161 (0)
>Depth: 2, leaf buckets: 406, nodes: 459132
>Average data length: 1.00, total dup: 0, max dup: 0
>Fill distribution:
>0 - 19% = 0
>20 - 39% = 0
>40 - 59% = 13
>60 - 79% = 2
>80 - 99% = 391
>
>Index STUDENTBOOKINGS_INDX7 (1)
>Depth: 2, leaf buckets: 389, nodes: 459132
>Average data length: 0.00, total dup: 452666, max dup: 2958
>Fill distribution:
>0 - 19% = 1
>20 - 39% = 2
>40 - 59% = 90
>60 - 79% = 3
>80 - 99% = 293
>
>Index STUDENTBOOKINGS_INDX3 (8)
>Depth: 2, leaf buckets: 389, nodes: 459132
>Average data length: 0.00, total dup: 139434, max dup: 5
>Fill distribution:
>0 - 19% = 0
>20 - 39% = 1
>40 - 59% = 19
>60 - 79% = 1
>80 - 99% = 368
>
>Index STUDENTBOOKINGS_INDX15 (5)
>Depth: 2, leaf buckets: 377, nodes: 459132
>Average data length: 0.00, total dup: 363562, max dup: 23
>Fill distribution:
>0 - 19% = 0
>20 - 39% = 1
>40 - 59% = 49
>60 - 79% = 2
>80 - 99% = 325
>
>Index STUDENTBOOKINGS_INDX16 (6)
>Depth: 2, leaf buckets: 406, nodes: 459132
>Average data length: 1.00, total dup: 0, max dup: 0
>Fill distribution:
>0 - 19% = 0
>20 - 39% = 0
>40 - 59% = 13
>60 - 79% = 2
>80 - 99% = 391
>
>"Quinn Wildman (CodeGear Developer Support)"
><XXXX@XXXXX.COM>writes
>news:46813e39$XXXX@XXXXX.COM...
>>If you run gstat, what is the index depth for the index in
>>question? Approximately how records does the studentbookings table
>>have? What is your database page size?
>>
>>Brian Ellul writes:
>>>Windows 2003 SP2, IB2007. Freshly installed server.
>>>
>>>This morning as the the user started logging in, I was called and
>>>found out that no-one could get in the database. I looked in the
>>>log and found this error.
>>>
>>>JERRY (Server) Tue Jun 26 08:12:41 2007
>>>Database: D:\ACCOUNTS\DATABASE\EC.GDB
>>>internal gds software consistency check (exceeded index level
>>>(206), file: btr.c line: 3248)
>>>
>>>After a gfix, more errors came up......
>>>
>>>JERRY (Server) Tue Jun 26 08:24:03 2007
>>>Database: D:\ACCOUNTS\DATABASE\EC.GDB
>>>Index RDB$PRIMARY161 is corrupt (missing entries) in table
>>>STUDENTBOOKINGS (205) JERRY (Server) Tue Jun 26 08:24:03 2007
>>>Database: D:\ACCOUNTS\DATABASE\EC.GDB
>>>Index STUDENTBOOKINGS_INDX7 is corrupt on page 111145 in table
>>>STUDENTBOOKINGS (205) JERRY (Server) Tue Jun 26 08:24:07 2007
>>>Database: D:\ACCOUNTS\DATABASE\EC.GDB
>>>Index STUDENTBOOKINGS_INDX15 is corrupt (missing entries) in
>>>table STUDENTBOOKINGS (205) JERRY (Server) Tue Jun 26 08:24:07
>>>2007
>>>Database: D:\ACCOUNTS\DATABASE\EC.GDB
>>>Index STUDENTBOOKINGS_INDX16 is corrupt (missing entries) in
>>>table STUDENTBOOKINGS (205) JERRY (Server) Tue Jun 26 08:24:08
>>>2007
>>>Database: D:\ACCOUNTS\DATABASE\EC.GDB
>>>Index STUDENTBOOKINGS_INDX3 is corrupt (missing entries) in table
>>>STUDENTBOOKINGS (205) JERRY (Server) Tue Jun 26 08:24:19 2007
>>>Database: D:\ACCOUNTS\DATABASE\EC.GDB
>>>Index TEMP_AGENTPERFORMANCE_INDX2 has orphan child page at page
>>>124738 in table TEMP_AGENTPERFORMANCE (216) JERRY (Server) Tue
>>>Jun 26 08:24:49 2007
>>>Database: D:\ACCOUNTS\DATABASE\EC.GDB
>>>Page 119055 is an orphan
>>>
>>>and a lot of more of these orphan pages errors...
>>>
>>>OMG!!!! I haven't had a corruption like this since I was using
>>>IB5.6! IB7.1 was quite stable for me.
>>>
>>>Well I did a gfix -mend followed by a backup and restore and the
>>>database was up and running. Now something else just happened. I
>>>found out that in why of my tables I have duplicates Primary
>>>Keys... Yes, hard to believe but I found 8 duplicates records,
>>>obviously this screwing up my application. Unfortunately, I
>>>can't check the DB right now but I assume that I have again a
>>>corruption.
>>>
>>>Is there anywhere I can start looking to solve these severe
>>>problems? The duplicates Primary Key is the one which is
>>>troubling me most (I remember this happening on IB5.6, but then
>>>it seems it got fixed). I read Craig's article on DB corruption
>>>and how to prevent but none seem to effect me.
>>>
>>>Regards
>>>Brian
>>>
>>>
>>>

I would guess that :

RDB$PRIMARY161

and

STUDENTBOOKINGS_INDX16

are the same indexes...If they are get rid of STUDENTBOOKINGS_INDX16.



--
Rob Schieck (TeamB)
In fact they are. I had to re-create a new Index because I needed the
STUDENTBOOKINGS_INDX16 to be used in PLANs, something which I can not do with
the Primarykey, since the I have read somewhere that the name can change.
 

Re: IB2007 stability

Brian Ellul writes:
Quote
In fact they are. I had to re-create a new Index because I needed the
STUDENTBOOKINGS_INDX16 to be used in PLANs, something which I can't
do with the Primarykey, since the I have read somewhere that the name
can change.
The name can change, yes, but this is no. 67 on the list of reasons
why you shouldn't use PLANs. :/
--
Craig Stuntz [TeamB] ?Vertex Systems Corp. ?Columbus, OH
Delphi/InterBase Weblog : blogs.teamb.com/craigstuntz
Everything You Need to Know About InterBase Character Sets:
blogs.teamb.com/craigstuntz/articles/403.aspx
 

Re: IB2007 stability

Brian Ellul writes:
Quote

In fact they are. I had to re-create a new Index because I needed the
STUDENTBOOKINGS_INDX16 to be used in PLANs, something which I can't
do with the Primarykey, since the I have read somewhere that the name
can change.
You should not be specifying plans in "production" queries. Plans are great
for diagnosing performance, but corrections to performance should be done by
working with the query, the indexes, and table design.
--
Wayne Niddery - Winwright, Inc (www.winwright.ca)
"Nurture your mind with great thoughts. To believe in the heroic makes
heroes." ?Benjamin Disraeli