I've been tasked with assessing the benefits of changing our DB
platform in some areas. We currently use SQL 2000 and 2005 and I'm
pushing for SQL 2008. The actual requirement is to handle high
concurrency transient data for our website. We currently serve about
150 concurrent users for this part I'm looking at. We will have a
persisted data store (2008 - possibly using service broker) and the
transient (short term life span) data will be held on multiple servers
for it's duration. It's a bit like sharding, but the transient data
does not need to go everywhere (static data will). We will use some
mechanism to be aware of data location. We can scale up and out easily
with this approach. The disagreement I currently have is over MySQL or
MS SQL (I want MS SQL Web Edition for this part and Enterprise for
persisted data).

Everything we are looking at will be 64 Bit and a ground up install.
Forget costs and roadmap for now, what I'm interested in is
performance. Also forget the difficulties of working on two sets of
tools etc...

Some of our architects wanted to bring in MySQL Cluster as it as some
in-memory capabilities that they felt would be faster than what we
have (which suffers from poor design - we are totally re-writing
things). We benchmarked the same setup against SQL2008. MS SQL was 10
times faster. The idea then moved on to MySQL MEMORY engine (think of
this like global temporary tables) and INNODB (closest to MS SQL). To
be honest I suspect there is minimal understanding of the proc and
buffer cache etc...

Under all of the tests, we have managed to prove MS SQL 2008 has
outperformed all of the current MySQL engines (even with increased
spec for MySQL in some areas). We have tested with Windows and RedHat
for MySQL. It still has not beaten MS SQL 2008. Any in-memory
capabilities are pretty much negated due to the proc and buffer cache
and I can clearly see this is as you would expect.

I have also suggested (thanks to some help from this forum) about
using SSD's for certain areas to help any disk I/O.

So, onto my question.....

Is there anywhere I can get an unbiased set of comprehensive tests
between MySQL and MS SQL 2008 ?

I need this as I have challenged the decisions of the chief architect
and have got this referred to the IT Director (we are a FTSE 250
company so it's decent size scale). Not the most popular move I admit.
I do have the backing of our data architect though. I need some
evidence to prove or disprove some benchmark results either way. If
these back up what I've found it will help a lot (and yes I will
provide them if they counter my argument as I want the best solution
either way but need to justify changing things if we do)

Thanks


Ryan

I should add we brought in two SUN consultants to do their setup and
give that a fair crack of the whip.

"Ryan" <ryanofford (AT) hotmail (DOT) com> wrote

Probably not.

Honestly, it sounds like YOU have done your homework. Sounds like the CA
has to prove HIS side of the case.

If after all this testing you're consistently seeing SQL Server 2008 beat
out MySQL, I can't really see the continued insistance on MySQL.

Seems like someone is putting a personal agenda in front of actual facts.


--
Greg Moore
Ask me about lily, an RPI based CMC.

We got the results back from testing the MySQL MEMORY engine last
night. It died on it's arse due to table locking. This MySQL engine
doesn't do row locking, so it's table locking all the way. We managed
to find out there were 5 million table locks queued up (350 concurrent
users and about 20 minutes in) and it behaved as expected. It was also
using 4 CPU and 16GB RAM as opposed to 1 CPU and 4GB RAM (for SQL
2008). OK, the locking would always be the issue, but at least we've
allowed the 'lets throw hardware at it argument to go away'.

Now to test INNODB.

I'll likely write up and publish some details at some point on this as
I can't find anything conclusive anywhere else. I'll add some more
info on this in case it's of use to someone else.

Ryan

On Jun 11, 12:12*am, Ryan <ryanoff... (AT) hotmail (DOT) com> wrote:
Good for you for making people actually prove their assertions. You
already identified what is probably the biggest cause of the current
poor performance (" . . . (which suffers from poor design - we are
totally re-writing
things) . . .").

A well designed schema with properly written queries can perform *many
times* (sometimes even hundreds of thousands of times) faster than a
bad design. It's fortunate that you can re-write the system, many
times for one reason or another, people aren't allowed to do that. I
suspect that any hardware based speed increases for the current system
will pale in comparison to the increases you'll get when the properly
designed system comes online.

Design considerations aside, I'm very pleased to see empirical test
results that confirm what had suspected about the cache and locking.

Question: Did your tests make use of SQL Server 2008's data
compression? I am not sure what the compression does on the write
side of things, but on the read side, it can result in monster speed
increases. We have seen radical performance increases when querying
large tables that are compressed vs the uncompressed version. Our
applications are mostly reporting, so I can't tell you what
compression does on the writing side. On one hand, compression seems
like it might slow down writes, but if the algorithm is efficient (it
appears to be so), and CPU is cheap, the compression may even cut down
I/O on the write side enough to outweigh the overhead of doing the
compression.

Interestingly, the worse the design, the more compression can help on
the read side. Bad designs generally have tons of redundancy from all
the denormalization, making tables very large and full of near-
duplicated information. Compression cuts that stuff down to size, so
that a *lot* more of the table fits in memory.

Thanks,

Bill

I got involved in a project where they were using MySQL. One web page was
making about 32,000 queries to build it. The developer got that down to
about 3,200. Yeah, that definitely helped. This was admittedly an older
version of MySQL that did not do subselects, so that was being done on the
app tier.


--
Greg Moore
Ask me about lily, an RPI based CMC.

"bill" <billmaclean1 (AT) gmail (DOT) com> wrote

On Jun 11, 12:12 am, Ryan <ryanoff... (AT) hotmail (DOT) com> wrote:
Good for you for making people actually prove their assertions. You
already identified what is probably the biggest cause of the current
poor performance (" . . . (which suffers from poor design - we are
totally re-writing
things) . . .").

