tag:blogger.com,1999:blog-46149600189348579752024-02-20T13:14:00.312+00:00MablomyAll I need to say about MySQL.Unknownnoreply@blogger.comBlogger30125tag:blogger.com,1999:blog-4614960018934857975.post-90376969532055782472019-05-04T10:15:00.000+01:002019-05-14T17:39:21.764+01:00Finding Tables without Primary KeyHaving a primary key defined for each user table is best practice for performance in InnoDB. And when using Group Replication or InnoDB Cluster for automatic high availability it is (almost) mandatory. (The full story see <a href="https://lefred.be/content/mysql-group-replication-and-table-design/" target="_blank">here</a>.) So it is wise to check if you have tables running without primary key. You can identify these tables by running:<br />
<div style="font-size: 11pt; margin: 0cm 0cm 0.0001pt;">
<span style="font-family: "courier new" , "courier" , monospace;">SELECT t.table_schema, t.table_name FROM tables AS t </span></div>
<div style="font-size: 11pt; margin: 0cm 0cm 0.0001pt;">
<span style="font-family: "courier new" , "courier" , monospace;"> LEFT JOIN key_column_usage AS c </span></div>
<div style="font-size: 11pt; margin: 0cm 0cm 0.0001pt;">
<span style="font-family: "courier new" , "courier" , monospace;"> ON (t.table_name = c.table_name AND </span></div>
<div style="font-size: 11pt; margin: 0cm 0cm 0.0001pt;">
<span style="font-family: "courier new" , "courier" , monospace;"> c.constraint_schema = t.table_schema AND </span></div>
<div style="font-size: 11pt; margin: 0cm 0cm 0.0001pt;">
<span style="font-family: "courier new" , "courier" , monospace;"> c.constraint_name = 'PRIMARY' ) </span></div>
<div style="font-size: 11pt; margin: 0cm 0cm 0.0001pt;">
<span style="font-family: "courier new" , "courier" , monospace;"> WHERE t.table_schema NOT IN (</span><span style="font-family: "courier new" , "courier" , monospace; font-size: 11pt;">"mysql", </span><span style="font-family: "courier new" , "courier" , monospace; font-size: 11pt;">"information_schema", </span></div>
<div style="font-size: 11pt; margin: 0cm 0cm 0.0001pt;">
<span style="font-family: "courier new" , "courier" , monospace;"> "performance_schema", "sys") </span></div>
<div style="font-size: 11pt; margin: 0cm 0cm 0.0001pt;">
<span style="font-family: "courier new" , "courier" , monospace;"> AND</span><span style="font-family: "courier new" , "courier" , monospace; font-size: 11pt;"> c.constraint_name IS NULL</span></div>
<div style="font-size: 11pt; margin: 0cm 0cm 0.0001pt;">
<span style="font-family: "courier new" , "courier" , monospace; font-size: 11pt;"> </span><span style="font-family: "courier new" , "courier" , monospace;">AND t.table_type = "BASE TABLE"</span><span style="font-family: "courier new" , "courier" , monospace; font-size: 11pt;">;</span><span style="font-family: "courier new" , "courier" , monospace; font-size: 11pt;"> </span></div>
<br />
And if you want to make life even easier, you can add this as a report to the sys schema:<br />
<div style="font-size: 11pt; margin: 0cm 0cm 0.0001pt;">
<span style="font-family: "courier new" , "courier" , monospace;">CREATE VIEW sys.schema_tables_without_pk AS</span></div>
<div style="margin: 0cm 0cm 0.0001pt;">
<div style="font-size: 11pt; margin: 0cm 0cm 0.0001pt;">
<span style="font-family: "courier new" , "courier" , monospace;">SELECT t.table_schema, t.table_name FROM tables AS t </span></div>
<div style="font-size: 11pt; margin: 0cm 0cm 0.0001pt;">
<span style="font-family: "courier new" , "courier" , monospace;"> LEFT JOIN key_column_usage AS c </span></div>
<div style="font-size: 11pt; margin: 0cm 0cm 0.0001pt;">
<span style="font-family: "courier new" , "courier" , monospace;"> ON (t.table_name = c.table_name AND </span></div>
<div style="font-size: 11pt; margin: 0cm 0cm 0.0001pt;">
<span style="font-family: "courier new" , "courier" , monospace;"> c.constraint_schema = t.table_schema AND </span></div>
<div style="font-size: 11pt; margin: 0cm 0cm 0.0001pt;">
<span style="font-family: "courier new" , "courier" , monospace;"> c.constraint_name = 'PRIMARY' ) </span></div>
<div style="font-size: 11pt; margin: 0cm 0cm 0.0001pt;">
<span style="font-family: "courier new" , "courier" , monospace;"> WHERE t.table_schema NOT IN (</span><span style="font-family: "courier new" , "courier" , monospace; font-size: 11pt;">"mysql", </span><span style="font-family: "courier new" , "courier" , monospace; font-size: 11pt;">"information_schema", </span></div>
<div style="font-size: 11pt; margin: 0cm 0cm 0.0001pt;">
<span style="font-family: "courier new" , "courier" , monospace;"> "performance_schema", "sys") </span></div>
<div style="font-size: 11pt; margin: 0cm 0cm 0.0001pt;">
<span style="font-family: "courier new" , "courier" , monospace;"> AND</span><span style="font-family: "courier new" , "courier" , monospace; font-size: 11pt;"> c.constraint_name IS NULL</span></div>
<div style="font-size: 11pt; margin: 0cm 0cm 0.0001pt;">
<span style="font-family: "courier new" , "courier" , monospace; font-size: 11pt;"> AND t.table_type = "BASE TABLE";</span><span style="font-family: "courier new" , "courier" , monospace; font-size: 11pt;"> </span></div>
<div style="font-size: 11pt; margin: 0cm 0cm 0.0001pt;">
<br /></div>
<div style="font-size: 11pt; margin: 0cm 0cm 0.0001pt;">
<span style="font-size: small;">It is easy to detect but a little more challenging to solve. You need to consider your application and potential load when adding a new primary key. One solution is to add a new auto_increment column. In many cases this might help already. In other cases you already have a natural primary key in your table definition, It's just not defined as such.</span></div>
<div style="font-size: 11pt; margin: 0cm 0cm 0.0001pt;">
<span style="font-size: small;"><br /></span></div>
<div style="margin: 0cm 0cm 0.0001pt;">
To add auto_increment columns to all affected tables (which I do not recommend without thinking about it and testing first!), you can use the beauty of the Python mode in MySQL Shell:</div>
<div style="margin: 0cm 0cm 0.0001pt;">
<div class="MsoNormal" style="font-size: 11pt; margin: 0cm 0cm 0.0001pt;">
<span style="font-family: "courier new" , "courier" , monospace;"><br /></span></div>
<div class="MsoNormal" style="font-size: 11pt; margin: 0cm 0cm 0.0001pt;">
<span style="font-family: "courier new" , "courier" , monospace;">$ mysqlsh root@localhost:33060 --py<o:p></o:p></span></div>
<div class="MsoNormal" style="font-size: 11pt; margin: 0cm 0cm 0.0001pt;">
<span style="font-family: "courier new" , "courier" , monospace;">MySQL Shell 8.0.16<o:p></o:p></span></div>
<div class="MsoNormal" style="font-size: 11pt; margin: 0cm 0cm 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="font-size: 11pt; margin: 0cm 0cm 0.0001pt;">
<span style="font-family: "courier new" , "courier" , monospace;">Copyright (c) 2016, 2019, Oracle and/or its affiliates. All rights reserved.<o:p></o:p></span></div>
<div class="MsoNormal" style="font-size: 11pt; margin: 0cm 0cm 0.0001pt;">
<span style="font-family: "courier new" , "courier" , monospace;">Oracle is a registered trademark of Oracle Corporation and/or its affiliates.<o:p></o:p></span></div>
<div class="MsoNormal" style="font-size: 11pt; margin: 0cm 0cm 0.0001pt;">
<span style="font-family: "courier new" , "courier" , monospace;">Other names may be trademarks of their respective owners.<o:p></o:p></span></div>
<div class="MsoNormal" style="font-size: 11pt; margin: 0cm 0cm 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="font-size: 11pt; margin: 0cm 0cm 0.0001pt;">
<span style="font-family: "courier new" , "courier" , monospace;">Type '\help' or '\?' for help; '\quit' to exit.<o:p></o:p></span></div>
<div class="MsoNormal" style="font-size: 11pt; margin: 0cm 0cm 0.0001pt;">
<span style="font-family: "courier new" , "courier" , monospace;">Creating a session to 'root@localhost:39010'<o:p></o:p></span></div>
<div class="MsoNormal" style="font-size: 11pt; margin: 0cm 0cm 0.0001pt;">
<span style="font-family: "courier new" , "courier" , monospace;">Fetching schema names for autocompletion... Press ^C to stop.<o:p></o:p></span></div>
<div class="MsoNormal" style="font-size: 11pt; margin: 0cm 0cm 0.0001pt;">
<span style="font-family: "courier new" , "courier" , monospace;">Your MySQL connection id is 762 (X protocol)<o:p></o:p></span></div>
<div class="MsoNormal" style="font-size: 11pt; margin: 0cm 0cm 0.0001pt;">
<span style="font-family: "courier new" , "courier" , monospace;">Server version: 8.0.16-commercial MySQL Enterprise Server - Commercial<o:p></o:p></span></div>
<div class="MsoNormal" style="font-size: 11pt; margin: 0cm 0cm 0.0001pt;">
<span style="font-family: "courier new" , "courier" , monospace;">No default schema selected; type \use <schema> to set one.<o:p></o:p></span></div>
<div class="MsoNormal" style="font-size: 11pt; margin: 0cm 0cm 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="font-size: 11pt; margin: 0cm 0cm 0.0001pt;">
<span style="font-family: "courier new" , "courier" , monospace;">MySQL localhost:33060+ ssl Py ><b>l=session.get_schema("sys").get_table("schema_tables_without_pk").select().execute().fetch_all()</b><o:p></o:p></span></div>
<div class="MsoNormal" style="font-size: 11pt; margin: 0cm 0cm 0.0001pt;">
<span style="font-family: "courier new" , "courier" , monospace;">MySQL localhost:33060+ ssl Py ><b>for val in l: session.sql("ALTER TABLE "+val[0]+"."+val[1]+" ADD COLUMN (__id int unsigned auto_increment PRIMARY KEY)");</b></span><span style="font-family: "calibri" , sans-serif;"><o:p></o:p></span></div>
<div class="MsoNormal" style="font-family: Calibri, sans-serif; font-size: 11pt; margin: 0cm 0cm 0.0001pt;">
<br /></div>
</div>
</div>
Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-4614960018934857975.post-7289101123650664832019-03-09T07:29:00.000+00:002019-03-09T07:29:59.455+00:00TTL - Perfect Accuracy by using an insertable VIEWOne more comment regarding TTL in MySQL:<br />
If you are looking for perfect accuracy and never want to access rows that are older than the defined TTL you can hide the table t (from my previous <a href="http://mablomy.blogspot.com/2019/03/ttl-time-to-live-in-mysql.html" target="_blank">post</a>) behind a view. This view will automatically select only rows within TTL lifespan:<br />
<span style="font-family: Courier New, Courier, monospace;">CREATE VIEW ttl as SELECT id, content, created_at FROM t </span><br />
<span style="font-family: Courier New, Courier, monospace;"> WHERE created_at >= NOW() - INTERVAL 10 SECOND;</span><br />
This view is insertable, so you can fully use this view and you are not distracted by the additional column "bucket".<br />
<span style="font-family: Courier New, Courier, monospace;">INSERT INTO ttl VALUES (NULL, "This is a test", NULL);</span><br />
You could even exclude column "created_at" from the view definition, if there was not bug <a href="https://bugs.mysql.com/bug.php?id=94550" target="_blank">#94550</a>. 'created_at' could be fully handled internally.<br />
This view does not affect performance much. In my simple test it did not show any affect. Just better usability and better accuracy of TTL.<br />
<h3>
Limitations</h3>
You cannot use foreign keys with your ttl'ed table and view. This is because partitioning and foreign keys are <a href="https://dev.mysql.com/doc/refman/8.0/en/partitioning-limitations.html" target="_blank">mutually exclusive</a>. If you need foreign keys go with the simple delete event procedure and forget about the view.<br />
Due to bug <a href="https://bugs.mysql.com/bug.php?id=94550" target="_blank">#94550</a> you have to set<span style="font-family: inherit;"> <span style="caret-color: rgb(68, 68, 68); color: #444444;"><i>explicit_defaults_for_timestamp</i></span><span style="background-color: white; color: #444444;"> to </span><span style="caret-color: rgb(68, 68, 68); color: #444444;"><i>OFF</i> and you </span></span>always have to insert NULL into column 'created_at'.<br />
In this whole setup the TTL is mentioned in four locations: In the partitioning definition, in the definition of the generated column 'bucket', in the cleaning event procedure and in the WHERE clause of the view. This makes it easier to screw up the setup. Make sure you use the same value everywhere. Same applies for the number of partitions in the table definition as well as the cleaning event procedure.Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-4614960018934857975.post-83513030945255764052019-03-08T10:17:00.002+00:002019-03-09T06:52:35.792+00:00TTL - Time-to-Live in MySQLA customer recently asked for a TTL feature in MySQL. The idea is to automatically delete rows from a certain table after a defined lifespan, e.g. 60 seconds. This feature is common in many NoSQL databases, but it is not available in MySQL. However MySQL offers all you need to implement this. And due to partitioning much more efficient than only deleting rows. Let's test it.<br />
<h3>
tl;dr</h3>
Partition the table and truncate partitions in a regular event procedure, that does the trick and comes at a fraction of the cost of regularly deleting rows. <br />
<h3>
The test case</h3>
The table needs a column to keep track of row age. This can be either a "created_at" column or an "expires_at" column. ("expires_at" has the additional advantage that each row can have an individual lifespan. Not possible in many NoSQL solutions.)<br />
So my table is<br />
CREATE TABLE `t` (<br />
`id` int(10) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,<br />
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,<br />
`content` varchar(42) DEFAULT NULL,<br />
);<br />
I tested two variants to implement a 10 seconds TTL on my table "t":<br />
<h3>
The simple solution</h3>
Run an event every 10 seconds to delete rows that have been created more than 10s ago.<br />
DELIMITER | <br />
CREATE EVENT ttl_delete<br />
ON SCHEDULE EVERY 10 SECOND STARTS '2019-03-04 16:00:00' DISABLE<br />
DO BEGIN<br />
DELETE FROM t WHERE created_at < NOW() - INTERVAL 10 SECOND;<br />
END |<br />
DELIMITER ;<br />
And index on "created_at" might improve performance for the DELETE job. But in any case it is quite expensive to scan the table and remove roughly 50% of the rows of a table, at least if the INSERT rate is high.<br />
<h3>
The efficient solution</h3>
Instead of DELETing we can use the much faster TRUNCATE operation. Obviously we do not want to TRUNCATE the whole table but if we distribute the inserted rows into partitions it is safe to truncate any partition that contains outdated rows. Let's define three partitions (or buckets): One that is currently being written to, one that holds rows of the last 10 seconds and one partition that can be truncated because the rows are older than 10 seconds. Key is to calculate the bucket from the current time. This can be done with the expression <span style="font-family: "courier new" , "courier" , monospace;">FLOOR(TO_SECONDS(NOW()/10)) % 3</span>, or more generic <span style="font-family: "courier new" , "courier" , monospace;">FLOOR(TO_SECONDS(NOW()/<i><b>ttl</b></i>))% <b>number_of_buckets</b><span style="font-family: inherit;"> </span></span><br />
Now we can partition the table by this expression. For that we add a generated column to calculate the bucket from the column "created_at" and partition the table by column "bucket". The table now looks like this:<br />
<span style="font-family: "courier new" , "courier" , monospace;">CREATE TABLE `t` (<br /> `id` int(10) unsigned NOT NULL AUTO_INCREMENT,<br /> `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,<br /> `content` varchar(42) DEFAULT NULL,<br /> `bucket` tinyint(4) GENERATED ALWAYS AS </span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> (floor(TO_SECONDS(`created_at`) / 10) % 3) STORED NOT NULL,<br /> PRIMARY KEY (`id`,`bucket`)<br />) PARTITION BY LIST (`bucket`)<br />(PARTITION p0 VALUES IN (0),<br /> PARTITION p1 VALUES IN (1),<br /> PARTITION p2 VALUES IN (2));</span><br />
And the event procedure is like this:<br />
<span style="font-family: "courier new" , "courier" , monospace;">DELIMITER |<br />CREATE EVENT ttl_truncate<br /> ON SCHEDULE EVERY 10 SECOND STARTS '2019-03-04 16:00:00' DISABLE<br /> DO BEGIN<br /> CASE FLOOR(TO_SECONDS(NOW())/10)%3<br /> WHEN 0 THEN ALTER TABLE test.t TRUNCATE PARTITION p1;<br /> WHEN 1 THEN ALTER TABLE test.t TRUNCATE PARTITION p2;<br /> WHEN 2 THEN ALTER TABLE test.t TRUNCATE PARTITION p0;<br /> END CASE;<br /> END|<br />DELIMITER ;</span><br />
<h3>
Watching the rows come and go</h3>
To verify that the procedure works as expected I created a small monitor procedure that displays each second the number of rows per partition. Then it is easy to follow in which partition data is currently added and when a partition gets truncated.<br />
<span style="font-family: "courier new" , "courier" , monospace;">DELIMITER |<br />CREATE PROCEDURE monitor()<br />BEGIN <br /> WHILE 1=1 DO<br /> SELECT "p0" AS "part", count(*) FROM t PARTITION (p0) <br /> UNION SELECT "p1", count(*) FROM t PARTITION (p1) <br /> UNION SELECT "p2", count(*) FROM t PARTITION (p2);</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> SELECT now() AS "NOW", floor(to_seconds(now())/10)%3 AS "Bucket";<br /> SELECT sleep(1);<br /> END WHILE;<br />END|<br />DELIMITER ;</span><br />
This procedure is not ideal. Too many count(*) will create quite some locking. But it is accurate. The alternative is to read data from INFORMATION_SCHEMA.partitions, but this does not give the exact row count, which I needed for verification. <br />
<h3>
Increasing Accuracy</h3>
If TTL is 10 seconds, deleting or truncating every 10 seconds means you have <i>at least</i> 10 seconds of rows available. In reality you will have 10 to 20 seconds worth of data, so on average 15 seconds (assuming a constant INSERT rate). If you run the cleaner job more often (say once per second) the average number of rows is 10.5 seconds worth of data. This comes at the cost of running the cleaning event more often. But it might be very beneficial to increase this accuracy because all other queries benefit from less data to operate on and less memory consumed by expired rows.<br />
If you go with the simple solution of a regular DELETE event, it is sufficient to schedule the event more often.<br />
If you prefer the TRUNCATE PARTITION option, it is necessary to increase the number of partitions or buckets to 12 (= 2 + TTL / how often to run the cleaning job).<br />
The expression for the calculated bucket column will be<br />
<span style="font-family: "courier new" , "courier" , monospace;">`bucket` tinyint(4) GENERATED ALWAYS AS </span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> (floor(TO_SECONDS(`created_at`) / <b><i>TTL</i></b>) % <i><b>#buckets</b></i>) STORED NOT NULL</span><br />
and the partitioning needs to be adapted as well. <br />
And the CASE construct in the cleaner event must be extended for each newly existing bucket/partition:<br />
<span style="font-family: "courier new" , "courier" , monospace;"> WHEN <i><b>n</b></i> THEN ALTER TABLE test.t TRUNCATE PARTITION p<i><b>(n+1)</b></i>;</span><br />
<h3>
What happens if...</h3>
... the event stops?<br />
Then you keep all your rows which will likely create some follow-up problems. As always: Proper monitoring is key. Think about <a href="https://www.mysql.com/products/enterprise/monitor.html" target="_blank">MySQL Enterprise Monitor</a> for example.<br />
<br />
... the event procedure runs at inaccurate timing due to overall system load?<br />
No big problem. It will never run too early. So it will never remove rows too early. If it runs too late it will clean rows too late so you have more garbage in your table which might affect other queries. The real TTL is increased if this happens.<br />
<h3>
Performance Considerations</h3>
By no means I am able to run proper performance tests. I am running on a Win10 laptop, VirtualBox with Oracle Linux and MySQL runs inside a Docker container. So plenty of reason to achieve bad numbers. But to compare the two implementations it should be sufficient.<br />
I have extended the cleaner events to report the time needed to execute the event procedure. Here the example of the simple cleaner job:<br />
<span style="font-family: "courier new" , "courier" , monospace;">CREATE EVENT ttl_delete<br /> ON SCHEDULE EVERY 10 SECOND STARTS '2019-03-04 16:00:00' DISABLE<br /> DO BEGIN<br /> <b>DECLARE t1,t2 TIME(6);<br /> SET t1=current_time(6);</b><br /> DELETE FROM t WHERE created_at < NOW() - INTERVAL 10 SECOND;</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> <b>SET t2=current_time(6);<br /> INSERT INTO ttl_report VALUES ("DELETE simple", now(),<br /> timediff(t2,t1));</b><br /> END|<br />DELIMITER ; </span><br />
The load was generated by mysqlslap, which only inserted rows in the table. Each test run starts the respective cleaner event, runs the mysqlslap load and then stops the cleaner event.<br />
<span style="font-family: "courier new" , "courier" , monospace;">mysql -h 127.0.0.1 -uroot -pXXX -e \<br /> "USE test; ALTER event ttl_delete ENABLE;"<br /> </span><br />
<span style="font-family: "courier new" , "courier" , monospace;">mysqlslap -h 127.0.0.1 -uroot -pXXX --create-schema=test \ </span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> --concurrency=5 --iterations=20 --number-of-queries=10000 </span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> --query="INSERT INTO test.t (created_at, content) VALUES </span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> (NULL,md5(id));"<br /> </span><br />
<span style="font-family: "courier new" , "courier" , monospace;">mysql -h 127.0.0.1 -uroot -pXXX -e \</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> "USE test; ALTER event ttl_delete DISABLE;"<br /><br />mysql -h 127.0.0.1 -uroot -pXXX -e \</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> "USE test; ALTER event ttl_truncate ENABLE;"<br /> </span><br />
<span style="font-family: "courier new" , "courier" , monospace;">mysqlslap -h 127.0.0.1 -uroot -pXXX --create-schema=test </span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> --concurrency=5 --iterations=20 --number-of-queries=10000 </span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> --query="INSERT INTO test.t (created_at, content) VALUES </span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> (NULL,md5(id));"<br />mysql -h 127.0.0.1 -uroot -pXXX -e \</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> "USE test; ALTER event ttl_truncate DISABLE;"</span><br />
<br />
The results are clearly in favor of truncating partitions. And the difference should be even higher the higher the INSERT rate gets. My poor setup achieved only less than 1000 inserts per second... <br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;">select who, avg(how_long) from ttl_report GROUP BY who;<br />+---------------+--------------------+<br />| who |avg(how_long) |<br />+---------------+--------------------+<br />| DELETE simple | 1.1980474444444444 |</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">| truncate | 0.0400163333333333 |<br />+---------------+--------------------+<br />3 rows in set (0.0014 sec)</span><br />
<br />
<h3>
Side note</h3>
You might wonder why my test load is<br />
<span style="font-family: "courier new" , "courier" , monospace;">INSERT INTO test.t (created_at, content) VALUES (NULL,'foo');"</span><br />
Why do I mention the column "created_at" but then store NULL to give it the default of current_timestamp? If I omit the created_at column in this INSERT statement I get an error from the generated column due to bug <a href="https://bugs.mysql.com/bug.php?id=94550" target="_blank">#94550</a>. Setting <i>explicit_defaults_for_timestamp</i> to <i>OFF </i>and then mentioning the timestamp column during INSERT is a workaround.Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-4614960018934857975.post-84456694004777295852017-07-28T14:30:00.001+01:002017-07-28T19:18:46.330+01:00Node.js and MySQL on the Oracle CloudLet's explore how to deploy a node.js app with MySQL backend on the Oracle Cloud. I want to cover several aspects:<br />
<ul>
<li>How to deploy and initialize a MySQL instance</li>
<li>How to adapt the source code</li>
<li>How to deploy the application</li>
<li>How to scale the application</li>
</ul>
There are different ways to configure this. I tested the easiest deployment with MySQL Cloud Service and the Application Container Cloud for the node.js part. All configurations are done via the cloud web GUI. There is also a REST interface available. But let's keep that for later.<br />
If you don't have access to the Oracle Cloud you can get a trial access <a href="https://cloud.oracle.com/en_US/tryit" target="_blank">here</a>. <br />
<h3>
How to deploy a MySQL instance</h3>
Once you logged into the Oracle cloud you can create new instances from the dashboard. The following screenshots describe the process.<br />
<div class="separator" style="clear: both; text-align: center;">
</div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjGwqTTd9SLwRc5FW5Lv5a4-87AtFVtU2A34z1Iv-kByY-cdHhghsaFkbyWSR2-GvwN2f13y_g12wxyy7rxoYabgbfdq9PZrb6jPWHngbd1M5l_K-cQlY4GgkL-UFUsrju3YEXYc5WNjHDT/s1600/Bildschirmfoto+2017-07-28+um+10.47.25.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="414" data-original-width="1011" height="262" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjGwqTTd9SLwRc5FW5Lv5a4-87AtFVtU2A34z1Iv-kByY-cdHhghsaFkbyWSR2-GvwN2f13y_g12wxyy7rxoYabgbfdq9PZrb6jPWHngbd1M5l_K-cQlY4GgkL-UFUsrju3YEXYc5WNjHDT/s640/Bildschirmfoto+2017-07-28+um+10.47.25.png" width="640" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
</div>
On the next screen we upload the public key for admin access to the instance. Either upload your own public key or generate a new key pair. (If you generate a new key pair you need to download the private key to your local machine.)<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh-Z2ZhH14iI0CqLS_zfxK5zu0_rTZ7U1xl5X3roq1ozkcBfJh220pFnTxXa6PXRX-HuClvDiAmm9K5AXZst5Xd_6ZNmSU0L5hkuS3jdSB7JzRX7xs2Nwg0ZsKETa0Hp0zOqHuw0AzRjcb4/s1600/Bildschirmfoto+2017-07-28+um+09.38.45.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="756" data-original-width="1023" height="472" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh-Z2ZhH14iI0CqLS_zfxK5zu0_rTZ7U1xl5X3roq1ozkcBfJh220pFnTxXa6PXRX-HuClvDiAmm9K5AXZst5Xd_6ZNmSU0L5hkuS3jdSB7JzRX7xs2Nwg0ZsKETa0Hp0zOqHuw0AzRjcb4/s640/Bildschirmfoto+2017-07-28+um+09.38.45.png" width="640" /></a></div>
<br />
I skipped the backup and monitoring configurations for this demo. Let's focus on the application instead. After creating the instance (approx. 10 min) you can navigate via the dashboard to this instance and get the IP address. This is needed for the next step.<br />
To initialize the database I ran this little script that runs ssh to the instance (using the private key), switch user to "oracle" and then call the MySQL CLI to run a few SQL statements.<br />
<script src="https://gist.github.com/mablomy/a60f43109defa77d03d3b0ae9e2656a4.js"></script>
<br />
<h3>
How to adapt the source code<ul>
</ul>
</h3>
The Application Container Cloud sets a few environment variables that should be used inside the application to adapt to the environment. In my case this are the following variables:<br />
<div>
<ul>
<li>PORT is the port number that the application should listen on</li>
<li>MYSQLCS_USER_NAME is the MySQL user name for the database backend</li>
<li>MYSQLCS_USER_PASSWORD is the corresponding password</li>
<li>MYSQLCS_CONNECT_STRING is the hostname and port of the database backend</li>
</ul>
</div>
<div>
I could have hardcoded the database connection parameters but that is inflexible if the architecture changes. So let's use these variables. The Node.js code looks like this:</div>
<div>
<br /></div>
<script src="https://gist.github.com/mablomy/94e5c0f73b1e98ed38f9bc2707281118.js"></script>
<br />
<div>
<h3>
How to deploy the application</h3>
There are two simple steps needed: Creating an application and defining service bindings. In my case the only service binding is the MySQL backend. But one step after the other. First let's create the application. First you need to create a manifest.json file to describe the application. Here is mine:</div>
<div>
<script src="https://gist.github.com/mablomy/fc56dce8190ed06038d9228058638a31.js"></script>
</div>
<div>
<br />
Ideally you create a zip archive with the source code, the manifest.json file and all other resources that your application needs. If you want to use my zip archive, feel free. You find it on <a href="https://github.com/mablomy/mess/blob/master/anota.zip" target="_blank">GitHub</a>.</div>
<div>
From the Oracle Cloud Dashboard click on "create instance -> application container" and then select "Create Instance" and "Node.js". (Java SE, Java EE, Python, Ruby and PHP are available as well.)<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgj6GZi9DrDKytgTDNGJ5SXX7NEMih9-KESejVs4oKoL_j8r0rnTob3U8doxyBCDYnUUKTbVIz1Xc99Wtq47fP4GU6Fr9L9w6KAjcd4bxpqTBFfjAAzfk_Y3ueETTU2AD-at4BrMN6HW6SE/s1600/Bildschirmfoto+2017-07-28+um+13.16.26.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="620" data-original-width="531" height="640" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgj6GZi9DrDKytgTDNGJ5SXX7NEMih9-KESejVs4oKoL_j8r0rnTob3U8doxyBCDYnUUKTbVIz1Xc99Wtq47fP4GU6Fr9L9w6KAjcd4bxpqTBFfjAAzfk_Y3ueETTU2AD-at4BrMN6HW6SE/s640/Bildschirmfoto+2017-07-28+um+13.16.26.png" width="548" /></a></div>
<br />
On the pop-up you define the application artifacts, number of application instances and the memory per instance. After you click "create" the application is deployed automatically within a few minutes.<br />
The last step is to connect the application service with the database backend. To achieve that, click on the application in the application overview page. Here you find the URL under which your application will be available. And on the left hand side you see three tabs:<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj-J_5K8Qo6kc14xfBnlQggOaXPgUJKd7msk2gVjkf1B0O7DO2-bGZmFPxvwLRc7e5CFrpNSxo1BTiAeg2d9gD9DaXcOsFYcG5Sbb3CCSGyqQx8al_Nn7yTmTwwjyeLxw8mneezY7EDFlCp/s1600/Bildschirmfoto+2017-07-28+um+14.10.29.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="542" data-original-width="1026" height="338" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj-J_5K8Qo6kc14xfBnlQggOaXPgUJKd7msk2gVjkf1B0O7DO2-bGZmFPxvwLRc7e5CFrpNSxo1BTiAeg2d9gD9DaXcOsFYcG5Sbb3CCSGyqQx8al_Nn7yTmTwwjyeLxw8mneezY7EDFlCp/s640/Bildschirmfoto+2017-07-28+um+14.10.29.png" width="640" /></a></div>
<br />
Overview, Deployments and Administration. Click on "Deployments". Here you can add the service binding as described in the following screenshot:<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhlR9PnzhxfQ9pOZQrq6jVYMPbqH-RUOHOoUCyFrjWIgTu_KK6j5mINN-BbGT7ZVpuQZYMW8jLnbRnAqVpBWFp1dmufIeHt1rrSqD8CMd4AKd4PXgZecc-BvjiXNsS-UUymRQh_PTj57uJp/s1600/Bildschirmfoto+2017-07-28+um+14.14.55.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="326" data-original-width="1007" height="206" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhlR9PnzhxfQ9pOZQrq6jVYMPbqH-RUOHOoUCyFrjWIgTu_KK6j5mINN-BbGT7ZVpuQZYMW8jLnbRnAqVpBWFp1dmufIeHt1rrSqD8CMd4AKd4PXgZecc-BvjiXNsS-UUymRQh_PTj57uJp/s640/Bildschirmfoto+2017-07-28+um+14.14.55.png" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
After modifying the service bindings you have to click "Apply changes". This will restart the application instances. Obviously needed because now the environment variables for the database backend are set correctly.<br />
That's it. We have an application. The URL to access the new app is listed in the application overview tab. Because this URL is not so nice for offering a short url service, I registered a new domain and forwarded that to the anota application. Maybe it is still running? Check <a href="http://anota.de/" target="_blank">here</a>.</div>
<div>
<h3>
How to scale the application</h3>
This is really easy. On the application overview tab you can just increase the number of instances and the memory per instance. After applying the changes, the Application Container Cloud platform will deploy new instances, stop spare instances or reconfigure the existing instances. If you use my ANOTA application, go to the <a href="http://anota.de/report.anota" target="_blank">report page</a>. The last line prints the hostname of the application server. Requests are automatically load balanced between the available application instances. </div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhVl9cnnOSckmMHJIWy9acU3Oe6RW1myw6B7JCeey-Py1gXV0XL0MEHfaJHNaHgB_PYcGLCwARBt_CnNjHnsiHuNtx8iG7uzHDZqlunYcFWOgT1X5i1uRgQ3ShBEvC8tzYgxnLgFhQnpor6/s1600/Bildschirmfoto+2017-07-28+um+14.54.35.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="498" data-original-width="1017" height="312" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhVl9cnnOSckmMHJIWy9acU3Oe6RW1myw6B7JCeey-Py1gXV0XL0MEHfaJHNaHgB_PYcGLCwARBt_CnNjHnsiHuNtx8iG7uzHDZqlunYcFWOgT1X5i1uRgQ3ShBEvC8tzYgxnLgFhQnpor6/s640/Bildschirmfoto+2017-07-28+um+14.54.35.png" width="640" /></a></div>
<h3>
Summary</h3>
<div>
There are some minor changes to the application to run on the Oracle Cloud Platform: Reading the port variable and database connection parameters from the provided environment variables and that's it. Deployment is really easy via the GUI. And scalability is so simple now that the full Oracle Cloud Plattform is available and can be provisioned within minutes. </div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div>
<br /></div>
Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-4614960018934857975.post-37321117475604087132017-06-01T09:13:00.000+01:002017-06-01T09:15:23.449+01:00MySQL Shell - Easy scriptingWith the introduction of MySQL InnoDB Cluster we also got the MySQL Shell (mysqlsh) interface. The shell offers scripting in Javascript (default), SQL or Python. This offers a lot more options for writing scripts on MySQL, for example it is much easier now to use multiple server connections in a single script.<br />
A customer recently asked for a way to compare the transaction sets between servers. That is useful when setting up replication or identifying the server that has most transactions applied already. So I wrote this little script which can be executed from the OS shell:<span style="font-family: "courier new" , "courier" , monospace;"><br /></span><br />
<pre style="background: #f0f0f0; border: 1px dashed #cccccc; color: black; font-family: "arial"; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;"> #!/usr/bin/mysqlsh -f
// it is important to connect to the X protocol port,
// usually it is the traditional port + "0"
//
var serverA="root:root@localhost:40010"
var serverB="root:root@localhost:50010"
shell.connect(serverA)
var gtidA=session.sql("SELECT @@global.gtid_executed").execute().fetchOne()[0]
shell.connect(serverB)
var gtidB=session.sql("SELECT @@global.gtid_executed").execute().fetchOne()[0]
//
// If you want to use pure XdevAPI the former statements should be
//
// gtid = session.getSchema("performance_schema").global_variables.select("VARIABLE_VALUE").where("VARIABLE_NAME='gtid_executed'").execute().fetchOne()[0]
//
println(" ")
println ("Transactions that exist only on "+serverA)
println (session.sql("SELECT gtid_subtract('"+gtidA+"','"+gtidB+"')").execute().fetchOne()[0])
println(" ")
println ("Transactions that exist only on "+serverB)
println (session.sql("SELECT gtid_subtract('"+gtidB+"','"+gtidA+"')").execute().fetchOne()[0])
</code></pre>
Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-4614960018934857975.post-88785272511977619852016-04-13T14:57:00.000+01:002016-04-13T15:00:01.477+01:00Query Rewrite Plugin and Binlog for ReplicationStarting with MySQL 5.7 we introduced the Query Rewrite Plugin. That tool is really useful for changing queries. Of course the best location to modify the query is the source code of the application, but this is not always possible. Either the application is not under your control or queries are generated from a framework like Hibernate and sometimes it is hard to change the query generation.<br />
If you are interested in details about the Query Rewrite Plugin, I recommend this blogpost from the MySQL Engineering: <a href="http://mysqlserverteam.com/the-query-rewrite-plugins/">http://mysqlserverteam.com/the-query-rewrite-plugins/</a><br />
Recently I was asked how this works in replication environments. Which query goes into the binlog?<br />
<br />
If you are using the Rewriter plugin that comes with MySQL 5.7, the answer is easy: This plugin only supports rewriting SELECT queries. SELECT queries don't get into the binlog at all. Simple.<br />
<br />
But you might write your own preparse or postparse plugin. In that case you can define the behavior with the server option --log-raw. See documentation here: <a href="https://dev.mysql.com/doc/refman/5.7/en/server-options.html#option_mysqld_log-raw">https://dev.mysql.com/doc/refman/5.7/en/server-options.html#option_mysqld_log-raw</a><br />
You can either bring the original query to the binlog or the rewritten query. So all flexibility you need. However be aware that --log-raw also affects logging of passwords in the general log file. With --log-raw passwords are written in plain text to the log files. So consider this side effect when switching --log-raw on or off.Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-4614960018934857975.post-66511029065020116652016-04-04T08:42:00.003+01:002016-04-13T15:00:37.891+01:00MySQL 5.7: Optimizer finds best index by expression The optimizer in MySQL 5.7 leverages generated columns. Generated columns will physically store data in two cases: Either the column is defined as STORED or you create an index on a virtual column. The optimizer will leverage such an index automatically if it encounters the same expression in a statement. Let's see an example:<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;">mysql> DESC squares;<br />+-------+------------------+------+-----+---------+-------+<br />| Field | Type | Null | Key | Default | Extra |<br />+-------+------------------+------+-----+---------+-------+<br />| dx | int(10) unsigned | YES | | NULL | |<br />| dy | int(10) unsigned | YES | | NULL | |<br />+-------+------------------+------+-----+---------+-------+<br />2 rows in set (0.00 sec)<br /><br />mysql> SELECT COUNT(*) FROM squares;<br />+----------+<br />| COUNT(*) |<br />+----------+<br />| 2097152 |<br />+----------+<br />1 row in set (0.77 sec)</span><br />
<br />
We have a large table with 2 million rows. Selecting rows by the surface area of squares can hardly leverage an index on dx or dy:<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;">mysql> EXPLAIN SELECT * FROM squares WHERE dx*dy=221\G<br />*************************** 1. row ***************************<br /> id: 1<br /> select_type: SIMPLE<br /> table: squares<br /> partitions: NULL<br /> type: ALL<br />possible_keys: NULL<br /> key: NULL<br /> key_len: NULL<br /> ref: NULL<br /> rows: 2092860<br /> filtered: 100.00<br /> Extra: Using where<br />1 row in set, 1 warning (0.00 sec)</span><br />
Now let's add an index over a generated, virtual column that defines the area:<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;">mysql> ALTER TABLE squares ADD COLUMN (area INT AS (dx*dy));<br />Query OK, 0 rows affected (0.02 sec)<br />Records: 0 Duplicates: 0 Warnings: 0<br /><br />mysql> ALTER TABLE squares ADD INDEX (area);<br />Query OK, 0 rows affected (5.24 sec)<br />Records: 0 Duplicates: 0 Warnings: 0</span><br />
<br />
Now we can run query again:<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;">mysql> EXPLAIN SELECT * FROM squares WHERE dx*dy=221\G<br />*************************** 1. row ***************************<br /> id: 1<br /> select_type: SIMPLE<br /> table: squares<br /> partitions: NULL<br /> type: ref<br /><span style="color: red;"><b>possible_keys: area<br /> key: area</b></span><br /> key_len: 5<br /> ref: const<br /> rows: 18682<br /> filtered: 100.00<br /> Extra: NULL<br />1 row in set, 1 warning (0.00 sec)</span><br />
I did not change the query! The WHERE condition is still dx*dy. Nevertheless the optimizer finds the generated column, sees the index and decides to leverage that.<br />
So you can add complex indexes and without changing the application code you can benefit from these indexes. That makes life much easier.<br />
<br />
One limitation though: It seems the optimizer recognizes expressions only in the WHERE clause. It will not use the generated column and index for the SELECT expression:<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;">mysql> EXPLAIN SELECT SUM(dx*dy) FROM squares\G<br />*************************** 1. row ***************************<br /> id: 1<br /> select_type: SIMPLE<br /> table: squares<br /> partitions: NULL<br /> type: ALL<br /><span style="color: red;"><b>possible_keys: NULL<br /> key: NULL</b></span><br /> key_len: NULL<br /> ref: NULL<br /> rows: 2092860<br /> filtered: 100.00<br /> Extra: NULL<br />1 row in set, 1 warning (0.00 sec)<br /><br />mysql> EXPLAIN SELECT SUM(area) FROM squares\G<br />*************************** 1. row ***************************<br /> id: 1<br /> select_type: SIMPLE<br /> table: squares<br /> partitions: NULL<br /> type: index<br />possible_keys: NULL<br /> <span style="color: red;"><b>key: area</b></span><br /> key_len: 5<br /> ref: NULL<br /> rows: 2092860<br /> filtered: 100.00<br /> <span style="color: red;"><b>Extra: Using index</b></span><br />1 row in set, 1 warning (0.00 sec)</span><br />
<br />Unknownnoreply@blogger.com2tag:blogger.com,1999:blog-4614960018934857975.post-62120394653949024722016-04-04T07:59:00.004+01:002019-05-14T17:26:10.719+01:00CHECK constraint for MySQL - NOT NULL on generated columns<span style="background-color: yellow;">Update: Starting with MySQL 8.0.16 we do have CHECK constraints implemented in SQL! See <a href="http://dasini.net/blog/2019/05/14/check-constraints-in-mysql/" target="_blank">here</a>.</span><br />
<br />
During our recent TechTour event the idea came up to implement JSON document validation not necessarily via foreign keys (as I have shown <a href="http://mablomy.blogspot.de/2015/11/document-validation-of-json-columns-in.html" target="_blank">here</a>) but to define the generated column as NOT NULL. The generation expression must be defined in a way that it returns NULL for invalid data.<br />
DISCLAIMER: This has already been explored by <a href="http://yoku0825.blogspot.de/2015/08/an-idea-for-using-mysql-57s-generated.html" target="_blank">yoku0825 in his blogpost</a>. He deserves all credit!<br />
<br />
Let's do a short test:<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;">mysql> CREATE TABLE checker ( </span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> i int, </span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> i_must_be_between_7_and_12 BOOLEAN </span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> AS (IF(i BETWEEN 7 AND 12, true, NULL)) </span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> VIRTUAL NOT NULL);<br />Query OK, 0 rows affected (0.04 sec)<br /><br />mysql> INSERT INTO checker (i) VALUES (11);<br />Query OK, 1 row affected (0.01 sec)<br /><br />mysql> INSERT INTO checker (i) VALUES (12);<br />Query OK, 1 row affected (0.01 sec)<br /><br />mysql> INSERT INTO checker (i) VALUES (13);<br />ERROR 1048 (23000): Column 'i_must_be_between_7_and_12' cannot be null</span><br />
<br />
<br />
<br />
As you can see I used the column name to create a meaningful error message when inserting invalid data. It is perfectly possible to add a generated validation column for each data column so that you run several check constraints.<br />
Or you can even check a combination of columns:<br />
<br />
<span style="font-family: "courier new" , "courier" , monospace;">mysql> CREATE TABLE squares (</span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> dx DOUBLE, </span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> dy DOUBLE, </span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> area_must_be_larger_than_10 BOOLEAN </span><br />
<span style="font-family: "courier new" , "courier" , monospace;"> AS (IF(dx*dy>10.0,true,NULL)) NOT NULL);<br />Query OK, 0 rows affected (0.05 sec)<br /><br />mysql> INSERT INTO squares (dx,dy) VALUES (7,4);</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">Query OK, 1 row affected (0.01 sec)<br /><br />mysql> INSERT INTO squares (dx,dy) VALUES (2,4);</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">ERROR 1048 (23000): Column 'area_must_be_larger_than_10' cannot be null</span><br />
<br />
As generated columns are virtual by default this costs no extra storage. Data volume is the same. The expression is evaluated when inserting or updating data.<br />
If you add a validation column to an already existing table and want to verify all existing rows, you could define the validation column as STORED (instead of the default VIRTUAL). This will fail if there are any invalid rows in your existing data set. However in normal operation a virtual column seems more appropriate for performance reasons. So I recommend to always use VIRTUAL validation columns and check pre-existing data separately with a small procedure. Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-4614960018934857975.post-39128827770779463442016-02-02T12:21:00.002+00:002019-05-14T15:54:00.957+01:00Looking for the smallest possible MySQL Footprint<div style="margin-bottom: 0cm;">
<span style="background-color: yellow;">UPDATE: Starting with MySQL 8.0.16 we have introduced the new minimal tar ball distribution. Take a look <a href="https://mysqlrelease.com/2019/05/the-amazing-shrinking-tarball/" target="_blank">here</a>.</span><br />
<br />
MySQL is known and famous for it’s
simplicity and small size, especially compared to other RDBMSs. But
what if you want to deploy on tiny hardware? I mean something even smaller
than RaspberryPi?</div>
<div style="margin-bottom: 0cm;">
I tested three steps to make the MySQL
footprint as small as possible. All my tests were compiled for Oracle
Linux 7 on x64 platform. I did not test any ARM cross compile. And
these are the steps:</div>
<div style="margin-bottom: 0cm;">
</div>
<ol>
<li>Compile my own binary</li>
<li>Remove all unnecessary
tools/files</li>
<li>Strip symbol information from
binary</li>
</ol>
<br />
<div style="margin-bottom: 0cm;">
Let’s take a closer look at the tree
steps.</div>
<div style="margin-bottom: 0cm;">
<br /></div>
<h3>
Compile my own binary</h3>
<div style="margin-bottom: 0cm;">
MySQL is available as a source release.
Using that you can configure the make process. That is documented
pretty well in the <a href="http://dev.mysql.com/doc/refman/5.7/en/source-installation.html" target="_blank">Reference Manual</a>. By
switching off some
<a href="http://dev.mysql.com/doc/refman/5.7/en/source-configuration-options.html" target="_blank">options</a> I was able to reduce the binary size from 240MB to 216MB. I
switched off some performance_schema features, removed some storage
engines that are irrelevant in most environments anyway (like
ARCHIVE, NDB, EXAMPLE, …) and I removed all options for profiling.
The final CMAKE statement is at the bottom of this post.</div>
<div style="margin-bottom: 0cm;">
<br /></div>
<h3>
Remove unnecessary tools</h3>
<div style="margin-bottom: 0cm;">
I removed scripts and binaries from
the distribution. Ted has written an interesting <a href="http://mysql-nordic.blogspot.de/2015/02/creating-minimal-mysql-installation-for.html" target="_blank">blog post</a> about
this. The remaining share directory contains some SQL
scripts for installing additional tools. You need these at most once
during setup and never again. So let’s remove these. If you are
happy to live without textual error messages you can also remove the
errmsg-utf8.txt file as well and all translations in the country
specific subdirs. And if you can live with reduced charset support,
you can even remove the rest of the share directory. You are running
essentially only with a mysqld binary.</div>
<div style="margin-bottom: 0cm;">
<br /></div>
<h3>
Strip symbol information from binary</h3>
<div style="margin-bottom: 0cm;">
All compilations are done with extended
diagnosis information in the binary. These symbol data helps if you
want to analyze a core dump for example. Symbols are included by
default in the MySQL binaries. These take a surprisingly large amount
of space. You can remove these symbols from the binary with the tool
“<a href="http://mysql-nordic.blogspot.de/2015/02/creating-minimal-mysql-installation-for.html" target="_blank"><i>strip(1)</i></a>. After stripping
the binary size came down to 24MB, which is only 10% of the initial
size.</div>
<div style="margin-bottom: 0cm;">
<br /></div>
<h3>
More ideas</h3>
<div style="margin-bottom: 0cm;">
There are some more options to use either system libraries or the libraries that come with the source code. Using existing libraries from the system might help save a few bytes.</div>
<div style="margin-bottom: 0cm;">
<br /></div>
<h3>
Summary</h3>
<div style="margin-bottom: 0cm;">
It is possible to make MySQL very lean
for your (embedded) system. Despite all the functionality that we
added to MySQL in the releases since MySQL 5.1 you get a full
featured RDBMS with only a handful of MB. Here are my final results:</div>
<div style="margin-bottom: 0cm;">
<br /></div>
<div style="margin-bottom: 0cm;">
</div>
<ul>
<li>MySQL 5.6, minimal features: 79MB, stripped 13MB</li>
<li>MySQL 5.7, default features: 240MB, stripped 24MB</li>
<li>MySQL5.7, minimal features: 216MB, stripped 24MB (removing features brings minimal savings only)</li>
</ul>
<br />
<h3>
Addendum</h3>
<div style="margin-bottom: 0cm;">
This is the CMAKE statement I used to compile MySQL 5.7 on Oracle Linux 7:</div>
<div style="margin-bottom: 0cm;">
<span style="background-color: white; font-family: "helvetica"; font-size: 12px; line-height: normal;">cmake . -DCMAKE_INSTALL_PREFIX=/home/testy/TQ/dist-mysql-5.7.10/ \</span><br />
<span style="background-color: white; font-family: "helvetica"; font-size: 12px; line-height: normal;"> -DDOWNLOAD_BOOST=1 \</span><br />
<span style="background-color: white; font-family: "helvetica"; font-size: 12px; line-height: normal;"> -DWITH_BOOST=/home/testy/TQ/boost/ \</span><br />
<span style="background-color: white; font-family: "helvetica"; font-size: 12px; line-height: normal;"> -DDISABLE_PSI_COND=1 \</span><br />
<span style="background-color: white; font-family: "helvetica"; font-size: 12px; line-height: normal;"> -DDISABLE_PSI_FILE=1 \</span><br />
<span style="background-color: white; font-family: "helvetica"; font-size: 12px; line-height: normal;"> -DDISABLE_PSI_IDLE=1 \</span><br />
<span style="background-color: white; font-family: "helvetica"; font-size: 12px; line-height: normal;"> -DDISABLE_PSI_MEMORY=1 \</span><br />
<span style="background-color: white; font-family: "helvetica"; font-size: 12px; line-height: normal;"> -DDISABLE_PSI_METADATA=1 \</span><br />
<span style="background-color: white; font-family: "helvetica"; font-size: 12px; line-height: normal;"> -DDISABLE_PSI_MUTEX=1 \</span><br />
<span style="background-color: white; font-family: "helvetica"; font-size: 12px; line-height: normal;"> -DDISABLE_PSI_RWLOCK=1 \</span><br />
<span style="background-color: white; font-family: "helvetica"; font-size: 12px; line-height: normal;"> -DDISABLE_PSI_SOCKET=1 \</span><br />
<span style="background-color: white; font-family: "helvetica"; font-size: 12px; line-height: normal;"> -DDISABLE_PSI_SP=1 \</span><br />
<span style="background-color: white; font-family: "helvetica"; font-size: 12px; line-height: normal;"> -DDISABLE_PSI_STAGE=1 \</span><br />
<span style="background-color: white; font-family: "helvetica"; font-size: 12px; line-height: normal;"> -DDISABLE_PSI_STATEMENT=1 \</span><br />
<span style="background-color: white; font-family: "helvetica"; font-size: 12px; line-height: normal;"> -DDISABLE_PSI_STATEMENT_DIGEST=1 \</span><br />
<span style="background-color: white; font-family: "helvetica"; font-size: 12px; line-height: normal;"> -DDISABLE_PSI_TABLE=1 \</span><br />
<span style="background-color: white; font-family: "helvetica"; font-size: 12px; line-height: normal;">-DWITH_ARCHIVE_STORAGE_ENGINE=0 \</span><br />
<span style="background-color: white; font-family: "helvetica"; font-size: 12px; line-height: normal;">-DWITH_BLACKHOLE_STORAGE_ENGINE=0 \</span><br />
<span style="background-color: white; font-family: "helvetica"; font-size: 12px; line-height: normal;">-DWITH_EXAMPLE_STORAGE_ENGINE=0 \</span><br />
<span style="background-color: white; font-family: "helvetica"; font-size: 12px; line-height: normal;">-DWITH_FEDERATED_STORAGE_ENGINE=0 \</span><br />
<span style="background-color: white; font-family: "helvetica"; font-size: 12px; line-height: normal;">-DWITH_PARTITION_STORAGE_ENGINE=0 \</span><br />
<span style="background-color: white; font-family: "helvetica"; font-size: 12px; line-height: normal;">-DWITH_PERFSCHEMA_STORAGE_ENGINE=0 \</span><br />
<span style="background-color: white; font-family: "helvetica"; font-size: 12px; line-height: normal;">-DENABLED_PROFILING=0 \</span><br />
<span style="background-color: white; font-family: "helvetica"; font-size: 12px; line-height: normal;">-DENABLE_DEBUG_SYNC=0 \</span><br />
<span style="background-color: white; font-family: "helvetica"; font-size: 12px; line-height: normal;">-DENABLE_DTRACE=0 \</span><br />
<span style="background-color: white; font-family: "helvetica"; font-size: 12px; line-height: normal;">-DENABLE_GCOV=0 \</span><br />
<span style="background-color: white; font-family: "helvetica"; font-size: 12px; line-height: normal;">-DENABLE_GPROF=0 \</span><br />
<span style="background-color: white; font-family: "helvetica"; font-size: 12px; line-height: normal;">-DOPTIMIZER_TRACE=0 \</span><br />
<span style="background-color: white; font-family: "helvetica"; font-size: 12px; line-height: normal;">-DWITH_CLIENT_PROTOCOL_TRACING=0 \</span><br />
<span style="background-color: white; font-family: "helvetica"; font-size: 12px; line-height: normal;">-DWITH_DEBUG=0 \</span><br />
<span style="background-color: white; font-family: "helvetica"; font-size: 12px; line-height: normal;">-DWITH_INNODB_EXTRA_DEBUG=0 </span></div>
Unknownnoreply@blogger.com4tag:blogger.com,1999:blog-4614960018934857975.post-41493277446355680762015-11-26T14:21:00.000+00:002015-11-26T14:21:04.855+00:00JSON memory consumptionI got some more questions on the new JSON data type and functions during our TechTours. And I like to summarize the answers in this blogpost.<br />
<h3>
Memory consumption </h3>
The binary format of the JSON data type should consume more memory. But how much?
I did a little test by comparing a freshly loaded 25,000 row dataset stored as JSON and stored as TEXT. Seven top level attributes per JSON document. Average JSON_DEPTH is 5.9 . Let's see:<br />
<pre>mysql> DESC data_as_text;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| doc | text | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> SELECT COUNT(*),AVG(JSON_LENGTH(doc)) FROM data_as_text;
+----------+-----------------------+
| COUNT(*) | AVG(JSON_LENGTH(doc)) |
+----------+-----------------------+
| 25359 | 7.0000 |
+----------+-----------------------+
1 row in set (<span style="color: red;"><b>0.81 sec</b></span>)
mysql> DESC data_as_json;
+-------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| doc | json | NO | | NULL | |
+-------+---------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> SELECT COUNT(*),AVG(JSON_LENGTH(doc)) FROM data_as_json;
+----------+-----------------------+
| COUNT(*) | AVG(JSON_LENGTH(doc)) |
+----------+-----------------------+
| 25359 | 7.0000 |
+----------+-----------------------+
1 row in set (<span style="color: red;"><b>0.08 sec</b></span>)
mysql> select name,allocated_size/1024/1024 AS "size in MB" from information_schema.innodb_sys_tablespaces where name like "%temp%";
+-------------------+-------------+
| name | size in MB |
+-------------------+-------------+
| temp/data_as_json | 23.00390625 |
| temp/data_as_text | 22.00390625 |
+-------------------+-------------+
2 rows in set (0.00 sec)
</pre>
The increased memory consumption is 1/22 <b>in this case</b>, which is roughly 4,5%. At the same time you see the benefit: The full table scan with some JSON operation has a 90% reduction in runtime when using JSON datatype.<br />
Don't take this number for real. Of course it depends on the number of JSON attributes, character set and others. Just a rough indication. If you want all the details look at the JSON architecture in <a href="http://dev.mysql.com/worklog/task/?id=8132" target="_blank">WL#8132</a>. Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-4614960018934857975.post-85953820167624025502015-11-23T09:21:00.000+00:002015-11-23T09:31:03.834+00:00Document validation of JSON columns in MySQLStarting with the new release MySQL 5.7 there is support to store JSON documents in a column. During our recent Tech Tour events we got questions about document validation, so ensuring that a JSON document has a certain structure. (Funny. It all started with the idea to be schema-free. Now people seem to need schema enforcement.)<br />
I have two ideas how to implement a schema validation for JSON columns. The first one is by leveraging generated columns together with a foreign key. The second idea is by implementing a trigger. Today I want to focus on the generated columns and foreign keys.<br />
When defining foreign keys with generated columns there are two limitations we need to be aware of:<br />
<ul>
<li>Foreign keys require indexes. JSON columns cannot be indexed. We need to leverage other types.</li>
<li>Only STORED generated columns are supported for foreign keys.</li>
</ul>
So here is an example of an address table that leverages JSON to define an arbitrary number of phone number entries per row. In fact I use a mixed model of relational (e.g. to enforce a strict model for name NOT NULL) and document so that phone numbers are more free to define.<br />
<br />
<pre style="background-image: URL(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhMZusaYM1WT-OmwTEK8llMFEKNxOOqR1aDYc5rPqURJw6SEzmu1wSpyFo3KTNnGzF_39MbSSrHSw5KUVlFGZSAXLCsLreaMp_jtcwGE2KZxueVDBs_j8KTzuNZN5AEjRThr77UKNGOFEU4/s320/codebg.gif); background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;"> CREATE TABLE `people` (
`name` varchar(30) NOT NULL,
`firstname` varchar(30) DEFAULT NULL,
`birthdate` date DEFAULT NULL,
`phones` json DEFAULT NULL,
`phonekeys` varchar(30) GENERATED ALWAYS AS (json_keys(phones)) STORED,
KEY `phonekeys` (`phonekeys`));
</code></pre>
<br />
<br />
The generated column phonekeys is a string that includes the types of phone numbers for each row. Some sample data:<br />
<br />
<pre style="background-image: URL(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhMZusaYM1WT-OmwTEK8llMFEKNxOOqR1aDYc5rPqURJw6SEzmu1wSpyFo3KTNnGzF_39MbSSrHSw5KUVlFGZSAXLCsLreaMp_jtcwGE2KZxueVDBs_j8KTzuNZN5AEjRThr77UKNGOFEU4/s320/codebg.gif); background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;"> mysql> INSERT INTO people (name,firstname,birthdate,phones)
VALUES ("Plumber", "Joe, the", "1972-05-05",'{"work": "+1(555)24680"}');
Query OK, 1 row affected (0.00 sec)
...some more inserts...
mysql> SELECT * FROM people;
+---------+-----------+------------+--------------------------------------------------------+-----------------------+
| name | firstname | birthdate | phones | phonekeys |
+---------+-----------+------------+--------------------------------------------------------+-----------------------+
| Doe | John | 1995-04-17 | {"mobile": "+491715555555", "private": "+49305555555"} | ["mobile", "private"] |
| Dian | Mary | 1963-12-12 | {"work": "+43987654321"} | ["work"] |
| Plumber | Joe, the | 1972-05-05 | {"work": "+1(555)24680"} | ["work"] |
+---------+-----------+------------+--------------------------------------------------------+-----------------------+
3 rows in set (0.00 sec)
</code></pre>
<br />
<br />
The column phonekeys gets populated automatically.<br />
To check that we use "correct" attributes in our JSON object we can now create a table that contains the valid JSON keys:<br />
<br />
<pre style="font-family:arial;font-size:12px;border:1px dashed #CCCCCC;width:99%;height:auto;overflow:auto;background:#f0f0f0;;background-image:URL(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhMZusaYM1WT-OmwTEK8llMFEKNxOOqR1aDYc5rPqURJw6SEzmu1wSpyFo3KTNnGzF_39MbSSrHSw5KUVlFGZSAXLCsLreaMp_jtcwGE2KZxueVDBs_j8KTzuNZN5AEjRThr77UKNGOFEU4/s320/codebg.gif);padding:0px;color:#000000;text-align:left;line-height:20px;"><code style="color:#000000;word-wrap:normal;"> CREATE TABLE `valid_keys` (
`keylist` varchar(30) NOT NULL,
PRIMARY KEY (`keylist`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+------------+--------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
... after some inserts...
mysql> SELECT * FROM valid_keys;
+-------------------------------+
| keylist |
+-------------------------------+
| ["mobile", "private", "work"] |
| ["mobile", "private"] |
| ["work"] |
+-------------------------------+
3 rows in set (0.00 sec)
</code></pre>
<br />
Now we can define a foreign key with the people table as a child table:<br />
mysql> alter table people add foreign key (phonekeys) references valid_keys (keylist);<br />
<br />
That should enforce that inserted JSON documents in the people table must have a list of attributes that matches any entry in the valid_keys table. Let's try:<br />
<br />
<pre style="background-image: URL(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhMZusaYM1WT-OmwTEK8llMFEKNxOOqR1aDYc5rPqURJw6SEzmu1wSpyFo3KTNnGzF_39MbSSrHSw5KUVlFGZSAXLCsLreaMp_jtcwGE2KZxueVDBs_j8KTzuNZN5AEjRThr77UKNGOFEU4/s320/codebg.gif); background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;">
mysql> INSERT INTO people (name,phones) VALUES ("me", JSON_OBJECT("work","12243"));<br />
Query OK, 1 row affected (0.01 sec)<br />
<br />
mysql> INSERT INTO people (name,phones) VALUES ("my friend", JSON_OBJECT("home","12243"));<br />
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`mario`.`people`, CONSTRAINT `people_ibfk_1` FOREIGN KEY (`phonekeys`) REFERENCES `valid_keys` (`keylist`))<br />
mysql>
</code>
</pre>
<br />
<br />
Works fine. "home" is not an allowed attribute. I can leverage the foreign keys to make sure my phone numbers match a certain attribute list. However it is not perfectly simple to use. With five different allowed attributes in an arbitrary order you would have to add all permutations to the valid_keys table. With five attributes you end up with 6! permutations ("not defining an attribute" is also an option, hence six), which results in 720 rows for valid_keys. But it is a first start. For more complex explamples the ideas with triggers might be more favorable.<br />
<br />Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-4614960018934857975.post-33766312265045383322015-04-09T16:09:00.001+01:002015-04-10T13:18:08.897+01:00Secondary Indexes on XML BLOBs in MySQL 5.7When storing XML documents in a BLOB or TEXT column there was no way to create indexes on individual XML elements or attributes. With the new <a href="http://dev.mysql.com/doc/refman/5.7/en/create-table.html#create-table-generated-columns" target="_blank">auto generated columns</a> in MySQL 5.7 <a href="http://mysqlserverteam.com/the-mysql-5-7-7-release-candidate-is-available/" target="_blank">(1st Release Candidate available now!)</a> this has changed! Let me give you an example. Let's work on the following table:<br />
<pre style="background-image: URL(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhMZusaYM1WT-OmwTEK8llMFEKNxOOqR1aDYc5rPqURJw6SEzmu1wSpyFo3KTNnGzF_39MbSSrHSw5KUVlFGZSAXLCsLreaMp_jtcwGE2KZxueVDBs_j8KTzuNZN5AEjRThr77UKNGOFEU4/s320/codebg.gif); background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: 300px; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;"> mysql> SELECT * FROM country\G
*************************** 1. row ***************************
docid: 1
doc: <country>
<name>Germany</name>
<population>82164700</population>
<surface>357022.00</surface>
<city name="Berlin"><population></population></city>
<city name="Frankfurt"><population>643821</population></city>
<city name="Hamburg"><population>1704735</population></city>
</country>
*************************** 2. row ***************************
docid: 2
doc: <country>
<name>France</name>
<surface></surface>
<city name="Paris"><population>445452</population></city>
<city name="Lyon"></city>
<city name="Brest"></city>
<population>59225700</population>
</country>
*************************** 3. row ***************************
docid: 3
doc: <country>
<population>10236000</population>
<name>Belarus</name>
<city name="Brest"><population></population></city>
</country>
*************************** 4. row ***************************
docid: 4
doc: <country>
<name>Pitcairn</name>
<population>52</population>
</country>
4 rows in set (0,00 sec)
</code></pre>
<br />
The table has only two columns: docid and doc. Since MySQL 5.1 it is possible to extract the population value thanks to the <a href="http://dev.mysql.com/doc/refman/5.7/en/xml-functions.html" target="_blank">XML functions</a> like ExtractValue(...). But sorting the documents by the population of a country was impossible because population is not a dedicated column in the table. Starting with MySQL 5.7.6 DMR we can add an auto generated column that contains only the population. Let’s create that column:<br />
<br />
<pre style="background-image: URL(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhMZusaYM1WT-OmwTEK8llMFEKNxOOqR1aDYc5rPqURJw6SEzmu1wSpyFo3KTNnGzF_39MbSSrHSw5KUVlFGZSAXLCsLreaMp_jtcwGE2KZxueVDBs_j8KTzuNZN5AEjRThr77UKNGOFEU4/s320/codebg.gif); background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;"> mysql> <b>ALTER TABLE country ADD COLUMN population INT UNSIGNED AS (CAST(ExtractValue(doc,"/country/population") AS UNSIGNED INTEGER)) STORED;</b>
Query OK, 4 rows affected (0,21 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> <b>ALTER TABLE country ADD INDEX (population);</b>
Query OK, 0 rows affected (0,22 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> <b>SELECT docid FROM country ORDER BY population ASC;</b>
+-------+
| docid |
+-------+
| 4 |
| 3 |
| 2 |
| 1 |
+-------+
4 rows in set (0,00 sec)
</code></pre>
<br />
The population value is extracted automatically from each document, stored in a dedicated column and the index is maintained. Really simple now. Note that the population value of the cities is NOT extracted.<br />
<br />
What happens if we want to look for city names? Each document may contain several city names. First let’s extract the city names with the XML function and store it in an auto generated column again:<br />
<br />
<pre style="background-image: URL(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhMZusaYM1WT-OmwTEK8llMFEKNxOOqR1aDYc5rPqURJw6SEzmu1wSpyFo3KTNnGzF_39MbSSrHSw5KUVlFGZSAXLCsLreaMp_jtcwGE2KZxueVDBs_j8KTzuNZN5AEjRThr77UKNGOFEU4/s320/codebg.gif); background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: 300px; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;"> mysql> <b>ALTER TABLE country ADD COLUMN cities TEXT AS (ExtractValue(doc,"/country/city/@name")) STORED; </b>
Query OK, 4 rows affected (0,62 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> <b>SELECT docid,cities FROM country; </b>
+-------+--------------------------+
| docid | cities |
+-------+--------------------------+
| 1 | Berlin Frankfurt Hamburg |
| 2 | Paris Lyon Brest |
| 3 | Brest |
| 4 | |
+-------+--------------------------+
4 rows in set (0,01 sec)
</code></pre>
<br />
The XML function ExtractValue extracts the name attribute of all cities and concatenates these with whitespace. That makes it easy for us to leverage the FULLTEXT index in InnoDB:<br />
<br />
<pre style="background-image: URL(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhMZusaYM1WT-OmwTEK8llMFEKNxOOqR1aDYc5rPqURJw6SEzmu1wSpyFo3KTNnGzF_39MbSSrHSw5KUVlFGZSAXLCsLreaMp_jtcwGE2KZxueVDBs_j8KTzuNZN5AEjRThr77UKNGOFEU4/s320/codebg.gif); background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;"> mysql> <b>ALTER TABLE country ADD FULLTEXT (cities); </b>
mysql> <b>SELECT docid FROM country WHERE MATCH(cities) AGAINST ("Brest");</b>
+-------+
| docid |
+-------+
| 2 |
| 3 |
+-------+
2 rows in set (0,01 sec)
</code></pre>
<br />
All XML calculations are done automatically when storing data. Let’s add another XML document and query again:<br />
<br />
<pre style="background-image: URL(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhMZusaYM1WT-OmwTEK8llMFEKNxOOqR1aDYc5rPqURJw6SEzmu1wSpyFo3KTNnGzF_39MbSSrHSw5KUVlFGZSAXLCsLreaMp_jtcwGE2KZxueVDBs_j8KTzuNZN5AEjRThr77UKNGOFEU4/s320/codebg.gif); background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;"> mysql> <b>INSERT INTO country (doc) VALUES ('<country><name>USA</name><city name="New York"/><population>278357000</population></country>');</b>
Query OK, 1 row affected (0,00 sec)
mysql> SELECT * FROM country WHERE MATCH(cities) AGAINST ("New York");
+-------+----------------------------------------------------------------------------------------------+------------+----------+
| docid | doc | population | cities |
+-------+----------------------------------------------------------------------------------------------+------------+----------+
| 5 | <country><name>USA</name><city name="New York"/><population>278357000</population></country> | 278357000 | New York |
+-------+----------------------------------------------------------------------------------------------+------------+----------+
1 row in set (0,00 sec)
</code></pre>
<br />
Does this also work with JSON documents? There are <a href="https://blogs.oracle.com/svetasmirnova/entry/json_udf_functions_version_04" target="_blank">JSON functions</a> available in a labs release. These functions are currently implemented as user defined functions (UDF) in MySQL. UDFs are not supported in auto generated columns. So we have to wait until JSON functions are built-in to MySQL.<br />
UPDATE: See this <a href="http://mysqlserverteam.com/json-labs-release-effective-functional-indexes-in-innodb/" target="_blank">blogpost</a>. There is a first labs release to use JSON functional indexes. <br />
<br />
<h3>
What did we learn? tl;dr</h3>
With MySQL 5.7.6 it is possible to automatically create columns from XML elements or attributes and maintain indexes on this data. Search is optimized, MySQL is doing all the work for you. And Brest is not only in France but also a <a href="http://en.wikipedia.org/wiki/Brest,_Belarus" target="_blank">city in Belarus</a>.<br />
<br />Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-4614960018934857975.post-83301861824950182812015-03-24T19:11:00.000+00:002015-03-24T19:12:01.860+00:00Profiling Stored Procedures in MySQL 5.7With the changes to performance_schema in MySQL 5.7 Development Milestone Release it is now possible to analyze and profile the execution of stored programs. This is highly useful if you develop more complex stored procedures and try to find the bottlenecks. The "old" performance_schema up to MySQL 5.6 only reported a CALL statement with a runtime, but no information on statements that were executed WITHIN the stored procedure.
Now let's try this in the latest MySQL 5.7.6 DMR release. After creating some test table and a test stored procedure we need to activate the events_statements_history_long consumer, which is OFF by default:<br />
<br />
<span style="font-family: "Courier New",Courier,monospace;">mysql> UPDATE setup_consumers SET ENABLED="YES" </span><br />
<span style="font-family: "Courier New",Courier,monospace;"> WHERE NAME = "events_statements_history_long"; </span><br />
<br />
Then let's call the stored procedure that we want to inspect:<br />
<br />
<span style="font-family: "Courier New",Courier,monospace;">mysql> CALL test.massinsert(400,405); </span><br />
<br />
To avoid that we overwrite data from the events_statements_history_long table with the following queries, let's deactivate that consumer ASAP. If you have some concurrent load running on your system, it may be wise to leverage the filter options in performance_schema like setup_actors and/or setup_objects.<br />
<br />
<span style="font-family: "Courier New",Courier,monospace;">mysql> UPDATE setup_consumers SET ENABLED="NO" </span><br />
<span style="font-family: "Courier New",Courier,monospace;"> WHERE NAME = "events_statements_history_long"; </span><br />
<br />
Next step is to find our CALL statement in the events_statements_history_long table:<br />
<br />
<span style="font-family: "Courier New",Courier,monospace;">mysql> SELECT event_id,sql_text, </span><br />
<span style="font-family: "Courier New",Courier,monospace;"> CONCAT(TIMER_WAIT/1000000000,"ms") AS time </span><br />
<span style="font-family: "Courier New",Courier,monospace;"> FROM events_statements_history_long </span><br />
<span style="font-family: "Courier New",Courier,monospace;"> WHERE event_name="statement/sql/call_procedure";
+----------+-------------------------------+-----------+ </span><br />
<span style="font-family: "Courier New",Courier,monospace;">| event_id | sql_text | time |
+----------+-------------------------------+-----------+ </span><br />
<span style="font-family: "Courier New",Courier,monospace;">| 144 | call massinsert(100,105) | 0.2090ms | </span><br />
<span style="font-family: "Courier New",Courier,monospace;">| 150 | call massinsert(100,105) | 79.9659ms | </span><br />
<span style="font-family: "Courier New",Courier,monospace;">| 421 | CALL test.massinsert(400,405) | 74.2078ms |
+----------+-------------------------------+-----------+ </span><br />
<span style="font-family: "Courier New",Courier,monospace;">3 rows in set (0,03 sec) </span><br />
<br />
You see: I tried this stored procedure three times. The one I want to inspect in detail is event_id 421. Let's look at all nested statement events that came from 421:<br />
<br />
<pre style="font-family:arial;font-size:12px;border:1px dashed #CCCCCC;width:99%;height:auto;overflow:auto;background:#f0f0f0;;background-image:URL(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhMZusaYM1WT-OmwTEK8llMFEKNxOOqR1aDYc5rPqURJw6SEzmu1wSpyFo3KTNnGzF_39MbSSrHSw5KUVlFGZSAXLCsLreaMp_jtcwGE2KZxueVDBs_j8KTzuNZN5AEjRThr77UKNGOFEU4/s320/codebg.gif);padding:0px;color:#000000;text-align:left;line-height:20px;"><code style="color:#000000;word-wrap:normal;"> mysql> SELECT EVENT_NAME, SQL_TEXT,
CONCAT(TIMER_WAIT/1000000000,"ms") AS time
FROM events_statements_history_long
WHERE nesting_event_id=421 ORDER BY event_id;
+--------------------------+-----------------------------------+-----------+
| EVENT_NAME | SQL_TEXT | time |
+--------------------------+-----------------------------------+-----------+
| statement/sp/stmt | SET @i = first | 0.0253ms |
| statement/sp/stmt | SET @i = @i + 1 | 0.0155ms |
| statement/sp/stmt | INSERT INTO a VALUES (@i,MD5(@i)) | 45.6425ms |
| statement/sp/jump_if_not | NULL | 0.0311ms |
| statement/sp/stmt | SET @i = @i + 1 | 0.0297ms |
| statement/sp/stmt | INSERT INTO a VALUES (@i,MD5(@i)) | 4.9695ms |
| statement/sp/jump_if_not | NULL | 0.0726ms |
| statement/sp/stmt | SET @i = @i + 1 | 0.0365ms |
| statement/sp/stmt | INSERT INTO a VALUES (@i,MD5(@i)) | 6.8518ms |
| statement/sp/jump_if_not | NULL | 0.0343ms |
| statement/sp/stmt | SET @i = @i + 1 | 0.0316ms |
| statement/sp/stmt | INSERT INTO a VALUES (@i,MD5(@i)) | 9.9633ms |
| statement/sp/jump_if_not | NULL | 0.0309ms |
| statement/sp/stmt | SET @i = @i + 1 | 0.0274ms |
| statement/sp/stmt | INSERT INTO a VALUES (@i,MD5(@i)) | 5.6235ms |
| statement/sp/jump_if_not | NULL | 0.0308ms |
+--------------------------+-----------------------------------+-----------+
16 rows in set (0,06 sec)
</code></pre>
<span style="font-family: "Courier New",Courier,monospace;"> </span><br />
Now we have the statements that were executed in the stored procedure "massinsert(400,405)" with their individual execution times and in order of execution. We have all other information available as well, not only execution time. We can access number of rows affected, sql error text, used algorithms, ... All information that performance_schema offers for statement events.
This is a great way to analyze your stored procedures. find the most costly statements and improve performance of your stored programs. That is really a great enhancement to performance_schema.
Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-4614960018934857975.post-47587905553942576702015-03-13T17:59:00.000+00:002015-03-13T18:03:07.696+00:00Auto Generated Columns in MySQL 5.7: Two Indexes on one Column made easyOne of my customers wants to search for names in a table. But sometimes the search is case insensitive, next time search should be done case sensitive. The index on that column always is created with the collation of the column. And if you search with a different collation in mind, you end up with a full table scan. Here is an example:<br />
<br />
<h3>
The problem </h3>
<pre style="background-image: URL(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhMZusaYM1WT-OmwTEK8llMFEKNxOOqR1aDYc5rPqURJw6SEzmu1wSpyFo3KTNnGzF_39MbSSrHSw5KUVlFGZSAXLCsLreaMp_jtcwGE2KZxueVDBs_j8KTzuNZN5AEjRThr77UKNGOFEU4/s320/codebg.gif); background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;"> mysql> SHOW CREATE TABLE City\G
*************************** 1. row ***************************
Table: City
Create Table: CREATE TABLE `City` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Name` char(35) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`CountryCode` char(3) NOT NULL DEFAULT '',
`District` char(20) NOT NULL DEFAULT '',
`Population` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`ID`),
KEY `CountryCode` (`CountryCode`),
KEY `Name` (`Name`),
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1
1 row in set (0,00 sec)
</code></pre>
<br />
The collation of the column `Name` is utf8_bin, so case sensitive. Let's search for a City:<br />
<pre style="background-image: URL(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhMZusaYM1WT-OmwTEK8llMFEKNxOOqR1aDYc5rPqURJw6SEzmu1wSpyFo3KTNnGzF_39MbSSrHSw5KUVlFGZSAXLCsLreaMp_jtcwGE2KZxueVDBs_j8KTzuNZN5AEjRThr77UKNGOFEU4/s320/codebg.gif); background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;"> mysql> SELECT Name,Population FROM City WHERE Name='berlin';
Empty set (0,00 sec)
mysql> EXPLAIN SELECT Name,Population FROM City WHERE Name='berlin';
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| 1 | SIMPLE | City | NULL | ref | Name | Name | 106 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0,00 sec)
</code></pre>
<br />
<br />
Very efficient statement, using the index. But unfortunately it did not find the row as the search is based on the case sensitive collation.<br />
Now let's change the collation for the WHERE clause:<br />
<pre style="background-image: URL(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhMZusaYM1WT-OmwTEK8llMFEKNxOOqR1aDYc5rPqURJw6SEzmu1wSpyFo3KTNnGzF_39MbSSrHSw5KUVlFGZSAXLCsLreaMp_jtcwGE2KZxueVDBs_j8KTzuNZN5AEjRThr77UKNGOFEU4/s320/codebg.gif); background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;"> mysql> SELECT Name,Population FROM City WHERE Name='berlin' COLLATE utf8_general_ci;
+--------+------------+
| Name | Population |
+--------+------------+
| Berlin | 3386667 |
+--------+------------+
1 row in set (0,00 sec)
mysql> EXPLAIN SELECT Name,Population FROM City WHERE Name='berlin' COLLATE utf8_general_ci;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | City | NULL | ALL | Name | NULL | NULL | NULL | 4108 | 10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 3 warnings (0,00 sec)
</code></pre>
<br />
<br />
The result is what we wanted but the query creates a full table scan. Not good. BTW: The warnings point you to the fact that the index could not be used.<br />
<br />
<h3>
The solution</h3>
Now let's see how <a href="http://dev.mysql.com/doc/refman/5.7/en/create-table.html#create-table-generated-columns" target="_blank">auto generated columns</a> in the new <a href="http://mysqlserverteam.com/the-mysql-5-7-6-milestone-release-is-available/" target="_blank">MySQL 5.7 Development Milestone Release</a> can help us. First let's create a copy of the Name column but with a different collation:<br />
<pre style="background-image: URL(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhMZusaYM1WT-OmwTEK8llMFEKNxOOqR1aDYc5rPqURJw6SEzmu1wSpyFo3KTNnGzF_39MbSSrHSw5KUVlFGZSAXLCsLreaMp_jtcwGE2KZxueVDBs_j8KTzuNZN5AEjRThr77UKNGOFEU4/s320/codebg.gif); background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;"> mysql> ALTER TABLE City ADD COLUMN Name_ci char(35) CHARACTER SET utf8 AS (Name) STORED;
Query OK, 4079 rows affected (0,50 sec)
Records: 4079 Duplicates: 0 Warnings: 0
</code></pre>
<br />
"<i><span style="font-family: "Courier New",Courier,monospace;">AS (Name) STORED</span></i>" is the new stuff: In the brackets is the expression to calculate the column value. Here it is a simple copy of the Name column. The keyword <span style="font-family: "Courier New",Courier,monospace;"><i>STORED</i></span> means that the data is physically stored and not calculated on the fly. This is necessary to create the index now:
<br />
<pre style="background-image: URL(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhMZusaYM1WT-OmwTEK8llMFEKNxOOqR1aDYc5rPqURJw6SEzmu1wSpyFo3KTNnGzF_39MbSSrHSw5KUVlFGZSAXLCsLreaMp_jtcwGE2KZxueVDBs_j8KTzuNZN5AEjRThr77UKNGOFEU4/s320/codebg.gif); background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;"> mysql> ALTER TABLE City ADD INDEX (Name_ci);
Query OK, 0 rows affected (0,13 sec)
Records: 0 Duplicates: 0 Warnings: 0
</code></pre>
<br />
As utf8_general_ci is the default collation with utf8, there is no need to specify this with the new column. Now let's see how to search:<br />
<br />
<pre style="background-image: URL(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhMZusaYM1WT-OmwTEK8llMFEKNxOOqR1aDYc5rPqURJw6SEzmu1wSpyFo3KTNnGzF_39MbSSrHSw5KUVlFGZSAXLCsLreaMp_jtcwGE2KZxueVDBs_j8KTzuNZN5AEjRThr77UKNGOFEU4/s320/codebg.gif); background: #f0f0f0; border: 1px dashed #CCCCCC; color: black; font-family: arial; font-size: 12px; height: auto; line-height: 20px; overflow: auto; padding: 0px; text-align: left; width: 99%;"><code style="color: black; word-wrap: normal;">
mysql> SELECT Name, Population FROM City WHERE Name_ci='berlin';
+--------+------------+
| Name | Population |
+--------+------------+
| Berlin | 3386667 |
+--------+------------+
1 row in set (0,00 sec)
mysql> EXPLAIN SELECT Name, Population FROM City WHERE Name_ci='berlin';nbsp;
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | City | NULL | ref | Name_ci | Name_ci | 106 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0,00 sec)
</code></pre>
<br />
Now we can search case sensitive (...WHERE Name=...) and case insensitive (WHERE Name_ci=...) and leverage indexes in both cases.
<br />
<h3>
tl;dr</h3>
Use auto generated columns in MySQL 5.7 to create an additional index with a different collation. Now you can search based on different indexes.
Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-4614960018934857975.post-75012644105944872812014-03-19T09:48:00.001+00:002014-03-19T09:48:48.791+00:00MySQL Cluster on Raspberry Pi - Sub-second failoverMySQL Cluster claims to achieve sub-second failover without any data loss for commited transactions. And I always wanted to show this in a demo. Now we created that demo finally. See <a href="http://markswarbrick.wordpress.com/2013/09/" target="_blank">Mark's blog</a> and <a href="http://mysql-med.blogspot.de/2013/09/mysql-cluster-n-pi.html" target="_blank">Keith's blog</a> for setting up MySQL Cluster on RaspberryPi.<br />
The nice thing about the RPis is that you can easily pull the plug to test failover. Ok, that is only one possible failure scenario but for sure the most obvious and more impressive than "kill -9".<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="separator" style="clear: both; text-align: center;">
<iframe allowfullscreen='allowfullscreen' webkitallowfullscreen='webkitallowfullscreen' mozallowfullscreen='mozallowfullscreen' width='320' height='266' src='https://www.youtube.com/embed/6840cMBql9s?feature=player_embedded' frameborder='0'></iframe></div>
<br />
That demo application is constantly using the database for storing new lines, removing old lines and reading all line data for the graphical view. There is no caching. It uses JDBC directly. <br />
To document the setup here is the config.ini file for MySQL Cluster:<br />
<blockquote class="tr_bq">
<span style="font-family: "Courier New",Courier,monospace;">[ndb_mgmd]<br />hostname=192.168.0.101<br />NodeId=1<br /><br />[ndbd default]<br /><b>diskless=1</b><br />noofreplicas=2<br />DataMemory=2M<br />IndexMemory=1M<br />DiskPageBufferMemory=4M<br />StringMemory=5<br />MaxNoOfConcurrentOperations=1K<br />MaxNoOfConcurrentTransactions=500<br />SharedGlobalMemory=500K<br />LongMessageBuffer=512K<br />MaxParallelScansPerFragment=16<br />MaxNoOfAttributes=100<br />MaxNoOfTables=20<br />MaxNoOfOrderedIndexes=20<br /><b>HeartbeatIntervalDbDb=10</b> </span></blockquote>
<blockquote class="tr_bq">
<span style="font-family: "Courier New",Courier,monospace;">[ndbd]<br />hostname=192.168.0.6<br />datadir=/home/pi/mysql/ndb_data<br />NodeId=3<br /><br />[ndbd]<br />hostname=192.168.0.11<br />datadir=/home/pi/mysql/ndbd_data<br />NodeId=4<br /><br />[mysqld]<br />NodeId=50</span> </blockquote>
I made the cluster diskless so it will not write any logs and table spaces to disk. The SD card performance was not great and it does not affect failover behavior.<br />
I also reduced the HeatbeatIntervallDbDb so that nodes detect immediately (well, 10ms) if a heartbeat is missed. After a few missed heartbeats cluster reconfigures and the remaining node takes responsibility and service continues. <br />
BTW: Pulling the plug is nice but every now and then I had to manually fsck the root-fs during reboot.<br />
<br />Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-4614960018934857975.post-34662667152881742252013-03-12T14:22:00.000+00:002013-03-12T14:24:37.502+00:00New in 5.6: --innodb_read_only, running MySQL from DVDI recently met two distinct customers who want to use MySQL as a read-only database, stored on a read-only medium like a DVD. With MyISAM this was easily possible. Starting with MySQL 5.6 it is also possible to run this setup with InnoDB based databases. There is a new option:<br />
<div>
<br /></div>
<div>
--innodb_read_only</div>
<div>
<br /></div>
<div>
See <a href="http://dev.mysql.com/doc/refman/5.6/en/innodb-read-only-instance.html" target="_blank">details in the reference manual</a>. This options opens all tablespaces and InnoDB log files as read-only. A small test showed that you need to set some more options in the my.cnf to avoid any write access to @@datadir:</div>
<div>
<br />
innodb-read-only</div>
<div>
<div>
log-error=/tmp/mysql-ro.err</div>
<div>
pid-file=/tmp/mysql-ro.pid</div>
<div>
event-scheduler=disabled</div>
</div>
<div>
<br /></div>
<div>
I was a bit surprised why I had to disable the event scheduler. But on the other hand, what use has a regularly running statement that cannot store any data? After all your database is read only ;-)</div>
<div>
<br /></div>
<div>
And together with the new fulltext indexes in InnoDB and maybe compressed table spaces you can now deploy catalogue applications or reference manuals on DVD.</div>
<div>
<br /></div>
Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-4614960018934857975.post-5463601533219507202012-05-29T08:06:00.001+01:002012-05-30T11:28:06.514+01:00What is ndb doing?In MySQL cluster each SQL statement is translated inside the NDB storage engine to NDB low level protocol that is sent to the data nodes. For the performance it is most interesting how many data is moved between data nodes and MySQL. To monitor this there are several NDB status variables that you can monitor. See <a href="http://dev.mysql.com/doc/refman/5.5/en/mysql-cluster-ndb-api-statistics.html" target="_blank">this link for more documentation</a>.<br />
<br />
(There are also the NDBINFO tables that reflect cluster status. But these are only global values. The status variables also show session status. More about NDBINFO is <a href="http://dev.mysql.com/doc/refman/5.5/en/mysql-cluster-ndbinfo.html" target="_blank">here</a>.)<br />
<br />
To easily report the NDB status on an individual SQL statement, I wrote a little script that gives you the ndb status variables and automatically calculates the diffs before and after the statement in question:
<br />
<br />
<br />
<div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt; margin-left: 0cm; margin-right: 0cm; margin-top: 0cm;">
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">#! /bin/bash<o:p></o:p></span></div>
</div>
<div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt; margin-left: 0cm; margin-right: 0cm; margin-top: 0cm;">
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">./mysql -t -h olga64 test <<EOF<o:p></o:p></span></div>
</div>
<div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt; margin-left: 0cm; margin-right: 0cm; margin-top: 0cm;">
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">CREATE TEMPORARY TABLE tmp_before LIKE INFORMATION_SCHEMA.SESSION_STATUS;<o:p></o:p></span></div>
</div>
<div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt; margin-left: 0cm; margin-right: 0cm; margin-top: 0cm;">
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">CREATE TEMPORARY TABLE tmp_after LIKE INFORMATION_SCHEMA.SESSION_STATUS;<o:p></o:p></span></div>
</div>
<div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt; margin-left: 0cm; margin-right: 0cm; margin-top: 0cm;">
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">INSERT INTO tmp_before SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VAR<span class="apple-style-span">IABLE_NAME LIKE 'ndb_api%session%';</span><o:p></o:p></span></div>
</div>
<div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt; margin-left: 0cm; margin-right: 0cm; margin-top: 0cm;">
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">$1<o:p></o:p></span></div>
</div>
<div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt; margin-left: 0cm; margin-right: 0cm; margin-top: 0cm;">
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">INSERT INTO tmp_after SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARI<span class="apple-style-span">ABLE_NAME LIKE 'ndb_api%session%';</span><o:p></o:p></span></div>
</div>
<div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt; margin-left: 0cm; margin-right: 0cm; margin-top: 0cm;">
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">SELECT tmp_before.VARIABLE_NAME, tmp_after.VARIABLE_VALUE - tmp_before.VARIABLE_<span class="apple-style-span">VALUE AS 'VALUE' FROM tmp_after INNER JOIN tmp_before USING (VARIABLE_NAME) WHERE tmp_after.VARIABLE_VALUE <> tmp_before.VARIABLE_VALUE;</span><o:p></o:p></span></div>
</div>
<div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt; margin-left: 0cm; margin-right: 0cm; margin-top: 0cm;">
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">EOF</span></div>
</div>
<br />
<br />
<br />
And here are some small examples of understanding what NDB does:<br />
<br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"></span><br />
<div style="font-family: Helvetica;">
<div class="MsoNormal" style="font-family: 'Times New Roman', serif; font-size: 12pt; margin-bottom: 0.0001pt; margin-left: 0cm; margin-right: 0cm; margin-top: 0cm;">
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span style="font-family: Courier;">[root@olga64 bin]# ./ndb_prof.sh "SELECT COUNT(*) FROM t;"<o:p></o:p></span></span></div>
</div>
<div style="font-family: Helvetica;">
<div class="MsoNormal" style="font-family: 'Times New Roman', serif; font-size: 12pt; margin-bottom: 0.0001pt; margin-left: 0cm; margin-right: 0cm; margin-top: 0cm;">
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span style="font-family: Courier;">+----------+<o:p></o:p></span></span></div>
</div>
<div style="font-family: Helvetica;">
<div class="MsoNormal" style="font-family: 'Times New Roman', serif; font-size: 12pt; margin-bottom: 0.0001pt; margin-left: 0cm; margin-right: 0cm; margin-top: 0cm;">
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span style="font-family: Courier;">| COUNT(*) |<o:p></o:p></span></span></div>
</div>
<div style="font-family: Helvetica;">
<div class="MsoNormal" style="font-family: 'Times New Roman', serif; font-size: 12pt; margin-bottom: 0.0001pt; margin-left: 0cm; margin-right: 0cm; margin-top: 0cm;">
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span style="font-family: Courier;">+----------+<o:p></o:p></span></span></div>
</div>
<div style="font-family: Helvetica;">
<div class="MsoNormal" style="font-family: 'Times New Roman', serif; font-size: 12pt; margin-bottom: 0.0001pt; margin-left: 0cm; margin-right: 0cm; margin-top: 0cm;">
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span style="font-family: Courier;">| 32771 |<o:p></o:p></span></span></div>
</div>
<div style="font-family: Helvetica;">
<div class="MsoNormal" style="font-family: 'Times New Roman', serif; font-size: 12pt; margin-bottom: 0.0001pt; margin-left: 0cm; margin-right: 0cm; margin-top: 0cm;">
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span style="font-family: Courier;">+----------+<o:p></o:p></span></span></div>
</div>
<div style="font-family: Helvetica;">
<div class="MsoNormal" style="font-family: 'Times New Roman', serif; font-size: 12pt; margin-bottom: 0.0001pt; margin-left: 0cm; margin-right: 0cm; margin-top: 0cm;">
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span style="font-family: Courier;">+--------------------------------------------+----------+<o:p></o:p></span></span></div>
</div>
<div style="font-family: Helvetica;">
<div class="MsoNormal" style="font-family: 'Times New Roman', serif; font-size: 12pt; margin-bottom: 0.0001pt; margin-left: 0cm; margin-right: 0cm; margin-top: 0cm;">
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span style="font-family: Courier;">| VARIABLE_NAME | VALUE |<o:p></o:p></span></span></div>
</div>
<div style="font-family: Helvetica;">
<div class="MsoNormal" style="font-family: 'Times New Roman', serif; font-size: 12pt; margin-bottom: 0.0001pt; margin-left: 0cm; margin-right: 0cm; margin-top: 0cm;">
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span style="font-family: Courier;">+--------------------------------------------+----------+<o:p></o:p></span></span></div>
</div>
<div style="font-family: Helvetica;">
<div class="MsoNormal" style="font-family: 'Times New Roman', serif; font-size: 12pt; margin-bottom: 0.0001pt; margin-left: 0cm; margin-right: 0cm; margin-top: 0cm;">
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span style="font-family: Courier;">| NDB_API_WAIT_SCAN_RESULT_COUNT_SESSION | 3 |<o:p></o:p></span></span></div>
</div>
<div style="font-family: Helvetica;">
<div class="MsoNormal" style="font-family: 'Times New Roman', serif; font-size: 12pt; margin-bottom: 0.0001pt; margin-left: 0cm; margin-right: 0cm; margin-top: 0cm;">
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span style="font-family: Courier;">| NDB_API_WAIT_META_REQUEST_COUNT_SESSION | 2 |<o:p></o:p></span></span></div>
</div>
<div style="font-family: Helvetica;">
<div class="MsoNormal" style="font-family: 'Times New Roman', serif; font-size: 12pt; margin-bottom: 0.0001pt; margin-left: 0cm; margin-right: 0cm; margin-top: 0cm;">
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span style="font-family: Courier;">| NDB_API_WAIT_NANOS_COUNT_SESSION | 19495775 |<o:p></o:p></span></span></div>
</div>
<div style="font-family: Helvetica;">
<div class="MsoNormal" style="font-family: 'Times New Roman', serif; font-size: 12pt; margin-bottom: 0.0001pt; margin-left: 0cm; margin-right: 0cm; margin-top: 0cm;">
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span style="font-family: Courier;">| NDB_API_BYTES_SENT_COUNT_SESSION | 132 |<o:p></o:p></span></span></div>
</div>
<div style="font-family: Helvetica;">
<div class="MsoNormal" style="font-family: 'Times New Roman', serif; font-size: 12pt; margin-bottom: 0.0001pt; margin-left: 0cm; margin-right: 0cm; margin-top: 0cm;">
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span style="font-family: Courier;">| NDB_API_BYTES_RECEIVED_COUNT_SESSION | 280 |<o:p></o:p></span></span></div>
</div>
<div style="font-family: Helvetica;">
<div class="MsoNormal" style="font-family: 'Times New Roman', serif; font-size: 12pt; margin-bottom: 0.0001pt; margin-left: 0cm; margin-right: 0cm; margin-top: 0cm;">
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span style="font-family: Courier;">| NDB_API_TRANS_START_COUNT_SESSION | 1 |<o:p></o:p></span></span></div>
</div>
<div style="font-family: Helvetica;">
<div class="MsoNormal" style="font-family: 'Times New Roman', serif; font-size: 12pt; margin-bottom: 0.0001pt; margin-left: 0cm; margin-right: 0cm; margin-top: 0cm;">
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span style="font-family: Courier;">| NDB_API_TRANS_CLOSE_COUNT_SESSION | 1 |<o:p></o:p></span></span></div>
</div>
<div style="font-family: Helvetica;">
<div class="MsoNormal" style="font-family: 'Times New Roman', serif; font-size: 12pt; margin-bottom: 0.0001pt; margin-left: 0cm; margin-right: 0cm; margin-top: 0cm;">
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span style="font-family: Courier;">| NDB_API_TABLE_SCAN_COUNT_SESSION | 1 |<o:p></o:p></span></span></div>
</div>
<div style="font-family: Helvetica;">
<div class="MsoNormal" style="font-family: 'Times New Roman', serif; font-size: 12pt; margin-bottom: 0.0001pt; margin-left: 0cm; margin-right: 0cm; margin-top: 0cm;">
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span style="font-family: Courier;">| NDB_API_SCAN_BATCH_COUNT_SESSION | 2 |<o:p></o:p></span></span></div>
</div>
<div style="font-family: Helvetica;">
<div class="MsoNormal" style="font-family: 'Times New Roman', serif; font-size: 12pt; margin-bottom: 0.0001pt; margin-left: 0cm; margin-right: 0cm; margin-top: 0cm;">
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span style="font-family: Courier;">| <span class="Apple-style-span" style="background-color: yellow;">NDB_API_READ_ROW_COUNT_SESSION | 2 </span>|<o:p></o:p></span></span></div>
</div>
<div style="font-family: Helvetica;">
<div class="MsoNormal" style="font-family: 'Times New Roman', serif; font-size: 12pt; margin-bottom: 0.0001pt; margin-left: 0cm; margin-right: 0cm; margin-top: 0cm;">
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span style="font-family: Courier;">| NDB_API_TRANS_LOCAL_READ_ROW_COUNT_SESSION | 2 |<o:p></o:p></span></span></div>
</div>
<div style="font-family: Helvetica;">
<div class="MsoNormal" style="font-family: 'Times New Roman', serif; font-size: 12pt; margin-bottom: 0.0001pt; margin-left: 0cm; margin-right: 0cm; margin-top: 0cm;">
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span style="font-family: Courier;">| NDB_API_ADAPTIVE_SEND_FORCED_COUNT_SESSION | 1 |<o:p></o:p></span></span></div>
</div>
<div style="font-family: Helvetica;">
<div class="MsoNormal" style="font-family: 'Times New Roman', serif; font-size: 12pt; margin-bottom: 0.0001pt; margin-left: 0cm; margin-right: 0cm; margin-top: 0cm;">
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span style="font-family: Courier;">+--------------------------------------------+----------+</span></span></div>
</div>
<br />
<br />
So SELECT COUNT(*) only returns two rows (NDB_API_READ_ROW_COUNT_SESSION). Probably one row per fragment. (I have a two node cluster).
COUNT(*) is optimized! But if you add a WHERE condition:<br />
<br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"></span><br />
<div style="font-family: Helvetica;">
<div class="MsoNormal" style="font-family: 'Times New Roman', serif; font-size: 12pt; margin-bottom: 0.0001pt; margin-left: 0cm; margin-right: 0cm; margin-top: 0cm;">
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span style="font-family: Courier;">[root@olga64 bin]# ./ndb_prof.sh "SELECT COUNT(*) FROM t WHERE a<100;"</span><o:p></o:p></span></div>
</div>
<div style="font-family: Helvetica;">
<div class="MsoNormal" style="font-family: 'Times New Roman', serif; font-size: 12pt; margin-bottom: 0.0001pt; margin-left: 0cm; margin-right: 0cm; margin-top: 0cm;">
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span style="font-family: Courier;">+----------+</span><o:p></o:p></span></div>
</div>
<div style="font-family: Helvetica;">
<div class="MsoNormal" style="font-family: 'Times New Roman', serif; font-size: 12pt; margin-bottom: 0.0001pt; margin-left: 0cm; margin-right: 0cm; margin-top: 0cm;">
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span style="font-family: Courier;">| COUNT(*) |</span><o:p></o:p></span></div>
</div>
<div style="font-family: Helvetica;">
<div class="MsoNormal" style="font-family: 'Times New Roman', serif; font-size: 12pt; margin-bottom: 0.0001pt; margin-left: 0cm; margin-right: 0cm; margin-top: 0cm;">
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span style="font-family: Courier;">+----------+</span><o:p></o:p></span></div>
</div>
<div style="font-family: Helvetica;">
<div class="MsoNormal" style="font-family: 'Times New Roman', serif; font-size: 12pt; margin-bottom: 0.0001pt; margin-left: 0cm; margin-right: 0cm; margin-top: 0cm;">
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span style="font-family: Courier;">| 99 |</span><o:p></o:p></span></div>
</div>
<div style="font-family: Helvetica;">
<div class="MsoNormal" style="font-family: 'Times New Roman', serif; font-size: 12pt; margin-bottom: 0.0001pt; margin-left: 0cm; margin-right: 0cm; margin-top: 0cm;">
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span style="font-family: Courier;">+----------+</span><o:p></o:p></span></div>
</div>
<div style="font-family: Helvetica;">
<div class="MsoNormal" style="font-family: 'Times New Roman', serif; font-size: 12pt; margin-bottom: 0.0001pt; margin-left: 0cm; margin-right: 0cm; margin-top: 0cm;">
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span style="font-family: Courier;">+--------------------------------------------+----------+</span><o:p></o:p></span></div>
</div>
<div style="font-family: Helvetica;">
<div class="MsoNormal" style="font-family: 'Times New Roman', serif; font-size: 12pt; margin-bottom: 0.0001pt; margin-left: 0cm; margin-right: 0cm; margin-top: 0cm;">
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span style="font-family: Courier;">| VARIABLE_NAME | VALUE |</span><o:p></o:p></span></div>
</div>
<div style="font-family: Helvetica;">
<div class="MsoNormal" style="font-family: 'Times New Roman', serif; font-size: 12pt; margin-bottom: 0.0001pt; margin-left: 0cm; margin-right: 0cm; margin-top: 0cm;">
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span style="font-family: Courier;">+--------------------------------------------+----------+</span><o:p></o:p></span></div>
</div>
<div style="font-family: Helvetica;">
<div class="MsoNormal" style="font-family: 'Times New Roman', serif; font-size: 12pt; margin-bottom: 0.0001pt; margin-left: 0cm; margin-right: 0cm; margin-top: 0cm;">
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span style="font-family: Courier;">| NDB_API_WAIT_SCAN_RESULT_COUNT_SESSION | 3 |</span><o:p></o:p></span></div>
</div>
<div style="font-family: Helvetica;">
<div class="MsoNormal" style="font-family: 'Times New Roman', serif; font-size: 12pt; margin-bottom: 0.0001pt; margin-left: 0cm; margin-right: 0cm; margin-top: 0cm;">
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span style="font-family: Courier;">| NDB_API_WAIT_META_REQUEST_COUNT_SESSION | 2 |</span><o:p></o:p></span></div>
</div>
<div style="font-family: Helvetica;">
<div class="MsoNormal" style="font-family: 'Times New Roman', serif; font-size: 12pt; margin-bottom: 0.0001pt; margin-left: 0cm; margin-right: 0cm; margin-top: 0cm;">
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span style="font-family: Courier;">| NDB_API_WAIT_NANOS_COUNT_SESSION | 18267962 |</span><o:p></o:p></span></div>
</div>
<div style="font-family: Helvetica;">
<div class="MsoNormal" style="font-family: 'Times New Roman', serif; font-size: 12pt; margin-bottom: 0.0001pt; margin-left: 0cm; margin-right: 0cm; margin-top: 0cm;">
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span style="font-family: Courier;">| NDB_API_BYTES_SENT_COUNT_SESSION | 140 |</span><o:p></o:p></span></div>
</div>
<div style="font-family: Helvetica;">
<div class="MsoNormal" style="font-family: 'Times New Roman', serif; font-size: 12pt; margin-bottom: 0.0001pt; margin-left: 0cm; margin-right: 0cm; margin-top: 0cm;">
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span style="font-family: Courier;">| NDB_API_BYTES_RECEIVED_COUNT_SESSION | 3248 |</span><o:p></o:p></span></div>
</div>
<div style="font-family: Helvetica;">
<div class="MsoNormal" style="font-family: 'Times New Roman', serif; font-size: 12pt; margin-bottom: 0.0001pt; margin-left: 0cm; margin-right: 0cm; margin-top: 0cm;">
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span style="font-family: Courier;">| NDB_API_TRANS_START_COUNT_SESSION | 1 |</span><o:p></o:p></span></div>
</div>
<div style="font-family: Helvetica;">
<div class="MsoNormal" style="font-family: 'Times New Roman', serif; font-size: 12pt; margin-bottom: 0.0001pt; margin-left: 0cm; margin-right: 0cm; margin-top: 0cm;">
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span style="font-family: Courier;">| NDB_API_TRANS_CLOSE_COUNT_SESSION | 1 |</span><o:p></o:p></span></div>
</div>
<div style="font-family: Helvetica;">
<div class="MsoNormal" style="font-family: 'Times New Roman', serif; font-size: 12pt; margin-bottom: 0.0001pt; margin-left: 0cm; margin-right: 0cm; margin-top: 0cm;">
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span style="font-family: Courier;">| NDB_API_RANGE_SCAN_COUNT_SESSION | 1 |</span><o:p></o:p></span></div>
</div>
<div style="font-family: Helvetica;">
<div class="MsoNormal" style="font-family: 'Times New Roman', serif; font-size: 12pt; margin-bottom: 0.0001pt; margin-left: 0cm; margin-right: 0cm; margin-top: 0cm;">
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span style="font-family: Courier;">| NDB_API_SCAN_BATCH_COUNT_SESSION | 2 |</span><o:p></o:p></span></div>
</div>
<div style="font-family: Helvetica;">
<div class="MsoNormal" style="font-family: 'Times New Roman', serif; font-size: 12pt; margin-bottom: 0.0001pt; margin-left: 0cm; margin-right: 0cm; margin-top: 0cm;">
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span style="font-family: Courier;">| <span class="Apple-style-span" style="background-color: yellow;">NDB_API_READ_ROW_COUNT_SESSION | 99</span> |</span><o:p></o:p></span></div>
</div>
<div style="font-family: Helvetica;">
<div class="MsoNormal" style="font-family: 'Times New Roman', serif; font-size: 12pt; margin-bottom: 0.0001pt; margin-left: 0cm; margin-right: 0cm; margin-top: 0cm;">
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span style="font-family: Courier;">| NDB_API_TRANS_LOCAL_READ_ROW_COUNT_SESSION | 99 |</span><o:p></o:p></span></div>
</div>
<div style="font-family: Helvetica;">
<div class="MsoNormal" style="font-family: 'Times New Roman', serif; font-size: 12pt; margin-bottom: 0.0001pt; margin-left: 0cm; margin-right: 0cm; margin-top: 0cm;">
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span style="font-family: Courier;">| NDB_API_ADAPTIVE_SEND_FORCED_COUNT_SESSION | 1 |</span><o:p></o:p></span></div>
</div>
<div style="font-family: Helvetica;">
<div class="MsoNormal" style="font-family: 'Times New Roman', serif; font-size: 12pt; margin-bottom: 0.0001pt; margin-left: 0cm; margin-right: 0cm; margin-top: 0cm;">
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span style="font-family: Courier;">+--------------------------------------------+----------+</span></span></div>
</div>
<br />
<br />
<i>'a'</i> is the primary key. This statement sends all 99 rows, well only the primary keys and mysqld is counting.
This one can get more expensive, depending on the row count and primary key size. Look at <span class="Apple-style-span" style="font-family: Courier; font-size: 16px;">NDB_API_BYTES_RECEIVED_COUNT_SESSION</span>: 3248 bytes sent. This is roughly 32 bytes per row that is sent.Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-4614960018934857975.post-84667116038844986132012-03-12T12:11:00.001+00:002012-03-12T12:17:11.315+00:00Why should I consider memcached plugin?My last post explained what to expect from memcached plugin in MySQL 5.6 (<a href="http://labs.mysql.com/">labs release</a>). But I want to take a step back and think about "why" first. Why is memcached plugin of interest at all? What can I gain from using this instead of plain SQL?<div><br /></div><div>First: I don't see this as a replacement for memcached. If you want memory caching with memcached then use memcached.</div><div><br /></div><div>But the memcached plugin to MySQL is a replacement or addition to the SQL interface to MySQL. So instead of using SQL queries in your application to persist or retrieve data from MySQL you can use the memcached interface. And what are the benefits?</div><div><ul><li>Much higher performance</li><li>Easier scalability via sharding</li><li>Simpler application coding</li></ul></div><div><br /></div><div><h3>1. Performance</h3></div><div>Performance is always good. But there are two different aspects of performance: Latency (or runtime) of a specific query and throughput. And the relation of these two measures is not easy and simple to explain. So we need to discuss both:</div><div><br /></div><div><h4>1.1 Latency</h4></div><div>Latency is the time it takes to execute an individual query. If using SQL the server needs to parse that SQL query (which is not so easy because SQL is quite a complex language), then optimize the execution plan of a query and finally execute it. Memcached interface is much simpler so parsing is close to nothing and accessing a single row by an index does not require any optimization. Only the real query execution is the same. I did a test in a VM with one virtual disk. (Not the best benchmark environment ;-) I inserted single rows in multiple threads via C-API (using SQL) and via libmemcachd (using the new plugin). The mean latency for a single query was 20%-30% higher for SQL queries. Considering that my IO configuration was bad, in a better environment the share of query execution would be smaller so the benefit of memcached plugin (no parsing, no optimization) would be even bigger.</div><div><h4>1.2 Throughput</h4></div><div>Usually lower latency helps improving the throughput. If each query is finished earlier, the server can already start working on the next query. This is true only if the bottleneck is really the CPU or RAM. If latency is mainly due to disk IO, it is not so helpful. During my test I achieved up to 50% higher throughput with memcached plugin. Again, a suitable IO config can probably improve this advantage even further.</div><div><h3>2. Scalability</h3></div><div>If a single server is no longer enough one solution may be sharding. With SQL you usually have two options: Switch to MySQL cluster, which offers<a href="https://blogs.oracle.com/MySQL/entry/scaling_web_databases_auto_sharding"> auto-sharding</a>. The other alternative is to implement sharding on your own in your application, which requires some effort.</div><div>With memcached plugin it is easier: For connecting to the database you would usually use some library like <a href="http://www.libmemcached.org/libMemcached.html">libmemcached</a>. And many of these libraries offer ... a sharding feature. This is a natural feature for memcached libraries as the goal of memcached is to offer a distributed cache. Depending on the key value (or a hash of it) the library determines which instance to talk to. There is no need for you to extend your application code and manage different database connections. In libmemcached you can simply define your connectstring as "SERVER=donald:11211 SERVER=dagobert:11211 SERVER=daisy:11211". And that's it. Libmemcached will automatically shard your data to all three database servers.</div><div>Well, one thing must be mentioned: If you want to query your data via SQL and do aggregation, you have to aggregate in your application. The SQL interface is still not auto-sharded. If you need this kind of functionality, you should consider MySQL Cluster. Cluster will shard your data automatically, wether coming via memcached API or SQL. See <a href="http://www.clusterdb.com/mysql-cluster/scalabale-persistent-ha-nosql-memcache-storage-using-mysql-cluster/">more in Andrew's blog</a>. And for MySQL Cluster the memcache interface is already recommended for production use in MySQL Cluster 7.2.</div><div><h3>3. Ease of use</h3></div><div>The typical architecture is very often application - memcache layer - MySQL database. And for read access the application talks to memcache first, and if this results in a "miss", the application will re-do the query but with a different protocol to the MySQL server. You must implement both protocols: memcache API and SQL. If you use the memcached plugin, you can now access the database directly with the same API functions that you already use for memcache. That makes development much easier. </div><div>The sharding feature in the memcached library will make it easier to scale the database. You do not have to implement it on your own.</div><div>"Easy of use" only applies if your application is happy with doing only low level data access to a key value store. Once you also need higher level data access like JOINs or aggregation queries, you will again use memcache API and SQL in the same application. So nothing will change to before.</div><div><h3>4. Availability?</h3></div><div>Not a huge argument. By using the memcached plugin you essentially work with MySQL and you will leverage all possible HA options that are available for MySQL to make your data highly available. It is worth mentioning that memcached plugin supports replication. You have to define <span class="Apple-style-span" style="font-family:'courier new';">daemon_memcached_enable_binlog = 1</span>. That's it.</div><div><h3>Code examples</h3></div><div>Just for your reference here is the shortcut version of the code that I used to test SQL and memcache API in connecting to MySQL server.</div><div><h4>C-API</h4></div><div><span class="Apple-style-span" style="font-family:'courier new';">#include <mysql.h></mysql.h></span></div><div><span class="Apple-style-span" style="font-family:'courier new';">...</span></div><div><span class="Apple-style-span" style="font-family:'courier new';"> MYSQL *mysql;</span></div><div><span class="Apple-style-span" style="font-family:'courier new';"> char stmt[256];</span></div><div><span class="Apple-style-span" style="font-family:'courier new';"> int key = 0;</span></div><div><div><span class="Apple-style-span" style="font-family:'courier new';"> mysql = mysql_init(NULL);</span></div><div><span class="Apple-style-span" style="font-family:'courier new';"> if (mysql_real_connect(mysql,</span><span class="Apple-style-span" style=" ;font-family:'courier new';">"localhost",</span><span class="Apple-style-span" style=" ;font-family:'courier new';">"user",</span><span class="Apple-style-span" style=" ;font-family:'courier new';">"password",</span></div><div><span class="Apple-style-span" style=" ;font-family:'courier new';"> "schema",</span><span class="Apple-style-span" style=" ;font-family:'courier new';">3306,</span><span class="Apple-style-span" style=" ;font-family:'courier new';">NULL,</span><span class="Apple-style-span" style=" ;font-family:'courier new';">0L)</span><span class="Apple-style-span" style=" ;font-family:'courier new';"> == NULL) {</span></div><div><span class="Apple-style-span" style="font-family:'courier new';"> goodbye();</span></div><div><span class="Apple-style-span" style="font-family:'courier new';"> }</span></div></div><div><div><span class="Apple-style-span" style="font-family:'courier new';"> for (;;) {</span></div><div><span class="Apple-style-span" style="font-family:'courier new';"> sprintf (stmt, "INSERT INTO kvstore VALUES('key%d','%s',0,0,0)",</span></div><div><span class="Apple-style-span" style="font-family:'courier new';"> key++,value);</span></div><div><span class="Apple-style-span" style=" ;font-family:'courier new';"> if (mysql_query (mysql, stmt) != 0) {</span><span class="Apple-style-span" style=" ;font-family:'courier new';">goodbye();</span><span class="Apple-style-span" style=" ;font-family:'courier new';">}</span></div></div><div><div><div><span class="Apple-style-span" style="font-family:'courier new';"> }</span></div></div></div><div><h4>Libmemcached</h4></div><div><div><span class="Apple-style-span" style="font-family:'courier new';">#include <libmemcached h=""></libmemcached></span></div><div><span class="Apple-style-span" style="font-family:'courier new';">...</span></div><div><span class="Apple-style-span" style="font-family:'courier new';"> char *config_string = "--SERVER=localhost:11211";</span></div></div><div><div><span class="Apple-style-span" style="font-family:'courier new';"> memcached_st *memc;</span></div><div><span class="Apple-style-span" style="font-family:'courier new';"> memcached_return_t rc;</span></div></div><div><div><span class="Apple-style-span" style="font-family:'courier new';"> memc = memcached(config_string, strlen(config_string));</span></div><div><span class="Apple-style-span" style="font-family:'courier new';"> if (memc == NULL) {goodbye();}</span></div><div><span class="Apple-style-span" style="font-family:'courier new';"> for (;;) {</span></div><div><span class="Apple-style-span" style="font-family:'courier new';"> rc= memcached_set(memc, key, strlen(key), value, strlen(value), </span></div><div><span class="Apple-style-span" style="font-family:'courier new';"> (time_t)0, (</span><span class="Apple-style-span" style=" ;font-family:'courier new';">uint32_t)0);</span></div><div><span class="Apple-style-span" style="font-family:'courier new';"> if (rc != MEMCACHED_SUCCESS) goodbye();</span></div><div><span class="Apple-style-span" style="font-family:'courier new';"> }</span></div></div>Unknownnoreply@blogger.com2tag:blogger.com,1999:blog-4614960018934857975.post-32297573589079090142012-01-18T13:13:00.002+00:002012-01-18T13:56:43.910+00:00Playing with Memcached Plugin<div>
I am currently playing a lot with the new memcached interface to MySQL. Making MySQL a "NoSQL" solution. </div>
<div>
<br /></div>
<div>
<b>Why should I access the same data via SQL and noSQL protocol? </b></div>
<div>
A simple noSQL protocol like memcached only has lightweight access methods like "set" or "get". No complex parsing, no optimizing, no execution of expressions. And this additional overhead in SQL can be tremendous: I did a set of SELECT statements only based on primary key. Buffer cache is 50% of the table size. With ENGINE=InnoDB it takes 7.6 seconds to read the data. If I switch to BLACKHOLE engine it takes 6.4 seconds! BLACKHOLE has no code in the storage engine. So queries on BLACKHOLE engine create only load on parser and optimizer but nothing in the storage engine. But if I run on InnoDB it adds only 1 second or 15% runtime. Obviously the main part of execution time is outside the storage engine. Erkan found the same behaviour <a href="http://linsenraum.de/documents/doag-mynosql.pdf" target="_blank">here. See page 12.</a> The memcached interface accesses the storage engine directly. So it bypasses all the computing in parser, optimizer, expression evaluation and so on. Of course with INSERT statements the part of InnoDB gets bigger as there is more I/O work to do. But nevertheless there can be huge performance gains in using a simpler protocol. <i>If you only want a glass of milk, don't buy a cow.</i> </div>
<div>
<br /></div>
<div>
<b>Why not use memcached directly? Why that plugin to MySQL?</b></div>
<div>
Memcache is a memory caching but not a persistent datastore. The memcached plugin for MySQL stores the data in an InnoDB table. So you get persistence, nearly unlimited table size and still you can access your data through SQL if you want more complex stuff like COUNT(*) WHERE pkey LIKE ="page:%"; This would not be possible in memcached. But with the memcached plugin you can store data with memcached SET and report on your data with SQL queries.</div>
<div>
<br /></div>
<div>
<b>How can I test it?</b></div>
<div>
Download the labs release from http://labs.mysql.com, do the usual install and then follow the README-innodb_memcached file. It is very simple. Only executing a small sql script and you are ready to test memcached. BUT: If you SET data via telnet to memcached you can retrieve it via GET but you will probably not see the data in the table via SQL. This was a bit confusing to me at least. The secret is the variable daemon_memcached_w_batch_size which is set to 32 by default. The memcached plugin will commit data changes only after 32 SET commands (or INCR, DECR, ...). This batching is good for performance. In fact currently you cannot set daemon_memcached_w_batch_size to values lower than 32. Only bigger is possible. One exception is replication: If you enable replication for the plugin, daemon_memcached_w_batch_size is set to 1. See below.</div>
<div>
If you want to see memcached data changes immediately even before a commit, you can set your session to SET TRANSACTION ISOLATION TO READ-UNCOMMITTED;</div>
<div>
<br /></div>
<div>
<b>What about performance?</b></div>
<div>
This is a more complex issue. I will write a separate blog post with some performance discussions. But the summary:</div>
<div>
The main parameter is the daemon_memcached_w_batch_size. That will batch 32 statements in memcache protocol into one transaction on InnoDB side.</div>
<div>
My first tests showed that (as expected) access via memcached protocoll offers nearly twice the throughput of SQL for writing. This is especially useful if you have only few user connections. If there are many simultaneous connections IO becomes the bottleneck and not the SQL processing. </div>
<div>
<br /></div>
<div>
<br /></div>
<div>
<b>What about replication and memcached interface?</b></div>
<div>
This works seamlessly. You only have to specify innodb_direct_access_enable_binlog=1. Currently this variable is not (yet?) dynamic. So best to put it into my.cnf/my.ini. The aforementioned daemon_memcached_w_batch_size is set to 1 in this case which means each SET operation is committed separately. As binlog group commit is not implemented in this labs release, this affects performance very badly. But binlog group commit is already in another labs release and this would probably solve a lot of this performance issue.</div>
<div>
More about replication and memcached can be found here: <a href="http://blogs.innodb.com/wp/2011/10/innodb-memcached-with-binlog-capability/">http://blogs.innodb.com/wp/2011/10/innodb-memcached-with-binlog-capability/</a></div>
<div>
<br /></div>
<div>
<b>What if I want more than one value column?</b></div>
<div>
Memcached only knows one value column. That's it. But the plugin to MySQL can help. Look at the configuration table innodb_memcache.config_options:</div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">mysql> SELECT * FROM innodb_memcache.config_options;</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">+-----------+-------+</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">| name | value |</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">+-----------+-------+</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">| separator | | |</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">+-----------+-------+</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">1 row in set (0.00 sec)</span></div>
<div>
This is the magic character to separate different columns. Take another look at my innodb_memcache.containers table:</div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">mysql> SELECT * FROM innodb_memcache.containers;</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">+------+-----------+----------+-------------+-----------------+-------+------------+--------------------+------------------------+</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">| name | db_schema | db_table | key_columns | value_columns | flags | cas_column | expire_time_column | unique_idx_name_on_key |</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">+------+-----------+----------+-------------+-----------------+-------+------------+--------------------+------------------------+</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">| bbb | test | kvstore | key | value,val2,val3 | flags | cas | expires | PRIMARY |</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">+------+-----------+----------+-------------+-----------------+-------+------------+--------------------+------------------------+</span></div>
<div>
<br /></div>
<div>
What is the value to memcached protocol is split into three different columns on the MySQL side. And the separator is the "|" pipe character. Let's try:</div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">[root@olga ~]# telnet localhost 11211</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">Trying ::1...</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">Connected to localhost.</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">Escape character is '^]'.</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">set mykey 0 0 14</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">abcd|1234|WXYZ</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">STORED</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">get mykey</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">VALUE mykey 0 14</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">abcd|1234|WXYZ</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">END</span></div>
<div>
And in MySQL we find the following data:</div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">mysql> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">Query OK, 0 rows affected (0.00 sec)</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><br /></span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">mysql> SELECT * FROM kvstore WHERE `key`="mykey";</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">+-------+-------+-------+------+---------+------+------+</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">| key | value | flags | cas | expires | val2 | val3 |</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">+-------+-------+-------+------+---------+------+------+</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">| mykey | abcd | 0 | 1 | 0 | 1234 | WXYZ |</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">+-------+-------+-------+------+---------+------+------+</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">1 row in set (0.00 sec)</span></div>
<div>
Don't forget to set transaction isolation level to read-uncommitted. Due to write batching the last SET statements may not be visible otherwise.</div>
<div>
<br /></div>
<div>
<b>What happens with Foreign Keys?</b></div>
<div>
NoSQL usually means that data is unstructured and the knowledge about data is no longer in the database but in the application. So I am not sure if foreign keys are relevant to traditional key value store applications. But if you distribute your value into different columns in the innodb table, it might be interesting. So let's test it:</div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">CREATE TABLE `kvstore` (</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> `key` varchar(32) NOT NULL DEFAULT '',</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> `value` varchar(1024) DEFAULT NULL,</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> `flags` int(11) DEFAULT NULL,</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> `cas` bigint(20) unsigned DEFAULT NULL,</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> `expires` int(11) DEFAULT NULL,</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> `val2` varchar(32) DEFAULT NULL,</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> `val3` varchar(32) NOT NULL,</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> PRIMARY KEY (`key`),</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> KEY `val3` (`val3`),</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> CONSTRAINT `kvstore_ibfk_1` FOREIGN KEY (`val3`) REFERENCES `refdata` (`val3`)</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">) ENGINE=InnoDB;</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><br /></span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">mysql> SELECT * FROM refdata;</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">+------+---------------------+</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">| val3 | somefield |</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">+------+---------------------+</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">| ABCD | Another good val3 |</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">| WXYZ | This entry is valid |</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">+------+---------------------+</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">2 rows in set (0.00 sec)</span></div>
<div>
So we can add ABCD or WXYZ as the last field via memcached. Let's see what happens:</div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">set mykey3 0 0 14</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">abcd|1234|ABCD </span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">STORED</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">get mykey3</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">VALUE mykey3 0 14</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">abcd|1234|ABCD</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">END</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">set mykey4 0 0 14</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">efgh|5678|EFGH</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">NOT_STORED <--- That is cool! Memcached plugin appreciates foreign keys!</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">get efgh</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">END</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">get mykey4</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">END</span></div>
<div>
So foreign key constraints are enforced with the memcached plugin.</div>
<div>
<br /></div>
<div>
<b>How can I access multiple tables via memcached?</b></div>
<div>
Memcached does not know anything about tables. There is only one data store. Usually memcached programmers use a trick to simulate different data stores: They include the table name into the key. So the key is something like "user:4711" or "page:/shop/home" or "session:fh5hjk543bjk". But still all data is in a single table in MySQL. If you want to report via SQL on only one type of data like "session:" you can add "...WHERE pkey LIKE "session:%" to your query. To make it comfortable you can also define different views:</div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">mysql> SELECT * FROM kvstore;</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">+----------------------+-------------------------------+-------+------+---------+</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">| key | value | flags | cas | expires |</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">+----------------------+-------------------------------+-------+------+---------+</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">| session:grcn34r834cn | 2012-01-12 08:32|4711 | 0 | 0 | 0 |</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">| session:k35jnjkj56ff | 2012-01-14 23:11|4713 | 0 | 0 | 0 |</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">| user:4711 | dummy|secret|Berlin | 0 | 0 | 0 |</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">| user:4712 | superman|unkown|London | 0 | 0 | 0 |</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">| user:4713 | wonderwoman|dontknow|New York | 0 | 0 | 0 |</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">+----------------------+-------------------------------+-------+------+---------+</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">5 rows in set (0.00 sec)</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><br /></span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">mysql> CREATE VIEW user AS SELECT RIGHT(`key`,4) AS userID, value FROM kvstore WHERE `key` LIKE "user%";</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">Query OK, 0 rows affected (0.37 sec)</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><br /></span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">mysql> SELECT * FROM user;</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">+--------+-------------------------------+</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">| userID | value |</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">+--------+-------------------------------+</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">| 4711 | dummy|secret|Berlin |</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">| 4712 | superman|unkown|London |</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">| 4713 | wonderwoman|dontknow|New York |</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">+--------+-------------------------------+</span></div>
<div>
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">3 rows in set (0.00 sec)</span></div>
<div>
<br /></div>
<div>
<b>Summary</b></div>
<div>
Memcached plugin is easy to enable and offers very lightweight access protocoll to InnoDB data.</div>
<div>
You can store values into different columns. Foreign key relationships are enforced.</div>
<div>
You can also replicate data that is stored via memcached plugin to slave servers.</div>
<div>
The most important tuning parameter is daemon_memcached_w_batch_size, which is 32 by default, 1 if replicating.</div>
<div>
<br /></div>
<div>
I will add a more enhanced use case of the memcached configuration in another post that should show the benefits of using the same data via memcached protocoll and SQL at the same time.</div>
<div>
<br /></div>Unknownnoreply@blogger.com4tag:blogger.com,1999:blog-4614960018934857975.post-67742856761727561462010-03-29T21:20:00.000+01:002013-03-22T08:50:46.320+00:00MySQL is so slow on Windows... Really?<span class="Apple-style-span" style="font-family: Helvetica; font-size: medium;"></span><br />
<div>
<span class="Apple-style-span" style="font-family: Helvetica; font-size: medium;">Last week a customer called me and reported that MySQL was 30 times slower than MS SQL server. Oooops. That's a lot. No way to argue or throw smoke bombs. 30 times slower!</span></div>
<div>
<span class="Apple-style-span" style="font-family: Helvetica; font-size: medium;">It was a standard installation of MySQL (typical install option) on plain Windows 7 and the same for MS SQL Server 2008. The test run was a batch of 30.000 INSERT commands in an SQL script. Runtime was 1 minute on MSSQL and 30 minutes on MySQL.</span></div>
<div>
<span class="Apple-style-span" style="font-family: Helvetica; font-size: medium;">Some tests later we found out that it was only bad on InnoDB. MyISAM was as fast as MSSQL. (I didn't care which one was a bit faster. I didn't care as long as InnoDB was 30 times slower) Finally we nailed the problem down to one parameter in MySQL:</span></div>
<div>
<span class="Apple-style-span" style="font-family: Helvetica; font-size: medium;"><a href="http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit">innodb_flush_log_at_trx_commit</a></span></div>
<div>
<span class="Apple-style-span" style="font-family: Helvetica; font-size: medium;">Each INSERT statement is a single transaction (autocommit mode). MySQL is configured very faithfully and ensures that each transaction is really stored on disk. This is necessary for <a href="http://en.wikipedia.org/wiki/ACID">ACID compliance.</a> D in ACID stands for 'durability'. To store data durable, at least the log file has to be written physically. That's why MySQL when a transaction commits forces the operating system to flush its buffers and even forces the disk cache to flush its buffer. That's the meaning of flush_log_at_trx_commit = 1 in the my.ini or my.cnf file.</span></div>
<div>
<span class="Apple-style-span" style="font-family: Helvetica; font-size: medium;">MSSQL is much more relaxed with your data. It writes the data to disk device. But it may stay in the disk cache, and MSSQL does not care. If you have a crash, your data is not up-to-date on the physical disk and you may lose data. This is definitely not ACID compliant. Microsoft documented this here:</span></div>
<div>
<span class="Apple-style-span" style="font-family: Helvetica; font-size: medium;"><a href="http://support.microsoft.com/default.aspx?scid=kb;en-us;234656">http://support.microsoft.com/default.aspx?scid=kb;en-us;234656</a></span></div>
<div>
<ul>
<li><span class="Apple-style-span" style="font-family: Helvetica; font-size: medium;"><i>By default, the disk cache is enabled. Use the 'Disk Properties', Hardware tab to access the 'Properties', 'Policy' tab to control the disk cache setting. (Note Some drives do not honor this setting. These drives require a specific manufacturer utility to disable cache.)</i></span></li>
<li><span class="Apple-style-span" style="font-family: Helvetica; font-size: medium;"><i>...</i></span></li>
<li><span class="Apple-style-span" style="font-family: Helvetica; font-size: medium;"><i>Disk caching should be disabled in order to use the drive with SQL Server.</i></span></li>
</ul>
</div>
<div>
<span class="Apple-style-span" style="font-family: Helvetica; font-size: medium;">So to have a fair comparison beween MSSQL and MySQL either</span></div>
<div>
<ul>
<li><span class="Apple-style-span" style="font-family: Helvetica; font-size: medium;">set innodb_flush_log_at_trx_commit = 0 (or 2)<br />This forces the flush to disk only once per second and brings good performance but data is not 100% safe on disk (unless you have a battery backed write cache)</span></li>
<li><span class="Apple-style-span" style="font-family: Helvetica; font-size: medium;">disable the disk cache in Windows 7<br />This will force MSSQL to write physically to disk. And then MSSQL is 30 times slower than before. ;-)</span></li>
</ul>
</div>
<div>
<span class="Apple-style-span" style="font-family: Helvetica; font-size: medium;">Lessons learned:</span></div>
<div>
<ul>
<li><span class="Apple-style-span" style="font-family: Helvetica; font-size: medium;">Think a lot about how to do a fair comparison.</span></li>
<li><span class="Apple-style-span" style="font-family: Helvetica; font-size: medium;">Either run (unsafe and fast), or (safe and slow) or (safe and fast and expensive) with a battery backed write cache controller</span></li>
<li><span class="Apple-style-span" style="font-family: Helvetica; font-size: medium;">Read the manual for MSSQL. There may be important news on page 3647+x.</span></li>
</ul>
</div>
<div>
<span class="Apple-style-span" style="font-family: Helvetica; font-size: medium;">The funny thing, that confused me a lot: On my Mac InnoDB was at around 1 minute. And even MySQL on Windows 7 in Virtual Box on my Mac was about 1 minute. The reason is: MySQL on Mac makes a fsync() that does not flush the disk cache. It flushes only the buffer cache of the operating system. And Windows 7 on Virtual Box: The disk is a plain file on the host OS. And this file is of course buffered in the host OS. So Virtualization may destroy your ACID compliance...</span></div>
<div>
<span class="Apple-style-span" style="font-family: Helvetica; font-size: medium;">If you need more info on the different cache levels for file IO here is a very good link:</span></div>
<div>
<span class="Apple-style-span" style="font-family: Helvetica; font-size: medium;"><a href="http://smallvoid.com/article/hard-disk-cache.html">http://smallvoid.com/article/hard-disk-cache.html</a></span></div>
<div>
<span class="Apple-style-span" style="font-family: Helvetica; font-size: medium;"><br /></span></div>
Unknownnoreply@blogger.com3tag:blogger.com,1999:blog-4614960018934857975.post-57932445347388781862010-01-11T11:46:00.000+00:002012-01-18T12:57:31.325+00:00"How to find the source of queries in MySQL Query Analyzer" or "SQL comments in Query Analyzer"<a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgQeyYfTZofmGzCI3G6K851nc01gumOUHURwIraKw06DdOEgvKacSc6FNGj4x63CsFMVwieRV_B_jJRMYAhXGjO_bhHIe7SxwhHDPsFxC77zy5g6iqlQox85L6RiVXIhyphenhyphenKbHNYTc2mItvk/s1600-h/Bild+1.png"><img style="float:right; margin:0 0 10px 10px;cursor:pointer; cursor:hand;width: 200px; height: 114px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgQeyYfTZofmGzCI3G6K851nc01gumOUHURwIraKw06DdOEgvKacSc6FNGj4x63CsFMVwieRV_B_jJRMYAhXGjO_bhHIe7SxwhHDPsFxC77zy5g6iqlQox85L6RiVXIhyphenhyphenKbHNYTc2mItvk/s200/Bild+1.png" border="0" alt="" id="BLOGGER_PHOTO_ID_5425449397089602642" /></a><br /><span class="Apple-style-span" style=" ;font-family:Helvetica;font-size:medium;"><div>MySQL Enterprise Monitor offers a tool called "Query Analyzer" (QuAn). QuAn sits between any client app and the MySQL server and logs every query and its runtime statistics. A very cool tool for analyzing your SQL. More information is available <a href="http://www.mysql.com/products/enterprise/query.html">here</a>.</div><div>If you identify a query, that needs some improvement, sometimes it is hard to identify the source of that query as well. With hundreds of different PHP scripts for example it is not easy to know, which one issued the query, that you want to modify.</div><div>A good way to achieve this is adding C-style SQL comments. Let's look at an example:</div><div><span class="Apple-tab-span" style="white-space:pre"> </span><span class="Apple-style-span" style="font-family:'courier new';">SELECT * FROM mytable /*main.php*/;</span></div><div>Query Analyzer will strip that comment off before archiving the query. This is ok, because QuAn wants to consolidate all similar queries and this comment is irrelevant for the query.</div><div>But you can use <a href="http://dev.mysql.com/doc/refman/5.1/en/comments.html">version specific comments in MySQL</a>. QuAn cannot ignore these comments, </div><div>because they may be relevant for query execution. And this is the solution for our problem:</div><div><span class="Apple-tab-span" style="white-space:pre"> </span><span class="Apple-style-span" style="font-family:'courier new';">SELECT * FROM mytable /*!99999 main.php*/;</span></div><div>This SQL comment will be executed on MySQL version 9.99.99 and later. But this version does not exist. So in reality the comment gets not executed at all. Currently we are at MySQL 5.1.42 so it will take some time before we reach MySQL 9.99.99 ;-)</div><div>The comment is in QuAn and will be logged. So you see the comment when monitoring the query and you know immediately, where that query came from.</div><div><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgvdA3qlrNjA6jK6lEzl5J9OlzXS3BLbcnmwECXjIaEzjJfg0aO7yCSEO0YMzXp1m3ZMuI-YOGosgV3pxlSX_D1a3ZHgpfL5GcCI_0sV9C6yOCJBomhVczS3A-cGJeWr8yoi_yZ7VkEHyE/s1600-h/Bild+2.png"><img style="float:left; margin:0 0 10px 10px;cursor:pointer; cursor:hand;width: 294px; height: 228px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgvdA3qlrNjA6jK6lEzl5J9OlzXS3BLbcnmwECXjIaEzjJfg0aO7yCSEO0YMzXp1m3ZMuI-YOGosgV3pxlSX_D1a3ZHgpfL5GcCI_0sV9C6yOCJBomhVczS3A-cGJeWr8yoi_yZ7VkEHyE/s400/Bild+2.png" border="0" alt="" id="BLOGGER_PHOTO_ID_5425449565832200914" /></a><br /></div><div><br /></div><div><br /></div></span>Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-4614960018934857975.post-24527974177390959762009-11-13T18:59:00.000+00:002012-01-18T12:57:31.319+00:00How to install MySQL Enterprise Monitor agents in a failover environment<div style="text-align: left;">MySQL Enterprise Monitor is a tool to watch and analyze multiple MySQL environments from a single web based dashboard. More information is available on the <a href="http://www.mysql.com/products/enterprise/monitor.html">MySQL homepage</a>. Each MySQL instance is monitored by a small agent that connects to the MySQL instance and reads statistics that is sent to the MySQL Enterprise Monitor (MEM) Server.</div>That setup is very easy. But if the MySQL server is in a cluster failover configuration, there are some things to consider when installing the MEM agent:<br /><br />What do you want?<br /><br />Do you want to have two entries in the MEM dashboard for both physical servers?<div>This is good because:<a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjHOajwmKI9kqL9Xk8-HSHKmeR3W_mmZViUiMLbiGD9gp8sfF9GVKD6leticCiLBLppq7doo0sdGREWV6oWWIe8YVPlqVKBiHgz62c8ZVSaKA0jxOCZtSwc1KWsJGdQW5JNfA69xv0FjgU/s1600-h/Bild+3.png"><img style="float:right; margin:0 0 10px 10px;cursor:pointer; cursor:hand;width: 320px; height: 200px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjHOajwmKI9kqL9Xk8-HSHKmeR3W_mmZViUiMLbiGD9gp8sfF9GVKD6leticCiLBLppq7doo0sdGREWV6oWWIe8YVPlqVKBiHgz62c8ZVSaKA0jxOCZtSwc1KWsJGdQW5JNfA69xv0FjgU/s320/Bild+3.png" border="0" alt="" id="BLOGGER_PHOTO_ID_5403674158811808530" /></a><ul><li>You can monitor them separately, you can define different rules for both servers in case they offer different capabilities.</li><li>You can immediately see, which physical server runs the MySQL instance. The other entry will always report either "MySQL server down" or "MEM agent not reachable". </li></ul>This is not so good because:<br /><ul><li>You cannot watch the data if a failover occurred. E.g. you can only see graphs for a specific physical host.</li><li>You get red alarms because the agent on the passive node cannot reach the MySQL instance. This alarm is harmless. But it will train you to ignore red alarms. Not good.</li></ul>If you like this approach <a href="http://simambe.blogspot.com/2009/11/installing-mem-agent-in-cluster-on.html">here is the description how to install</a>.<br /><br />Do you want to see only <b>one</b> entry in the MEM dashboard that displays the data no matter, which physical server is running the instance at the moment?<br />This is good because:<a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj1Juu4bjQq_LJlIa7qt-cEXplRwewMUkKL7SSfZvIbYTPlZ39iY2ZYrETvuk4AGB9iJR_zP5F4PDYbktvTRlhS-wlQR5PY2CDwKPmR96_0iSVDbQZvvMBiEC-uSS93MDMWETvg4SVQC9w/s1600-h/Bild+4.png"><img style="float:right; margin:0 0 10px 10px;cursor:pointer; cursor:hand;width: 320px; height: 227px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj1Juu4bjQq_LJlIa7qt-cEXplRwewMUkKL7SSfZvIbYTPlZ39iY2ZYrETvuk4AGB9iJR_zP5F4PDYbktvTRlhS-wlQR5PY2CDwKPmR96_0iSVDbQZvvMBiEC-uSS93MDMWETvg4SVQC9w/s320/Bild+4.png" border="0" alt="" id="BLOGGER_PHOTO_ID_5403674987629200530" /></a><ul><li>You have continuous data even if a failover occurred during the period. E.g. you can watch the cache hit rate graph of your MySQL server and you will see only a dip where the failover took place.</li><li>You will not see false events like "MySQL server is down on the passive node". Of course it is down. That's why it is called the passive node ;-)</li></ul>This is not so good because:<br /><ul><li>It's not so easy to see which physical server currently runs the MySQL instance: In the meta info on the dashboard screen you see the physical hostname that runs the MySQL instance.</li><li>You need to apply the same rules to both physical servers. This may be a problem if they have different capabilities, e.g. if the backup node is smaller.</li></ul>If you prefer this approach <a href="http://simambe.blogspot.com/2009/11/installing-mem-agent-on-cluster-on.html">here is the description how to install</a>.<br />By the way: I recommend this way, as the disadvantages are really small and the installation is even a little easier. Both procedures work for all common HA frameworks like SunCluster, OpenHA Cluster, Linux Heartbeat, Microsoft Windows Cluster.</div>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-4614960018934857975.post-63128021966686325192009-11-13T18:40:00.000+00:002012-01-18T12:57:31.328+00:00Installing MEM agent on a cluster on the logical host<span class="Apple-style-span" style=" ;font-family:Helvetica;font-size:medium;">The goal is to have only one entry in the Enterprise Monitor Dashboard that shows the status of the MySQL instance, no matter on which physical server in runs. There are two ways to achieve this:<div><ul><li>You can install the agent on both physical nodes</li><li>You can install the agent on a shared storage.</li></ul></div><div>In either case you have to make sure, that only one agent runs at a time. You have to integrate the agent into your cluster framework. I will not describe how this works, as it is highly dependant on your cluster framework.</div><div><br /></div><div>The following description assumes, that you will install the agent on both physical nodes.</div><div><br /></div><div><ol><li>Install the agent but DO NOT START the agent yet.<br /><br /></li><li>Edit the [<span class="Apple-style-span" style="font-family:'courier new';"><b><i>agent-installdir]</i></b>/etc/mysql-monitor-agent.ini<br /><span class="Apple-style-span" style=" ;font-family:Helvetica, serif;">In the [mysql-proxy] section add the following line:<br /><span class="Apple-style-span" style="font-family:'courier new';">agent-host-id=<b><i>[logical hostname]</i></b></span></span></span></li><li><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style=" ;font-family:Helvetica, serif;"><span class="Apple-style-span" style="font-family:'courier new';"><b><i><span class="Apple-style-span" style=" font-style: normal; font-weight: normal; font-family:Helvetica, serif;">Do steps 1. and 2. for the other cluster node as well.<br /><br /></span></i></b></span></span></span></li><li><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style=" ;font-family:Helvetica, serif;"><span class="Apple-style-span" style="font-family:'courier new';"><b><i><span class="Apple-style-span" style=" font-style: normal; font-weight: normal; font-family:Helvetica, serif;">Include the agent in the cluster's failover group so that it will start automatically on that node, where the MySQL instance is running.<br /><br /></span></i></b></span></span></span></li><li><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style=" ;font-family:Helvetica, serif;"><span class="Apple-style-span" style="font-family:'courier new';"><b><i><span class="Apple-style-span" style=" font-style: normal; font-weight: normal; font-family:Helvetica, serif;">Start the agent via the cluster framework.<br /><br /></span></i></b></span></span></span></li><li><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style=" ;font-family:Helvetica, serif;"><span class="Apple-style-span" style="font-family:'courier new';"><b><i><span class="Apple-style-span" style=" font-style: normal; font-weight: normal; font-family:Helvetica, serif;">The entry in MEM will get the hostname of the first node that runs MySQL. Even after a failover this name will stay. You should change that name to the virtual hostname of the virtual ip adress of your MySQL service. Use the MEM BUI, choose the tab "Settings". From the secondary menu right below the tabs choose "Manage Servers". If you move the mouse over the server name a submenu will appear. Choose "Rename" and enter the name of the virtual IP.</span></i></b></span></span></span></li></ol></div></span>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-4614960018934857975.post-61253288971827320712009-11-13T15:17:00.000+00:002012-01-18T12:57:31.327+00:00Installing MEM agent in a cluster on the physical hosts<span class="Apple-style-span" style=" ;font-family:Helvetica;font-size:medium;"><div>To install the MEM agent in a way that both physical servers are listed in the MEM dashboard, you have to install the agent on both physical nodes. But: Do not start the agent after the installation!</div><div>There are three different IDs in MEM: agent-uuid, mysql-uuid and host-id. Usually they are generated automatically and you will never notice these IDs. For more information about the meaning of the different IDs look <a href="http://blogs.sun.com/DatabaseEmporium/entry/agents_hosts_and_instances_oh">at this very good explanation from Jonathon Coombes</a>.</div><div>The agent stores the uuid and the hostid in a MySQL table called mysql.inventory. After a failover the other agent on the new node will notice "wrong" hostid and uuid entries in the inventory table. The agent will stop and ask you to TRUNCATE mysql.inventory. But with this procedure MEM creates a new instance, so all old data is lost. Not good for a failover environment.</div><div>So in case of a failover you have to provide the mysql.inventory table, that the agent expects.</div><div>And here is how you can achieve this:</div><div><ol><li>Install the MEM agent on the node that currently runs MySQL. Start the agent.<br /><br /></li><li>Make a table that stores the hostid and uuid for every physical host:<br /><span class="Apple-style-span" style=" ;font-family:'courier new', serif;">USE mysql;<br />CREATE TABLE inventory_hostname LIKE inventory;<br />DROP INDEX `PRIMARY` ON inventory_hostname;<br />ALTER TABLE inventory_hostname ADD COLUMN (hostname VARCHAR(64));<br />ALTER TABLE inventory_hostname ADD PRIMARY KEY (hostname,name);<br />INSERT INTO inventory_hostname SELECT *,@@hostname FROM inventory;<br />SELECT * FROM inventory_hostname;<br /><span class="Apple-style-span" style=" ;font-family:Helvetica, serif;"><span class="Apple-style-span" style="font-family:'courier new', serif;"><br /></span>The newly created table should look like this:<br /><span class="Apple-style-span" style=" ;font-family:'courier new', serif;"><pre>+--------+--------------------------------------+----------+<br />| name | value | hostname |<br />+--------+--------------------------------------+----------+<br />| uuid | 96936e90-56bd-4eb1-aef3-e708d149a4cb | wclus-1 |<br />| hostid | mac:{005056a138c10000} | wclus-1 |<br />+--------+--------------------------------------+----------+</pre><br /><span class="Apple-style-span" style=" ;font-family:Helvetica, serif;">(Notice that the hostid is based on the mac address in my case. Usually this is the public ssh host key.)<br /><br /></span></span></span></span></li><li><span class="Apple-style-span" style=" ;font-family:'courier new', serif;"><span class="Apple-style-span" style=" ;font-family:Helvetica, serif;"><span class="Apple-style-span" style=" ;font-family:'courier new', serif;"><span class="Apple-style-span" style=" ;font-family:Helvetica, serif;">Stop the agent<br /><br /></span></span></span></span></li><li><span class="Apple-style-span" style=" ;font-family:'courier new', serif;"><span class="Apple-style-span" style=" ;font-family:Helvetica, serif;"><span class="Apple-style-span" style=" ;font-family:'courier new', serif;"><span class="Apple-style-span" style=" ;font-family:Helvetica, serif;">Empty the inventory_table:<br /><span class="Apple-style-span" style=" ;font-family:'courier new', serif;">TRUNCATE mysql.inventory;<br /><br /></span></span></span></span></span></li><li><span class="Apple-style-span" style=" ;font-family:'courier new', serif;"><span class="Apple-style-span" style=" ;font-family:Helvetica, serif;"><span class="Apple-style-span" style=" ;font-family:'courier new', serif;"><span class="Apple-style-span" style=" ;font-family:Helvetica, serif;"><span class="Apple-style-span" style=" ;font-family:'courier new', serif;"><span class="Apple-style-span" style=" ;font-family:Helvetica, serif;">Failover the MySQL instance to the other node.<br /><br /></span></span></span></span></span></span></li><li><span class="Apple-style-span" style=" ;font-family:'courier new', serif;"><span class="Apple-style-span" style=" ;font-family:Helvetica, serif;"><span class="Apple-style-span" style=" ;font-family:'courier new', serif;"><span class="Apple-style-span" style=" ;font-family:Helvetica, serif;"><span class="Apple-style-span" style=" ;font-family:'courier new', serif;"><span class="Apple-style-span" style=" ;font-family:Helvetica, serif;">Install and start the agent on the other node. It will save new values in inventory.<br /><br /></span></span></span></span></span></span></li><li><span class="Apple-style-span" style=" ;font-family:'courier new', serif;"><span class="Apple-style-span" style=" ;font-family:Helvetica, serif;"><span class="Apple-style-span" style=" ;font-family:'courier new', serif;"><span class="Apple-style-span" style=" ;font-family:Helvetica, serif;"><span class="Apple-style-span" style=" ;font-family:'courier new', serif;"><span class="Apple-style-span" style=" ;font-family:Helvetica, serif;">Copy these new values to the inventory_hostname table:<br /><span class="Apple-style-span" style="font-family:'courier new';">USE mysql;<br />INSERT INTO inventory_hostname SELECT *,@@hostname FROM inventory;<br /><span class="Apple-style-span" style=" ;font-family:Helvetica, serif;">Both nodes should be visible in MEM dashboard right now.<br /><br />With every failover we need to populate the inventory table with the host-specific rows. Easiest way (and independent on the operating system and cluster framework) is to define an init-file:<br /><br /></span></span></span></span></span></span></span></span></li><li><span class="Apple-style-span" style=" ;font-family:'courier new', serif;"><span class="Apple-style-span" style=" ;font-family:Helvetica, serif;"><span class="Apple-style-span" style=" ;font-family:'courier new', serif;"><span class="Apple-style-span" style=" ;font-family:Helvetica, serif;"><span class="Apple-style-span" style=" ;font-family:'courier new', serif;"><span class="Apple-style-span" style=" ;font-family:Helvetica, serif;"><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style=" ;font-family:Helvetica, serif;">On both nodes create a file named </span><span class="Apple-style-span" style="font-family:'courier new';"><b><i>[MySQL Basedir]</i></b>/mysql_init_HA_MEM.sql</span><span class="Apple-style-span" style=" ;font-family:Helvetica, serif;"> with the following statements:<br /><span class="Apple-style-span" style="font-family:'courier new';">USE mysql;<br />REPLACE INTO inventory SELECT name,value FROM inventory_hostname WHERE hostname=@@hostname;<br /><br /></span></span></span></span></span></span></span></span></span></li><li><span class="Apple-style-span" style=" ;font-family:'courier new', serif;"><span class="Apple-style-span" style=" ;font-family:Helvetica, serif;"><span class="Apple-style-span" style=" ;font-family:'courier new', serif;"><span class="Apple-style-span" style=" ;font-family:Helvetica, serif;"><span class="Apple-style-span" style=" ;font-family:'courier new', serif;"><span class="Apple-style-span" style=" ;font-family:Helvetica, serif;"><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style=" ;font-family:Helvetica, serif;"><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style=" ;font-family:Helvetica, serif;">On both nodes edit you my.cnf or my.ini file. In the section [mysqld] add the following line:<br /><span class="Apple-style-span" style="font-family:'courier new';">init-file=<b><i>[MySQL Basedir]</i></b>/mysql_init_HA_MEM.sql<br /><span class="Apple-style-span" style=" ;font-family:Helvetica, serif;">(If you already have an init-file defined you can add the commands "USE mysql; REPLACE..." to you init-file.)<br /><br /></span></span></span></span></span></span></span></span></span></span></span></span></li><li><span class="Apple-style-span" style=" ;font-family:'courier new', serif;"><span class="Apple-style-span" style=" ;font-family:Helvetica, serif;"><span class="Apple-style-span" style=" ;font-family:'courier new', serif;"><span class="Apple-style-span" style=" ;font-family:Helvetica, serif;"><span class="Apple-style-span" style=" ;font-family:'courier new', serif;"><span class="Apple-style-span" style=" ;font-family:Helvetica, serif;"><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style=" ;font-family:Helvetica, serif;"><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style=" ;font-family:Helvetica, serif;"><span class="Apple-style-span" style="font-family:'courier new';"><span class="Apple-style-span" style=" ;font-family:Helvetica, serif;">Start all agents and try to failover the MySQL instance. Check that you init-file really modifies the inventory table.</span></span></span></span></span></span></span></span></span></span></span></span></li></ol></div><div><div><br /></div></div></span>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-4614960018934857975.post-55417248052020614972009-05-14T09:09:00.000+01:002012-01-18T12:57:31.324+00:00Installing MySQL in Solaris 10 zones / containers<span class="Apple-style-span" style="font-family: Helvetica; font-size: 12px; "><div>Now that installing MySQL in Solaris zones is even officially supported by the MySQL support group (see <a href="http://www.mysql.com/about/legal/supportpolicies/policies-06.html#q03">http://www.mysql.com/about/legal/supportpolicies/policies-06.html#q03</a>), the question is: What is the right way of installing MySQL in a zone. Of course this depends on what you want to achieve. The following description is based on Solaris 10. On Opensolaris this is different (somewhat easier, as there are no more sparse root zones.)</div><div><br /></div><div>If you run a local zone as a whole root zone, you can easily install MySQL from tarball or the package installer.</div><div><br /></div><div>If you run a local zone as a sparse root zone, there are different options:</div><div><br /></div><div>First you cannot use the package installer, as this procedure will copy binaries to /usr/bin. But /usr/bin is inherited from the global zone and write protected. You have to use the tarball installation.</div><div><br /></div><span class="Apple-style-span" style="font-weight: bold;"><span class="Apple-style-span" style="font-size: medium;">1. Make /usr/local/mysql writable</span></span><br />The tar ball will install in /usr/local/mysql. You can create a symbolic link in your GLOBAL ZONE:<br />> ln -s /localsoftware/mysql /usr/local/mysql<br />This link points to a directory, that is not inherited. So in every zone /usr/local/mysql will point to a dedicated directory with write permission. You untar the software in the zone in /localsoftware<br /><br /><span class="Apple-style-span" style="font-weight: bold;"><span class="Apple-style-span" style="font-size: medium;">2. Make /usr/local writable</span></span><br />If you want no software from /usr/local in the global zone to be available in the local zones as well, you can use solution#1 even with /usr/local. So in every zone, /usr is inherited and write protected. But /usr/local points to a directory, that is writable. This is my personal favorit:</span><div><span class="Apple-style-span" style="font-family: Helvetica; font-size: 12px; ">In the global zone:<span class="Apple-style-span" style="font-family: 'courier new';"></span></span></div><div><span class="Apple-style-span" style="font-family: Helvetica; font-size: 12px; "><span class="Apple-style-span" style="font-family: 'courier new';"><span class="Apple-tab-span" style="white-space:pre"> </span>> mv /usr/local /LOCAL<br /></span><span class="Apple-style-span" style="font-family: 'courier new';"><span class="Apple-tab-span" style="white-space:pre"> </span>> ln -s /LOCAL /usr/local</span><br />and in the local zone<br /><span class="Apple-tab-span" style="white-space: pre; "><span class="Apple-style-span" style="font-family: 'courier new';"> </span></span><span class="Apple-style-span" style="font-family: 'courier new';">> mkdir /LOCAL<br /></span><span class="Apple-tab-span" style="white-space: pre; "><span class="Apple-style-span" style="font-family: 'courier new';"> </span></span><span class="Apple-style-span" style="font-family: 'courier new';">> cd /usr/local<br /></span><span class="Apple-tab-span" style="white-space: pre; "><span class="Apple-style-span" style="font-family: 'courier new';"> </span></span><span class="Apple-style-span" style="font-family: 'courier new';">> gtar -xzf /anywhere/mysql.tgz</span><br /><br /><span class="Apple-style-span" style="font-weight: bold;"><span class="Apple-style-span" style="font-size: medium;">3. Install in different location</span></span><br />You can untar the MySQL tarball in any other location, that is not inherited from the global zone. Maybe /opt/ ? Check with mount, what directories are loopback mounted from the global zone with read-only flag.<br /><br /><span class="Apple-style-span" style="font-weight: bold;"><span class="Apple-style-span" style="font-size: medium;">4. Install MySQL globally for all zones</span></span><br />You can untar MySQL in the global zone's /usr/local. Then every local zone has a MySQL installation as well. But then it is important, that all write access in MySQL is done to a writable directory, like <span class="Apple-style-span" style="font-style: italic;">/var/lib/mysql</span>. Otherwise MySQL in the local zone will stop, because it cannot write it's logfile/errorfile/datafiles in <span class="Apple-style-span" style="font-style: italic;">/usr/local/mysql/data</span><br /><br /></span></div>Unknownnoreply@blogger.com0