Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Redshift is 400x Bigger than MySQL Yet MySQL is More Popular

DZone's Guide to

Redshift is 400x Bigger than MySQL Yet MySQL is More Popular

Is Amazon Redshift really becoming predominant in the world of analytic databases, taking over its smaller siblings (like MySQL) as an analytics backend?

· Big Data Zone
Free Resource

Need to build an application around your data? Learn more about dataflow programming for rapid development and greater creativity. 

There are good reasons for the hype around Amazon Redshift. Redshift is blazing fast and not that much more expensive than MySQL or PostgreSQL, the traditional mainstay of data engineers. But is Amazon Redshift really becoming predominant in the world of analytic databases, taking over its smaller siblings (like MySQL) as an analytics backend?

It would be glib not to concede that MySQL is designed for online transaction processing (OLTP), and the comparison is apples to oranges. However, for those who have been in the trenches, the story of “exploiting” MySQL as an analytic database must sound all too familiar: With a combination of intelligent schema design and sharding, many data engineering teams have built not-so-enterprise, poor man’s data warehouses atop MySQL and PostgreSQL.

While this solution costs a fraction of a similar solution using Teradata or Oracle, it is far from free: It is expensive to maintain and scale any data infrastructure because of the cost of the people managing it. Enter Redshift. By paying a bit of money to Amazon, Redshift customers can run the same queries faster and with much less maintenance. Is Amazon Redshift therefore a no-brainer?

Our own data paints a more nuanced picture, however. Because Treasure Data’s service fills the data lake/data refinery gap between event data and data warehouses, we can track which structured data stores our customers output data to: MySQL, PostgreSQL, Redshift, or Tableau Server, to name a few. In Treasure Data’s lingo, we’re looking at Result Output.

The most popular Result Output is not Redshift, as one might expect. It’s MySQL, as shown in the graph below:
MYSQLKingofDatamart

The graph shows the frequency of Treasure Data Result Outputs since January 2015, and MySQL clearly continues to dominate by this measure. One theory for this outcome is that many organizations use MySQL as a data mart: a data store that stores summarized data for quick lookups such as daily active users, weekly bounce rates, monthly revenue, etc. out

On the other hand, loading data into Redshift requires great care. You must ensure that distkey is set properly, the COPY command is run properly, and your tables are vacuumed judiciously to ensure performance. In database parlance, Redshift is read-optimized while MySQL is (comparatively) write-optimized. MySQL can effectively load small volumes of data more frequently. In contrast, Redshift is more efficient at loading large volumes of data less frequently.

Hence, it might be more informative to look at the total output data volume rather than frequency. Indeed, plotting the total output data volume gives us a very different picture, as shown below:
RedshiftDwarfstheRestinVolume

As you can see, Redshift dwarfs the other three in terms of data volume, leading us to believe that Redshift is used less as a data mart and more as a structured counterpart to Treasure Data’s data lake. The comparison with MySQL is indeed staggering. Zooming into September 2015, the Result Output for Redshift is 400 times larger than that for MySQL:
RedshiftOutputSize

Does this mean we should all migrate from MySQL to Redshift for analytics? The answer really depends on your needs. For example, if you need to update your data frequently without much engineering work and your dataset remains small, MySQL is still a fine choice as an analytics backend, especially if you design schema correctly. However, if you have a large dataset that you want to make available to an army of data analysts for various complex analytical queries, your engineers might prefer Redshift to maintaining a sharded MySQL cluster.

If you are interested in learning more, sign up for our webinar on moving data from MySQL to Redshift.

Check out the Exaptive data application Studio. Technology agnostic. No glue code. Use what you know and rely on the community for what you don't. Try the community version.

Topics:
redshift ,amazon ,mysql ,postresql ,trends

Published at DZone with permission of Kiyoto Tamura, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

THE DZONE NEWSLETTER

Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

X

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}