If you need to create a big database into MySQL Cluster with:

• A lot of tables indexes, columns, and tables
• A lot of records
  

there are a few things to think about:

• If a table has > ~90M records, you have to create the table with MAX_ROWS=<amount of records in table anticipating growth>:
  CREATE TABLE t1(...) ENGINE=ndbcluster MAX_ROWS=200000000;
  This way the data node will allocate more partitions for the table, since there is a limitation in how many records that can be stored in one partition (and the limit is around 90M records).
  
• Many tables / table objects --> Make sure you increase MaxNoOfTables (kernel limit is 20320 tables). This creates a table object pool of size MaxNoOfTables.
  
  • Each table you create will use one table object. …

Recently a colleague at Sun was asking me if MySQL can tell him to determine if we are currently in summer time or winter time. He was doing some data analysis of his house where he has installed solar panels.

I am not aware of what he wants to do exactly, but possibly he wants all the data in solar time. So UTC could help him because UTC does not change much over time.

Next thing which came to my mind is, that possibly the good place to do such math calculations is the application code and not the database.

But never the less I was interested in how to solve this IN the database.

By default your MySQL server relies on your servers time zone. [1]

So if your server is set-up correctly you should be capable to determine if you are in summer time or winter time by your current time, UTC time and the offset you have to UTC.
mysql> SELECT …

A very wise systems programmer once told me: “Don’t guess. Measure.” Since then, I’ve learned the hard way that guessing too much about performance is death by a thousand cuts. For RethinkDB, dozens of factors for I/O alone affect performance (not to mention memory, buses, caches, and CPU cores). In order to design the fastest database on Earth, we constantly test the following factors:

• Performance of read and write operations.
• Behavior for random and sequential workloads:
  • For random workloads, the behavior of uniform, normal, and power distributions (with different distribution parameters).
  • For sequential workloads, the seek direction and various strides .
…

The favourite Open Query course modules as well as reworked and brand new ones, with November/December 2009 dates for Brisbane, Sydney, Canberra and Melbourne listed below. You can register for days/modules individually, to suit your time, budget and current needs. Your trainers are Sean, Ray and Arjen (see OQ people).

For the Canberra and Melbourne days which are DBA/HA, registrations for all of the modules in a series before 15 October will receive a copy of the “High Performance MySQL” book (normal bookstore price is AUD 105).

Canberra

• Thu 5 Nov: MySQL High Availability – Strategy and Tools
• Fri 6 Nov: MySQL Cluster …

I ported/reimplemented show table_statistics for the Facebook patch on Launchpad. This first appeared in the Google patch. In this patch the Database and Rows_requested columns have been added and the Rows_changed_x_indexes column removed. This adds two commands: show table_statistics to report on per-table activity and flush table_statistics to reset the counters. Data is only collected for non-temporary MyISAM and InnoDB tables.

This is always enabled. I think it has minimal impact on performance and tests are in progress to verify that. But it is probably a bad idea to run flush table_statistics in a loop without sleeping.

One entry is listed for each table for which there was activity and includes the following …

From Stack Overflow:

Are the following queries efficient in MySQL:

SELECT  *
FROM    table
WHERE   field & number = number
-- to find values with superset of number's bits

SELECT  *
FROM    table
WHERE   field | number = number
-- to find values with subset of number's bits

, if an index for the field has been created?

If not, is there a way to make it run faster?

An index can be used for the following things:

1. To limit the number of records scanned
2. To lower the number of row lookups

When doing a full table scan, every record should be fetched and examined. If the table contains say, 1,000,000 records, and each record is 100 bytes long, then 100 …

Oracle isn't going to give up on its acquisition of MySQL because MySQL is a strategic wedge for it against Microsoft.

Firstly, I have to thank my co-workers Singer Wang and Gerry Narvaja for doing a lot of the work that resulted in this comparison.

After running both InnoDB Hot Backup and Xtrabackup, we have found that there is a measurable but not large difference between the resources that Xtrabackup and InnoDB Hot Backup consume.

Xtrabackup:

• Free
  
• takes 1.1% longer (2 min during a 3 hour backup)
  
• uses 1.4% more space (1G more in a 70G backup — this was for uncompressed backups)
  
• uses 1.115% more cpu overall
  
• split as 0.12% user, 0.66% nice, 0.025% system, …

Open source master data management got a boost on Monday when Talend announced that they acquired Xtentis MDM from Amalto. This product was geared towards creation of repository-style MDM applications, for example a product master data repository or a customer key cross-reference hub.

Xtentis was a Java and XML-based product with an Eclipse UI, so it's a reasonably good technical fit with Talend's tools. While the product information links have been removed from their web site, you can still access the Xtentis product data sheet if you're interested in the functionality and …

Some of you may have run the mysql-test-run tool which is the MySQL test suite. But did you know there are actually multiple suites? If you just run the tool, you don’t get everything!

Check out the mysql-test/suites subdirectory. That’s all the stuff you don’t get when just running the tool normally. If you take a peek at the Makefiles, you will find a target test-bt (build team) which shows the extra calls and parameters for the additional suites.

OurDelta has had some interesting cases where a build that’s otherwise ok would fail when users tried the test suite on their installation. We reckon such a test should definitely pass, and thus we had some more homework to do. So now OurDelta builds with as many tests as exist enabled, on all platforms and architectures. Slow yes, but that’s not an argument to not test something, right? Failing tests are often indicative of other issues, so at the very least …