MySQL: Getting Creative with Partitioning (Performance Results)

I decided to run some very basic performance test comparing the non-partitioned table with a primary key, and a partitioned table with a primary key and a unique constraint enforced via a secondary table explained in my previous post.

Overall, it appears that with partitioning, as the data/rows scale, the inserts actually get faster :) This is what I would expect theoretically, so score one for partitioning.

That said, I'm not a benchmark tester, I'm a DBA, so I'm sure plenty of folks could explain why my testing isn't exactly accurate. For the purpose of the tests, I turned off query cache on the servers (though, since the tests were run with primarily inserts, it may not have made much difference). Tables were created with engine=innodb as default.

I do believe, it's enough to suggest going forward with this and doing scale testing on an application with this if you require unique keys that are part of the primary key due to partitioning restrictions in MySQL.

The tables are created for the test:

Sequence Tables:

CREATE TABLE my_test_sequence
(
id integer NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id)
) AUTO_INCREMENT = 1001;

CREATE TABLE my_test_part_sequence
(
id integer NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id)
) AUTO_INCREMENT = 1001;

Non Partitioned Table with PK and Unique Constraint:

CREATE TABLE test_base
(
id INTEGER NOT NULL AUTO_INCREMENT,
other_id INTEGER NOT NULL ,
name varchar(64) ,
PRIMARY KEY (id),
UNIQUE KEY (other_id)
);

Partitioned Table with PK without Unique Constraint:

CREATE TABLE test_part
(
id INTEGER NOT NULL AUTO_INCREMENT,
other_id INTEGER NOT NULL ,
name varchar(64),
PRIMARY KEY (id,other_id)
)
PARTITION BY KEY (id)
PARTITIONS 50;

Partitioned Enforcement Table with for Unique Constraint (Partitions Suggested by Mikael Ronstrom):

CREATE TABLE test_unique_part
(
other_id INTEGER NOT NULL,
PRIMARY KEY (other_id)
)
PARTITION BY KEY (other_id)
PARTITIONS 50;

Trigger for checking unique constraint:

DELIMITER /

CREATE TRIGGER enforce_unique
AFTER INSERT ON test_part
FOR EACH ROW BEGIN
INSERT INTO test_unique_part SET other_id = NEW.other_id;
END;
/

DELIMITER ;

test_base.sql insert scripts:

INSERT INTO my_test_sequence VALUES ('');
INSERT INTO test_base (other_id,name) VALUES (last_insert_id(), rand());

First 50,000 Rows (0 to start):


Non-Partitioned with just PK:

$ time mysqlslap -u testing --create-schema=test --query=test_base.sql -c 10 -i 5000 -F ";"
Benchmark
Average number of seconds to run all queries: 0.100 seconds
Minimum number of seconds to run all queries: 0.028 seconds
Maximum number of seconds to run all queries: 0.211 seconds
Number of clients running queries: 10
Average number of queries per client: 2

real 8m26.624s
user 0m1.154s
sys 0m3.473s

Partitioned with alternate table for unique Index:

$ time mysqlslap -u testing --create-schema=test --query=test_part.sql -c 10 -i 5000 -F ";"

Benchmark
Average number of seconds to run all queries: 0.051 seconds
Minimum number of seconds to run all queries: 0.020 seconds
Maximum number of seconds to run all queries: 0.376 seconds
Number of clients running queries: 10
Average number of queries per client: 2

real 4m23.382s
user 0m0.892s
sys 0m2.369s

Second 100,000 Rows (50,000 to start):

Non-Partitioned with just PK:

$ time mysqlslap -u root --create-schema=test --query=test_base.sql -c 10 -i 10000 -F ";"
Benchmark
Average number of seconds to run all queries: 0.051 seconds
Minimum number of seconds to run all queries: 0.020 seconds
Maximum number of seconds to run all queries: 0.376 seconds
Number of clients running queries: 10
Average number of queries per client: 2

real 4m23.382s
user 0m0.892s
sys 0m2.369s

