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

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....

SQLServer 2005: Full Text Issue

In my opinion, there is security, and then there is the absurd paranoia. Microsoft tends to ride this line a little too much lately. Trying to be the end all to security, they forget that we have firewalls, access lists, etc, etc that should be considered when locking things down.

The interesting issue I've run into with Full Text searches now, is , somewhat not surprisingling, a known Microsoft Bug / Feature

The question I'd like to pose Microsoft is this:

If the work break files I've installed came from the CD / Microsoft bits that I got from Microsoft, should I be able to assume that they've verified where they've came from? And for that matter, If I install a third - party work break file after my SQLServer Full Text install, shouldn't it be my responsibility to verify the validity of those files?

Why should Microsoft automatically be checking these for me? (and why, should they do it every time I load the word breaker info into memory?

BTW - if you are having the problem, it presents itself something like this:

Running SQLServer 2005 (or possibly other servers), with Full Text Indexing, you notice that the initial call to the query engine is slow (surprisingly, 45 seconds consistently). Subsequent queries perform as expected. Then after 10 or 15 minutes, the query processing engine appears to 'go to sleep' and you have another 45 second response time for a query to indexing service.

SQLServer: Transactional Replication Issues

After seeing a few friends at the SQLPass 2007 Summit in Colorado and discussing some of the finer 'features' of pier to pier transactional replication and large database systems (my friends work on the replication team at Microsoft), I quickly ran into one of my big grips with P2P replication and SQLServer 2005.

I had noted that being able to set up P2P from a 3rd party backup tool is essential with large databases (> 100GB) because creating a backup with SQLServer 2005 just takes way too long. The problem is, however, that SQLServer must know which LSN the backup was on when it was restored to the replicated server, so that any transactions that have occurred on the main database since the backup was taken can be correctly applied in the correct order.

The only way for SQLServer to get that is by reading the header in the backup file, which can't be done via a third party backup tool (SQLLightspeed, SQLBackup, etc, etc). My friends implied that if I knew the LSN, it could be done. I'm not quite there yet.

However, it wouldn't be that hard for Microsoft to allow a third party backup tool to call a procedure letting replication know what the current LSN is, thus fixing the problem. Hmm...

Maybe one day. For now, we'll have to deal with 5 hour backups and 2 hours restores in order to setup P2P transactional replication from backup, at least until SQLServer 2008 come around and do the backups for us with it's supposedly blazing fast compression, etc.

I'm not holding my breath....

SQLServer: Tracking Down Stat Issues

The old debate of auto update statistics. I've had issues with it in the past, and coming from an old UNIX / Oracle background, I've always been of the opinion that I know best ( probably not, but it's fun to believe that).

The advantage of use sp_updatestats over update statistics is that is looks at sys.sysindexes for the status of indexes and whether or not they need to be updated or not, before the tables/indexes are scanned for updating stats.

Given this, we can then get a look at distribution changes in order to determine how often we should be updating stats on different tables indexes.

The following script gives a look at how many changes have been done since statistics on an object were last updated:

SELECT a.rowcnt AS total_rows,
a.rowmodctr AS rows_updated,
object_name (a.id) AS table_name,
a.name AS stats_object
FROM sys.sysindexes a
WHERE object_name (a.id) IN ( [table name],[table name],...)
ORDER BY 2 DESC,1 DESC

Then just keep track of how fast the rows_updated grows, and use that to get an idea of how often stats should be updated.

SQLServer: The problem with SQLServer is the OS it runs on :)

Yea,

No matter what way you slice it, the really problem I have with SQLServer, is the operating system it runs on. Seems time after time my SQLServer issues are more related to the OS than anything else.

The latest issue surrounds dual core processors and parallelism.

By default, SQLServer's optimizer seems to look at the number of processors on a systems, multiply it by 2, and use that for the number of parallel threads when running dml in parallel. This was all find and dandy till the OS reported 16 processors on a 8 proc / dual core machine.

Yea, cause then SQLServer says, ohh - I can do this in parallel.... let's see... 16 x 2 = 32 threads! Yea baby!

Uh... great, now my query takes 2x as long because I'm waiting in a line at the door to get in.

Figures.

Take this into account when doing bulk loads, large queries, things that the optimizer might want to parallelize. Either set the max degree of parallelizm at the server level, or use MAXDOP at the query level.

SQLServer 2005: Capturing Processes Waiting / Blocked

OK - it's out of order (this is supposed to be a followup on partitioning ), but I'm always writing some code and then later losing it and re-creating it, so I decided to put this up today...

Everyonce in a while we've had short 'blocking / waiting' where things back up in the system. It's kindof a pain in the you know what when it disappears after a few minutes, so I wrote some code to grab all the SQL that's waiting, and stick it in a temp table for later use. Obviously, a working table in a separate database might be more practical, and maybe a few more options on the stored procedures, but for now, here goes:

1. Create a procedure to check for waiting SQL:

CREATE PROCEDURE blocked_queries]
@thresh DECIMAL (5,2) = 1.0,
@dbname VARCHAR (128) = NULL
AS
DECLARE
@dbstart INT,
@dbend INT
BEGIN
IF @dbname IS NOT NULL
BEGIN
SET @dbstart = DB_ID (@dbname)
SET @dbend = @dbstart + 1
END
ELSE
BEGIN
SET @dbstart = 4
SET @dbend = 999
END

