JBossDeveloper

Hi Steven, sorry to be late but in these days I'm more out of office then usual...

Current teiid server is 8.13.3, but I could try others if needed.

The table as said above is in mysql server, defined in the simplest way as a model in a dynamic vdb

in the zip attached you'll find mysql create, teiid ddsl and vdb xml, hth.

In the next days  planned to

- replicate the table 'ag' on real postgres server, and debug/trace odbc selecting from php/odbc on that table.

- replicate the above selection on windows, and debug/trace odbc selecting from php/odbc on that table.

I feel - just feel - that I aready tried something similar in windows, with much faster results.

I never felt teiid odbc slow linking tables from windows/msaccess, in the past. But this was for older teiid versions... And I could be wrong, as usual

Odata queries seemed much faster then php/odbc on that same linux server, iirc.

I could also try libreoffice on linux, linking teiid both with odbc and jdbc, if this helps.

Feel free to suggest other setups, tests, other, I'll do whatever I'm able to help you guys...

I just need some time and peace of mind in order to not add my own mistakes

Marco

I'm happy to hear that you had some success understanding my logs, because I dad not (I could not have) the faintest idea... 

Do you think there is/was also a problem with the client opening multiple sessions, and would the above fixes also address that, if it was a problem?

Marco

I just checked with the "real" postgrtesql server: it does happen the same thing, every page refresh gets a new session.

So this should not be on Teiid side... Now I need to dig into that too... any advice on where is best to post something about this? php ? postgresql ? unixodbc ?

I tried to run the same script on a windows+php server, and the same script does not behave like that. Refreshing thre web page, te same connection is used...

Although there are so many differencies between my tested linux cases... all software versions are different... this will be though to solve, I fear...

Anyway, is the first above (solved) issue in 9 beta 1? So that I could try it?

Thanks,

Marco

Just briefly tried 9 beta 1 and while it's definitely better (thanks) it's still much slower than direct mysql...

Of course there's the vdb overhead but... it's still too slow, although "usable" at least to start tests between the web server and teiid.

the 50-field table is now loaded in about 0.6 - 0.7 seconds, from the lan, while direct mysql (odbc) often shows 0.002 - 0.02 seconds (edit: with SQL_NO_CACHE) for the same query and all.

[edit] for comparison, teiid 8.13.3 times are between 4 and 12 seconds...[/edit]

using odbc_pconnect sessions are still multiple, but this may be due to the php using different apache threads/process for every page reload.

with odbc_connect teiid times grow to 0.8-0.9s while for mysql I get 0.015-0.020s for the same query. In this case of course no session is kept alive by teiid and mysql.

I wonder what could be the benefit of solving TEIID-4122 in this respect... But at least,it's promising.

In the next days I'll test more about 9 beta1 about my whole setup.

Thanks,

Marco

Before try tuning teiid inner things, which I know nothing (yet) about, I'll try to have a good test case, not just a bunch of simple sql queries...

Also, in squirrelsql results of real-use queries with joins, even between non-jdbc models (like on "file" or "google" resource adapters) are quite fast, usually.

If you know of some good test (eg that you can compare to other or previous results) let me know, I'll try my best to help you (providing logs or whatever needed).

Just to add some better rough examples, today I tried to fetch a bigger result and modified my basic php test like you see below

This now gets all 50 columns for about 30k records. The results timing is between 2.0 and 2.5 seconds.

I also tried to include that query twice in the script, the second one with reversed order by, and the timing is roughly the same.

<?php

$time_start = microtime(true);

$dsn="teiid";

//$dsn="test";

//$dsn="mysql";

//$dsn="pgtest";

$connection = odbc_pconnect($dsn, $user, $password);

switch ($dsn) {

  case "teiid":

    $query_string="SELECT * FROM ag ";

    break;

  case "mysql":

    $query_string="SELECT SQL_NO_CACHE * FROM ag WHERE id=7496";

    break;

}

echo "<li>Served by: $dsn";

$result=odbc_exec($connection , $query_string);

echo "<li>record count: " . odbc_num_rows($result); // <= this returns -1

$time_end = microtime(true);

$time = $time_end - $time_start;

echo "<li>Got results in $time seconds\n";

$rc=0;

while($arr = odbc_fetch_array($result)) {

    echo "<li>rn:".$rc++ . " => ". "id: " . $arr['id'] . " rs: " . $arr['rag_soc']. " pi: " . $arr['pi'];

}

odbc_free_result($result) ;

?>

Marco

As said I need to define a more useful test script, but I tried quickly with/without usedeclarefetch...

running the same above sql queries, it seems that on that table (50 fields, 30k rows)

- with usedeclarefetch = 1 "select * from table" is slightly faster and "select * from table limit 25" is slightly slower

- with usedeclarefetch = 0 "select * from table" is slightly slower and "select * from table limit 25" is slightly faster

take this with a grain of salt, since it's really a raw quick test, I'll study something more useful

I also thought to create two odbc dsn, one with and the other wthout usedeclarefetch,

and in the same script try both query types (with or without limits) on both odbc dsn (with or without usedeclarefetch)

I could also dump separately connection and response times for each case, and run queries either on same connection or closing/reopening it each time...

I could also iterate as many times as is useful connections and queries...

As to compare JDBC/ODBC client performances I can think of a few alternatives

- Comparing "SquirrelSQL (jdbc->teiid, jdbc->mysql)" and "ODBC Query Tool (odbc->teiid, odbc->mysql)" timings

- Use an ETL like pentaho kettle to get data from teiid with OBC or JDBC and compare timings

.. or else (?)

On the other hand, as said before, I have also a php script which uses odata (through curl request) to get the same data from teiid (paginated as in "select * from table limit 25") and it seems much, much speedier.

[edit]forget this, odata script was selecting just 3 fields... doing that, also odbc is much faster... sorry [/edit]

Of course, in theory I could also use a mix of connection methods to get different kinds of data with different methods like Odata, odbc (w/wo/usedeclarefetch) or else...

But this would make app developement a bit more complex, probably. We'll see. But odbc timings optimization is my preferred solution, first.

I also thought to use sqlrelay (SQL Relay), although I never used it, as a further intermediate abstraction layer between my php client (or else) and teiid.

It should be able to get client native mysql queries and relay them to different backend database, but it seems that it cannot connect through jdbc, unless I am missing somewthing, so it would not help. I'll ask there, if there is any way. It seems a useful bit of software, though. I don't know of any other similar tool, and I keep it under my radar, just in case.

When the test suite is setup (again, please suggest whatever is best to help), I can get the teiid logs you need to maybe spot optimization paths...

Thanks, also for your patience... this is going to be a long thread...

Marco

Ok,

I created a basic real-world application, and I am testing it. On 9beta.1

Sometimes (it seemes random to me) it happens this error, and the application shows a page containing two separate logs:

So:

• php error:
  • "Severity: Warning
    Message: odbc_exec(): SQL error: [unixODBC]Could not send Query(connection dead); Could not send Query(connection dead), SQL state 08S01 in SQLExecDirect"
• codeigniter (framework) error
  • "Error Number: 08S01
    

    [unixODBC]Could not send Query(connection dead); Could not send Query(connection dead)

    SELECT count(*) as n_az FROM ag"

(the simple select is just the first query tried by the page model, to count page items)

Usually simply reloading the page works (sometimes it needs two times).

Note: I know I could work around this, using maybe an ajax request, to detect failures and retry the request, thus not crashing the app, but for now I just need to test if there are other connection problems (it could be a million reasons, I know).

What can I do on Teiid side to get useful info, on those random errors?

Marco