December 22, 2007

Sysbench - test bails on duplicate key entries

Since few days am doing a benchmark of mysql 4.1 and 5.1 on different hardware using sysbench so that I can test MySQL along with threads, cpu and IO related. But most of the time, when I mix the OLTP test with mysql to have writes; then the test bails when there is a duplicate key entry:

1
2
3

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

as I run with multiple threads using a shell script; which runs for about 12-16 hrs…so I did not wanted some tests to bail in middle…as having a duplicate key for the updates is fine … so applied a simple patch by having extra argument –mysql-ignore-duplicates, which makes the test to continue even on the duplicate key errors … another option is to reissue the query with different numbers, but thought this is enough for base testing.

Here is the patch file for mysql-ignore-duplicates and can also be downloaded from here.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

-- 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));
Posted @ 11:32 am :: Database :: No comments

December 13, 2007

MySQL 5.1 - LOAD DATA INFILE

Today to debug one of the issue related to InnoDB where the INSERT INTO .. SELECT simply bails with an error as “ERROR 1105 (HY000): Unknown error”; I simply started loading the data onto 5.1 server as it was consistent in 4.1 version (even though it was noted as fixed). So, LOAD DATA INFILE suddenly pops with an error as ..

1
2
3
4
5
6
7
8
9

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)

As you can see I have the local_infile enabled in server. And further reading enabled me that I need to start my mysql command line utility also with –local-infile option… well…looks like time for me to read more documentation and feature changes.

Posted @ 11:11 am :: Database, MySQL :: No comments

December 9, 2007

iTunes could not connect to the iPhone

Well..thats the case since few weeks …. even earlier, when I plugin my iPhone to the PC desktop running Windows Vista (happens on XP too)..on and off iTunes does not connect immediately when iPhone is attached to the system and it keeps recycling the connection by beeping..so, reboot will normally fix that …

But lately iTunes crashes most of the time when I plugin my iPhone and only option is to kill it from the Task manager. Looks like a bug to me in the iTunes.. and I have the latest iTunes along with the latest up2date software on my iPhone as well.

Here is the screen shot of the error from iTunes when it crashed..

Posted @ 1:30 am :: iPhone :: 3 comments

Internet Explorer History

I started using IE7 since its beta releases and I never had any major problems so far and on and off I tried Firefox and always decided to use IE. Even when I had a Macbook Pro, one main drawback was lack of IE support and the browsers out there does not render some of my daily visited websites completely…

Anyway, back to what is IE now..I have the history to keep for 20 days (may be default) …but after every 20 days IE automatically deletes all my browsing URLs history but keeps all other stuff in the cache. But the funny thing is; it deletes all the URLs history which are used everyday (most of them are used multiple times a day).. am still wondering why don’t IE just delete the ones which are not used for last 20 days instead of deleting everything for every 20 days..

Now, i just changed my history to 999 days…lets see .. if it at least keeps it for 2 years..

Posted @ 1:19 am :: Internet Explorer :: No comments

December 6, 2007

Two Years With Yahoo! Publisher Network

On 2005-Dec-05, I received the first check from Yahoo! publisher network (YPN) and may be I started using it a month before that. Even though I could not find any info on when I joined the YPN, may be good idea if they could display in the YPN profile whats the age of the account…I am still successfully running the YPN on all my blog sites (currently this is the only active blog, rest of them are all history).

Before switching to YPN, I used to have Google adwords; and once I made a switch, I never had a chance to look back…and never had any problems with YPN…interestingly I found YPN was displaying the content related ads much better than adwords by picking the tags from the blog entry…where as adwords most of the time used to display random ads which are not even related in any form.

Posted @ 8:26 pm :: General, Yahoo! :: 1 comment

December 5, 2007

MySQL 5.1 - query optimizer regression

Since few days I was debugging a case from Yahoo! Japan team (thanks to Akira Kitada) where the query runs fine in about 0 secs with the 5.1.16 build where as the same query takes almost close to 2 minutes with the latest 5.1.22 release.

One can simply identify that this is a optimizer issue as it is not picking the right index. Lets consider the following two tables urls and urls_categories…

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

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

And here is the explain output for a simple inner join query when ran with MySQL version 5.1.16:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

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)

and here is what you get when ran with MySQL 5.1.22:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

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)

as you can cleary see, for urls_categories table, the key search_index is missing and optimizer is not picking that. Even if I force to use the key, still no effect:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49

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)

As a result of the above bad index use, the query takes about 1m 20 secs with 5.1.22 where as the same one runs in about 0.0 secs as shown below..

with 5.1.22:

1
2
3
4

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)

with 5.1.16:

1
2
3
4

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)

Here is the bug report to mysql team: MySQL 5.1 optimizer regression.

Posted @ 7:55 pm :: Database, MySQL :: No comments

December 4, 2007

Brought Nintendo Wii

Finally after debating for a while which gaming console to own, opted to buy Nintendo Wii (NTDOY.PK).

It was pretty much easy to setup as in the quick setup guide and it took about 5 minutes. I have to spend 10 minutes to read the operations manual in order to get started with Wii console. I was surprised to see the built-in wireless connection which connected to Nintendo after the setup and updated with the latest 3.1 software version.

Once the system is up2date, I started playing few games like bowling, tennis and boxing after setting up my own Mii. I liked all three games in the sports game which came as part of the console, even though started playing tennis more often than the rest now.

The only drawback I noticed is its composite video output. As I connected to my 42″ Philips LCD HDTV the images are not that crispy due to 480P output from Wii console. As I have a HDMI up converter Phillips DVD player, may be worth to test by using this as the intermediate layer between TV and Wii Console to see how it outputs. Either way, its a great genius product from Nintendo.

Posted @ 9:24 pm :: Personal :: 3 comments