Venu Anuganti Blog

mysql> show status like '%cache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_hits             | 0        |
| Qcache_inserts          | 0        |
| Qcache_not_cached       | 1        |
| Qcache_queries_in_cache | 0        |
+-------------------------+----------+
11 rows in set (0.01 sec)
 
mysql> create table t1(c1 int);
Query OK, 0 rows affected (0.00 sec)
 
mysql> insert into t1 values(10),(20),(30);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
 
mysql> select * from t1;
+------+
| c1   |
+------+
|   10 |
|   20 |
|   30 |
+------+
3 rows in set (0.00 sec)
 
mysql> prepare stmt1 from 'select * from t1 where c1=?';
Query OK, 0 rows affected (0.00 sec)
Statement prepared
 
mysql> set @a=10;
Query OK, 0 rows affected (0.00 sec)
 
mysql> execute stmt1 using @a;
+------+
| c1   |
+------+
|   10 |
+------+
1 row in set (0.00 sec)
 
mysql> show status like '%Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_hits             | 0        |
| Qcache_inserts          | 1        |
| Qcache_not_cached       | 1        |
| Qcache_queries_in_cache | 1        |
+-------------------------+----------+
8 rows in set (0.00 sec)
 
mysql> select * from t1 where c1=10;
+------+
| c1   |
+------+
|   10 |
+------+
1 row in set (0.00 sec)
 
mysql> show status like '%Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_hits             | 1        |
| Qcache_inserts          | 1        |
| Qcache_not_cached       | 2        |
| Qcache_queries_in_cache | 1        |
+-------------------------+----------+
8 rows in set (0.00 sec)
 
mysql> select * from t1 where c1=20;
+------+
| c1   |
+------+
|   20 |
+------+
1 row in set (0.00 sec)
 
mysql> show status like '%Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_hits             | 1        |
| Qcache_inserts          | 2        |
| Qcache_not_cached       | 2        |
| Qcache_queries_in_cache | 2        |
+-------------------------+----------+
8 rows in set (0.01 sec)
 
mysql> set @a=20;
Query OK, 0 rows affected (0.02 sec)
 
mysql> execute stmt1 using @a;
+------+
| c1   |
+------+
|   20 |
+------+
1 row in set (0.00 sec)
 
mysql> show status like '%Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_hits             | 2        |
| Qcache_inserts          | 2        |
| Qcache_not_cached       | 2        |
| Qcache_queries_in_cache | 2        |
+-------------------------+----------+
8 rows in set (0.00 sec)
 
mysql> select * from t1 where c1=20;
+------+
| c1   |
+------+
|   20 |
+------+
1 row in set (0.00 sec)
 
mysql> show status like '%Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_hits             | 3        |
| Qcache_inserts          | 2        |
| Qcache_not_cached       | 2        |
| Qcache_queries_in_cache | 2        |
+-------------------------+----------+
8 rows in set (0.00 sec)
 
mysql> select * from t1 where c1=30;
+------+
| c1   |
+------+
|   30 |
+------+
1 row in set (0.00 sec)
 
mysql> show status like '%Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_hits             | 3        |
| Qcache_inserts          | 3        |
| Qcache_not_cached       | 2        |
| Qcache_queries_in_cache | 3        |
+-------------------------+----------+
8 rows in set (0.00 sec)
 
mysql> set @a=30;
Query OK, 0 rows affected (0.00 sec)
 
mysql> execute stmt1 using @a;
+------+
| c1   |
+------+
|   30 |
+------+
1 row in set (0.00 sec)
 
mysql> show status like '%Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_hits             | 4        |
| Qcache_inserts          | 3        |
| Qcache_not_cached       | 2        |
| Qcache_queries_in_cache | 3        |
+-------------------------+----------+
8 rows in set (0.00 sec)

Threads started!
ALERT: failed to execute mysql_stmt_execute(): Err1062 Duplicate entry 'XXXXX' for key X
FATAL: database error, exiting...

-- sysbench-0.5.0/sysbench/drivers/mysql/drv_mysql.c   2006-10-10 12:22:51.000000000 -0700
+++ venu-patch/sysbench/drivers/mysql/drv_mysql.c       2007-12-22 02:30:36.492383000 -0800
@@ -65,6 +65,7 @@
    SB_ARG_TYPE_STRING, "auto"},
   {"mysql-ssl", "use SSL connections, if available in the client library", SB_ARG_TYPE_FLAG, "off"},
   {"myisam-max-rows", "max-rows parameter for MyISAM tables", SB_ARG_TYPE_INT, "1000000"},
+  {"mysql-ignore-duplicates", "Ignore the duplicate key errors", SB_ARG_TYPE_FLAG, "off"},
 
   {NULL, NULL, SB_ARG_TYPE_NULL, NULL}
 };
@@ -87,6 +88,7 @@
   unsigned int       myisam_max_rows;
   mysql_drv_trx_t    engine_trx;
   unsigned int       use_ssl;
+  unsigned int       ignore_duplicates;
 } mysql_drv_args_t;
 
 #ifdef HAVE_PS
@@ -232,6 +234,10 @@
   args.db = sb_get_value_string("mysql-db");
   args.myisam_max_rows = sb_get_value_int("myisam-max-rows");
   args.use_ssl = sb_get_value_flag("mysql-ssl");