A well designed schema with properly written queries can perform *many
times* (sometimes even hundreds of thousands of times) faster than a
bad design. It's fortunate that you can re-write the system, many
times for one reason or another, people aren't allowed to do that. I
suspect that any hardware based speed increases for the current system
will pale in comparison to the increases you'll get when the properly
designed system comes online.

Design considerations aside, I'm very pleased to see empirical test
results that confirm what had suspected about the cache and locking.

Question: Did your tests make use of SQL Server 2008's data
compression? I am not sure what the compression does on the write
side of things, but on the read side, it can result in monster speed
increases. We have seen radical performance increases when querying
large tables that are compressed vs the uncompressed version. Our
applications are mostly reporting, so I can't tell you what
compression does on the writing side. On one hand, compression seems
like it might slow down writes, but if the algorithm is efficient (it
appears to be so), and CPU is cheap, the compression may even cut down
I/O on the write side enough to outweigh the overhead of doing the
compression.

Interestingly, the worse the design, the more compression can help on
the read side. Bad designs generally have tons of redundancy from all
the denormalization, making tables very large and full of near-
duplicated information. Compression cuts that stuff down to size, so
that a *lot* more of the table fits in memory.

Thanks,

Bill

MySQL INNODB didn't perform as well. So what we have had is as
follows :

1). MS SQL 2008 was the quickest (using 1 CPU and 4GB RAM). Average
page load times were 2.5 seconds (a combined figure - most were <30
ms)
2). MySQL Memory ran a fraction slower until 25 minutes in (see below)
then it fell off a cliff (4 CPU and 16GB RAM)
3). MySQL INNODB ran slower (4 CPU and 16GB RAM) with about 4.5
seconds on page load times (most were < 1 second)
4). MySQL Cluster was approx 10 times slower than MS SQL 2008 and
failed our none functional requirements.(4 CPU and 16GB RAM per data
node of which we had two and 2 CPU and 8GB for the Application Nodes
of which we had two - 4 servers in total)

To conclude, MS SQL 2008 ran quicker than MySQL on a quarter of the
CPU and RAM.

This was as close as we can get with identical code and schema
(indexes were tuned to perform the best on each platform). Windows
Server 2008. was used throughout (except for Cluster which needed
RedHat). We were throwing approx 370 concurrent users in an automated
and isolated test at each solution. Everything was 64 Bit.

(at 20 minutes into the test we start a purge process that gets rid of
anything older than 20 minutes. This runs every minute. Tests are only
really valid once they go beyond this point as we want data to be as
it would be in live. With MySQL Memory Engine, it took a few minutes
to become a problem. For us, this is a realistic scenario that we were
asked to produce).

All we need to do now is get the conclusions drawn up and put the
figures forward.

On 11 Juni, 17:00, Ryan <ryanoff... (AT) hotmail (DOT) com> wrote:
Would be interesting to know how PostgreSQL would stand in this
competition.
MySql always did bad when concurrency and several writing users are
involved,
PostgreSQL is brilliant there.

/Björn Lundin

For anyone interested in the results of this, all of the full facts
and figures were put forward. Other alternatives were not allowed. To
change to MySQL will cost approx £1.5m for the project this was for.
It was comprehensively outperformed by MS SQL yet......MySQL was
chosen as the DB platform to be used as the headline figure for the
licence seems to be what they want (£64k per year). TCO is
considerably higher.

This was against the recommendations of myself (15 years experience
with MS SQL) another colleague with the same experience and the Data
Architect (more experience than both of us). On the basis of the
decision, it was then also chosen for another project (with a
different set of requirements so who knows if it is suitable there). 5
days training has been booked in (as this is felt sufficient for a
high concurrency platform) and some SUN consultants will be working on
this until is goes live (in about 10 weeks).

The outcome, both of us DBA's have walked off the project. I've handed
my notice in and start a new position elsewhere in August (there were
more issues than this if I'm honest, but it was a big factor - I do
100 miles a day round trip and have a second child imminent so when
all things were weighed up.....).

Just goes to prove how well politics integrates into the work place.
Probably sounds like sour grapes. Maybe so. :-) It was a good test
either way !

Thanks for the feedback.

I don't think it's really sour grapes. I think you're better off and quite
honestly, so is the company. It's often better for a company to have
everyone rowing in the same direction (even if it's the wrong direction :-)
than have folks each trying to row their own way.

You'll be happier. And yes, quite honestly, this sounds like politics.

Oh and as for going live in 10 weeks. I'd impressed if that happens. :-)

Good luck.

--
Greg Moore
Ask me about lily, an RPI based CMC.

"Ryan" <ryanofford (AT) hotmail (DOT) com> wrote

For anyone interested in the results of this, all of the full facts
and figures were put forward. Other alternatives were not allowed. To
change to MySQL will cost approx £1.5m for the project this was for.
It was comprehensively outperformed by MS SQL yet......MySQL was
chosen as the DB platform to be used as the headline figure for the
licence seems to be what they want (£64k per year). TCO is
considerably higher.

This was against the recommendations of myself (15 years experience
with MS SQL) another colleague with the same experience and the Data
Architect (more experience than both of us). On the basis of the
decision, it was then also chosen for another project (with a
different set of requirements so who knows if it is suitable there). 5
days training has been booked in (as this is felt sufficient for a
high concurrency platform) and some SUN consultants will be working on
this until is goes live (in about 10 weeks).

The outcome, both of us DBA's have walked off the project. I've handed
my notice in and start a new position elsewhere in August (there were
more issues than this if I'm honest, but it was a big factor - I do
100 miles a day round trip and have a second child imminent so when
all things were weighed up.....).

Just goes to prove how well politics integrates into the work place.
Probably sounds like sour grapes. Maybe so. :-) It was a good test
either way !