Large Bionocular Telescope brings the Universe into Sharper Focus
via LBT Press Release March 2012 Images and Captions.
I got the chance to help these folks with some database work. Talk about “big data”.
Categories
Category: MySQL
Categories
mysqlnd_ms – Replication and Load Balancing
The mysqlnd replication and load balancing plugin (mysqlnd_ms) adds easy to use MySQL replication support to all PHP MySQL extensions that use mysqlnd.
Categories
Things to chase after
MySQL specific?
select make_set(255,’bit0′,’bit1′,’bit2′,’bit3′,’bit4′,’bit5′,’bit6′,’bit7′);
select (1,2,3)=(1,2,3);
Categories
Hacking FRM files to switch data types
The solution was to change the datatype from TINYINT to TINYINT UNSIGNED. The problem is that this is a very large dataset, ~14TB across a bunch of systems. We could use our online schema change tool (OSC) to do this change, but it would still take a week or two to run.
via Hacking FRM files to switch data types:
Basically a metadata change that needn’t affect the underlying data.
In Rdb data (re)write causes new datatype to be used. No need to update the entire table when this kind of change is made. Versioned metadata is such a fine thing.
Categories
SQL: selecting top N records per group
Based on a similar concept, combined with string walking, I now present a query which selects top-n records for each group, ordered by some condition. It will require no subqueries. It executes faster than its more conventional alternatives.
via SQL: selecting top N records per group | code.openark.org.
DBAs facing the problem of corporate data explosion have an excellent new tool to help them in the MySQL 5.0 Archive storage engine. Whether it’s a data warehousing, data archiving, or data auditing situation, MySQL Archive tables can be just what the doctor ordered when it comes to maintaining large amounts of standard or sensitive information, while keeping storage costs at a bare-bones minimum.
via MySQL :: The MySQL 5.0 Archive Storage Engine.
Should probably investigate using ARCHIVE storage for the multi-year history tables
Categories
MySQL replication recovery
MySQL replication can stop if slave fails to execute SQL statement from the binary log. From that moment, slave prints last error and waits for replication recovery. If master has consistent snapshot, then is only necessary to re-point slave to the new master position. It can be done with change master to or sql_slave_skip_counter.
However, sometimes you want to apply binary logs to a MySQL instance, without having those changes applied to the binary logs themselves. One option is to restart the server binary logging disabled, and after the load is finished, restart the server with binary logging re-enabled. This is not always possible nor desirable, so there’s a better way, that works in at least versions 4.1 and up
via Applying binary logs without adding to the binary log | The Pythian Blog.
SET SESSION sql_log_bin=0;
Categories
mysql –xml and some XSLT
Somebody asked in Freenode the other day how to get their data out of MySQL into a specific XML format.
Both mysqldump and the mysql client have –xml options. The output from the cli looks like this:
via A Little Noise » Blog Archive » mysql’s –xml and some XSLT.