+  args.ignore_duplicates = sb_get_value_flag("mysql-ignore-duplicates");
+
+  if (args.ignore_duplicates)
+       log_text(LOG_ALERT, "WARNING: Duplicate Key errors will be ignored");
 
   use_ps = 0;
 #ifdef HAVE_PS
@@ -640,6 +646,12 @@
       if (rc == ER_LOCK_DEADLOCK || rc == ER_LOCK_WAIT_TIMEOUT ||
           rc == ER_CHECKREAD)
         return SB_DB_ERROR_DEADLOCK;
+         if (args.ignore_duplicates && rc == ER_DUP_ENTRY) {
+            log_text(LOG_ALERT, "WARNING: Ignoring the duplicate error on execute mysql_stmt_execute(): Err%d %s",
+                    mysql_errno(con->ptr),
+                    mysql_error(con->ptr));
+            return SB_DB_ERROR_NONE;
+         }
       log_text(LOG_ALERT, "failed to execute mysql_stmt_execute(): Err%d %s",
                mysql_errno(con->ptr),
                mysql_error(con->ptr));

mysql> LOAD DATA LOCAL INFILE 'venudata-all.txt' INTO TABLE DOCUMENT_DESC FIELDS TERMINATED BY ',';
ERROR 1148 (42000): The used command is not allowed with this MySQL version
mysql> show variables like '%infile%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | ON    |
+---------------+-------+
1 row in set (0.00 sec)

| urls  | CREATE TABLE `urls` (
  `url_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `url` varbinary(2000) NOT NULL,
  `url_status` enum('EXISTS','DEAD','N/A') NOT NULL,
  `yservice_id` smallint(5) unsigned DEFAULT NULL,
  `created_by` smallint(5) unsigned NOT NULL,
  `modified_by` smallint(5) unsigned NOT NULL,
  `created_on` datetime NOT NULL,
  `modified_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `nocheck` tinyint(1) NOT NULL DEFAULT '0',
  `url_md5` char(32) NOT NULL,
  PRIMARY KEY (`url_id`),
  UNIQUE KEY `url_unique` (`url_md5`),
  KEY `modified_on` (`modified_on`),
  KEY `url` (`url`(50)),
  KEY `yservice_id` (`yservice_id`),
  KEY `modified_by` (`modified_by`),
  KEY `created_by` (`created_by`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
 
 
| urls_categories | CREATE TABLE `urls_categories` (
  `url_id` int(10) unsigned NOT NULL,
  `category_id` tinyint(3) unsigned NOT NULL,
  `workflow` enum('NEW','ACTIVATED','DEACTIVATED','REMOVED') NOT NULL,
  `searchtype_id` tinyint(3) unsigned NOT NULL,
  `work_id` tinyint(3) unsigned NOT NULL,
  `created_by` smallint(5) unsigned NOT NULL,
  `modified_by` smallint(5) unsigned NOT NULL,
  `created_on` datetime NOT NULL,
  `modified_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `comment` varchar(200) NOT NULL,
  PRIMARY KEY (`url_id`,`category_id`),
  KEY `search_index` (`modified_on`,`workflow`,`category_id`),
  KEY `rd_index` (`url_id`,`category_id`,`workflow`),
  KEY `category_id` (`category_id`),
  KEY `work_id` (`work_id`),
  KEY `modified_by` (`modified_by`),
  KEY `created_by` (`created_by`),
  KEY `searchtype_id` (`searchtype_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

mysql> explain select SQL_NO_CACHE * from urls u inner join urls_categories uc  using (url_id) order by uc.modified_on limit 10\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: uc
         type: index
possible_keys: PRIMARY,rd_index
          key: search_index
      key_len: 6
          ref: NULL
         rows: 3121058
        Extra:
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: u
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: test.uc.url_id
         rows: 1
        Extra:
2 rows in set (0.00 sec)

mysql> explain select SQL_NO_CACHE * from urls u inner join urls_categories uc  using (url_id) order by uc.modified_on limit 10\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: uc
         type: ALL
possible_keys: PRIMARY,rd_index
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3121045
        Extra: Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: u
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: test.uc.url_id
         rows: 1
        Extra:
2 rows in set (0.00 sec)

mysql> explain select SQL_NO_CACHE * from urls u inner join urls_categories uc force key(search_index,PRIMARY,rd_index)  using (url_id) order by uc.modified_on limit 10\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: uc
         type: ALL
possible_keys: PRIMARY,rd_index
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3121045
        Extra: Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: u
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: test.uc.url_id
         rows: 1
        Extra:
2 rows in set (0.00 sec)
 
mysql> explain select SQL_NO_CACHE * from urls u inner join urls_categories uc force key(search_index)  using (url_id) order by uc.modified_on limit 10\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: uc
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3121045
        Extra: Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: u
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: test.uc.url_id
         rows: 1
        Extra:
2 rows in set (0.00 sec)

mysql> select SQL_NO_CACHE * from urls u inner join urls_categories uc  using (url_id) order by uc.modified_on limit 10;
...
...
10 rows in set (1 min 14.55 sec)

mysql> select SQL_NO_CACHE * from urls u inner join urls_categories uc  using (url_id) order by uc.modified_on limit 10;
...
...
10 rows in set (0.00 sec)