CODEWITHSUNDEEP
mariadbodbc
Dusan P
Dusan P

Reputation: 11

MariaDB ODBC Connector performance issue

We try to switch from MySQL 5.0.96 to MariaDB 10.5.9. Move data to MariaDB server was easy but problem is with speed of MariaDB ODBC connector. I tried 3.1.12 with default settings without SSL and our applications (mostly written in Embarcadero RAD Studio) showed very poor performance. When I tried MySQL ODBC connector 8.0.24 everything ran smoothly. Queries through MariaDB's connector was 3-5 times slower. In general query log I found that MariaDB's connector generates a lot of queries to information_scheme.columns table and repeatedly it reads variable TX_ISOLATION. Short extract from general query log - MariaDB connector:

3 Query    SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.SESSION_VARIABLES WHERE VARIABLE_NAME='TX_ISOLATION'
3 Prepare    select * from stanice
3 Execute    select * from stanice
3 Prepare    SELECT TABLE_SCHEMA AS TABLE_CAT, NULL AS TABLE_SCHEM, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION KEY_SEQ, 'PRIMARY' PK_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_KEY = 'pri' AND TABLE_SCHEMA LIKE 'unicentrum' AND TABLE_NAME LIKE 'stanice'  ORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION              
3 Execute    SELECT TABLE_SCHEMA AS TABLE_CAT, NULL AS TABLE_SCHEM, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION KEY_SEQ, 'PRIMARY' PK_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_KEY = 'pri' AND TABLE_SCHEMA LIKE 'unicentrum' AND TABLE_NAME LIKE 'stanice'  ORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION
3 Close stmt
3 Reset stmt
3 Query    SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.SESSION_VARIABLES WHERE VARIABLE_NAME='TX_ISOLATION'
3 Prepare    SELECT * FROM stanice WHERE Sit_ID = ? ORDER BY Sit_ID, Podsit_ID, ID
3 Execute    SELECT * FROM stanice WHERE Sit_ID = 1 ORDER BY Sit_ID, Podsit_ID, ID
3 Prepare    SELECT TABLE_SCHEMA AS TABLE_CAT, NULL AS TABLE_SCHEM, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION KEY_SEQ, 'PRIMARY' PK_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_KEY = 'pri' AND TABLE_SCHEMA LIKE 'unicentrum' AND TABLE_NAME LIKE 'stanice'  ORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION
3 Execute    SELECT TABLE_SCHEMA AS TABLE_CAT, NULL AS TABLE_SCHEM, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION KEY_SEQ, 'PRIMARY' PK_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_KEY = 'pri' AND TABLE_SCHEMA LIKE 'unicentrum' AND TABLE_NAME LIKE 'stanice'  ORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION
3 Close stmt
3 Reset stmt

MySQL connector:

3 Query    SELECT @@tx_isolation
...
3 Query    select * from stanice
3 Prepare    SELECT * FROM stanice WHERE Sit_ID = ? ORDER BY Sit_ID, Podsit_ID, ID
3 Execute    SELECT * FROM stanice WHERE Sit_ID = '1' ORDER BY Sit_ID, Podsit_ID, ID
3 Close stmt

I think those "unnecessary" queries are making difference in performance of those connectors. Any ideas what could I do to speed up MariaDB's connector? Thanks

Upvotes: 1

Views: 681

Answers (1)

Lawrin Novitsky
Lawrin Novitsky

Reputation: 141

I've opened https://jira.mariadb.org/browse/ODBC-313 for the problem you've described. It would be better to continue there, as at least ODBC trace of the run with both drivers is required to address main issue.

Upvotes: 3

Related Questions