Hi,
I'm new with MariaDB. We try to switch from MySQL 5.0.96 to MariaDB
10.5.9. Move data to MariaDB was easy but now we have problems with
speed of ODBC connector. I tried MariaDB ODBC connector 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 all over again it reads variable
TX_ISOLATION.
Short extract - 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. Does anybody have any ideas what could I do to
speed up MariaDB's connector? We prefer not using MySQL connectors anymore.
Thanks in advance
Dušan Pavlica
_______________________________________________
Mailing list: https://launchpad.net/~maria-discuss
Post to : [email protected]
Unsubscribe : https://launchpad.net/~maria-discuss
More help : https://help.launchpad.net/ListHelp
