Uploaded image for project: 'MariaDB Connector/ODBC'
  1. MariaDB Connector/ODBC
  2. ODBC-286

Very Poor Performance on Remote Connection

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • N/A
    • 3.2
    • General
    • None
    • windows

    Description

      Maridb odbc is too slow when you connect a remote server. Same server connected with mysql odbc gives very fast response. Please look into the matter as mariadb user want to use maridb odbc.
      P.S. i am using Visual Foxpro with mariadb and using mariadb 3.1 driver.

      Attachments

        1. MYSQL.LOG
          1.90 MB
        2. SQL.LOG
          2.15 MB

        Issue Links

          is caused by

          Bug - A problem which impairs or prevents the functions of the product. ODBC-313 Redundant SQLPrimaryKeys calls in applications created with Embarcadero

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed
          relates to

          New Feature - A new feature of the product, which has yet to be developed. MDEV-16670 Improve execute direct (prepare and execute) performance

          • Major - Major loss of function.
          • Open

          Task - A task that needs to be done. MDEV-19237 Skip sending metadata when possible for binary protocol

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Task - A task that needs to be done. ODBC-222 Make SQLExecDirect to use text protocol(i.e. mysql_query)

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            View 15 older comments
            Lawrin Lawrin Novitsky added a comment -

            The main theory is that is because of prepared statements in maodbc vs text protocol in myodbc.
            MariaDB has some optimization for direct execution of the prepared statements. But it's disabled, when compression is used. But it apparently it doesn't remove all the overhead.
            However, mariadb log has 388 more API calls. And in particular 9 more calls of SQLDirectExec, i.e. 9 more queries. With all accompanying metadata calls, rows fetching, column data getting etc, they actually can be responsible for all those 388 calls. Anyway, those queries and calls are making ~10% of total number and unlikely can be responsible for the whole performance time difference. But still requires some research at least to figure out why are they there, and not in the myodbc trace.

            Lawrin Lawrin Novitsky added a comment - The main theory is that is because of prepared statements in maodbc vs text protocol in myodbc. MariaDB has some optimization for direct execution of the prepared statements. But it's disabled, when compression is used. But it apparently it doesn't remove all the overhead. However, mariadb log has 388 more API calls. And in particular 9 more calls of SQLDirectExec, i.e. 9 more queries. With all accompanying metadata calls, rows fetching, column data getting etc, they actually can be responsible for all those 388 calls. Anyway, those queries and calls are making ~10% of total number and unlikely can be responsible for the whole performance time difference. But still requires some research at least to figure out why are they there, and not in the myodbc trace.
            anilgenius Anil Sharma added a comment -

            One more area you need to research when we connect the remote server MariaDB odbc lost the connection within a minute until you have an auto-reconnect option enabled. which is not required in the MySQL odbc case.

            anilgenius Anil Sharma added a comment - One more area you need to research when we connect the remote server MariaDB odbc lost the connection within a minute until you have an auto-reconnect option enabled. which is not required in the MySQL odbc case.
            Lawrin Lawrin Novitsky added a comment -

            That is probably they either use either longer wait timeout by default, or set interactive client option by default, that eventually means the same - longer timeout. I am not sure that is a good idea, but we need to add that option. I opened ODBC-288 for this.

            Lawrin Lawrin Novitsky added a comment - That is probably they either use either longer wait timeout by default, or set interactive client option by default, that eventually means the same - longer timeout. I am not sure that is a good idea, but we need to add that option. I opened ODBC-288 for this.
            Lawrin Lawrin Novitsky added a comment -

            Some of cases described there can affect this issue as well

            Lawrin Lawrin Novitsky added a comment - Some of cases described there can affect this issue as well
            Lawrin Lawrin Novitsky added a comment -

            3.2.0 with text protocol support is about to be released. I am tempted to close this ticket, as I suspected binary vs text was slower in this case, but leaving it open to take another look.

            Lawrin Lawrin Novitsky added a comment - 3.2.0 with text protocol support is about to be released. I am tempted to close this ticket, as I suspected binary vs text was slower in this case, but leaving it open to take another look.

            People

              Lawrin Lawrin Novitsky
              anilgenius Anil Sharma
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.