tag:blogger.com,1999:blog-4614960018934857975.comments2023-11-27T09:32:36.643+00:00MablomyUnknownnoreply@blogger.comBlogger18125tag:blogger.com,1999:blog-4614960018934857975.post-6676190591462610162017-07-05T10:36:50.891+01:002017-07-05T10:36:50.891+01:00Thank you for sharing. Your blog has given me that...Thank you for sharing. Your blog has given me that thing which I never expect to get from all over the websites. Nice post guys!<br /><br /><a href="http://www.papdan.com/" rel="nofollow">Melbourne Web Developer</a>seravina danniellahttps://www.blogger.com/profile/17416651554211077082noreply@blogger.comtag:blogger.com,1999:blog-4614960018934857975.post-84438795892903106372016-04-05T09:11:32.695+01:002016-04-05T09:11:32.695+01:00Good point. Your example is even simpler without t...Good point. Your example is even simpler without the aggregation function.<br />I also tested ... WHERE dy*dx=221; so reverse order of factors. That is mathematically the same but the optimizer does not recognize the expression. However I doubt that is a big issue. If your application is really creating both types of expressions either create both virtual columns with indexes (probably costly) or use the Query Rewrite Plugin.Mario Beckhttps://www.blogger.com/profile/04044394976329123982noreply@blogger.comtag:blogger.com,1999:blog-4614960018934857975.post-58146787890593123582016-04-04T21:35:47.160+01:002016-04-04T21:35:47.160+01:00The aggregate function is indeed not using an inde...The aggregate function is indeed not using an index, but also something simpler like this is not (yet) using the index:<br /><br />CREATE TABLE squares (dx int unsigned, dy int unsigned);<br />insert into squares select round(rand()*100,0), round(rand()*100,0);<br />insert into squares select round(rand()*100,0), round(rand()*100,0) from squares; -- until COUNT(*) == 2097152<br />ALTER TABLE squares ADD COLUMN (area INT AS (dx*dy));<br />ALTER TABLE squares ADD INDEX (area);<br />ALTER TABLE squares ADD INDEX `dx__area` (dx, area);<br />EXPLAIN SELECT * FROM squares WHERE dx*dy=221\G<br />EXPLAIN SELECT dx*dy FROM squares WHERE dx=5\G<br />EXPLAIN FORMAT=JSON SELECT dx*dy FROM squares WHERE dx=5\G<br />EXPLAIN FORMAT=JSON SELECT area FROM squares WHERE dx=5\GDaniël van Eedenhttps://www.blogger.com/profile/14757324605223498151noreply@blogger.comtag:blogger.com,1999:blog-4614960018934857975.post-36280861733478621132016-04-04T14:06:22.773+01:002016-04-04T14:06:22.773+01:00Good 'hack' here for working around the la...Good 'hack' here for working around the lack of CHECK constraints in MySQL. And works - as the final case shows - even with expressions on multiple columns (sort of EXTENDED CHECK). I don't think it works with virtual columns in MariaDB unfortunately (https://mariadb.com/kb/en/mariadb/virtual-computed-columns/ does not list NOT NULL as valid syntax)Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-4614960018934857975.post-69929084023482405922016-03-10T11:13:43.603+00:002016-03-10T11:13:43.603+00:00That was a great idea. Thanks a lot! It wasn't...That was a great idea. Thanks a lot! It wasn't me but Ted who tested it. The MySQL binary is now down to 8.2MB! See details in Ted's blog: http://mysql-nordic.blogspot.se/2016/03/mysql-footprint-less-than-10mb.htmlMario Beckhttps://www.blogger.com/profile/04044394976329123982noreply@blogger.comtag:blogger.com,1999:blog-4614960018934857975.post-42071524826651135932016-02-03T17:13:16.106+00:002016-02-03T17:13:16.106+00:00Did you see how small it goes after packing with U...Did you see how small it goes after packing with UPX?<br />http://upx.sourceforge.net/sbesterhttps://www.blogger.com/profile/01382626013503952498noreply@blogger.comtag:blogger.com,1999:blog-4614960018934857975.post-48796078463118429502016-02-03T13:21:29.194+00:002016-02-03T13:21:29.194+00:00I ran MySQL Cluster on RPi. See here: http://mablo...I ran MySQL Cluster on RPi. See here: http://mablomy.blogspot.de/2014/03/mysql-cluster-on-raspberry-pi-sub.html<br />This time the target platform is more something like SOC chips, e.g. i.MX6 https://en.wikipedia.org/wiki/I.MX with only 128MB RAM and 4GB flash storage. In these environments every MB counts...Mario Beckhttps://www.blogger.com/profile/04044394976329123982noreply@blogger.comtag:blogger.com,1999:blog-4614960018934857975.post-14460428645759650932016-02-03T13:09:33.354+00:002016-02-03T13:09:33.354+00:00Even a Raspberry PI has 1 GB of RAM and at least 4...Even a Raspberry PI has 1 GB of RAM and at least 4GB hard drive, so even running MySQL Cluster on a Raspberry PI is okMikael Ronstromhttps://www.blogger.com/profile/07134215866292829917noreply@blogger.comtag:blogger.com,1999:blog-4614960018934857975.post-38210440438468107862015-12-29T14:29:08.501+00:002015-12-29T14:29:08.501+00:00Thanks for sharing your thoughts in this post.Thanks for sharing your thoughts in this post.Nickole Dinardohttps://www.blogger.com/profile/05435675622017105709noreply@blogger.comtag:blogger.com,1999:blog-4614960018934857975.post-15067024400398576072013-03-22T08:49:23.749+00:002013-03-22T08:49:23.749+00:00You are right. I will add that to the blog post. T...You are right. I will add that to the blog post. Thanks for spotting this.Mario Beckhttps://www.blogger.com/profile/04044394976329123982noreply@blogger.comtag:blogger.com,1999:blog-4614960018934857975.post-25172110304109252032013-03-22T08:34:36.117+00:002013-03-22T08:34:36.117+00:00Best performance will be with set innodb_flush_log...Best performance will be with set innodb_flush_log_at_trx_commit = 0<br />As stated in MySQL config:<br /><br />If set to 1, InnoDB will flush (fsync) the transaction logs to the<br />disk at each commit, which offers full ACID behavior. If you are<br />willing to compromise this safety, and you are running small<br />transactions, you may set this to 0 or 2 to reduce disk I/O to the<br />logs. Value 0 means that the log is only written to the log file and<br />the log file flushed to disk approximately once per second. Value 2<br />means the log is written to the log file at each commit, but the log<br />file is only flushed to disk approximately once per second.Vitaly Dyatlovhttps://www.blogger.com/profile/13707544514554521197noreply@blogger.comtag:blogger.com,1999:blog-4614960018934857975.post-85597491732834736702012-03-30T02:01:15.793+01:002012-03-30T02:01:15.793+01:00Hi Mario.
>> For InnoDB logs and replicatio...Hi Mario.<br /><br />>> For InnoDB logs and replication logs (binlog) I do not expect any batching<br />I really want this function. so I'm wondering memcached plugin can eliminate previous change of the same key.<br />Anyway, memcached plugin still run every single query(can be batched) to innodb storage engine.<br />Right ?<br /><br />Really thanks.토토https://www.blogger.com/profile/13365204800755945163noreply@blogger.comtag:blogger.com,1999:blog-4614960018934857975.post-69724181287566648812012-03-29T14:42:13.423+01:002012-03-29T14:42:13.423+01:00As always with InnoDB the data will be stored in t...As always with InnoDB the data will be stored in the buffer pool first. If you modify that data before it is committed, it is really batched. You will write only the final version of the data block. For InnoDB logs and replication logs (binlog) I do not expect any batching. The parameter "daemon_memcached_w_batch_size" means that each SET will result in one INSERT or UPDATE on InnoDB, and every n (default=32) statements, the memcached plugin will add a "COMMIT".Mario Beckhttps://www.blogger.com/profile/04044394976329123982noreply@blogger.comtag:blogger.com,1999:blog-4614960018934857975.post-69659230442497015772012-03-29T14:22:40.689+01:002012-03-29T14:22:40.689+01:00Hi Mario, thanks for good post.
I'm really in...Hi Mario, thanks for good post.<br /><br />I'm really interested in Memcached intergrated InnoDB.<br />Above post, you said power of "daemon_memcached_w_batch_size" option.<br />I'm wondering memcached plugin can merge multiple update statement.<br /><br />for example, below query is buffering in memcached plugin.<br />00:00;00 update tb_test set v=1 where id=1;<br />00:00;01 update tb_test set v=2 where id=2;<br />00:00;02 update tb_test set v=3 where id=1;<br />00:00;03 update tb_test set v=4 where id=2;<br />00:00;05 ...<br /><br />memcached plugin can merge these batched update statements like below,<br />00:00;01 update tb_test set v=2 where id=2;<br />00:00;03 update tb_test set v=4 where id=2;<br />00:00;05 ...<br /><br />Really thanks for your answer in advance.토토https://www.blogger.com/profile/13365204800755945163noreply@blogger.comtag:blogger.com,1999:blog-4614960018934857975.post-8192553293044120042012-03-12T20:09:26.045+00:002012-03-12T20:09:26.045+00:00Hi Mario
You're right that your IO setup does...Hi Mario<br /><br />You're right that your IO setup doesn't really reflect a realistic setup. From use of HandlerSocket and Galera, we find that the SQL parsing overhead is about half of query processing. In other words even with a write-only workload (ie one where there is always still some I/O) you could expect to get 2x benefit from this kind of NoSQL interface.<br /><br />On a read-only and in-memory workload (no I/O at all) the benefit from HandlerSocket was 7x more throughput.<br /><br />The interesting feature with HandlerSocket was that they ended up being faster than Memcached itself. This is probably also true for MySQL Memcache API. You should try it one day.hingohttps://www.blogger.com/profile/09201666166374161923noreply@blogger.comtag:blogger.com,1999:blog-4614960018934857975.post-17966691332757985722012-03-12T14:22:18.283+00:002012-03-12T14:22:18.283+00:00I think a huge benefit you're discussing, but ...I think a huge benefit you're discussing, but not naming separately, is consistently. With memcached, you have two copies of the data, and the man with two watches never knows what time it is. With a memcached interface to MySQL, you have only one copy -- and it is consistent. This is a huge win.Baronhttps://www.blogger.com/profile/01621441847303652718noreply@blogger.comtag:blogger.com,1999:blog-4614960018934857975.post-20716640778395147852012-01-19T14:23:50.059+00:002012-01-19T14:23:50.059+00:00Hi Mario, great post. If someone wants to use the ...Hi Mario, great post. If someone wants to use the Memcached API but with MySQL as the storage engine rather than InnoDB then that's possible too.... http://www.clusterdb.com/mysql-cluster/scalabale-persistent-ha-nosql-memcache-storage-using-mysql-cluster/<br /><br />With the MySQL Cluster version you can also use the key prefix to map keys/values to differnt columns in different tables - that way you can add Memcached API access to your existing schema.Andrew Morganhttps://www.blogger.com/profile/08593968879243742268noreply@blogger.comtag:blogger.com,1999:blog-4614960018934857975.post-83016349939336321972010-07-09T06:57:07.156+01:002010-07-09T06:57:07.156+01:00With the new MEM 2.2 (Spring Release 2010) and the...With the new MEM 2.2 (Spring Release 2010) and the Connector Plugins for Java and .NET you get automatic stack trace information in Query Analyzer. No more need for this workaround. For php and other connectors you still need the comments as described above.Mariohttp://www.blogger.com/profile/04044394976329123982noreply@blogger.com