Persistent statistics and partitions

Today when I was studying for the MySQL 5.6 exams.

I was studying for these two items:

• Create and utilize table partitioning

• Obtain MySQL metadata from INFORMATION_SCHEMA tables

The first step is to create a table, partition it with a hash.

mysql> CREATE TABLE pfoo (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255))
    -> PARTITION BY HASH(id) PARTITIONS 4;
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO pfoo(name) VALUES('test01'),('test02'),('test03'),('test04'),
    -> ('test05'),('test06'),('test07'),('test08'),('test09'),('test10'),('test11');
Query OK, 11 rows affected (0.00 sec)
Records: 11  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM pfoo;
+----+--------+
| id | name   |
+----+--------+
|  4 | test04 |
|  8 | test08 |
|  1 | test01 |
|  5 | test05 |
|  9 | test09 |
|  2 | test02 |
|  6 | test06 |
| 10 | test10 |
|  3 | test03 |
|  7 | test07 |
| 11 | test11 |
+----+--------+
11 rows in set (0.00 sec)

mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM information_schema.partitions 
    -> WHERE TABLE_NAME='pfoo';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |          2 |
| p1             |          3 |
| p2             |          3 |
| p3             |          3 |
+----------------+------------+
4 rows in set (0.01 sec)

The sequence in the id column looks random, but it isn't. It's in partition order.

mysql> SELECT id,name,MOD(id,4) FROM pfoo;
+----+--------+-----------+
| id | name   | MOD(id,4) |
+----+--------+-----------+
|  4 | test04 |         0 |
|  8 | test08 |         0 |
|  1 | test01 |         1 |
|  5 | test05 |         1 |
|  9 | test09 |         1 |
|  2 | test02 |         2 |
|  6 | test06 |         2 |
| 10 | test10 |         2 |
|  3 | test03 |         3 |
|  7 | test07 |         3 |
| 11 | test11 |         3 |
+----+--------+-----------+
11 rows in set (0.00 sec)

So nothing new or unexpected here.
So now we're going to change the number of partitions.

mysql> ALTER TABLE pfoo PARTITION BY HASH(id) PARTITIONS 6;
Query OK, 11 rows affected (0.32 sec)
Records: 11  Duplicates: 0  Warnings: 0

mysql> SELECT PARTITION_NAME, TABLE_ROWS FROM information_schema.partitions 
    -> WHERE TABLE_NAME='pfoo';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0             |          0 |
| p1             |          0 |
| p2             |          2 |
| p3             |          0 |
| p4             |          0 |
| p5             |          0 |
+----------------+------------+
6 rows in set (0.01 sec)

mysql> SELECT COUNT(*) FROM pfoo;
+----------+
| COUNT(*) |
+----------+
|       11 |
+----------+
1 row in set (0.00 sec)

So we've changed the number of partitions from 4 to 6. There are still 11 rows, but the information in information_schema.partitions seems to be wrong.
This is because innodb_stats_persistent is enabled.

mysql> SELECT * FROM mysql.innodb_table_stats WHERE table_name LIKE 'pfoo#%';
+---------------+------------+---------------------+--------+----------------------+--------------------------+
| database_name | table_name | last_update         | n_rows | clustered_index_size | sum_of_other_index_sizes |
+---------------+------------+---------------------+--------+----------------------+--------------------------+
| cert56        | pfoo#P#p0  | 2013-10-26 12:12:28 |      0 |                    1 |                        0 |
| cert56        | pfoo#P#p1  | 2013-10-26 12:12:28 |      0 |                    1 |                        0 |
| cert56        | pfoo#P#p2  | 2013-10-26 12:12:28 |      2 |                    1 |                        0 |
| cert56        | pfoo#P#p3  | 2013-10-26 12:12:28 |      0 |                    1 |                        0 |
| cert56        | pfoo#P#p4  | 2013-10-26 12:12:28 |      0 |                    1 |                        0 |
| cert56        | pfoo#P#p5  | 2013-10-26 12:12:28 |      0 |                    1 |                        0 |
+---------------+------------+---------------------+--------+----------------------+--------------------------+
6 rows in set (0.00 sec)