SELECT DB_NAME (b.database_id) AS [database],
x.host_name AS blocked_host,
a.session_id AS sid,
a.wait_type,
ROUND (CAST (a.wait_duration_ms / 1000.0 AS DECIMAL (5,2)),2,1) as
wait_time_sec,
(
SELECT
SUBSTRING
(text, b.statement_start_offset/2 + 1,
(CASE
WHEN b.statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(max), text)) * 2
ELSE b.statement_end_offset
END - b.statement_start_offset
) / 2
)
FROM sys.dm_exec_sql_text(b.sql_handle)
) AS blocked_query ,
a.blocking_session_id AS blocking_sid,
(
SELECT
SUBSTRING
(text, c.statement_start_offset/2 + 1,
(CASE
WHEN c.statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(max), text)) * 2
ELSE c.statement_end_offset
END - c.statement_start_offset
) / 2
)
FROM sys.dm_exec_sql_text(c.sql_handle)
) AS blocking_query
FROM sys.dm_os_waiting_tasks a
join sys.dm_exec_sessions x ON a.session_id = x.session_id
join sys.dm_exec_requests b ON a.session_id = b.session_id
left outer join sys.dm_exec_requests c ON a.blocking_session_id =
c.session_id
WHERE b.database_id >= @dbstart
AND b.database_id < @dbend
AND a.wait_duration_ms / 1000.00 > @thresh
END

2. Now Create the Temporary Table:

create table #blocked_queries
(
database_name varchar(20),
blocked_host varchar(50),
sid int,
wait_type varchar(20),
wait_time_sec decimal (5,2),
blocked_query varchar(4000),
blocking_sid int,
blocking_query varchar(4000),
lock_time datetime default getdate()
)

3. Last Use the Stored Procedure to Popoulate the Temporary Table:

declare
@blocked_count int,
@done int
begin
select @blocked_count = count(*) from #blocked_queries
set @done = 10000

while @blocked_count <= @done begin insert into #blocked_queries ( database_name, blocked_host, sid , wait_type, wait_time_sec, blocked_query, blocking_sid, blocking_query ) exec tp_blocked_queries 1.0,

select @blocked_count = count(*) from #blocked_queries
end
end

Now all you have to do is hope you catch the culprit in action, and that it won't be too difficult to fix, right? :)

TUNING: Taking Advantage of New Features

Wouldn't it be great if we always were able to design things from scratch? So many things could be re-hashed and re-designed. One of the real reason's we're (DBA's that is) frustrated all the time with poorly scalable products, is that we don't pro-actively encourage evolving architectures in a meaningful, planned way.

It's always those developers throwing things over the wall, or the feature requests from product managers - never giving the time to design it right the first time. There may be some thread of truth to those statements, but if we take advantage of those new features, bug fixes, maint windows, we could do a lot for ourselves in terms of performance tuning.

Here's a couple examples to get you started thinking:

A new feature:

1. Does it require new tables?
• Have we thought about partitioning should the data in those tables scale up?
• Have we thought about primary keys and which columns should or should not be included given usage and future modifications?
• Have we thought about foreign keys and associated indexes?
• Have we thought about normalization?
  
2. Is the new feature heavy read or heavy write?
• Should we be considering additional data files and/or data stores?
• Are there certain types of indexes that might be more prudent in the long term?
• How does this fit in with our HA / disaster recovery scenarios?
  
3. Does the new feature have any long term reporting benefits?
• Are changes easily monitored?
• Is the data easily transformed to dimensions / facts?
• Should there be some archival process?

Those are just a few questions that might be thought about and pondered when new features or bug fixes come across in our applications from a database perspective. Many times, we rely on developers who are under tight schedules and deadlines to think these things through, when they are really focusing on usability and functionality.

If we want to make our lives easier, we need to be the ones asking those questions, and in such a way that helps the development team see the benefit in making those decisions now, and not a year from now when we've added a new customer with 500,000 new transactions a day...

As DBA's we need to strive to get on the front end of the development cycles to make it as easy as possible for our development teams to take advantage of features and designs that will allow scalability and manageability with our databases - whatever those databases might be.

If you're not doing that, then in my opinion, you might as well outsource your database administration to some company that just gonna monitor alerts and backup your data....

SQLServer: Joining Views in Queries

