What just happened to the database?

It's always fun when you come into work and notice that one of your database monitors/graphs has changed from showing almost no row accesses/sec to 40,000 rows/sec in a matter of minutes. And then by lunch time they are showing 90,000 rows/sec. What's up with that?

MySQL Enterprise Monitor Row Accesses:






Well, of course the first thing you do is check all your cacti monitors, because there is obviously something wrong with the monitoring system.

Cacti Innodb Row Accesses:






Hmm... it's not the monitor. Must be a change in the app...

So you check with everyone, and no, nothings changed in the app. In fact, nothing even appears to be different (same basic app response times, same # requests/sec to the app, etc, etc)

Database Activity:






So it must be a query cache issue...

Query Cache:






Apparently not... :(

So what could cause that kind of dramatic increase in index rows read / sec ?

In reality, looking at the data distribution, the optimizer apparently decided to 'explain' it all away....

Current explain plan:

mysql> \e
-> ;
+--------------+-------------------------------+---------+------+------+----------+
possible_keys
| key | key_len | ref | rows | filtered
| Extra
+--------------+-------------------------------+---------+------+------+----------+
UIDX_TOTAL_RATING_OWNER_ABOUT,IDX_TOTAL_RATING_ABOUT,IDX_TOTAL_RATING_OWNER
| UIDX_TOTAL_RATING_OWNER_ABOUT | 180 | NULL | 5 | 80.00
| Using where; Using index; Using filesort |
+--------------+-------------------------------+---------+------+------+----------+
1 row in set, 1 warning (0.00 sec)


Previous explain plan:

mysql> \e
-> ;
+--------------+-------------------------------+---------+------+------+----------+
possible_keys
| key | key_len | ref | rows | filtered
| Extra
+--------------+-------------------------------+---------+------+------+----------+
UIDX_TOTAL_RATING_OWNER_ABOUT,IDX_TOTAL_RATING_ABOUT,IDX_TOTAL_RATING_OWNER
|IDX_TOTAL_RATING_OWNER | 42 | const,const | 1 | 100.00
| Using where |
+--------------+-------------------------------+---------+------+------+----------+
1 row in set, 1 warning (0.03 sec)

Interesting what a little change in data and a different path through the optimizer can do...

MySQL Partitioned Tables with Trigger enforced FK constraints

Well, I suppose its' true you can't use the official MySQL ALTER TABLE statement to do it, but if you're willing to trust your trigger coding abilities you can.

All you need is an extra table and a couple triggers.

The concept is fairly straight forward:

1. Create a before insert trigger on the child table that validates the parent exists in the parent table.
2. If there is no parent found, then insert a row into a third table that automatically forces an error (say, dup key on index)
   
3. Create a before delete trigger on the parent table that validate there are no children in the child table.
4. If there are children found, then insert a row into the third table that automatically forces an error (dup key on index)

It could also be extended to handle cascading deletes, but I'm personally not a fan of that, so I leave that to you... :)

Here's an example:
- Note: This example uses Innodb tables, but it can be done with MyISAM tables just as well.

---

Parent Table (orders) :

CREATE TABLE IF NOT EXISTS orders
(
id             INT         NOT NULL AUTO_INCREMENT,
customer_name  VARCHAR(30) NOT NULL,
total_amount   DECIMAL,
order_date     DATETIME ,
PRIMARY KEY (id,customer_name)
)
ENGINE=innodb
PARTITION BY RANGE (id)
(
PARTITION p1    VALUES LESS THAN (10),
PARTITION p2    VALUES LESS THAN (20),
PARTITION p3    VALUES LESS THAN (30),
PARTITION p4    VALUES LESS THAN (40),
PARTITION p_max VALUES LESS THAN MAXVALUE
);

---

Child Table (order_items) :

CREATE TABLE IF NOT EXISTS  order_items
(
id           INT         NOT NULL AUTO_INCREMENT,
order_id     INT         NOT NULL ,
item_name    VARCHAR(30) NOT NULL,
item_desc    VARCHAR(4000),
item_amount  DECIMAL,
PRIMARY KEY (id,order_id)
)
ENGINE=innodb
PARTITION BY HASH (order_id)
PARTITIONS 20
;

---

Special Table (fk_trigger) used to enforce FK:

CREATE TABLE IF NOT EXISTS fk_trigger
(
id    INT NOT NULL PRIMARY KEY DEFAULT 0
)
ENGINE=innodb
;

---

Insert default row into special table (fk_trigger)
that will force all inserts to fail:

INSERT INTO fk_trigger VALUES();

---

Child Trigger (order_item_fk_trig) used to check
that parent row exists before insert:

DELIMITER /

CREATE TRIGGER order_item_fk_trig
BEFORE INSERT ON  order_items
FOR EACH ROW BEGIN
  DECLARE my_count INT;
  SELECT   count(*)
  INTO     @my_count
  FROM     orders
  WHERE    id = NEW.order_id;

  IF (@my_count = 0) THEN
      INSERT INTO fk_trigger VALUES ();
  END IF;
END;
/

DELIMITER ;


Parent Trigger (order_fk_trig) used to check
that child row exists before delete:


DELIMITER /

CREATE TRIGGER order_fk_rev_trig
BEFORE DELETE ON  orders
FOR EACH ROW BEGIN
  DECLARE my_count INT;
  SELECT  count(*)
  INTO    @my_count
  FROM    order_items
  WHERE   order_id = OLD.id;

  IF (@my_count > 0) THEN
     INSERT INTO fk_trigger VALUES ();
  END IF;
END;
/

DELIMITER ;


Insert some data into the parent table

INSERT INTO orders
(customer_name,total_amount,order_date)
VALUES
('Customer 1',300.00,now()),
('Customer 2',600.00,now());


Insert some data into the child table

INSERT INTO order_items
(order_id,item_name,item_desc, item_amount)
VALUES
(1,'This 1','Stuff 1',100),
(1,'This 2','Stuff 2',100),
(1,'This 3','Stuff 3',100),
(2,'This 1','Stuff 1',200),
(2,'This 2','Stuff 2',200),
(2,'This 3','Stuff 3',200);


Try to delete order #1 (without deleting it's children)

DELETE FROM orders
WHERE  id = 1;


Results:

ERROR 1062 (23000): Duplicate entry '0' for key 'PRIMARY'


Try to insert orderitems for order #3 (Which doesn't exist)

INSERT INTO order_items
(order_id,item_name,item_desc, item_amount)
VALUES
(3,'This 3','Stuff 1',100);

Results:

ERROR 1062 (23000): Duplicate entry '0' for key 'PRIMARY'

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: Innodb internals

Went to the MySQL Performance Tuning Class last week. I would highly recommend it to any DBA new to MySQL, or any MySQL DBA looking to boost their tuning skills and understanding of some of the various features as they relate to performance.

The next few posts will have to do with information I gleaned from that class...

It's always good to understand what's happening underneath the covers when it comes to database performance and evaluating database architectures/designs for new products or features. One of the great things about the innodb engine is it's Oracleish approach to transaction management. The key is Oracle'ish'. As an Oracle DBA looking at MySQL, it's easy to assume that things like undo tablespaces and rollbacks work the same in innodb as they to in Oracle.

Well, you know what happens when we 'Assume'....

One of the key differences is that innodb does not have an undo tablespace or rollback segment per say. Innodb uses the concept of versioned rows, not to unlike SQLServer 2005's concept of versioned rows. SQLServer implements it by storing versions of the row being changed in the tempdb database (though only if the option is turned on). Innodb implements it with storing versions of the row being changed withing the tablespace itself.

Hmm... The obvious implication is fragmentation. As a row gets updated by multiple threads, or a long running transaction, innodb must keep copies of the row with different version numbers (rowid1, version1/rowid 1, version2, etc, etc) within the table so that a select statement has access to that row while the row is potentially changed by some other transaction. This allows for selects not to block updates, but when a transaction is committed, then the versioned rows are marked for delete. This can then cause a high level of fragmentation within the table and/or tablespace.

So, we as DBA's need to consider things like this when we give advice as to what engine should be used for a given application or database table with MySQL as a back end, as well as things like where they should reside on disk, and whether it's ok to use all tables in one tablespace (innodb default), or file_per_table option for innodb to keep fragmentation issues local to the associated tables/indexes. It may also affect the kinds of maintenance and how often it's needed with respect to rebuilds/defrags....

MySQL innodb plugin

Recently I've spent hours compiling MySQL 5.1.22 and trying to get my cent0s configured for innodb and partitioning. I tried every imaginable way to run configure, use different plugins, etc.

I messed with this forever, then finally looked at the config options, and sure enough, the ./configure expects innobase, not innodb as the plugin name:

from: http://www.innodb.com/support/tips

On MySQL 5.1, ./configure expects a different parameter:

$ ./configure –with-plugins=innobase