Issue Details (XML | Word | Printable)

Key: CORE-3146
Type: Improvement Improvement
Status: Open Open
Priority: Minor Minor
Assignee: Unassigned
Reporter: Savin Gorup
Votes: 0
Watchers: 1
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Progress output in GBAK

Created: 26/Sep/10 09:00 PM   Updated: 01/Oct/10 07:36 PM
Component/s: GBAK
Affects Version/s: None
Fix Version/s: None

Environment: All platforms


 Description  « Hide
During execution of GBAK on large database (>50 GB) the process seems to hang for a very long time, even if using -V switch.
It would be nice if there would be a command-line option to print a progress bar while doing work, for example on every 100 pages.
Like this:
----
...
gbak: writing index STAT_CAS_ID
gbak: writing data for table STAT
gbak: = ( 100 of 1673456 )
gbak: = ( 200 of 1673456 )
...
gbak: =========== ( 836700 of 1673456 )
...
gbak: ====================== ( 1673456 of 1673456 )


 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Ann Harrison added a comment - 26/Sep/10 09:13 PM
Unfortunately, gbak doesn't know in advance that there are 1673456 rows in the
table. It could count them, but counting them is very nearly as expensive as
reading them and writing them to the backup file.

Gabor Boros added a comment - 27/Sep/10 06:36 AM
The suggestion talked about pages not rows. ;-)

Savin Gorup added a comment - 29/Sep/10 06:55 AM
I know counting rows is expensive. The gbak output does not have to be record-count-precise nor time-precise, it should just give an indication of "something going on". The page count seems a good approximation (but maybe I am wrong). Even if gbak would simply print only a page number (I know data is sparse) it is currently processing it would be vastly better than complete silence.
I propose -vv switch since it is a standard way to do such things.
If core developers think this is a good thing (tm) I may even hack up a patch?

Savin Gorup added a comment - 29/Sep/10 07:35 AM
Aha. I checked the actual code doing backup (backup.epp). If I understand correctly, it calls put_data() which relies on isc_receive() to get the actual data from server. The later does not give any progress indication, since it is just waiting for server to give the next record. While server is seeking for the next record, nothing happens and isc_receive() blocks. I am not familiar with internals of FB - is there any mechanism in FB to force server to report progress to clients? This would probably benefit not only gbak but any sql query that is taking long time...

Damyan Ivanov added a comment - 29/Sep/10 07:54 AM
GBAK does report progress somewhat:

gbak: writing index FK_EVENTS_EVENT_TYPE
gbak: writing data for table EVENTS
gbak:20000 records written
gbak:40000 records written
gbak:60000 records written
...
This is with a -v switch.

Of course, 20 thousands records may take a while to transmit, depending on data type and network speed.

Savin Gorup added a comment - 30/Sep/10 04:58 PM
Yes, I am aware of that. There are two problems with this approach.
1. The record count is not configurable. The 20000 interval is a const int. This can be remedied somehow by inventing new command line option.
2. The record count is not a progress indicator. If I have 10 GB of deleted records in a table, gbak won't output a single line for hours!
As I said, the problem lies in underlying call to isc_receive(). I am eager to hear core developers thougths on my previous comment.

Dmitry Yemanov added a comment - 01/Oct/10 08:46 AM
The engine cannot provide you with any progress indication, because it does not know how many records are in the cursor and when it would finish reading them. Cursor is not materialized when you execute a select statement, rows are being read from pages while you fetch from the cursor.

Savin Gorup added a comment - 01/Oct/10 02:52 PM
I am not intimate with internal workings of server and I believe that it does not know in advance how many rows there are to be processed. The original proposal was on purpose avoiding records (rows). However, engine certainly knows what it is doing at the moment (which page it is processing). In sequencial queries like backup (or all with plan natural) this is fairly good progress indicator, even if data ordering on pages is not sequential. It is quite enough for user to get some feedback on ongoing operation, even if it it just "jumping numbers" of some sort.

Dmitry Yemanov added a comment - 01/Oct/10 03:01 PM
Believe or not, the engine doesn't know in advance how many pages to process either. The situation is completely similar to the records one.

Savin Gorup added a comment - 01/Oct/10 03:25 PM
No, I was probably not clear enough. It does not know how many pages it will process; it does know which page it is processing at the moment, doesn't it?

Dmitry Yemanov added a comment - 01/Oct/10 03:46 PM
Sure. It also knows which record it's processing. But I fail to see how it can provide you with any progress indicator. Yes, it would tell you the engine is doing something, but you know the same even if nothing is displayed. The situation is especially complex when you have 10GB of deleted data and gbak performs the garbage collection pass. By design, the engine is not going to return from isc_receive() with any information because the task (read and return one row) is not completed.

Savin Gorup added a comment - 01/Oct/10 04:29 PM
It centrainly can provide an indicator of progress. We've had far worse in history (accumulating dots, rolling cursors -\|/- ...). User now stares at blank screen and only "top" tells him that engine is actually doing something (if he has access to server at all). At what rate he does not know. Imagine if he would see a page number every 10 seconds: aha, 1000 pages per second, one million pages to do, two passes, that gives, maximum 17 hours. Maybe less. It seems pretty much beter then nothing. The same would apply to long-running sql queries.
Nonetheless, FB server apparently lacks mechanism to send such "out-of-band" progress information to client. How hard would be in your opinion to implement such mechanism in the existing protocol? If not too daunting I might take a stab myself.

Adriano dos Santos Fernandes added a comment - 01/Oct/10 05:07 PM
You're dreaming too much...

I would also like to live in this world.. What's the DMBS that informs you about the progress of a query execution?

Savin Gorup added a comment - 01/Oct/10 06:09 PM
Hmmm, oracle? (using V$SESSION_LONGOPS view; works for rman from experience!). Also, patches to MySQL are available. A work on PostgreSQL is in progress.

FB server knows something. A connection between server and client exists. Servers sends something to client; a "record" or "a page I am working on" periodically. Client reports to user "hey, server is doing something. Look, it is working on page #...". What I would like to hear is technical reasons why it could not be done (or be very hard to do) in FB. Dreams sometimes do come true.

Adriano dos Santos Fernandes added a comment - 01/Oct/10 06:57 PM
> Hmmm, oracle? (using V$SESSION_LONGOPS view; works for rman from experience!)

If you're comparing things this way, then you should look at MON$ tables.

Damyan Ivanov added a comment - 01/Oct/10 07:07 PM
Hm, I've got an idea.

What if the current behaviour of -V is changed from emitting a line every X rows to emitting a line every X seconds? A configurable X would be really nice.

Not sure if this deserves another ticket.

Adriano dos Santos Fernandes added a comment - 01/Oct/10 07:19 PM
> What if the current behaviour of -V is changed from emitting a line every X rows to emitting a line every X seconds? A configurable X would be really nice.

What's the reason for this? User could look at timer in screen... It's not different.

Damyan Ivanov added a comment - 01/Oct/10 07:36 PM
A timer serves to tell you for how long a process is running.

A timmed -V would serve different purposes:

1) when the backup is slow (garbage-collection, tables with many big columns or BLOBs), it tells you that something is happening.

2) when the backup is very fast (table with few, small columns), it avoids flooding the user with "rolling" text that is impossible to follow.

Anyway, it is just an idea that I think is easy to implement without heavy architectural changes.