February 26, 2008

Query Cache - Prepared Statements

For years, MySQL never supported query cache with prepared statements. When I initially implemented MySQL prepared statements and its associated C API in MySQL 4.1 version; prepared statements were just saving the parsing cost of the query and nothing much. But now, the things are totally different.

Lot of developers stayed away from prepared statements due to this limitation (atleast for select statements). But now the latest MySQL 5.1 server does support the caching of prepared statements with some limitations.

I just wrote a simple C API test which uses mysql_prepare, mysql_execute and mysql_real_query and all seemed to work as expected and the cache works even with intermixed (prepare and non-prepare) on the same binary protocol. Here is a simple example how the QCache works with MySQL command line client when queries were intermixed with prepared and non-prepare execution on 5.1.23 server.

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
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173

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)
Posted @ 3:33 pm :: Database, MySQL :: No comments

February 24, 2008

Windows Vista - External USB hard drive

Since couple of weeks on my Windows Vista (Dell Precision 470, 2 Xeon with 2G RAM), the external USB hard disk (500GB Western Digital MyBook) is getting annoyed, still could not figured it out what is going on. Suddenly the drive contents becomes empty even though the disk is mounted…and doing a power recycle on the external disk does get the disk remounted and access to the contents…but still was wondering why it is becoming so…

Its not one time or two times, it happened more than 10 times in the last one month. Yesterday even my Sandisk MP3 player became in the same way…this leads to suspect that there is something hidden bug in VISTA which is causing this or might be my system as this is almost 2 years old.

Posted @ 1:22 pm :: General :: No comments

February 8, 2008

MySQL MyISAM and Merge Tables Incompatibility - II

Few months back we noticed the incompatibility change related to MyISAM that was introduced in 4.1.23 and recent 5.0 and 5.1 MySQL versions which breaks when one wants to upgrade to 4.1.23 or latest 5.0 or 5.1 when the MyISAM or Merga table is created with 4.0 or earlier 4.1 version.

I reported a bug to MySQL team way back; and looks like they now pushed a patch to 5.1.

This happens even with Merge tables and looks like merge tables are overlooked in that bug report and I am not 100% sure if the patch fixes the merge table issue. This bug is triggered only when the MyISAM table is created with a key column and Merge is associated with that table.

For example, consider the following MyISAM and Merge tables in 4.1:

1
2
3
4
5
6
7
8
9
10

mysql> CREATE TABLE IF NOT EXISTS base_t1 (
    -> id int NOT NULL PRIMARY KEY
    -> ) TYPE = MyISAM;
Query OK, 0 rows affected (0.00 sec)
 
mysql>
mysql> CREATE TABLE IF NOT EXISTS merge_t1 (
    -> id int NOT NULL PRIMARY KEY
    -> ) TYPE = MRG_MyISAM INSERT_METHOD=LAST UNION=(base_t1);
Query OK, 0 rows affected (0.01 sec)

and when it comes to 5.0 or 5.1; the merge table completely fails to open the table files.

1
2
3
4
5
6
7
8
9
10

mysql> desc base_t1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) |      | PRI | 0       |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
 
mysql> desc merge_t1;
ERROR 1168 (HY000): Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist

So, the only option is to drop and re-create the tables back in the latest MySQL version. In case if the MyISAM table had blob/text fields; then you need to drop and re-create both MyISAM and Merge tables. Even though you can re-create the merge table, the table creation works; but again it fails when you try to do any operation on the table, so one needs to fix MyISAM as well.

1
2
3
4
5
6
7
8

mysql> create table mt4(id int) TYPE=MRG_MYISAM INSERT_METHOD=LAST UNION=(t4);
Query OK, 0 rows affected, 1 warning (0.00 sec)
 
mysql> select * from mt3;
Empty set (0.00 sec)
 
mysql> select * from mt4;
ERROR 1168 (HY000): Unable to open underlying table which is differently defined or of non-MyISAM type

I mad a patch in the internal Yahoo! builds to overcome from this issue and hopefully MySQL will address both the issues as lot of people might start migrating from the existing old tables (even though it is always a good idea to re-create the tables and populate the data when there is major version change)

Posted @ 11:22 pm :: MySQL :: 1 comment

February 4, 2008

MySQL Enterprise Monitor

Today I attended a webex demo of MySQL Enterprise Monitor from MySQL. As most of the Yahoo’s are interested in learning about this tool, so arranged a web-ex demo from MySQL. MySQL is kind enough to host this event.

I always thought a dedicated monitoring and alerting system is completely missing from MySQL product line for all these days, and I can see that this tool is heading in the right direction to capture the market. Currently it monitors all server variables, errors and identifies any critical conditions upfront to avoid a disaster. But the new features that are in the pipeline for the coming months seemed to be promising (like upgrade adviser, Load balancing, query analyzer and connection manager).

As this is not like a single node stand-alone architecture, instead it works on client-server architecture where the agent runs in the same box as that of MySQL server and collects and reports to centralized service repository (dashboard) where you have all the information.

Time for me to get the evaluation copy and start looking into how well we can adopt this.

Posted @ 6:23 pm :: MySQL :: No comments