I. 09/17 06:58:31. SQL Anywhere Network Server Version 12.0.1.3750 I. 09/17 06:59:58. ... recovery 0% complete I. 09/17 19:25:43. ... recovery 50% complete I. 09/18 08:42:31. ... recovery 99% complete I. 09/18 09:10:31. Recovery complete This was a single log file (6,739,460,096 bytes), with one day of activity. I'm glad I wasn't in a hurry. Jim |
|
We have looked into the transactions in this log file and all look normal. We do not have any tables without primary keys. We are chalking this up to a slower machine and the issue John brought up regarding recovery being single threaded. |
That's a very big log file (or at least it seems it - you don't say how big the database is). Could there be unnecessary stuff going on? If it is anomalous it might be worth translating it in to SQL and seeing what is in it (you need a fair amount of time and something that will read very big text files)..
We had an example recently where was was a process going on where the data could be safely discarded if the database restarted (it only related to individual sessions and was deleted at logoff.) We changed the tables involved to shared global non-transnational temporary tables. It went faster and log file size fell by 90%.
Was that an ordinary recovery after a server crash where both the database and log files were OK, or was it the application of the log file to a restored database file?
If the latter, perhaps more frequent backups should be taken, and/or HA should be used.
If the former, then perhaps something exciting is going on, like massive transactions that deserve more frequent commits (e.g., deleting millions of rows in one statement, etc).
Generally, recovery runs as fast or faster than the original operations, so if recovery runs "too long" the solution is to reduce the amount of work that needs to be recovered... which is what Justin said using fewer words :)
Has the recovery taken place on the same machine (and configuration) as the original database operations?
Needing more time to recover than to generate the original operations seems weird when running on the same HW.
"Needing more time to recover than to generate the original operations" ...how do you know that's true? The OP said "one day of activity".
I don't know that, apparently. However, the recovery has lasted more than 26 hours, and I guess one day of activity still has 24 hours at maximum:)
What was the line previous to the first "% complete" message. Was it "Transaction log..." or "Rollback log..."? If it was "Rollback log..." then you might be rolling back a massive transaction (or transactions).
Normal recovery only applies transactions since the last checkpoint. Have you done something to suppress checkpoints or make them extremely infrequent? For example, do you set -gc or -gr on the server command line?
Also, there are some cases where recovery can take much longer than then original statement. For example, if you have a large table without a primary key defined then a "delete from T" goes pretty quickly but recovery will take a long time since, on recovery, each delete is a single-row delete. Without a primary key, each row deletion requires a sequential table scan and that becomes an O(n^2) algorithm.
Also, runtime modifications can be done in parallel on multiprocessor machines; however, recovery is single threaded. Recovery in this case can also take longer than the original execution.
It is also worth verifying that you gave the server sufficient cache to perform the recovery. Usually, default cache settings work but there are cases where they are not sufficient. If you captured the console log, you should be able to find out out how much cache was used.
From my comment above:
OK, I stand corrected: It only seems weird, if one doesn't know the facts. John does, of course.
@John: That is excellent information, worthy to be part of the docs, I'd say...
ha ha you got me, I didn't notice the day number.
"worthy to be part of the docs" ...yes, indeed, since recovery time is ALWAYS inversely proportional to personal urgency (takes longest when you're in the most hurry).
Re: "... worthy to be part of the docs ..." - well, I'm not sure but John's information could be there already? It has certainly been mentioned before (in this forum I think?) and in Glenn/Ivan's whitepapers (I think?). At one point I think SQLA even had a warning message in the log when it saw a table that did not have a primary key (or maybe I just dreamed that?). If it is (or not) is moot if the information cannot be easily found in the documentation and/or the docs are not read. :-(
I have sent an email to the doc team to suggest some improvements.
Sorry for the delay. The database is 250 GB. This is a full subscription to a SQL remote consolidated with approx 130 remote sites. Two of the sites are full subscriptions. This was performed as part of a test of disaster recovery, all data being recovered was a result of replication no actual transactions were run on the database.
The hardware is different the recovery was performed on a Poweredge 2900, the database runs on a much more capable Virtual Machine. So this could account for the time, however the 2900 is also quite capable 15K 6 GBS SCSI, WIN 2K3 R2 4 GB Memory. We run tests like this on this machine all the time, I've never seen it take this long.
Kudos on actually testing recovery. More kudos on creating a recovery test that uncovers a problem before reality strikes. Please keep us informed of your progress in searching for a solution.
Primary-key-less-tables was only one small part of John's comment... chances are the other valuable material is not yet in the Help.
Agreed. Personally, I was not aware of the "on recovery, each delete is a single-row delete" and the "recovery is always single-threaded" issues...
And these may hurt folks who would certainly not forget to declare fitting PKs...
I was including the part about the deletes in my statement... I've seen/heard of this class of problems in many customer performance issues and I would have thought that this was documented somewhere (e.g. in one or more performance whitepapers). As mentioned, I've passed on this thread and some suggestions to the doc team to have them improve the docs.
Don't worry, you have noticed it within the 24 hours grace time:)
Though it's somewhat embarrassing that the great keeper of DATEDIFF stumbles over such a detail...
Hm, I'm not really understanding what role replication takes here - does the log contain uncommitted OPs (though in that case, it shouldn't matter whether they result from local or replicated transactions?
Or do you try to re-build the remote by re-syncing with the cons?
As to replication: AFAIK, the server within its transaction log management needs to deal with publications whereas subscriptions are dealt with by the SQL Remote message agent. - And within a remote, there are usually only "full subscriptions back to the cons", so usually there's no filtering on the message agent side, either.
Your correct replication has no effect I mentioned only because of the questions regarding size and the fact that the transactions in the log file should not be associated with any work tables as Justin mentioned since these are not replicated.