Bug #4350 Updates are slower in MySQL 4.0.20

Submitted: 30 Jun 2004 19:21 Modified: 1 Jul 2004 5:38
Reporter: Matt Solnit Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S1 (Critical)
Version:4.0.20a OS:Windows (Windows XP Professional SP1)
Assigned to: MySQL Verification Team CPU Architecture:Any

[30 Jun 2004 19:21] Matt Solnit
Description:
Using MySQL Embedded server on Windows.

Execute the attached C program in MySQL 4.0.6-gamma.
Execute it again in MySQL 4.0.20a.

Executes more slowly in 4.0.20a.

How to repeat:
#include <stdarg.h>
#include <stdlib.h>
#include <stdio.h>
#ifdef _WIN32
#include <windows.h>
#include <winsock.h>
#endif
#include <mysql.h>

// This number should be divisible by the number of symbols (4)
#define NUM_UPDATES 100

static void die(MYSQL *db, char *fmt, ...);
MYSQL *db_connect(const char *dbname);
void db_query(MYSQL* db, const char* query);
int db_query_no_output(MYSQL* db, const char* query);
void db_disconnect(MYSQL *db);

int get_time_stamp()
{
  // Not sure how this would work on Unix??
#ifdef _WIN32
  return GetTickCount();
#endif
}

void create_test_tables(MYSQL* cnn)
{
  /*
  | _stock_insertupdate | CREATE TABLE `_stock_insertupdate` (
  `ArrivalTime` datetime default NULL,
  `symbol` varchar(5) default NULL,
  `open` double default NULL,
  `high` double default NULL,
  `low` double default NULL,
  `last` double default NULL,
  `previous` double default NULL,
  `change` double default NULL,
  `volume` int(11) default NULL,
  `SysIterID` bigint(20) NOT NULL default '0',
  `SysIterTrID` bigint(20) NOT NULL default '0',
  `SysIterTotalsChild` int(11) NOT NULL default '0',
  `SysIteradctimestampMilliseconds` int(11) NOT NULL default '0',
  PRIMARY KEY  (`SysIterID`)
) TYPE=InnoDB |
  */
  db_query(cnn, "DROP TABLE IF EXISTS _test");
  db_query(cnn, "CREATE TABLE IF NOT EXISTS _test (last DOUBLE, SysIterID BIGINT NOT NULL, SysIteradctimestampmilliseconds INT NOT NULL, PRIMARY KEY (SysIterID)) TYPE=InnoDB");
}

void seed_test_tables(MYSQL* cnn)
{
  int i;

  db_query(cnn, "DELETE FROM _test");
  for (i = 0; i < NUM_UPDATES; i++)
  {
    char query[100];
    sprintf(query, "INSERT INTO _test (SysIterID) VALUES (%d)", i);
    db_query(cnn, query);
  }
}

void perform_updates(MYSQL* cnn)
{
  int i;

  srand((unsigned)time(NULL));

  for (i = 0; i < NUM_UPDATES; i++)
  {
    int random;
    char query[100];
    int affected;

    random = rand();

    sprintf(query, "UPDATE _test SET SysIteradctimestampmilliseconds = %d, last = 10.4 WHERE SysIterID = %d", random, i);
    affected = db_query_no_output(cnn, query);

    if (affected != 1)
      die(cnn, "Expected 1 update!");
  }
}

int main(void)
{
    int nRetCode = 0;
    MYSQL *one;
    MYSQL_RES* res;
    MYSQL_ROW row;
    int start, end;

    const char *server_groups[] = {
        "test_libmysqld_SERVER", "embedded", "server", NULL
    };

    const int ARG_COUNT = 2;

    // Don't conflict with running instance of MySQL
    const char *args[] = {
        "thisappname",
        "--datadir=c:\\mysql\\data2"
    };

    /*
    system("rmdir /s /q c:\\mysql\\data2");
    system("mkdir c:\\mysql\\data2");
    system("mkdir c:\\mysql\\data2\\test");
    */

    // Visual Studio 7.1 complains if this line is any earlier than here:
    printf("Test of MySQL Embedded update peformance.\n");
    printf("Using version ");
    printf(MYSQL_SERVER_VERSION);
    printf(" \n");

    printf("\n");
    printf("Initializing Embedded MySQL...\n");
    mysql_server_init(ARG_COUNT, args, (char **)server_groups);

    printf("\n");
    printf("Connecting to test database...\n");
    one = db_connect("test");

    db_query(one, "SELECT VERSION()");

    res = mysql_store_result(one);
    row = mysql_fetch_row(res);
    printf("SELECT VERSION() returns '%s'\n", row[0]);
    mysql_free_result(res);

    printf("Creating test tables...\n");
    create_test_tables(one);

    printf("Seeding test tables...\n");
    seed_test_tables(one);

    printf("Performing updates...\n");
    start = get_time_stamp();
    perform_updates(one);
    end = get_time_stamp();

    printf("Completed %d updates in %d ms.\n", NUM_UPDATES, (end - start));

    printf("Disconnecting from database...\n");
    db_disconnect(one);

    /* This must be called after all other mysql functions */
    printf("Shutting down Embedded MySQL...\n");
    mysql_server_end();

    printf("Done.\n");
    return nRetCode;
}

static void die(MYSQL *db, char *fmt, ...)
{
    va_list ap;
    va_start(ap, fmt);
    vfprintf(stderr, fmt, ap);
    va_end(ap);
    (void)putc('\n', stderr);
    if (db)
        db_disconnect(db);
    exit(EXIT_FAILURE);
}

MYSQL * db_connect(const char *dbname)
{
    MYSQL *db = mysql_init(NULL);
    if (!db)
        die(db, "mysql_init failed: no memory");
    /*
     * Notice that the client and server use separate group names.
     * This is critical, because the server will not accept the
     * client's options, and vice versa.
     */
    mysql_options(db, MYSQL_READ_DEFAULT_GROUP, "test_libmysqld_CLIENT");
    if (!mysql_real_connect(db, NULL, NULL, NULL, dbname, 0, NULL, 0))
        die(db, "mysql_real_connect failed: %s", mysql_error(db));

    return db;
}

void db_query(MYSQL* db, const char* query)
{
    if (mysql_query(db, query))
        die(db, "mysql_query failed: %s", mysql_error(db));
}

int db_query_no_output(MYSQL* db, const char* query)
{
  MYSQL_RES* res;

  int affected = 0;

  db_query(db, query);

  res = mysql_store_result(db);
  if (res == NULL)
  {
    // Query did not return rows; check if it's an UPDATE or if an error occurred
    if (mysql_field_count(db) != 0)
    {
      // Query should have returned rows; an error occurred
      die(db, "mysql_store_result failed: %s", mysql_error(db));
    }
    else
    {
      // Query was an UPDATE, get the affected rows
      affected = mysql_affected_rows(db);
    }
  }
  else
  {
    affected = mysql_num_rows(res);
    mysql_free_result(res);
  }

  return affected;
}

void db_disconnect(MYSQL *db)
{
    mysql_close(db);
}
[30 Jun 2004 19:29] MySQL Verification Team
I am begin to test this issue.
[1 Jul 2004 0:02] MySQL Verification Team
This only happens with InnoDB engine when tested with MyISAM 
I found same performance behavior.
[1 Jul 2004 5:26] MySQL Verification Team
I am changing the status for not a bug because I found that just is
different default value for InnoDB key innodb_flush_log_at_trx_commit
in 4.0.6 = 0 and 4.0.20 = 1. So using in the my.ini file:

[embedded]
innodb_flush_log_at_trx_commit=0

the test presents similar result for both versions of Embedded Server.

Thanks for the bug report.
[1 Jul 2004 5:38] MySQL Verification Team
Additional Info:

The Manual in InnoDB chapter stands: 

innodb_flush_log_at_trx_commit 
.......
 The default value is 1 (prior to MySQL 4.0.13, the default is 0).

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.053 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.