| Subject | Re: [firebird-support] Restore error during unique index creation |
|---|---|
| Author | Thomas Steinmaurer |
| Post date | 2013-10-13T07:32:13Z |
Dmitry,
7min 58sec to 5min 57sec by using 16384 buffers instead of 1024. Firing
a trace session on the restored database shows half execution time upon
COMMIT RETAINING for 2 of the largest indexes. This all with the backup
and the restored database on the same rotating 7200pm disk.
Perhaps you become CPU bound in your tests more quicker than I did.
The problem with gbak is, that especially in the restore process, gbak
becomes CPU bound at the index creation stage very quickly. CPU bound on
a single physical core. gbak can't utilize a SMP environment at the moment.
And there is a sweet spot with various page cache sizes and temporary
storage module settings in firebird.conf, where increasing them doesn't
reduce restore time. It even doesn't help if moving from rotating to a
SSD disk, due to being strictly CPU bound to a single core.
If you have a look on a thread in firebird-devel started on Sept. 10,
2012, where I was mentioning InterBase XE3 got improved by parallel
index creation at restore. Sounds like a good move in InterBase XE3,
although I haven't tried that enhancement.
--
With regards,
Thomas Steinmaurer
http://www.upscene.com/
Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
> Saturday, October 12, 2013, 1:06:24 AM, you wrote:Thanks for the confirmation. ;-)
>
> TS> * Running th restore through the Services API/Manager
>
> right, this is the fastest way.
> TS> * Provide a larger page cache upon restore, because especially the indexIt isn't. At least not entirely. ;-)
> TS> creation step loves RAM. Don't forget to set the page buffers value back
> TS> again at database-level after the restore via e.g. gstat. I've seen
> TS> restore improvements of > 300% for largish indexes this way.
>
> This idea is useless.
> We have done complex restoreWith a scale 1 TPC-H backup database, I can reduce restore time from
> test, including specifying -bu nnn option, and found no difference
> for cache size from 1024 to 16384 pages (classic and superserver),
> for data only (-i) or full restore (data+indices).
7min 58sec to 5min 57sec by using 16384 buffers instead of 1024. Firing
a trace session on the restored database shows half execution time upon
COMMIT RETAINING for 2 of the largest indexes. This all with the backup
and the restored database on the same rotating 7200pm disk.
Perhaps you become CPU bound in your tests more quicker than I did.
The problem with gbak is, that especially in the restore process, gbak
becomes CPU bound at the index creation stage very quickly. CPU bound on
a single physical core. gbak can't utilize a SMP environment at the moment.
And there is a sweet spot with various page cache sizes and temporary
storage module settings in firebird.conf, where increasing them doesn't
reduce restore time. It even doesn't help if moving from rotating to a
SSD disk, due to being strictly CPU bound to a single core.
If you have a look on a thread in firebird-devel started on Sept. 10,
2012, where I was mentioning InterBase XE3 got improved by parallel
index creation at restore. Sounds like a good move in InterBase XE3,
although I haven't tried that enhancement.
--
With regards,
Thomas Steinmaurer
http://www.upscene.com/
Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.