0

I have a performance related database issue with CodeFluent Entities and MySql .NET connector.

I'm using:

CodeFluent Entities version 61214.820

MySql Connector 6.9.8

Compared to MySql .NET connector version 6.5.4, .Save() method insert performance DROPPED from 230 records per second to 10 records per second on a dev test machine, same project, same routine, same MySql DB engine version, just by switching connector versions.

Examining MySql query logs, I noticed there is a difference on the instructions issued to the database when .Save() method is called, such as:

Connector 6.5.4: SELECT * FROM mysql.proc WHERE 1=1 AND db LIKE 'entityname' AND name LIKE 'Entity_Save'

Connector 6.9.8: SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA LIKE 'entityname' AND ROUTINE_NAME LIKE 'Entity_Save'

I cannot determine if these differences have anything to do with the performance degradation, I just mentioned it so you can have more information to help my case.

Load methods also suffer from performance degradation.

All I described was reproduced simply by switching MySql .NET connectors. CodeFluent Entities was not touched. MySql connector 6.5.4 seems to give me the best performance relatively speaking, but it gives me a problem of intermittent errors making it difficult to deploy to production environments, bug report posted here.

I could observe the same issues with .NET MySql Connector 6.9.5.

Can this low performance issue be solved/investigated, to provide a way to use the latest .NET MySql connector version with CodeFluent Entities?

Renato Xavier
  • 123
  • 1
  • 9
  • 2
    It seems you can solve the `INFORMATION_SCHEMA` slowness by setting `innodb_stats_on_metadata=0` (https://www.percona.com/blog/2011/12/23/solving-information_schema-slowness/) – meziantou Jan 06 '16 at 14:42
  • @meziantou, I imagined I could have a breakthrough with the information you kindly provided! Unfortunately, I believe this variable you mentioned is already turned off (=0) by default on my MySql server version, since I tested on a fresh install: `mysql> show variables like 'innodb_stats_on_metadata'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | innodb_stats_on_metadata | OFF | +--------------------------+-------+ 1 row in set (0.01 sec)` – Renato Xavier Jan 11 '16 at 12:20
  • Please, any other insight? I'm clueless, with production environment impossible to gain reliability. – Renato Xavier Jan 11 '16 at 12:23
  • 1
    CodeFluent Entities calls `MySqlCommandBuilder.DeriveParameters` to create the command parameters. This method generates the statement `SELECT * FROM INFORMATION_SCHEMA.ROUTINES...`. So maybe the MySQL ADO.NET provider team can explain this change and how to fix the slowness... – meziantou Jan 20 '16 at 10:37

0 Answers0