help needed troubleshooting slow odbc usage
m.ardito Mar 29, 2016 11:43 AMHi all,
I am trying to debug a very slow odbc connection to teiid: I'm pretty sure it is due to some client factor but I wish to be sure (ie: learn) what can I do on teiid side to debug what/when is happening exactly in teiid as a response from the client query.
My ideal "test" client is a linux webserver running apache/php using odbc to get data from teiid.
teiid odbc dsn is like
=====================================
$ cat ./.odbc.ini
[teiid_db]
Description = PostgreSQL Unicode
Driver = /usr/lib/i386-linux-gnu/odbc/psqlodbcw.so
Trace = No
TraceFile =
Database = databasename
Servername = 192.168.x.y
Username = username
Password = password
Port = 35432
Protocol = 7.4-1
ServerType = Postgres
ReadOnly = No
ShowSystemTables = No
ShowOidColumn = No
FakeOidIndex = No
UseServerSidePrepare =1
ByteaAsLongVarBinary =1
UpdatableCursors =0
ConnSettings =
Optimizer =0
Ksqo =0
Debug =0
Fetch = 10000
UseDeclareFetch =1
=====================================
I also tested an identical odbc "mysql" dsn on the same server, which connects to a mysql db (the same also used as model in the teiid vdb), but I get very slow results when those results come from the "teiid" odbc dsn.
=====================================
[mysql]
Driver = /usr/lib/i386-linux-gnu/odbc/libmyodbc.so
SERVER = 192.168.x.y
PORT = 3306
DATABASE = databasename
OPTION = 3
USER = username
PASSWORD = password
=====================================
I did a few tests, with everything I have at hand:
- squirrel/win7 on the vdb, via jdbc
- odbc query tool (ODBC Query Tool download | SourceForge.net) same win7, odbc to teiid
- php framework (code igniter) with odbc database
- php, both using odbc_connect and pdo, on linux and windows
- python with pypyodbc, on linux and windows
and anywhere, in linux server, odbc/mysql is much faster than odbc/postgres (to teiid). Even very simple queries need 4-5 seconds to get the response, while mysql odbc is blasing fast...
selecting more fields from the vdb (or *) only makes thing worse...
On windows, thought, it seems less different (faster). But it's a different everything... so I don't know if it's a good comparison...
I know there are tons of different reasons, for this, and I'm willing to create the best possible setup to spot what is not working well... (and fix it)
So, how can teiid (or the as, or the server where's running, or else) help me in this debug job?
Marco