Partitioned with alternate table for unique Index:

$ time mysqlslap -u root --create-schema=test --query=test_part.sql -c 10 -i 10000 -F ";"
Benchmark
Average number of seconds to run all queries: 0.051 seconds
Minimum number of seconds to run all queries: 0.020 seconds
Maximum number of seconds to run all queries: 0.376 seconds
Number of clients running queries: 10
Average number of queries per client: 2

real 4m23.382s
user 0m0.892s
sys 0m2.369s

Third 500,000 Rows (150,000 to start):

Non-Partitioned with just PK:

$ time mysqlslap -u root --create-schema=test --query=test_base.sql -c 10 -i 50000 -F ";"
Benchmark
Average number of seconds to run all queries: 0.081 seconds
Minimum number of seconds to run all queries: 0.018 seconds
Maximum number of seconds to run all queries: 2.069 seconds
Number of clients running queries: 10
Average number of queries per client: 2


real 68m20.086s
user 0m10.791s
sys 0m31.492s

Partitioned with alternate table for unique Index:

$ time mysqlslap -u root --create-schema=test --query=test_part.sql -c 10 -i 50000 -F ";"
Benchmark
Average number of seconds to run all queries: 0.057 seconds
Minimum number of seconds to run all queries: 0.020 seconds
Maximum number of seconds to run all queries: 0.746 seconds
Number of clients running queries: 10
Average number of queries per client: 2


real 48m50.993s
user 0m8.341s
sys 0m24.062s

MySQL: Getting Creative with Partitioning

Lately, I've been trying to keep up with at least one of the MySQL Forums: Partitioning.

It's a great way to keep on top of issues surrounding partitioning, and also get an idea of what people are trying to do with the new 5.1 feature. Richard came up with an interesting problem that I jumped into only to realize that I hadn't done my homework, and my initial suggestion wouldn't work at all due to documented restrictions on partitioning in MySQL 5.1. :(

So, the question is, if I can't have a unique index on a partitioned table that does not contain all columns of the partition key, is there some other way to enforce that uniqueness?

We could use a trigger on the partitioned table that try's to insert / update into another non-partitioned table that contains that unique constraint:

   CREATE TABLE part_table
  (
     id INTEGER NOT NULL,
     other INTEGER NOT NULL,
     PRIMARY KEY (id,other)
  )  
   PARTITION BY KEY (id)
  PARTITIONS 20;

So the restriction can be seen by trying to create a unique index now on column 'other' :

 mysql> CREATE UNIQUE INDEX  test ON part_table  (other);
 ERROR 1503 (HY000): 
   A UNIQUE INDEX must include all columns in the table's partitioning function
mysql>

Create a new table for constraining the index that is not partitioned:

   CREATE TABLE unique_index_table
  (
     id INTEGER NOT NULL,
     PRIMARY KEY (id)
  )  ;

Create a trigger on the partitioned table for enforcing the uniqueness:

 DELIMITER /

 CREATE TRIGGER enforce_unique
    BEFORE INSERT ON  part_table
    FOR EACH ROW BEGIN 
          INSERT INTO unique_index_table  SET id = NEW.id;
   END;
 /

  DELIMITER ;   

Now, enforcement of the unique index can be done on the partitioned table:

 mysql>INSERT INTO part_table VALUES (1,1);
 Query OK, 1 row affected (0.00 sec)
 mysql> INSERT INTO part_table VALUES (1,2);
  ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
 mysql> INSERT INTO part_table  VALUES (1,1);
  ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
 mysql> INSERT INTO part_table  VALUES (2,1);
 Query OK, 1 row affected (0.00 sec)
 mysql> INSERT INTO part_table  VALUES (3,3);
 Query OK, 1 row affected (0.00 sec)
 mysql> INSERT INTO part_table  VALUES (3,2);
  ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY'
 mysql>

So, now it really becomes a question of performance. Functionally it works, but it it
really the best workaround?

I'll try some tests to compare the performance of this table partitioned with a trigger
enforced unique index against a non-partitioned table with a regular unique index
and see how the results are.