mysql> SELECT * FROM information_schema.global_variables 
    -> WHERE variable_name='innodb_stats_persistent';
+-------------------------+----------------+
| VARIABLE_NAME           | VARIABLE_VALUE |
+-------------------------+----------------+
| INNODB_STATS_PERSISTENT | ON             |
+-------------------------+----------------+
1 row in set (0.01 sec)

The statistics can be updated by running 'ANALYZE TABLE pfoo'. Or by just waiting a few seconds as InnoDB will update the statistics automatically if innodb_stats_auto_recalc is enabled, but this is not instantaneous.

MySQL Certification Study: Write a basic .NET application that uses MySQL

I've registered for the MySQL 5.6 beta exams, just like many more on Planet MySQL have done.

One of the topics for the MySQL 5.6 Developer exam is: "Write a basic .NET application that uses MySQL".

I have used Connector/Net a few times the last few years, but the last time I looked at some code involving Connector/Net was when I took the MySQL 5 Developer exam, which was long ago.

I'm using Linux on both my laptop and on servers for most projects. So I don't use or see many .NET code.

So I need a short refresh.

To use Connector/Net on Linux you will need to install Mono. The base for .NET is CIL, which exists in at least 4 versions: 1.0, 2.0 and 4.0. To compile C# code with mono you need the command which correspondends with the CIL version you are using.

• CIL 1.0: mcs
• CIL 2.0: gmcs
• CIL 4.0: dmcs

For Ubuntu you'll need these packages:

• libmono-system-data4.0-cil
• libmysql-cil-dev
• mono-runtime
• mono-dmcs
• pkg-config

So now we should be able to comple some C# code:
dmcs -r:System.Data.dll $(pkg-config --libs mysql-connector-net) example1.cs


And run it:
mono example1.exe

The Connector/Net Tutorial Intro on mysql.com has some example code.

I wrote some very very basic C# code based on the tutorial:
$ mono example1.exe
Connecting to MySQL...
5.6.12-log
Done.

The code:

using System;
using System.Data;

using MySql.Data;
using MySql.Data.MySqlClient;

public class Example1
{
 public static void Main(string[] args)
 {
  string connStr = "server=localhost;user=msandbox;database=test;port=5612;password=msandbox";
  MySqlConnection conn = new MySqlConnection(connStr);
  try
  {
   Console.WriteLine("Connecting to MySQL...");
   conn.Open();

   string sql = "SELECT VERSION()";
   MySqlCommand cmd = new MySqlCommand(sql, conn);
   MySqlDataReader rdr = cmd.ExecuteReader();

   while (rdr.Read())
   {
    Console.WriteLine(rdr[0]);
   }
  }
  catch (Exception ex)
  {
   Console.WriteLine(ex.ToString());
  }
  conn.Close();
  Console.WriteLine("Done.");
 }
}

OSS-DB Database certification

What will be first? The new and updated MySQL certification or the new OSS-DB exam which is announced by LPI in Japan?

The OSS-DB is only for PostgreSQL for now, but will cover more opensource databases in the future.

There seem to be two levels:

• Silver: Management consulting engineers who can improve large-scale database


• Gold: Engineers who can design, development, implementation and operation of the database

The google translate version can be found here. I found this info on Tatsuo Ishii's blog

MySQL Certification

According to the Oracle website the following certifications are available for MySQL:

• Oracle Certified Associate, MySQL 5.0/5.1/5.5
• Oracle Certified Professional, MySQL 5.0 Developer
• Oracle Certified Professional, MySQL 5.0 Database Administrator
• Oracle Certified Expert, MySQL 5.1 Cluster Database Administrator

There were two new versions released since MySQL 5.0. MySQL 5.0 was released in 2005, that's more than 5 years ago. And "MySQL 5.1 Cluster Database"? According to wikipedia the NDB release in the 5.1 source tree is old and not maintained. And Cluster 7.1 has many new features like multithreaded data nodes and disk based data.

So Oracle, please update the certifications.