Periko Support
2010-09-24 17:57:43 UTC
Hi my friends.
I have been this couple of days benchmarking mysql, one of this tools
is sysbench which we install from rpmforge.
The version I'm working on is:
sysbench.x86_64?????????????????????????? 0.4.10-1.el5.rf
With MyISAM engine no issue, the problem is with InnoDB, the issue is
with the second index creation, let see MyISAM:
sysbench --test=oltp --db-driver=mysql --mysql-user=root
--mysql-password=mypassword --mysql-db=sbtest
--mysql-table-engine=myisam --oltp-test-mode=simple
--oltp-table-size=10000 --mysql-socket=/var/lib/mysql/mysql.sock
--num-threads=8 --max-requests=1000 prepare
sysbench 0.4.10: multi-threaded system evaluation benchmark
Creating table 'sbtest'...
Creating 10000 records in table 'sbtest'...
I check my table and have all the data on great, but I want to work
with InnoDB. Now let see what happen with InnoDB:
sysbench --test=oltp --db-driver=mysql --mysql-user=root
--mysql-password=mypassword --mysql-db=sbtest
--mysql-table-engine=innodb --oltp-test-mode=simple
--oltp-table-size=10000 --mysql-socket=/var/lib/mysql/mysql.sock
--num-threads=8 --max-requests=1000 prepare
sysbench 0.4.10:? multi-threaded system evaluation benchmark
Creating table 'sbtest'...
ALERT: failed to execute MySQL query: `CREATE INDEX k on sbtest(k)`:
ALERT: Error 1031 Table storage engine for 'sbtest' doesn't have this option
FATAL: failed to create secondary index on table!
U can see the error, I start reading the src and found the part that
handle this process, but I still don't know if I'm not using the right
parameters or is a app issue, the file called:
sysbench/tests/oltp/sb_oltp.c
Have this:
line 371:
snprintf(query, query_len,
"CREATE TABLE %s ("
"id %s %s NOT NULL %s, "
"k integer %s DEFAULT '0' NOT NULL, "
"c char(120) DEFAULT '' NOT NULL, "
"pad char(60) DEFAULT '' NOT NULL, "
"PRIMARY KEY (id) "
?????????? ") %s",
args.table_name,
(args.auto_inc && driver_caps.serial) ? "SERIAL" : "INTEGER",
driver_caps.unsigned_int ? "UNSIGNED" : "",
(args.auto_inc && driver_caps.auto_increment) ?
"AUTO_INCREMENT" : "",
driver_caps.unsigned_int ? "UNSIGNED" : "",
(table_options_str != NULL) ? table_options_str : ""
);
Latter at line 410:
/* Create secondary index on 'k' */
snprintf(query, query_len,
"CREATE INDEX k on %s(k)",
args.table_name);
if (db_query(con, query) == NULL)
{
log_text(LOG_FATAL, "failed to create secondary index on table!");
goto error;
}
Working with InnoDB plugin on MySQL 5.1.50 community-edition x64, I
can see that once u created a table InnoDB u cannot add a another
Index, I still learning InnoDB, must possible but sysbench looks like
is not doing the right process.
I have try to fix this but my knowledge in programing are not to updated.
Someone here have see this behavior? Do I'm doing something wrong?
Does someone work with sysbench and InnoDB without this issue?
OS: Centos
Kernel: 5.52.6.18-194.11.3.el5xen
x64.
Thanks!!!
I have been this couple of days benchmarking mysql, one of this tools
is sysbench which we install from rpmforge.
The version I'm working on is:
sysbench.x86_64?????????????????????????? 0.4.10-1.el5.rf
With MyISAM engine no issue, the problem is with InnoDB, the issue is
with the second index creation, let see MyISAM:
sysbench --test=oltp --db-driver=mysql --mysql-user=root
--mysql-password=mypassword --mysql-db=sbtest
--mysql-table-engine=myisam --oltp-test-mode=simple
--oltp-table-size=10000 --mysql-socket=/var/lib/mysql/mysql.sock
--num-threads=8 --max-requests=1000 prepare
sysbench 0.4.10: multi-threaded system evaluation benchmark
Creating table 'sbtest'...
Creating 10000 records in table 'sbtest'...
I check my table and have all the data on great, but I want to work
with InnoDB. Now let see what happen with InnoDB:
sysbench --test=oltp --db-driver=mysql --mysql-user=root
--mysql-password=mypassword --mysql-db=sbtest
--mysql-table-engine=innodb --oltp-test-mode=simple
--oltp-table-size=10000 --mysql-socket=/var/lib/mysql/mysql.sock
--num-threads=8 --max-requests=1000 prepare
sysbench 0.4.10:? multi-threaded system evaluation benchmark
Creating table 'sbtest'...
ALERT: failed to execute MySQL query: `CREATE INDEX k on sbtest(k)`:
ALERT: Error 1031 Table storage engine for 'sbtest' doesn't have this option
FATAL: failed to create secondary index on table!
U can see the error, I start reading the src and found the part that
handle this process, but I still don't know if I'm not using the right
parameters or is a app issue, the file called:
sysbench/tests/oltp/sb_oltp.c
Have this:
line 371:
snprintf(query, query_len,
"CREATE TABLE %s ("
"id %s %s NOT NULL %s, "
"k integer %s DEFAULT '0' NOT NULL, "
"c char(120) DEFAULT '' NOT NULL, "
"pad char(60) DEFAULT '' NOT NULL, "
"PRIMARY KEY (id) "
?????????? ") %s",
args.table_name,
(args.auto_inc && driver_caps.serial) ? "SERIAL" : "INTEGER",
driver_caps.unsigned_int ? "UNSIGNED" : "",
(args.auto_inc && driver_caps.auto_increment) ?
"AUTO_INCREMENT" : "",
driver_caps.unsigned_int ? "UNSIGNED" : "",
(table_options_str != NULL) ? table_options_str : ""
);
Latter at line 410:
/* Create secondary index on 'k' */
snprintf(query, query_len,
"CREATE INDEX k on %s(k)",
args.table_name);
if (db_query(con, query) == NULL)
{
log_text(LOG_FATAL, "failed to create secondary index on table!");
goto error;
}
Working with InnoDB plugin on MySQL 5.1.50 community-edition x64, I
can see that once u created a table InnoDB u cannot add a another
Index, I still learning InnoDB, must possible but sysbench looks like
is not doing the right process.
I have try to fix this but my knowledge in programing are not to updated.
Someone here have see this behavior? Do I'm doing something wrong?
Does someone work with sysbench and InnoDB without this issue?
OS: Centos
Kernel: 5.52.6.18-194.11.3.el5xen
x64.
Thanks!!!