I'll post them as soon as I have the results. If anyone has a better way, I'm interested in knowing....

MySQL: Finally an ability to trace/profile

Finally! The ability to look a little closer into what's happening with SHOW PROFILE

Here’s how it works:

mysql> set profiling=1;
mysql> select count(*) from mysql.user;
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)

mysql> show profile;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000020 |
| checking query cache for query | 0.000250 |
| Opening tables                 | 0.000015 |
| System lock                    | 0.000010 |
| Table lock                     | 0.000009 |
| init                           | 0.000022 |
| optimizing                     | 0.000007 |
| executing                      | 0.000018 |
| end                            | 0.000004 |
| query end                      | 0.000002 |
| freeing items                  | 0.000019 |
| logging slow query             | 0.000002 |
| cleaning up                    | 0.000002 |
+--------------------------------+----------+
13 rows in set (0.01 sec)

In this case, we also get info on inserts (where you don’t with explain)

mysql> create table test (id integer not null auto_increment, my_name varchar(20),primary key (id));
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test (my_name) values ('Phil');
Query OK, 1 row affected (0.01 sec)

mysql> show profile;

+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000069 |
| checking permissions | 0.000008 |
| Opening tables       | 0.000134 |
| System lock          | 0.000004 |
| Table lock           | 0.000006 |
| init                 | 0.000017 |
| update               | 0.000093 |
| end                  | 0.000003 |
| query end            | 0.000003 |
| freeing items        | 0.002233 |
| logging slow query   | 0.000003 |
| cleaning up          | 0.000002 |
+----------------------+----------+

12 rows in set (0.00 sec)

Here's the steps I used to build the binaries for MySQL 5.1.24:

1) install bitkeeper client http://www.bitmover.com/bk-client2.0.shar
shell> /bin/sh bk-client2.0.shar
shell> cd bk-client2.0
shell> make
shell> make install

2) grab the current 5.1 source tree
shell> bkf clone bk://mysql.bkbits.net/mysql-5.1 mysql-5.1

3) Prepare the source for building
shell> cd mysql-5.1
shell> ./BUILD/autorun.sh

4) Build the snapshot
shell> CC=gcc CXX=gcc ./configure --prefix=/opt/app/mysql \
 --localstatedir=/opt/app/mysql/data --libexecdir=/opt/app/mysql/bin \
 --with-server-suffix="" --enable-thread-safe-client --enable-local-infile \
 --with-pic --with-fast-mutexes --with-client-ldflags="-static" \
 --with-mysqld-ldflags="-static" --with-zlib-dir=bundled --with-big-tables \
 --with-ssl --with-readline --with-embedded-server --with-archive-storage-engine \
 --with-blackhole-storage-engine --with-ndbcluster --with-csv-storage-engine \
 --with-example-storage-engine --without-falcon --with-federated-storage-engine \
 --with-partition --with-innodb --with-extra-charsets=all --enable-community-features \
 --with-comment="MySQL Enterprise Server (GPL) - Snapshot from `date +'%m-%d-%Y'`"
shell> make
shell> make test

5) Make the final installer package
shell> ./scripts/make_binary_distribution

6) Then just install the binaries with the file mysql-5.1.24-rc-linux-x86_64.tar.gz

Best Practices - Oops...

Yea, yea, yea... best practices.

I spent about a week troubleshooting issues on a MySQL 5.1 replication issue where certain transactions causing duplicate key on index errors stopped replication. Easy enough to fix, right?

 mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
 mysql> start slave;

Hmm... nice until it happens almost every minute...

So, after reading every link I could find on the internet, forum posts, upgrading to 5.1.24, I finally started to think I just didn't know what I was doing with replication.

Last ditch effort... ask dev and application engineers to help out...

"Oh... you mean one of the application servers is pointed at the slave server in read/write mode and is trying to insert data there as well?..."

:)

Yea, that's why it's a best practice to have your slave servers set READ ONLY .... duh...

Anyway, live and learn..