MySQL is fundamentally designed to be an OLTP database and while it does a fantastic job at that, its data warehouse features were built as “bolt on” additions. Can it be used for BI? Absolutely, I’ve used it a many customer sites. Does DynamoDB provide a better set of features\/capabilities for doing BI? We think so! Are they both 100% open source? You bet;why not choose the right tool for the right job then?<\/p>\n
DynamoDB (aka LucidDB) is a “purpose built for BI”<\/em> database. What does that mean? Well, I’ll be blogging about a lot of features that speak to our philosophy of a complete “BI Database” not just a fast one. One of the features that makes LucidDB complete, and not just a drag racer, is its ability to connect to remote data sources via JDBC and retrieve data. If you’re doing simple table replications, you don’t have to use an ETL tool, or do export or imports, or LOAD DATA INFILEs, etc. Our ability to connect to remote databases and access them as “remote tables” makes retrieving data into DynamoDB as easy as “insert into mytable select * from remote_table.”<\/p>\n Back to our original issue with our current MySQL<\/p>\n Our report is slow<\/strong>, and our database is big<\/strong>. How slow? Well, not really that bad, but at about 40s per query run that’s enough to tempt your business analyst to go fetch a coffee instead of continuing his work. How big? Well, not really that big, but at about 1.8GB it’s starting to get non trivial in terms of tuning the I\/O etc.<\/p>\n Our goal is to improve both using DynamoDB<\/strong>; we’ll leave MySQL as our main OLTP application. We’re not trying to replace it – in fact, we’ll embrace MySQL as the system of record and simply “slurp\/report” off this table in a separate reporting environment.<\/p>\n It’s a two step process.<\/p>\n Our Pentaho Report is based on the following SQL<\/p>\n SELECT t.Carrier as “CARRIER”, Step 1: Connect, and load the data into our DynamoDB table.<\/strong><\/p>\n — Create DynamoDB reporting table first create table faster.”ontime” ( — Get access the MySQL table OnTime in the OTP schema on host localhost CREATE SERVER MYSQL_REMOTE_SOURCE FOREIGN DATA WRAPPER import foreign schema OTP from server MYSQL_REMOTE_SOURCE into MYSQL_SOURCE;<\/p>\n — Load DynamoDB table from MySQL database directly Notice that last statement. You don’t have to export to intermediate files, or use an ETL tool (not that that’s bad, I’m a big fan of ETL tools!). You can use good old fashioned SQL to get data from a remote database into DynamoDB.<\/p><\/blockquote>\n Step 2: Change the Pentaho Report to use the new connection.<\/strong><\/p>\n We open up our report and change our connection from MySQL<\/p>\n We make some minor adjustment to the SQL (quoting some tables\/etc) and rerun our query and Voila, our report runs in 10s down from 40s, an improvement of 400%. <\/strong>Not a bad investment of a few minutes of time, I’d say. DynamoDB (LucidDB<\/a>) takes just a few minutes to install, and because of its focus on BI you should find things like retrieving data from remote data sources easy, and effective. Let’s be truthful here as well; once you speed up a report by 400% and reduce its storage by 6x your boss will be calling you<\/em><\/strong> a dynamo.<\/p>\n Notes: Full set of scripts posted here: mysql_relief.zip<\/a>. Original queries and dataset from Vadim at MySQLPerformanceBlog<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":" Here’s the scenario. You’ve got a table in MySQL for reporting that has a few million rows, and is denormalized for reporting. You’ve got a Pentaho Report that is querying this MySQL table. You have two problems with the current report. Your users are complaining that the query is slow, and they have to wait […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[5,11],"tags":[],"_links":{"self":[{"href":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/wp-json\/wp\/v2\/posts\/444"}],"collection":[{"href":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/wp-json\/wp\/v2\/comments?post=444"}],"version-history":[{"count":0,"href":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/wp-json\/wp\/v2\/posts\/444\/revisions"}],"wp:attachment":[{"href":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/wp-json\/wp\/v2\/media?parent=444"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/wp-json\/wp\/v2\/categories?post=444"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/wp-json\/wp\/v2\/tags?post=444"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}\n
![](\"\/entry_images\/2009-11-02_2301.png\")
<\/p>\n
\nc as “C”, c2 as “C2”, c*1000\/c2 as “C3” FROM
\n(SELECT Carrier, count(Carrier) AS c FROM ontime
\nWHERE DepDelay>10 GROUP BY Carrier) t JOIN
\n(SELECT Carrier, count(Carrier) AS c2 FROM ontime
\nGROUP BY Carrier) t2 ON (t.Carrier=t2.Carrier) ORDER BY c3 DESC;<\/p><\/blockquote>\n![\"200911022216\"](\"http:\/\/www.nicholasgoodman.com\/bt\/blog\/wp-content\/uploads\/2009\/11\/200911022216.jpg\")
<\/a>
\nThis takes approximately 40s to run on MySQL database running the same machine.<\/p>\n
\ncreate schema faster;<\/p>\n
\n“Year” int,
\n“Quarter” tinyint ,
\n“Month” tinyint ,
\n….. Abbreviated for Brevity ….
\n“Div5TailNum” varchar(10)
\n);<\/p>\n
\ncreate schema MYSQL_SOURCE;
\nset schema ‘MYSQL_SOURCE’;<\/p>\n
\nsys_jdbc OPTIONS (
\ndriver_class ‘com.mysql.jdbc.Driver’,
\nurl ‘jdbc:mysql:\/\/localhost\/otp?useCursorFetch=true’,
\nuser_name ‘root’,
\npassword ‘easy’,
\nfetch_size ‘1000’,
\ntable_types ‘TABLE’,
\nschema_name ‘otp’);<\/p>\n
\ninsert into FASTER.”ontime” select * from MYSQL_SOURCE.”ontime”;<\/p>\n<\/a>
\nto DynamoDB<\/p>\n<\/a>
\nNOTE: Until we finish our QA’ed builds we’re using LucidDB driver instead of DynamoDB but they are, one and the same.<\/p><\/blockquote>\n
\n<\/strong>
\nHow about storage? Our storage report shows that DynamoDB is using only .3 GB to store the same 7 Million records as compared to MySQL at 1.8GB, or 1\/6 of the storage.<\/p>\n