Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

External data source selects became slow [CORE5104] #5388

Closed
firebird-issue-importer opened this issue Feb 5, 2016 · 9 comments
Closed

External data source selects became slow [CORE5104] #5388

firebird-issue-importer opened this issue Feb 5, 2016 · 9 comments

Comments

@firebird-issue-importer
Copy link

firebird-issue-importer commented Feb 5, 2016

Submitted by: michalk1 (michalk1)

Since original FB3.0 RC1 release the execution of external selects became very slow. It seems that the connection is now not reused anymore and a new one is opened with every select. Ie., the following simple statement, which in FB 3.0.0.32136 was able to run 500 times per second, in the current snapshot build 3.0.0.32323 went down to 6/s.

EXECUTE BLOCK RETURNS (Cnt INTEGER) AS
begin
EXECUTE STATEMENT 'SELECT COUNT (*) FROM RDB$DATABASE'
ON EXTERNAL DATA SOURCE 'C:\TestDb.Fdb'
WITH COMMON TRANSACTION
AS USER 'sysdba' PASSWORD 'masterkey'
INTO :Cnt;
suspend;
end

Btw., the database connect itself is now slower than is used to be in FB 2.5. Where FB2.5 made 15 connections/second (connect and disconnect repeatedly), FB3.0 now does just 5 per second. But for common attachments this is probably not as important (can be workaround with connection pool if needed).

Commits: c0e078a 99cbccc FirebirdSQL/fbt-repository@f0943d7 FirebirdSQL/fbt-repository@3cf69a9

====== Test Details ======

> affects local and embedded connections only
Deferred because currently only remote protocol is used.

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Feb 5, 2016

Modified by: @hvlad

assignee: Vlad Khorsun [ hvlad ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Feb 5, 2016

Modified by: @hvlad

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 3.0 RC2 [ 10048 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Feb 6, 2016

Commented by: @pavel-zotov

> It seems that the connection is now not reused anymore and a new one is opened with every select.

It's not so and can be easy verified:

set list on;
select current_connection as main_conn from rdb$database;

set term ^;
execute block returns(iter smallint, eds_conn int) as
declare n int = 3;
declare stt varchar(100);
begin
stt = 'select current_connection from rdb$database';
iter = 1;
while ( iter <= n ) do
begin
execute statement stt
on external
'localhost:' || rdb$get_context('SYSTEM','DB_NAME')
as
user'SYSDBA'
password 'masterke'
-- role 'R' || right( uuid_to_char(gen_uuid()), 10 )
into eds_conn;

    suspend;

    iter = iter \+ 1;
end

end
^
set term ;^

On any FB (2.5, 3.0 RC1, 3.0 current snapshot) you will get smth like:

MAIN_CONN 49

ITER 1
EDS_CONN 50

ITER 2
EDS_CONN 50

ITER 3
EDS_CONN 50

(i.e. all iterations inside loop work within the same connection).
Uncomment statement "-- role 'R' || right( uuid_to_char(gen_uuid()), 10 )" and it will be as you've described: new connection for each new iteration of loop.

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Feb 6, 2016

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: No test => Deferred

Test Details: Have no idea how to make time comparison with results that are obtained on other FB version (they should be stored somewhere).

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Feb 8, 2016

Commented by: michalk1 (michalk1)

Pavel, by "select" I meant calls of the entire execute block from the original database, not selects inside the execute block itself. And in that case, current_connection really returnes different values in 3.0.0.32323 (unlike 3.0.0.32136). But it is ok now, Vlad already fixed it in 3.0.0.32330.

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Feb 8, 2016

Commented by: @pavel-zotov

michalk1, I still can`t understand how did you receive different transactions by mean "calls of the entire execute block from the original database".

Can you please provide sample testcase where one may see "connection is now not reused anymore and a new one is opened with every select" ?

PS.
You did not specify host/port in " ON EXTERNAL DATA SOURCE 'C:\TestDb.Fdb' ", so tx number could be equal only when you attach to database by local protocol, w/o specifying "host/port:" as prefix to connection string.

If we create database (/var/db/fb30/e30test.fdb) and view in it:

===
create or alter view v_info as
select current_timestamp as dts, mon$remote_protocol, mon$client_version, current_transaction as outer_tx
from mon$attachments
where mon$attachment_id=current_connection;
commit;

- and then run this script:

set list on;
select * from v_info;

set term ^;
execute block returns (dts timestamp, eds_protocol varchar(10), eds_clnt_version varchar(255), eds_tx int) as
begin
execute statement 'select * from v_info'
on external data source --'c:\testdb.fdb'
'/var/db/fb30/e30test.fdb'
with common transaction
as user 'sysdba' password 'masterke'
into dts, eds_protocol, eds_clnt_version, eds_tx;
suspend;
end
^
set term ;^

-- then it's easy to ensure that connections are the same only when we make "outer" connect to database (from command prompt) without specifying "host/port:".

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Feb 8, 2016

Commented by: michalk1 (michalk1)

> Can you please provide sample testcase where one may see "connection is now not reused anymore and a new one is opened with every select" ?

Ok, here it is:

1) Install FB 3.0.0.32323
2) Connect to any database as sysdba. I use TCP/IP connection (ie. 'localhost:C:\TestDb1.Fdb) , but it does not seem to matter here.
3) Start a new transaction.
4) Repeatedly call the following execute block and display the returned connection number

EXECUTE BLOCK RETURNS (eds_conn INTEGER) AS
begin
EXECUTE STATEMENT 'SELECT current_connection FROM RDB$DATABASE'
ON EXTERNAL DATA SOURCE '* Any local database here *'
AS USER 'sysdba' PASSWORD 'masterkey'
INTO :eds_conn;
suspend;
end

This statement should reuse the same transaction and connection in the external database, because it is called with the default - WITH COMMON TRANSACTION - setting. In 3.0.0.32323, however, the statement returns different connection numbers.
As I just found, connection string in the external data source statement *must be local* (ie. 'C:\TestDb2.Fdb') to prove the error. If I use remote connection string here (ie. 'localhost/3050:C:\TestDb2.Fdb'), the statement runs just fine even in FB 3.0.0.32323, and the returned connection number stays constant.
I run all the tests on Windows.
If you still can not reproduce it, I can build some simple test application.

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Feb 8, 2016

Commented by: @hvlad

Bug was introduced with fix for CORE5082 and affects local and embedded connections only

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Feb 8, 2016

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

Test Details: Have no idea how to make time comparison with results that are obtained on other FB version (they should be stored somewhere). => > affects local and embedded connections only
Deferred because currently only remote protocol is used.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants