MySQL Bugs: #4350: Updates are slower in MySQL 4.0.20

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

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);
}

I am begin to test this issue.

This only happens with InnoDB engine when tested with MyISAM 
I found same performance behavior.

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.

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).