Two Words: Bad Idea!

The problem with this is that the SQLServer optimizer has to determine the best cost for the set of joins, and then base that on a view that has additional joins underneath it.

It's almost always more efficient to rewrite the query to join the actual tables, and the optimizer is much faster at finding the Cost. We were also able to determine that when evaluating joins to view, the optimizer doesn't handle hints within the view. So if your view is supposed to use a nolock hint (SQLServer 2000), but you join it to a couple tables outside the view, you end up getting shared locks anyway.

Bummin.

Bad Idea!

SQLServer: To Lock or NOLOCK

I don't think anyone would argue that one of the biggest problems on SQLServer (besides that it runs on Windows) is how terrible it's locking and lock escalation process is.

Then when things seem to go slow when on inserts, the first thing the developers do is modify all selects to use the NOLOCK hint. Yea, so in theory that makes sense, but not only does it imply that data results may be inconsistent, but it also ignores the real issue. We need to focus our tuning efforts on how we lock and if we are forcing lock escalation (from Row to Extent to Page to Table to ...)

The other not so known issue is that SQLServer has a bug that can actually cause SQLServer to crash (it happended to us) when a select with NOLOCK is used and a delete happens at the same time...

-----------------------------------------------------------------------

FIX: A query may fail with retail assertion when you use the NOLOCK hint or the READ UNCOMMITTED isolation level

Article ID:827714
Last Review:August 13, 2004
Revision:3.0
On this Page

SYMPTOMS

CAUSE

RESOLUTION

WORKAROUND

STATUS

MORE INFORMATION

SYMPTOMS

When you run a query with the NOLOCK hint or with the transaction isolation level set to READ UNCOMMITTED, and a concurrent user is deleting rows from the table that is accessed by the query, the query may fail with a retail assertion, and you may receive an assertion error message that is similar to the following:
Msg 3624Location: Drive:\sql\ntdbms\storeng\drs\include\record.inl:1447Expression: m_SizeRec > 0 && m_SizeRec <= MAXDATAROWSPID: 63 Process ID: 1776-or- Msg 3624 Location: recbase.cpp:1378 Expression: m_offBeginVar <>

CAUSE

When the Microsoft SQL Server engine tries to search for a row when a query uses the NOLOCK hint or the READ UNCOMMITTED transaction isolation level, the target row may be deleted. Before you install this fix, SQL Server 2000 may incorrectly handle this situation and the query may fail with the assertion that is mentioned in the "Symptoms" section of this article.Note The READ UNCOMMITTED transaction isolation level is also known as a "dirty read."

RESOLUTION

A supported hotfix is now available from Microsoft, but it is only intended to correct the problem that is described in this article. Only apply it to systems that are experiencing this specific problem. This hotfix may receive additional testing. Therefore, if you are not severely affected by this problem, Microsoft recommends that you wait for the next Microsoft SQL Server 2000 service pack that contains this hotfix.To resolve this problem immediately, contact Microsoft Product Support Services to obtain the hotfix.

SQLServer: Statistics - Auto vs Manual update ?

Have you ever seen 'select statman(....)' running while your SQLServer is under heavy transactional load? If not, you either don't have auto update stats turned on, or you database is not really under heavy load.

By default, auto update is turned on, and SQLServer keeps track of how often indexes / tables are changed. Once 20% or 500 rows are changed, SQLServer starts updating statistics to improve performance on the chance that the distribution of data might change requiring subsequent changes to execution plans.

This is fine and dandy, except that in many cases, performance can be degridated when this happens. Question is, should we be having this set or not? And if we do have it set, should we be updating statistics on a regular basis?

We have found (beyond a reasonable doubt - even at a certain company's internal SQLServer databases) that statistics for SQLServer seem to get corrupt from the optimizers point of view over time. One theory is that auto stats may contribute to that. It's gotten so bad on some of our databases that we have to update stats every hour in order to be sure we 'fix' the situation as soon as possible after it happens. We plan on testing the difference with auto statistics on vs off on our databases and see what kind of performance differences we get and if there is any noticeable drop in corrupted statistics...

We'll let you know what we find...

SQLServer: Partitions

Initial testing with SQLServer 2000 partitions appears to work simillarly with Oracle from a select perspective.

Explain plans show accessing only local partition indexes, and not all partitions (or tables) in the union views. Inserts / updates appear to first check each partition, then perform the transaction only on the necessary partition.

Some of the issues we've specifically found with partitions are all the nitpicky rules associated with them.

• Underlying tables must have a primary key and the partition constraint must be part of it.
• Doesn't seem to work with between all the time with the optimizer.
• Must be running Enterprise Edition of SQLServer

Hmm.... Now that's about the same cost as Oracle ;)

And so on, and so on, and so on...

It'll be nice when SQLServer catches up with Oracle from a partitioning perspective, but at least it's a start.