Watch the number of average queries per second
Please pay attention to the load average of the server
I was astonished when I saw these numbers. Does anyone has the same MySQL performance?
The server we are talking about is a replicating slave server (double XEON processor, 1GB RAM) of www.inter.it, during AC Milan-Inter Milan match.
Watch the number of average queries per second
Please pay attention to the load average of the server
I was astonished when I saw these numbers. Does anyone has the same MySQL performance?
The server we are talking about is a replicating slave server (double XEON processor, 1GB RAM) of www.inter.it, during AC Milan-Inter Milan match.
In some situation (as described below in "how to repeat") the
table_rows column
in the information_schema.tables table is not computed
correctly.
The problem arise only for tables included in a merge table.
It seems that if you issue a select statement on a "merged table"
the following
insert statement does not update the metadata table_rows.
How-to-repeat
First of all let's try the example showing the expected behavior
mysql> create table t1 (a int);
Query OK, 0 rows affected (0.00 sec)
mysql> create table t2 (a int);
Query OK, 0 rows affected (0.01 sec)
mysql> create table t (a int) engine=merge union=(t1,t2)
insert_method=last;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t values(1),(2);
Query OK, 2 …
In some situation (as described below in “how to repeat”) the
table_rows column
in the information_schema.tables table is not computed
correctly.
The problem arise only for tables included in a merge table.
It seems that if you issue a select statement on a “merged table”
the following
insert statement does not update the metadata table_rows.
How-to-repeat
First of all let’s try the example showing the expected behavior
mysql> create table t1 (a int);
Query OK, 0 rows affected (0.00 sec)
mysql> create table t2 (a int);
Query OK, 0 rows affected (0.01 sec)
mysql> create table t (a int) engine=merge union=(t1,t2)
insert_method=last;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t values(1),(2);
Query OK, 2 rows …
I found a bug on the view implementation.
According to the manual some view is updatable if the view
satisfies the "one-to-one relationship" between the rows in the
view definition and the rows in the underlying tables. To be more
specific a view is not updatable if it contains any of the
following:
So, let's try this simple example:
mysql> create table t1 (a int);
Query OK, 0 rows affected (0.21 sec)
mysql> create …
I found a bug on the view implementation.
According to the manual some view is updatable if the view satisfies the “one-to-one relationship” between the rows in the view definition and the rows in the underlying tables. To be more specific a view is not updatable if it contains any of the following:
So, let’s try this simple example:
mysql> create table t1 (a int);
Query OK, 0 rows affected (0.21 sec)
mysql> create view v1 as select a from t1;
Query OK, 0 rows affected (0.00 sec) …
Today I tried on my database the WITH QUERY EXPANSION clause on a fulltext search.
I manage the website of a famous italian soccer club (www.inter.it), and we
have hundreds of thousands of pictures of actual and former
players, coaches, events and so on.
Every day a back-office tool involve our web editors in finding
pictures based on captions. I developed such a feature using a
simple full-text search.
So, let's try to find all pictures about "goalkeepers"
(unfortunately this word is not mainly used in picture
captions)
mysql> select count(*) from media where match(caption_en)
against('goalkeeper');
+----------+
| count(*) |
+----------+
| 60 |
+----------+
Let's try now the same query using WITH QUERY EXPANSION
mysql> select …
Today I tried on my database the WITH QUERY EXPANSION clause on a fulltext search.
I manage the website of a famous italian soccer club (www.inter.it), and we
have hundreds of thousands of pictures of actual and former
players, coaches, events and so on.
Every day a back-office tool involve our web editors in finding
pictures based on captions. I developed such a feature using a
simple full-text search.
So, let’s try to find all pictures about “goalkeepers”
(unfortunately this word is not mainly used in picture
captions)
mysql> select count(*) from media where match(caption_en)
against('goalkeeper');
+----------+
| count(*) |
+----------+
| 60 |
+----------+
Let’s try now the same query using WITH QUERY EXPANSION
mysql> select …
In MySQL 5, at the moment you can't write a recursive stored
functions. It is forbidden.
Instead you can write a recursive stored procedure. That is not
permitted by default but modifying a variable you can achieve
such a recursion.
The variable is max_sp_recursion_depth.
SET GLOBAL max_sp_recursion_depth = 0.
A value of zero means: "no recursion" (the default value)
SET GLOBAL max_sp_recursion_depth=255
A value greater then zero means: the maximun number of nested
recursion in a procedure. Max value is 255.
Pay attention to the variable named thread_stack, it
is the amount of memory allocated for the stack of a thread.
Writing a recursive routine the tipical error is to create an
infinite recursion An infinite recursion leads very soon to fill
up the stack. (the default value of the thread stack is less than
200K)
So, if you have …
If you are using replication and you have defined
binlog-ignore-db options on your master
configuration, pay attention to the fact that "if you have an
application that issues qualified notation queries without a
default database defined (e.g. USE mydb), that queries will never
replicate".
According to the explanation on the manual at "5.12.3. The Binary Log" in the case you have
options like binlog-ignore-db a query is written on
the binlog only if you have specified the default database
before. If not, sorry, your query don't replicate on the slaves
:-(
I faced the problem when one of my applications didn't replicate updates. I thought that was a bug and here you can see my bug-report, but I discovered soon that it wasn't. It's not a bug, it's a …
[Read more]