tag:blogger.com,1999:blog-314219542024-03-07T14:02:45.788-08:00mySQL DBA, Architecture, Dev, Scale, HA, Code <a href="https://www.linkedin.com/in/dathan"><img src="https://www.linkedin.com/img/webpromo/btn_viewmy_160x25.gif" width="160" height="25" border="0" alt="View Dathan's profile on LinkedIn"></a>Dathan Pattishallhttp://www.blogger.com/profile/00356367514107959723noreply@blogger.comBlogger185125tag:blogger.com,1999:blog-31421954.post-45742055340252335272023-02-07T15:43:00.001-08:002023-02-07T15:43:48.378-08:00ERROR 3546 (HY000) at line 24: @@GLOBAL.GTID_PURGED cannot be changed: the added gtid set must not overlap with @@GLOBAL.GTID_EXECUTED<h2 style="text-align: left;"> ERROR 3546 (HY000) at line 24: @@GLOBAL.GTID_PURGED cannot be changed: the added gtid set must not overlap with @@GLOBAL.GTID_EXECUTED<br /><br /><div style="text-align: left;"><div><span style="font-size: small; font-weight: normal;">As a MySQL 8.0 user, you may have encountered the following error message when trying to dump data from one database server and add that data to another server: <br /><br />"ERROR 3546 (HY000) at line 24: @@GLOBAL.GTID_PURGED cannot be changed: the added gtid set must not overlap with @@GLOBAL.GTID_EXECUTED." <br /><br />This error occurs when the Global Transaction Identifier (GTID) sets of the source and target servers overlap, probably from a previous import. A use case for this is importing staging into development as an example.</span></div><div><span style="font-size: small; font-weight: normal;"><br /></span></div><div><span style="font-size: small; font-weight: normal;">GTIDs are unique identifiers that are generated for each transaction in MySQL 8.0. They allow you to track changes to your data, even across multiple servers. When you receive this error message, it means that there is a conflict between the source and target server GTID sets.</span></div><div><span style="font-size: small; font-weight: normal;"><br /></span></div><div><span style="font-size: small; font-weight: normal;">The solution to this issue is to reset the master on the target server before importing the dump file. Resetting the master will erase all the binary logs and start a new one, allowing you to import the dump file without encountering the error.</span></div><div style="text-align: left;"><span style="font-size: small; font-weight: normal;"><br /><ol style="text-align: left;"><li>RESET MASTER</li><li>mysql -uroot db < dump.sql</li></ol><div><br /></div><div>It is nice to blog again, I am blogging here about mySQL and @ <a href="https://dathan.github.io/blog/">https://dathan.github.io/blog/</a> on random other things.<br /><br /></div></span></div></div></h2>Dathan Pattishallhttp://www.blogger.com/profile/00356367514107959723noreply@blogger.com0tag:blogger.com,1999:blog-31421954.post-33162249651388046722019-04-23T11:45:00.002-07:002019-04-23T11:45:53.889-07:00Debugging awslab's aws-service-operator with go delve on vscodeCurrently, I'm doing a lot of work in Kubernetes, especially around operators. One operator, in particular, I am working on is <a href="https://github.com/awslabs/aws-service-operator" target="_blank">aws-service-operator from awslabs</a>. We ran into a bug with the default behavior around the <a href="https://github.com/awslabs/aws-service-operator/blob/0784af1c09f3d7c2bb3c39e50a859bac9fd31860/cloudformation/dynamodb.yaml#L66-L72" target="_blank">dynamodb CR</a>. There is a bug in this cloudformation template that defaults RangeAttributeTypes into Strings, when the operator supports strings, number, bytes.<br />
<br />
<br />
I know this is a bug, the highlighted text from the click-through clearly states the bug, but how do I verify the bug? My environment is a macbook pro with vscode using all the go tools extensions.<br />
<br />
So let's set up the debug environment:<br />
<br />
First I need to setup the repo itself<br />
mkdir -p awslabs<br />
cd $GOPATH/src/github.com/awslabs<br />
git clone git@github.com:awslabs/aws-service-operator.git<br />
<br />
<br />
<br />
Now let's follow the <a href="https://github.com/awslabs/aws-service-operator/blob/7ff2311d7715fe06a7aeb6c3e84a9c9e22cbb661/development.adoc" target="_blank">development guideline</a> and build the environment outside of vscode (getting dep and everything working)<br />
<br />
<br />
<br />
$> code aws-service-operator // this is an extension from vscode to call it at the command line.<br />
<br />
Click the menu Debug, click Add Configuration. Paste below.<br />
<br />
<code>
{<br />
// Use IntelliSense to learn about possible attributes.<br />
// Hover to view descriptions of existing attributes.<br />
// For more information, visit: https://go.microsoft.com/fwlink/?linkid=830387<br />
"version": "0.2.0",<br />
"configurations": [<br />
{<br />
"name": "Launch",<br />
"type": "go",<br />
"request": "launch",<br />
"mode": "debug",<br />
"remotePath": "",<br />
"port": 2345,<br />
"host": "127.0.0.1",<br />
"program": "${workspaceRoot}/cmd/aws-service-operator",<br />
"env": {},<br />
"args": ["server","--kubeconfig=/Users/dathan.pattishall/.kube/config", "--region=us-west-2", "--cluster-name=dathan-eks-cluster", "--resources=s3bucket,dynamodb,sqs", "--bucket=wek8s-dathan-aws-service-operator", "--default-namespace=system-addons"],<br />
"showLog": true<br />
}<br />
]<br />
}</code><br />
<br />
Click the menu Debug and click Start Debugging. This assumes that you're using saml for aws auth, your auth is admin and has at least IAM EKSWorkerNodeRole. If you are using AWS-Admin like I am, you are good.<br />
<br />
<br />
Now let's start debugging. Put a breakpoint at line 101 of pkg/helpers/helpers.go. Step into<br /><br />resource, err := clientSet.CloudFormationTemplates(cNamespace).Get(cName, metav1.GetOptions{})<br />
<br />
You'll see that the application makes a call to itself to try to get the cloudformation templates you installed. If you didn't install any cloudformation template called dynamodb the default will be used:<br /><br />https://s3-us-west-2.amazonaws.com/cloudkit-templates/dynamodb.yaml<br />
<br />
<br />
This is where the bug is. The cloudformation yaml has a bug where it does not ref the Hash or Range Attribute Types and the workaround is to install a cloudformation CR.<br /><br /><br />
apiVersion: service-operator.aws/v1alpha1<br />
kind: CloudFormationTemplate<br />
metadata:<br />
name: dynamodb<br />
output:<br />
url: "https://s3-us-west-2.amazonaws.com/a-temp-public-test/dynamodb.yaml"<br />
<br />
output.url contains the ClouldFormationTemplate with a data field that defines the cloudformation template. I can only surmise that to make common code paths, that they will make extra API calls for reuseability, because even though the aws-service-operator has the CloudFormationTemplate, it needs to fetch it remotely due to how the code is constructed, making redundant calls. You'll see this in the debug. Make an API call to itself, then parse the YAML, then fetch the YAML from a remote endpoint.<br />
<br />
Now what we see here is that the operator needs to pull the CR from a REST endpoint or HTTP endpoint even though it already has it defined in K8s itself.<br />
<br />
The fix to the bug is as follows.<br /><br />From:<br /><span style="font-family: Courier New, Courier, monospace;"><br /></span><br />
<br />
<span style="font-family: Courier New, Courier, monospace;"> AttributeDefinitions:</span><br />
<span style="font-family: Courier New, Courier, monospace;"> -</span><br />
<span style="font-family: Courier New, Courier, monospace;"> AttributeName: !Ref HashAttributeName</span><br />
<span style="font-family: Courier New, Courier, monospace;"> AttributeType: "S"</span><br />
<span style="font-family: Courier New, Courier, monospace;"> -</span><br />
<span style="font-family: Courier New, Courier, monospace;"> AttributeName: !Ref RangeAttributeName</span><br />
<span style="font-family: Courier New, Courier, monospace;"> AttributeType: "S"</span><br />
<span style="font-family: Courier New, Courier, monospace;"><br /></span>
<span style="font-family: Courier New, Courier, monospace;"><br /></span>
<span style="font-family: Times, Times New Roman, serif;">To</span><span style="font-family: Arial, Helvetica, sans-serif;"> </span><br />
<span style="font-family: Courier New, Courier, monospace;"><br /></span>
<span style="font-family: Courier New, Courier, monospace;">AttributeDefinitions: </span><br />
<span style="font-family: Courier New, Courier, monospace;"> -</span><br />
<span style="font-family: Courier New, Courier, monospace;"> AttributeName: !Ref HashAttributeName</span><br />
<span style="font-family: Courier New, Courier, monospace;"> AttributeType: !Ref HashAttributeType</span><br />
<span style="font-family: Courier New, Courier, monospace;"> -</span><br />
<span style="font-family: Courier New, Courier, monospace;"> AttributeName: !Ref RangeAttributeName</span><br />
<span style="font-family: Courier New, Courier, monospace;"></span><br />
<span style="font-family: Courier New, Courier, monospace;"> AttributeType: !Ref RangeAttributeType</span><br />
<br />
<br />
<br />
Additional to this, awslabs uses N as a value. This value means false in YAML (why I don't know). Thus for the yaml passed to create a dynamodb table you need to quote it.<br />
<br />So in the end to create my table I need the following yaml to create the dynamodb table which I use to test the operator.<br /><br />
<pre data-meta="apiVersion: service-operator.aws/v1alpha1" lang="+" style="background-color: #f6f8fa; border-radius: 3px; box-sizing: border-box; color: #24292e; font-family: SFMono-Regular, Consolas, "Liberation Mono", Menlo, Courier, monospace; font-size: 11.9px; line-height: 1.45; margin-bottom: 16px; overflow-wrap: normal; overflow: auto; padding: 16px;"><code style="background: transparent; border-radius: 3px; border: 0px; box-sizing: border-box; display: inline; font-family: SFMono-Regular, Consolas, "Liberation Mono", Menlo, Courier, monospace; font-size: 11.9px; line-height: inherit; margin: 0px; overflow-wrap: normal; overflow: visible; padding: 0px; word-break: normal;">kind: DynamoDB
+ metadata:
+ name: sample-tablename
+ spec:
+ hashAttribute:
+ name: AuthorizationCode
+ type: "S"
+ rangeAttribute:
+ name: CreatedAt
+ type: "N"
+ readCapacityUnits: 10
+ writeCapacityUnits: 10</code></pre>
<br />
<br />
<br />
Notice the S is quoted along with the "N" otherwise, N equates to false.<br />
<br />
<br /><br /><br /><br /><br />In conclusion. Delve is awesome, the operator has a bug and I was table to figure it out with this debugging method to produce this case <a href="https://github.com/awslabs/aws-service-operator/issues/181">https://github.com/awslabs/aws-service-operator/issues/181</a>Dathan Pattishallhttp://www.blogger.com/profile/00356367514107959723noreply@blogger.com0tag:blogger.com,1999:blog-31421954.post-25909742250045639422019-02-12T14:37:00.001-08:002019-02-12T14:37:31.678-08:00Aurora mySQL differencesWorking with Aurora MySQL I thought would be a breeze, but its subtle differences make me scratch my head. Thus I need to find out more about this and write a post :)<br />
<br />
What is Aurora?<br />
<br />
It's a mySQL wire protocol compatible storage management system that sits on top of mySQL and modifies some innodb internals. You can read about more of the architecture <a href="https://aws.amazon.com/blogs/architecture/amazon-aurora-mysql-dba-handbook-connection-management/" target="_blank">here</a>: I think of it as a Proxy Storage Engine System<br />
<br />
<br />
The differences start from just starting the server. Aurora MySQL has Huge Page support turned on by default since AWS launches Aurora MySQL server with their custom flag for innodb large page support:<br />
<br />
innodb_shared_buffer_pool_uses_huge_pages<br />
<br />
This is not an open source setting documented by MySQL official <a href="https://www.google.com/search?ei=5CFiXNWtEane0gKA9bu4BA&q=site%3Adev.mysql.com+%2Binnodb_shared_buffer_pool_uses_huge_pages&oq=site%3Adev.mysql.com+%2Binnodb_shared_buffer_pool_uses_huge_pages&gs_l=psy-ab.3...11906.11906..12853...0.0..0.61.61.1......0....1..gws-wiz.......0i71.jp9rSbc1Nus" target="_blank">build</a>. In fact, there is not much information on this setting at all. I can only assume RDS instances are configured with Huge page support as detailed <a href="https://dev.mysql.com/doc/refman/5.7/en/large-page-support.html" target="_blank">here</a> and this custom setting for Aurora turns large page support on for mysqld.<br />
<br />
So, what else is different between <a href="https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Reference.html" target="_blank">Aurora and Innodb</a>? From Amazon's docs<br />
<br />
<blockquote class="tr_bq">
<div style="background-color: white; color: #444444; font-family: "Amazon Ember", "Open Sans", Helvetica, Arial, sans-serif; font-size: 16px; line-height: 1.5em;">
The following MySQL parameters do not apply to Aurora MySQL:</div>
<div class="itemizedlist" style="background-color: white; color: #444444; font-family: "Amazon Ember", "Open Sans", Helvetica, Arial, sans-serif; font-size: 16px;">
<ul class="itemizedlist" type="disc">
<li class="listitem" style="line-height: 1.5em;"><div style="line-height: 1.5em;">
<code class="code" style="font-family: Consolas, Courier, mono; overflow: auto;">innodb_adaptive_flushing</code></div>
</li>
<li class="listitem" style="line-height: 1.5em;"><div style="line-height: 1.5em;">
<code class="code" style="font-family: Consolas, Courier, mono; overflow: auto;">innodb_adaptive_flushing_lwm</code></div>
</li>
<li class="listitem" style="line-height: 1.5em;"><div style="line-height: 1.5em;">
<code class="code" style="font-family: Consolas, Courier, mono; overflow: auto;">innodb_checksum_algorithm</code></div>
</li>
<li class="listitem" style="line-height: 1.5em;"><div style="line-height: 1.5em;">
<code class="code" style="font-family: Consolas, Courier, mono; overflow: auto;">innodb_doublewrite</code></div>
</li>
<li class="listitem" style="line-height: 1.5em;"><div style="line-height: 1.5em;">
<code class="code" style="font-family: Consolas, Courier, mono; overflow: auto;">innodb_flush_method</code></div>
</li>
<li class="listitem" style="line-height: 1.5em;"><div style="line-height: 1.5em;">
<code class="code" style="font-family: Consolas, Courier, mono; overflow: auto;">innodb_flush_neighbors</code></div>
</li>
<li class="listitem" style="line-height: 1.5em;"><div style="line-height: 1.5em;">
<code class="code" style="font-family: Consolas, Courier, mono; overflow: auto;">innodb_io_capacity</code></div>
</li>
<li class="listitem" style="line-height: 1.5em;"><div style="line-height: 1.5em;">
<code class="code" style="font-family: Consolas, Courier, mono; overflow: auto;">innodb_io_capacity_max</code></div>
</li>
<li class="listitem" style="line-height: 1.5em;"><div style="line-height: 1.5em;">
<code class="code" style="font-family: Consolas, Courier, mono; overflow: auto;">innodb_log_buffer_size</code></div>
</li>
<li class="listitem" style="line-height: 1.5em;"><div style="line-height: 1.5em;">
<code class="code" style="font-family: Consolas, Courier, mono; overflow: auto;">innodb_log_file_size</code></div>
</li>
<li class="listitem" style="line-height: 1.5em;"><div style="line-height: 1.5em;">
<code class="code" style="font-family: Consolas, Courier, mono; overflow: auto;">innodb_log_files_in_group</code></div>
</li>
<li class="listitem" style="line-height: 1.5em;"><div style="line-height: 1.5em;">
<code class="code" style="font-family: Consolas, Courier, mono; overflow: auto;">innodb_max_dirty_pages_pct</code></div>
</li>
<li class="listitem" style="line-height: 1.5em;"><div style="line-height: 1.5em;">
<code class="code" style="font-family: Consolas, Courier, mono; overflow: auto;">innodb_use_native_aio</code></div>
</li>
<li class="listitem" style="line-height: 1.5em;"><div style="line-height: 1.5em;">
<code class="code" style="font-family: Consolas, Courier, mono; overflow: auto;">innodb_write_io_threads</code></div>
</li>
<li class="listitem" style="line-height: 1.5em;"><div style="line-height: 1.5em;">
<code class="code" style="font-family: Consolas, Courier, mono; overflow: auto;">thread_cache_size</code></div>
</li>
</ul>
</div>
<div style="background-color: white; color: #444444; font-family: "Amazon Ember", "Open Sans", Helvetica, Arial, sans-serif; font-size: 16px; line-height: 1.5em;">
The following MySQL status variables do not apply to Aurora MySQL:</div>
<div class="itemizedlist" style="background-color: white; color: #444444; font-family: "Amazon Ember", "Open Sans", Helvetica, Arial, sans-serif; font-size: 16px;">
<ul class="itemizedlist" type="disc">
<li class="listitem" style="line-height: 1.5em;"><div style="line-height: 1.5em;">
<code class="code" style="font-family: Consolas, Courier, mono; overflow: auto;">innodb_buffer_pool_bytes_dirty</code></div>
</li>
<li class="listitem" style="line-height: 1.5em;"><div style="line-height: 1.5em;">
<code class="code" style="font-family: Consolas, Courier, mono; overflow: auto;">innodb_buffer_pool_pages_dirty</code></div>
</li>
<li class="listitem" style="line-height: 1.5em;"><div style="line-height: 1.5em;">
<code class="code" style="font-family: Consolas, Courier, mono; overflow: auto;">innodb_buffer_pool_pages_flushed</code></div>
</li>
</ul>
</div>
<div class="aws-note" style="background-color: white; color: #444444; font-family: "Amazon Ember", "Open Sans", Helvetica, Arial, sans-serif; font-size: 16px; margin: 0.5em 2.7em 1em; padding: 0px;">
<div class="aws-note" style="font-weight: bold; line-height: 1.5em; margin-top: 0.5em; padding: 0px;">
Note</div>
<div style="line-height: 1.5em; margin-top: 0.5em; padding: 0px;">
These lists are not exhaustive.</div>
</div>
</blockquote>
<br />
<br />
<br />
<br />
In summary, Aurora uses mySQL but it's also a layer on top of mySQL. In all essence, it's just another storage engine which forks Innodb and provides management primitives built into the DBMS system.<br />
<br />
<br />
In the next weeks, I'll describe how we launch Aurora instances and why as well as capturing more differences that have not made it into this list.<br /><br /><br /><br />
<br />Dathan Pattishallhttp://www.blogger.com/profile/00356367514107959723noreply@blogger.com0tag:blogger.com,1999:blog-31421954.post-59826495058337199572018-07-20T18:14:00.000-07:002018-07-20T18:16:05.620-07:00Hackathon process per week Sprints IdeaI like hackathons. Hackathons provide the freedom to build outside the process. The forced speed to deliver something to demo and the fun self-deprecation of "ooh this is really ugly/bad TODO don't do this." in the source/commit logs which tells a great story. Also, a great side effect; people are really interested in refactoring and fixing the code especially if the demo went well.<br />
<br />
So, I started thinking what if we can take this naturally formed fun process and define a weekly sprint, with a daily standup reporting on the process to achieve the product goal, using a hackathon method.<br />
<br />
<h3>
Day 1 and 2</h3>
"How much can you get done in two days for the demo"<br />
<br />
<ul>
<li>This portion is no more than an hour planing. You talk to your team and divide up tasks for the hack you want to demo-in two days. For instance, "Johnny says I'll write the service" and "Amanda says I'll provide the data-it will be in MySQL". Sammy says "I'll write the front end to demo, Johnny let's agree what you'll send me, for now, I will simulate some pho data."</li>
<li>Then each person builds their part.</li>
<li>During the process, Johnny is building the interface from an un-authenticated HTTP Get request that has a JSON response to define what his service will return. Amanda finishes the process of testing some queries for functionality she checks in her part of how to get data, massage it and what tables are what, NOT performance.</li>
<li>Johnny sends a sample interface to Sammy so some dynamic data can be injected into the mockup when Sammy requests data. They agreed that a REST API using GET with a JSON response.</li>
<li>There are PR requests when sharing the same addition to the same place otherwise frequent merges</li>
<li>When fixing something that made it into master fix forward so check into master :P</li>
<li>Each check-in should be filled with a series of TODO, FIXME or "TODO don't do this" statements for speed until that's not needed when you have a refined process.</li>
<li>Demo</li>
</ul>
<div>
<br /></div>
<div>
<b>What does the individual developer each get? </b></div>
<div>
Each developer produced something quick to verify the viability of the idea. A vested interest to fix the hacks and beautify the code, reusing reusable parts, etc. </div>
<div>
<br /></div>
<div>
<b>What does the team get?</b></div>
<div>
The team feels that they got something out pretty quick, the team has some talking points of what to fix next and what systems the team envisions that could possibly be used in other parts of the code. Finally, the chance to learn something new in the knowledge transfer or the ability to fix an approach before going too far down the rabbit hole.</div>
<div>
<br /></div>
<h3>
Day 3 </h3>
<div>
The next day is mapping out what the developer wants to refactor, has to change and gets to delete. With knowledge transfer of the good, bad, and embarrassing things with an idea of the direction each person took. It is fun.</div>
<div>
<ul>
<li>This is looking over the queries to make they make sense. </li>
<li>Are the correct indexes there? </li>
<li>Are we really answering the correct questions efficiently if not how can we? </li>
<li>What hacks do we need to undo to provide what we delivered?</li>
<li>How do I test this thing? I need to make sure before I refactor I have reproducible tests. </li>
</ul>
<h3>
Day 4 </h3>
<div>
Document, Test, Refactor agree more as a team and focus on a code structure that enables adding the next round of features while setting standards of the direction going forward or revisiting them if need be.</div>
<div>
<br /></div>
<div>
<h3>
Day 5</h3>
</div>
<div>
Do more of the same or get a beer with the team.</div>
<div>
<br /></div>
<div>
<br /></div>
<div>
This process makes me feel that I am building something fast. The reason for the speed was to validate the idea or approach. Time is built into the process for testing, refactoring and documenting. The refactoring takes into account how to add new things faster. 50% building 50% testing, documenting, refactoring, making better. Producing a 4 day work week with daily standups</div>
<div>
<br /></div>
<h3>
What about a really big project and delivering constantly</h3>
</div>
<div>
<ul>
<li>Whiteboard what is needed to deliver such as what the product is, what does it solve, what are the features. </li>
<li>Answer what is alpha</li>
<li>Answer what is beta.</li>
<li>Divide and conquer the vision for each "hackathon period"</li>
<li>Adjust projection of delivery based on the previous hackathon progress</li>
<li>Keep working and visit each hackathon period to verify the correct thing is built correctly.</li>
<li>Profit from a fun fast paced delivery of code that treats features and delivery of great code the team all validates as equal partners.</li>
</ul>
<div>
<br /></div>
</div>
Dathan Pattishallhttp://www.blogger.com/profile/00356367514107959723noreply@blogger.com0tag:blogger.com,1999:blog-31421954.post-13492312188357673362018-01-27T10:10:00.000-08:002018-01-27T10:10:08.680-08:00Spotify Top 200 in mySQLI do a lot of data analysis lately, and I try to find answers to questions through data for my companies pressing questions. Let's look at the past year of 2017 and answer questions for people who like music.<br />
<br />
<br />
<script src="https://gist.github.com/dathan/08c37c2daea1a7900c66a1755e75502a.js"></script>
<br />
artist is the artist name<br />
track is the artist's track name<br />
list_date is which chart date the artist show up on the top200<br />
streams is the number of plays following spotify specific rules<br />
<br />
Let's look at the data set<br />
<span style="font-family: "courier new" , "courier" , monospace;"><br /></span>
<span style="font-family: "courier new" , "courier" , monospace;">select count(*) from spotify.top200 WHERE country='us' and list_date >= '2017-01-01' and list_date < '2018-01-01';</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">+----------+</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">| count(*) |</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">+----------+</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">| 74142 |</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">+----------+</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">1 row in set (0.04 sec)</span><br />
<br />
<br />
How many artists made it in the top200 for the United States?<br />
<span style="font-family: "courier new" , "courier" , monospace;"><br /></span>
<span style="font-family: "courier new" , "courier" , monospace;">mysql> select count(DISTINCT(artist)) from spotify.top200 WHERE country='us' and list_date >= '2017-01-01' and list_date < '2018-01-01';</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">+-------------------------+</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">| count(DISTINCT(artist)) |</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">+-------------------------+</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">| 527 |</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">+-------------------------+</span><br />
<span style="font-family: "courier new" , "courier" , monospace;">1 row in set (0.09 sec)</span><br />
<div>
<br /></div>
<div>
Wow, it's really hard to be a musician. Only 527 broke the top200.</div>
<div>
<br /></div>
<div>
How many tracks in 2017 broke the top200?</div>
<div>
<br /></div>
<div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;"> select count(DISTINCT(track)) from spotify.top200 WHERE country='us' and list_date >= '2017-01-01' and list_date < '2018-01-01';</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">+------------------------+</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">| count(DISTINCT(track)) |</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">+------------------------+</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">| 1682 |</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">+------------------------+</span></div>
</div>
<div>
<br /></div>
<div>
For the entire year, 1682 songs defined the united states listing habits for the most part.</div>
<div>
<br /></div>
<div>
<br /></div>
<div>
Who showed up the most in the top200 for 2017?</div>
<div>
<br /></div>
<div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">mysql> select artist,count(*) AS CNT from spotify.top200 WHERE country='us' and list_date >= '2017-01-01' and list_date < '2018-01-01' group by 1 order by 2 DESC LIMIT 10;</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">+------------------+------+</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">| artist | CNT |</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">+------------------+------+</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">| Drake | 3204 |</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">| Lil Uzi Vert | 1891 |</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">| Kendrick Lamar | 1874 |</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">| Post Malone | 1776 |</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">| Ed Sheeran | 1581 |</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">| The Weeknd | 1566 |</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">| Migos | 1550 |</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">| Future | 1536 |</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">| The Chainsmokers | 1503 |</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">| Kodak Black | 1318 |</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">+------------------+------+</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">10 rows in set (0.16 sec)</span></div>
</div>
<div>
<br /></div>
<div>
Drake killed it, but Lil Uzi Vert is the star of the year, IMHO. Drake has a pedigree while Lil Uzi just started running.</div>
<div>
<br /></div>
<div>
Also from these artists I can tell HIP HOP dominated us charts; Let's verify this assumption.</div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;"><br /></span></div>
<div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">mysql> select artist,SUM(streams) AS CNT from spotify.top200 WHERE country='us' and list_date >= '2017-01-01' and list_date < '2018-01-01' group by 1 order by 2 DESC LIMIT 10;</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">+------------------+------------+</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">| artist | CNT |</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">+------------------+------------+</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">| Drake | 1253877919 |</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">| Kendrick Lamar | 1161624639 |</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">| Post Malone | 954546910 |</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">| Lil Uzi Vert | 818889040 |</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">| Ed Sheeran | 714523363 |</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">| Migos | 682008192 |</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">| Future | 574005011 |</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">| The Chainsmokers | 557708920 |</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">| 21 Savage | 472043174 |</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">| Khalid | 463878924 |</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">+------------------+------------+</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">10 rows in set (0.48 sec)</span></div>
</div>
<div>
<br /></div>
<div>
<br /></div>
<div>
Yup hip hop dominated the top 10 steams.</div>
<div>
<br /></div>
<div>
What about tracks? What are the top 10 tracks by streams?</div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;"><br /></span></div>
<div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;"> select track,SUM(streams) AS CNT from spotify.top200 WHERE country='us' and list_date >= '2017-01-01' and list_date < '2018-01-01' group by 1 order by 2 DESC LIMIT 10;</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">+-------------------+-----------+</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">| track | CNT |</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">+-------------------+-----------+</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">| HUMBLE. | 340136186 |</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">| XO TOUR Llif3 | 314758565 |</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">| Congratulations | 283551832 |</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">| Shape of You | 280898054 |</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">| Unforgettable | 261753940 |</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">| Mask Off | 242524530 |</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">| Despacito - Remix | 241370570 |</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">| rockstar | 225517132 |</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">| Location | 224879215 |</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">| 1-800-273-8255 | 219689749 |</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">+-------------------+-----------+</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">10 rows in set (0.43 sec)</span></div>
</div>
<div>
<br /></div>
<div>
Which tracks and artists had the most time in the top200?</div>
<div>
<br /></div>
<div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">select artist,track,count(*) AS CNT from spotify.top200 WHERE country='us' and list_date >= '2017-01-01' and list_date < '2018-01-01' group by 2 order by 3 DESC LIMIT 10;</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">+------------------+-------------------------------------+-----+</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">| artist | track | CNT |</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">+------------------+-------------------------------------+-----+</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">| D.R.A.M. | Broccoli (feat. Lil Yachty) | 485 |</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">| French Montana | Unforgettable | 417 |</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">| PnB Rock | Selfish | 394 |</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">| Travis Scott | goosebumps | 365 |</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">| Post Malone | Go Flex | 365 |</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">| Childish Gambino | Redbone | 365 |</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">| Post Malone | Congratulations | 365 |</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">| Post Malone | White Iverson | 365 |</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">| Migos | Bad and Boujee (feat. Lil Uzi Vert) | 364 |</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">| Bruno Mars | That's What I Like | 364 |</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">+------------------+-------------------------------------+-----+</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">10 rows in set (0.20 sec)</span></div>
</div>
<div>
<br /></div>
<div>
Also from this data I can tell that Post Malone had a fantastic year!</div>
<div>
<br /></div>
<div>
<br /></div>
<div>
So, more questions can be answered, like who held the number 1 position on the top200 the most?</div>
<div>
<br /></div>
<div>
<div>
select artist,track,count(*) AS CNT from spotify.top200 WHERE country='us' and pos=1 and list_date >= '2017-01-01' and list_date < '2018-01-01' group by 2 order by 3 DESC LIMIT 10;</div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">+----------------+-------------------------------------+-----+</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">| artist | track | CNT |</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">+----------------+-------------------------------------+-----+</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">| Post Malone | rockstar | 105 |</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">| Kendrick Lamar | HUMBLE. | 67 |</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">| Ed Sheeran | Shape of You | 48 |</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">| Luis Fonsi | Despacito - Remix | 47 |</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">| Migos | Bad and Boujee (feat. Lil Uzi Vert) | 29 |</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">| 21 Savage | Bank Account | 20 |</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">| Drake | Passionfruit | 12 |</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">| Logic | 1-800-273-8255 | 10 |</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">| Taylor Swift | Look What You Made Me Do | 10 |</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">| French Montana | Unforgettable | 7 |</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">+----------------+-------------------------------------+-----+</span></div>
<div>
<span style="font-family: "courier new" , "courier" , monospace;">10 rows in set (0.26 sec)</span></div>
</div>
<div>
<br /></div>
<div>
<br /></div>
<div>
Wow can see hear that Post Malone is the star!</div>
<div>
<br /></div>
<div>
In summary, getting public data sources and doing simple queries can give a clearer insight into data to answer some pressing questions one may have.</div>
<div>
<br /></div>
<div>
With the schema above what questions would you answer?</div>
Dathan Pattishallhttp://www.blogger.com/profile/00356367514107959723noreply@blogger.com0tag:blogger.com,1999:blog-31421954.post-26592695087650857672018-01-19T13:01:00.002-08:002018-01-19T13:01:32.168-08:00Deploying Go Applications in Docker Containers using a Scratch Docker FileProgramming in golang is fantastic. I find it fun, expressive and simple to build concurrent programs. Deploying a golang app from laptop to production is as hard now as when it was back when I was building Monolithic services. A great way to deploy nowadays is to deploy microservices in containers. Containers keep the environment between laptop and AWS Linux instance in sync since in essence the environment is deployed and not just the code or binary.<br />
<div>
<br /></div>
<div>
Containerization of the environment is not ideal although. Sometimes you can ship containers of 1GB in size or more. Deploying that across the LAN is ok, over the WAN .. it is debatable. So, to deal with this problem I work with scratch Dockerfiles when deploying applications.</div>
<div>
<br /></div>
<div>
Starting from scratch Dockerfiles, I know that there is no real environment overhead since the environment is the most basic it can be. Additionally, I do not have to worry about the golang environment in my container because we are not going to ship "golang and all its packages", we are going to ship the binary itself. This is best described as an example.</div>
<div>
<br /></div>
<div>
<script src="https://gist.github.com/dathan/71ece26f1b1ac00be68d71e86b43d952.js"></script>
<br />
<br />
The Dockerfile, is like a Makefile but for your environment. Each line describes what the Dockerfile does. Prior to executing the docker file, we will need to set up the environment.<br />
<br />
GOOS=linux go build . <br />
<br />
This line will build the go program as a Linux binary.<br />
<br />
docker build -t dathanvp/goprogram:latest .<br />
<br />
This line says; execute the docker file and tag the image as dathanvp/goprogram.<br />
<br />
docker run -p 8282:8282 -v /Users/dathan/gocode/src/github.com/dathanvp/goprogram/logs:/mnt:rw dathanvp/goprogram:latest<br />
<br />
Now, this is the magic. Docker will open port 8282 and map it to port 8282 in the container. A volume is attached from my laptop to the container's /mnt directory with read and write privileges. (When executing my container in production only this line changes.) This volume is to keep the logs persistent. Containers reset state, thus losing anything generated and the reason for my volume. Finally docker run is going to run my image dathanvp/goprogram<br />
<br />
I deploy my container's to AWS by executing<br />
<br />
docker push dathanvp/goprogram<br />
<br />
This pushes my go program from my laptop to cloud.docker.com where my aws instances can then pull from, enabling running my programs in production without having to set up the environment on aws (other than docker of course).<br />
<br />
Finally, why do it this way? I want my program to run on my laptop and on my AWS ubuntu servers without having to keep golang development environments in sync. Additionally, I want my containers to be really small so I don't have to ship hundreds of megs around to start the application, which itself is about 13MB. Uploading from comcast sucks. So, in conclusion, this is the best way I've found so far :)<br />
<br />
Please let me know how you ship go applications and why.<br />
<br />
<br /></div>
Dathan Pattishallhttp://www.blogger.com/profile/00356367514107959723noreply@blogger.com0tag:blogger.com,1999:blog-31421954.post-17452609261285257872017-12-11T13:32:00.001-08:002017-12-11T13:32:38.726-08:00Designing a RDBMS SQL Table<div>
<br /></div>
<div>
Building tables initially should not really require a lot of thought. What? I'm suggesting that when designing a table think of the Table as a spreadsheet. Yes.</div>
<div>
<br /></div>
<div>
<br /></div>
<div>
For instance, let's create a table that combines all social scores of a users' media in a single table. We will call this table platform_resources.</div>
<div>
<br /></div>
<div>
What do we need to record the social score total of a single person?</div>
<div>
<ul>
<li>Who is this person? How I know this persona. </li>
<li>What is the platform? Which Social Platform does this reference refer to</li>
<li>What is the platform identifier? What is the social platform identifier</li>
<li>What is a common social score for each user? View, Likes, Comments</li>
</ul>
<div>
<br /></div>
</div>
<script src="https://gist.github.com/dathan/6aadcdf9c9341bb19120cbfd03478fe2.js"></script>
<div>
<br /></div>
<div>
So the table above answers my questions. For each piece of media that and interna_name owns, I am able to collect a summary of basic stats. By no means is this optimized. The row size is roughly<br />
<br />
21+51+51+4+4+4+4+4+256+4 = 403 bytes not taking into account the primary key which is very large and takes a small byte overhead due to the exceeding an internal limit.<br />
<br />
We are not optimizing yet, we are just answering questions.<br />
<br />
<br />
The Primary Key was picked to be platform_id, platform, internal_name. Following the Left Most prefix rule for composite indexes, we have roughly 3 indexes in 1 index. The original primary key, platform_id & platform, then finally platform_id. The primary key was picked to be this because for a platform the platform_id is unique and the person who owns this platform_id should be represented. Additionally, since we are using INNODB the table is sorted by the primary key.<br />
<br />
No optimizations just a basic table get's the job done. Now how would you optimize this table?<br />
First, you should ask what are you optimizing the table for? Disk size? Memory fit? Because its ugly and it bothers me?<br />
<br />
Let's estimate how this table will grow. This table is a MxN problem where for each internal_name they will have N resources per platform. The bounds of the growth are around 1000 items per year per platform. M is less than 20K so, It's really not worth it to optimize for any other reason just to do it because. So don't.<br />
<br />
If I had to optimize because the MxN problem turned into a huge overhead.<br />
First, I would reduce the row size of the table by making lookup tables for internal_name, platform, platform_id which keeps the primary key smaller - probably in 64 bits.<br />
<br />
Next, distribute the table by either date_taken range since queries will be more interested in the latest data, or we can distribute the table by internal_name; this is another post.<br />
<br />
Finally, sometimes you just need a table and you just want to query it like give me the total sum of views for all Instagram videos by a creator. The post is to think about optimizations when you need to think about optimizations and not beforehand. If your needs changes; change the schema to focus on the optimization you are going for. :)<br />
<br />
<br />
<br /></div>
Dathan Pattishallhttp://www.blogger.com/profile/00356367514107959723noreply@blogger.com0tag:blogger.com,1999:blog-31421954.post-68037812653197618292017-12-06T10:26:00.000-08:002017-12-06T10:30:07.020-08:00Back to Sharing stuff I learned<br />
I have not been regular in blog posts as I've just been focused on everything. I got lazy. Well, that is over.<br />
<br />
At Shots Studios, a teen social network consisting of nearly 2M lines of code is no more. Shots is now a one-stop shop for select Creators. We are a Production Studio, Ad/Talent Agency, Talent Management Media company focused on creating timeless content. A 21st-century answer to getting great content from great creators in front of their audience.<br />
<br />
Your internal monologue after reading this is how does this have anything to do with MySQL, HA, Scale, Coding; if not, this is still a good segway to explain how.<br />
<br />
Shots the App, did really well yet not well enough to compete with Snapchat and Instagram. We did gain a lot of insight, mainly in what is called Influencers. A large percentage of time in growing the Shots platform was handling their cases of spikey scale. When Influencers posted they would promote their Selfie on other platforms sending waves of teens all at once to their data. Honestly, this was an amazing challenge to scale on a tight budget. Cold to Performant in millisecond time, with a 600% increase in load/concurrency suddenly. The short answer to scale this was to keep data in memory - From this, we understood that influencers reach and ability to move users is more effective than Display Ads. Period.<br />
<br />
We did a huge analysis about our user base, and from that analysis, we made the decision to keep all "Influencers" in memory, and people who were the sticky users-the percentage of DAU that comes back with frequency. Next, to make sure that we did not saturate a network interface by keeping their data in memory on a single box, we replicated this subset of users among redundant pairs. Finally, we had to keep higher than normal frontends in reserve to handle the sudden burst without the startup delta of dynamic scaling pools.<br />
<br />
Now we use a subset of the tech developed to mine, analyze, data about Creators. Creators, were influencers but now create, perform, direct, edit content thus they are called Creators. For instance, we use a custom performant event tracking system to monitor the social engagement of all creators. If you heard of a site called socialblade, I basically duplicated it at a much higher precision then their data.<br />
<br />
With this we are able to tell which of a creator's content strikes a chord with users then we produce more of that performant content. For instance, <a href="https://shots.com/superheroes">https://shots.com/superheroes</a>. With this insight, analysis, data collection and maximizing the reach channels on platforms like YouTube, Instagram with a shoestring budget we are making data-rich informed decisions.<br />
<br />Dathan Pattishallhttp://www.blogger.com/profile/00356367514107959723noreply@blogger.com0tag:blogger.com,1999:blog-31421954.post-8887191012474347992017-05-23T17:29:00.000-07:002017-05-24T08:46:20.250-07:00Golang (Go) and BoltDBI've been using Go for some time now (3 years) and I am constantly impressed with the language's ease of use. I originally started my career in C-Unix System Programming, then Java, then PHP and now I am rather language agnostic. Out of all the languages I know, go is the most fun and there is a strong community behind it.<br />
<br />
<br />
<a href="https://github.com/boltdb/bolt" target="_blank">BoltDB</a> is yet another NoSQL Key-Value store, designed to be embedded and I happened across it for a small use case. I use GO to crawl sites and parse HTML DOM in a very concurrent manner to gather data for analysis from a variety of remote web sources. BoltDB is used to keep state as I transfer from my local mac book to a remote server and it is very easy to use. Basically, I needed a portable embedded database that is fast and resilient without setting up MySQL and keeping the schema in sync between dev and production. This is not user facing just a set of go packages that help me keep state so I can know where to pick up from in case of some sort of error, like I turn off my laptop or some random panic.<br />
<br />
<br />
Let's look at BoltDB usage. Below is my struct, everything is a string because I am not formatting or typing things yet.<br />
<br />
<br />
<pre style="background-color: black; color: #bbbbbb; font-family: 'Menlo'; font-size: 9.0pt;"><pre style="font-family: Menlo; font-size: 9pt;"><span style="color: #55ff55; font-weight: bold;">type </span><span style="color: #f0ed11; font-weight: bold;">TableRow </span><span style="color: #55ff55; font-weight: bold;">struct </span><span style="color: #ff5555; font-weight: bold;">{</span><span style="color: #ff5555; font-weight: bold;"> </span></pre>
<pre style="font-family: Menlo; font-size: 9pt;"><span style="color: #55ffff;">
</span></pre>
<pre style="font-family: Menlo; font-size: 9pt;"><span style="color: #55ffff;"> Title </span><span style="font-weight: bold;">string</span><span style="font-weight: bold;"> </span></pre>
<pre style="font-family: Menlo; font-size: 9pt;"><span style="color: #55ffff;"> Time </span><span style="font-weight: bold;">string</span><span style="font-weight: bold;"> </span></pre>
<pre style="font-family: Menlo; font-size: 9pt;"><span style="color: #55ffff;"> Anchor </span><span style="font-weight: bold;">string</span><span style="font-weight: bold;"> </span></pre>
<pre style="font-family: Menlo; font-size: 9pt;"><span style="color: #55ffff;"> Price </span><span style="font-weight: bold;">string</span><span style="font-weight: bold;"> </span></pre>
<pre style="font-family: Menlo; font-size: 9pt;"><span style="color: #55ffff;"> Notified </span><span style="font-weight: bold;">string </span><span style="color: #55ffff;">// could make this a Time Struct but let's be simple</span></pre>
<pre style="font-family: Menlo; font-size: 9pt;"><span style="color: #ff5555; font-weight: bold;">}</span></pre>
</pre>
<br />
<br />
Next, I create my.db if it doesn't exist. The function <b>check</b> looks to see if there are errors and panics. The line <b>defer db.Close()</b> will close the db at the end of the function which these calls are made from. The function <b>addRecord</b> will create a bucket called parser_bucket which is a const and add the key byte with value triggering a bucket creation if this is the first run. It is something fast to make a point and yes there are more efficient ways to do this.<br />
<br />
<pre style="background-color: black; color: #bbbbbb; font-family: 'Menlo'; font-size: 9.0pt;"><pre style="font-family: Menlo; font-size: 9pt;"><span style="color: #55ffff;">db</span><span style="color: #ffff55; font-weight: bold;">, </span><span style="color: #55ffff;">err </span><span style="color: #ffff55; font-weight: bold;">:= </span>bolt<span style="color: #ffff55; font-weight: bold;">.</span>Open<span style="color: #ff5555; font-weight: bold;">(</span><span style="color: white;">"my.db"</span><span style="color: #ffff55; font-weight: bold;">, </span><span style="color: #ff55ff;">0644</span><span style="color: #ffff55; font-weight: bold;">, &</span>bolt<span style="color: #ffff55; font-weight: bold;">.</span><span style="color: #f0ed11; font-weight: bold;">Options</span><span style="color: #ff5555; font-weight: bold;">{</span><span style="color: #55ffff;">Timeout</span>: <span style="color: #ff55ff;">10 </span><span style="color: #ffff55; font-weight: bold;">* </span>time<span style="color: #ffff55; font-weight: bold;">.</span><span style="color: #8251bb;">Second</span><span style="color: #ff5555; font-weight: bold;">})</span></pre>
<pre style="font-family: Menlo; font-size: 9pt;">check<span style="color: #ff5555; font-weight: bold;">(</span><span style="color: #55ffff;">err</span><span style="color: #ff5555; font-weight: bold;">)</span><span style="color: #55ff55; font-weight: bold;">
</span></pre>
<pre style="font-family: Menlo; font-size: 9pt;"><span style="color: #55ff55; font-weight: bold;">defer </span><span style="color: #55ffff;">db</span><span style="color: #ffff55; font-weight: bold;">.</span>Close<span style="color: #ff5555; font-weight: bold;">()</span><span style="color: #ff5555; font-weight: bold;">
</span>addRecord<span style="color: #ff5555; font-weight: bold;">(</span><span style="color: #55ffff;">db</span><span style="color: #ffff55; font-weight: bold;">, </span><span style="color: #ff5555; font-weight: bold;">[]</span><span style="font-weight: bold;">byte</span><span style="color: #ff5555; font-weight: bold;">(</span><span style="color: white;">"start"</span><span style="color: #ff5555; font-weight: bold;">)</span><span style="color: #ffff55; font-weight: bold;">, </span><span style="color: white;">"starting"</span><span style="color: #ff5555; font-weight: bold;">) </span><span style="color: #55ffff;">// create bucket when it doesn't exist</span></pre>
</pre>
<br />
<br />
The function addRecord takes 3 arguments; db - the boltdb struct, key a byte array and a value which can be anything, in our case, TableRow the struct above. The function is lower case so it is not "public". The interface v is marshaled into a byte array and stored in boltdb after it checks that the bucket is created. Finally, the addRecord function returns an error if an error occurred.<br />
<br />
<pre style="background-color: black; color: #bbbbbb; font-family: 'Menlo'; font-size: 9.0pt;"><span style="color: #55ff55; font-weight: bold;">func </span>addRecord<span style="color: #ff5555; font-weight: bold;">(</span><span style="color: #55ffff;">db </span><span style="color: #ffff55; font-weight: bold;">*</span>bolt<span style="color: #ffff55; font-weight: bold;">.</span><span style="color: #f0ed11; font-weight: bold;">DB</span><span style="color: #ffff55; font-weight: bold;">, </span><span style="color: #55ffff;">key </span><span style="color: #ff5555; font-weight: bold;">[]</span><span style="font-weight: bold;">byte</span><span style="color: #ffff55; font-weight: bold;">, </span><span style="color: #55ffff;">v </span><span style="color: #55ff55; font-weight: bold;">interface</span><span style="color: #ff5555; font-weight: bold;">{}) </span><span style="font-weight: bold;">error </span><span style="color: #ff5555; font-weight: bold;">{</span><span style="color: #ff5555; font-weight: bold;">
</span><span style="color: #ff5555; font-weight: bold;"> </span><span style="color: #55ffff;">value</span><span style="color: #ffff55; font-weight: bold;">, </span><span style="color: #55ffff;">err </span><span style="color: #ffff55; font-weight: bold;">:= </span>json<span style="color: #ffff55; font-weight: bold;">.</span>Marshal<span style="color: #ff5555; font-weight: bold;">(</span><span style="color: #55ffff;">v</span><span style="color: #ff5555; font-weight: bold;">)</span><span style="color: #ff5555; font-weight: bold;">
</span><span style="color: #ff5555; font-weight: bold;"> </span>check<span style="color: #ff5555; font-weight: bold;">(</span><span style="color: #55ffff;">err</span><span style="color: #ff5555; font-weight: bold;">)</span><span style="color: #ff5555; font-weight: bold;">
</span><span style="color: #ff5555; font-weight: bold;"> </span><span style="color: #55ff55; font-weight: bold;">return </span><span style="color: #55ffff;">db</span><span style="color: #ffff55; font-weight: bold;">.</span>Update<span style="color: #ff5555; font-weight: bold;">(</span><span style="color: #55ff55; font-weight: bold;">func</span><span style="color: #ff5555; font-weight: bold;">(</span><span style="color: #55ffff;">tx </span><span style="color: #ffff55; font-weight: bold;">*</span>bolt<span style="color: #ffff55; font-weight: bold;">.</span><span style="color: #f0ed11; font-weight: bold;">Tx</span><span style="color: #ff5555; font-weight: bold;">) </span><span style="font-weight: bold;">error </span><span style="color: #ff5555; font-weight: bold;">{</span><span style="color: #ff5555; font-weight: bold;"> </span></pre>
<pre style="background-color: black; color: #bbbbbb; font-family: 'Menlo'; font-size: 9.0pt;"><span style="color: #55ffff;"> bkt</span><span style="color: #ffff55; font-weight: bold;">, </span><span style="color: #55ffff;">err </span><span style="color: #ffff55; font-weight: bold;">:= </span><span style="color: #55ffff;">tx</span><span style="color: #ffff55; font-weight: bold;">.</span>CreateBucketIfNotExists<span style="color: #ff5555; font-weight: bold;">([]</span><span style="font-weight: bold;">byte</span><span style="color: #ff5555; font-weight: bold;">(</span><span style="color: #8251bb;">bucket</span><span style="color: #ff5555; font-weight: bold;">))</span></pre>
<pre style="background-color: black; color: #bbbbbb; font-family: 'Menlo'; font-size: 9.0pt;"><span style="color: #ff5555; font-weight: bold;"> </span></pre>
<pre style="background-color: black; color: #bbbbbb; font-family: 'Menlo'; font-size: 9.0pt;"><span style="color: #55ff55; font-weight: bold;"> if </span><span style="color: #55ffff;">err </span><span style="color: #ffff55; font-weight: bold;">!= </span><span style="font-weight: bold;">nil </span><span style="color: #ff5555; font-weight: bold;">{</span><span style="color: #ff5555; font-weight: bold;"> </span></pre>
<pre style="background-color: black; color: #bbbbbb; font-family: 'Menlo'; font-size: 9.0pt;"><span style="color: #55ff55; font-weight: bold;"> return </span><span style="color: #55ffff;">err</span><span style="color: #55ffff;"> </span></pre>
<pre style="background-color: black; color: #bbbbbb; font-family: 'Menlo'; font-size: 9.0pt;"><span style="color: #ff5555; font-weight: bold;"> }</span><span style="color: #ff5555; font-weight: bold;"> </span></pre>
<pre style="background-color: black; color: #bbbbbb; font-family: 'Menlo'; font-size: 9.0pt;"><span style="color: #ff5555; font-weight: bold;"> </span></pre>
<pre style="background-color: black; color: #bbbbbb; font-family: 'Menlo'; font-size: 9.0pt;"> fmt<span style="color: #ffff55; font-weight: bold;">.</span>Printf<span style="color: #ff5555; font-weight: bold;">(</span><span style="color: white;">"Adding KEY %s\n"</span><span style="color: #ffff55; font-weight: bold;">, </span><span style="color: #55ffff;">key</span><span style="color: #ff5555; font-weight: bold;">)</span><span style="color: #ff5555; font-weight: bold;"> </span></pre>
<pre style="background-color: black; color: #bbbbbb; font-family: 'Menlo'; font-size: 9.0pt;"><span style="color: #55ff55; font-weight: bold;"> return </span><span style="color: #55ffff;">bkt</span><span style="color: #ffff55; font-weight: bold;">.</span>Put<span style="color: #ff5555; font-weight: bold;">(</span><span style="color: #55ffff;">key</span><span style="color: #ffff55; font-weight: bold;">, </span><span style="color: #55ffff;">value</span><span style="color: #ff5555; font-weight: bold;">)</span><span style="color: #ff5555; font-weight: bold;"> </span></pre>
<pre style="background-color: black; color: #bbbbbb; font-family: 'Menlo'; font-size: 9.0pt;"><span style="color: #ff5555; font-weight: bold;"> })</span></pre>
<pre style="background-color: black; color: #bbbbbb; font-family: 'Menlo'; font-size: 9.0pt;"><span style="color: #ff5555; font-weight: bold;">}</span></pre>
<br />
<br />
To get a TableRow out of the database a read transaction is performed in BoltDB. This method is capitalized so it is a package public method. GetRecord returns a table row or panics if an error occurred.<br />
<br />
<pre style="background-color: black; color: #bbbbbb; font-family: 'Menlo'; font-size: 9.0pt;"><span style="color: #55ff55; font-weight: bold;">func </span>GetRecord<span style="color: #ff5555; font-weight: bold;">(</span><span style="color: #55ffff;">db </span><span style="color: #ffff55; font-weight: bold;">*</span>bolt<span style="color: #ffff55; font-weight: bold;">.</span><span style="color: #f0ed11; font-weight: bold;">DB</span><span style="color: #ffff55; font-weight: bold;">, </span><span style="color: #55ffff;">key </span><span style="font-weight: bold;">string</span><span style="color: #ff5555; font-weight: bold;">) </span><span style="color: #ffff55; font-weight: bold;">*</span><span style="color: #f0ed11; font-weight: bold;">TableRow </span><span style="color: #ff5555; font-weight: bold;">{</span><span style="color: #ff5555; font-weight: bold;">
</span><span style="color: #ff5555; font-weight: bold;"> </span><span style="color: #55ffff;">row </span><span style="color: #ffff55; font-weight: bold;">:= </span><span style="color: #f0ed11; font-weight: bold;">TableRow</span><span style="color: #ff5555; font-weight: bold;">{}</span><span style="color: #ff5555; font-weight: bold;"> </span><span style="color: #55ffff;">err </span><span style="color: #ffff55; font-weight: bold;">:= </span><span style="color: #55ffff;">db</span><span style="color: #ffff55; font-weight: bold;">.</span>View<span style="color: #ff5555; font-weight: bold;">(</span><span style="color: #55ff55; font-weight: bold;">func</span><span style="color: #ff5555; font-weight: bold;">(</span><span style="color: #55ffff;">tx </span><span style="color: #ffff55; font-weight: bold;">*</span>bolt<span style="color: #ffff55; font-weight: bold;">.</span><span style="color: #f0ed11; font-weight: bold;">Tx</span><span style="color: #ff5555; font-weight: bold;">) (</span><span style="font-weight: bold;">error</span><span style="color: #ff5555; font-weight: bold;">) {</span><span style="color: #ff5555; font-weight: bold;">
</span><span style="color: #ff5555; font-weight: bold;"> </span><span style="color: #55ffff;">bkt </span><span style="color: #ffff55; font-weight: bold;">:= </span><span style="color: #55ffff;">tx</span><span style="color: #ffff55; font-weight: bold;">.</span>Bucket<span style="color: #ff5555; font-weight: bold;">([]</span><span style="font-weight: bold;">byte</span><span style="color: #ff5555; font-weight: bold;">(</span><span style="color: #8251bb;">bucket</span><span style="color: #ff5555; font-weight: bold;">))</span><span style="color: #ff5555; font-weight: bold;"> </span></pre>
<pre style="background-color: black; color: #bbbbbb; font-family: 'Menlo'; font-size: 9.0pt;"><span style="color: #55ff55; font-weight: bold;"> if </span><span style="color: #55ffff;">bkt </span><span style="color: #ffff55; font-weight: bold;">== </span><span style="font-weight: bold;">nil </span><span style="color: #ff5555; font-weight: bold;">{</span><span style="color: #ff5555; font-weight: bold;"> </span></pre>
<pre style="background-color: black; color: #bbbbbb; font-family: 'Menlo'; font-size: 9.0pt;"><span style="color: #55ff55; font-weight: bold;"> return </span>fmt<span style="color: #ffff55; font-weight: bold;">.</span>Errorf<span style="color: #ff5555; font-weight: bold;">(</span><span style="color: white;">"Bucket %q not found!\n"</span><span style="color: #ffff55; font-weight: bold;">, </span><span style="color: #8251bb;">bucket</span><span style="color: #ff5555; font-weight: bold;">)</span></pre>
<pre style="background-color: black; color: #bbbbbb; font-family: 'Menlo'; font-size: 9.0pt;"><span style="color: #ff5555; font-weight: bold;"> }</span></pre>
<pre style="background-color: black; color: #bbbbbb; font-family: 'Menlo'; font-size: 9.0pt;"><span style="color: #ff5555; font-weight: bold;">
</span><span style="color: #ff5555; font-weight: bold;"> </span><span style="color: #55ffff;">val </span><span style="color: #ffff55; font-weight: bold;">:= </span><span style="color: #55ffff;">bkt</span><span style="color: #ffff55; font-weight: bold;">.</span>Get<span style="color: #ff5555; font-weight: bold;">([]</span><span style="font-weight: bold;">byte</span><span style="color: #ff5555; font-weight: bold;">(</span><span style="color: #55ffff;">key</span><span style="color: #ff5555; font-weight: bold;">))</span><span style="color: #ff5555; font-weight: bold;">
</span><span style="color: #ff5555; font-weight: bold;"> </span><span style="color: #55ff55; font-weight: bold;">if </span>len<span style="color: #ff5555; font-weight: bold;">(</span><span style="color: #55ffff;">val</span><span style="color: #ff5555; font-weight: bold;">) </span><span style="color: #ffff55; font-weight: bold;">== </span><span style="color: #ff55ff;">0 </span><span style="color: #ff5555; font-weight: bold;">{</span><span style="color: #ff5555; font-weight: bold;"> </span></pre>
<pre style="background-color: black; color: #bbbbbb; font-family: 'Menlo'; font-size: 9.0pt;"> fmt<span style="color: #ffff55; font-weight: bold;">.</span>Printf<span style="color: #ff5555; font-weight: bold;">(</span><span style="color: white;">"key %s does not exist\n"</span><span style="color: #ffff55; font-weight: bold;">, </span><span style="color: #55ffff;">key</span><span style="color: #ff5555; font-weight: bold;">)</span><span style="color: #ff5555; font-weight: bold;"> </span></pre>
<pre style="background-color: black; color: #bbbbbb; font-family: 'Menlo'; font-size: 9.0pt;"><span style="color: #55ff55; font-weight: bold;"> return </span><span style="font-weight: bold;">nil</span><span style="font-weight: bold;"> </span></pre>
<pre style="background-color: black; color: #bbbbbb; font-family: 'Menlo'; font-size: 9.0pt;"><span style="color: #ff5555; font-weight: bold;"> }</span><span style="color: #ff5555; font-weight: bold;">
</span><span style="color: #ff5555; font-weight: bold;"> </span><span style="color: #55ffff;">err </span><span style="color: #ffff55; font-weight: bold;">:= </span>json<span style="color: #ffff55; font-weight: bold;">.</span>Unmarshal<span style="color: #ff5555; font-weight: bold;">(</span><span style="color: #55ffff;">val</span><span style="color: #ffff55; font-weight: bold;">, &</span><span style="color: #55ffff;">row</span><span style="color: #ff5555; font-weight: bold;">)</span><span style="color: #ff5555; font-weight: bold;">
</span><span style="color: #ff5555; font-weight: bold;"> </span><span style="color: #55ff55; font-weight: bold;">return </span><span style="color: #55ffff;">err</span><span style="color: #55ffff;">
</span><span style="color: #55ffff;"> </span><span style="color: #ff5555; font-weight: bold;">})</span><span style="color: #ff5555; font-weight: bold;">
</span><span style="color: #ff5555; font-weight: bold;"> </span>check<span style="color: #ff5555; font-weight: bold;">(</span><span style="color: #55ffff;">err</span><span style="color: #ff5555; font-weight: bold;">)</span><span style="color: #ff5555; font-weight: bold;">
</span><span style="color: #ff5555; font-weight: bold;"> </span><span style="color: #55ff55; font-weight: bold;">return </span><span style="color: #ffff55; font-weight: bold;">&</span><span style="color: #55ffff;">row</span><span style="color: #55ffff;">
</span><span style="color: #ff5555; font-weight: bold;">}</span></pre>
<br />
<br />
<br />
<br />
<br />
Calling this function returns a TableRow reference. There are no real pointers in go but I conceptualize this internally as a pointer.<br />
<br />
This is it. This is all there really is to BoltDB. Read Transactions, Write Transactions that are concurrency-safe. You can even run the Unix command <b>strings</b> on the database file so see if you stored the data correctly as a sanity check and you should see json from the output (if that is your serializer).<br />
<br />
In conclusion, BoltDB is fast, so far safe and does exactly what I need. Store State, without expecting an external DB. Embedded databases are awesome and go is awesome. Give it a try.<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />Dathan Pattishallhttp://www.blogger.com/profile/00356367514107959723noreply@blogger.com0tag:blogger.com,1999:blog-31421954.post-17665142666610181132016-11-22T12:30:00.002-08:002017-12-14T20:11:38.412-08:00INNODB Tablespace Copy in Go LangI just uploaded a quick tool that I think you will find useful if you need to consolidate, expand innodb databases if tablespaces are in use.<br />
<div>
<br />
This golang application will copy an entire innodb database from one server to another server via scp.<br />
innodb-tablespace-copy follows the algorithm described <a href="http://dev.mysql.com/doc/refman/5.6/en/innodb-transportable-tablespace-examples.html" target="_blank">here</a>. This golang application copies 4 tables in parallel after setting up the remote environment. Then in parallel import the tablespaces. I've only used this application on Percona XTRADB 5.6 but it should work for all flavors of innodb that are out there.<br />
<br /></div>
<div>
Note to recover from interruption, this is done manually either by discarding the tablespace or by dropping the remote database.<br />
<br />
Feel free to add to it and make it better :)</div>
<div>
<br />
<div>
<a href="https://github.com/dathan/innodb-tablespace-copy" target="_blank">https://github.com/dathan/innodb-tablespace-copy</a></div>
</div>
Dathan Pattishallhttp://www.blogger.com/profile/00356367514107959723noreply@blogger.com0tag:blogger.com,1999:blog-31421954.post-39746031836834643922016-08-10T19:03:00.001-07:002016-08-10T19:04:36.601-07:00Tech Stack at Shots Quick Post<div>
The Shots APP we use the following technology to serve many millions of Photos, Videos and Cached Links.</div>
<div>
<br /></div>
<div>
LAMP</div>
<div>
<br /></div>
<div>
RedHat Enterprise 6 on the Front ends and DBs. Amazon Linux (Centos) on Elastic Search and Go servers</div>
<div>
Apache 2+</div>
<div>
Percona 5.6 XTRADB with some minor custom stuff (sharded)</div>
<div>
PHP</div>
<div>
<br /></div>
<div>
we have a little bit of Python, JAVA and a lot of GO!</div>
<div>
<br /></div>
<div>
<br /></div>
<div>
One of the current features which has wildly been successful is sharing links on mobile, which is very hard. Mobile is not built for links but fortunately Instagram and YouTube are. To make this work; we have the client read from the clipboard. The client makes a call home where the link is sent to a distributed worker system which fetches the content of the HTML page, finds the media, manipulates the media and then distributes the media on our CDN. Links only last for a few days.</div>
<div>
<br /></div>
<div>
This is like a poor man's <a href="https://www.ampproject.org/" target="_blank">AMP</a> and only took us a few days to write. We even retranscode videos to make sure the format fits our timeline and doesn't hog up to much bandwidth.</div>
<div>
<br /></div>
<div>
MySQL keeps state so the same link is not rebuilt and everything is fronted with Redis - since redis supports pipelined commands - which is great for a feed our size. The next feed version will be a go-tao-like system.</div>
<div>
<br /></div>
<div>
All in all for 3 days of work, the system works great and scales linearly. It is near real time. Give it a try. Link a Instagram or Youtube url and you will see for yourself.</div>
<div>
<br /></div>
<div>
<br /></div>
<div>
Some things that I'd like to do in the future is use QUIC on a websocket layer. To have a non-blocking messaging system which is blazing fast and works on spotty networks and integrate ROCKSDB.</div>
<div>
<br /></div>
<div>
But that's another post.</div>
<div>
<br /></div>
<div>
<br /></div>
Dathan Pattishallhttp://www.blogger.com/profile/00356367514107959723noreply@blogger.com0tag:blogger.com,1999:blog-31421954.post-9509082428489697232015-09-28T13:29:00.000-07:002015-09-28T13:29:14.684-07:00Wish there is another String DataType called LIST but there is notI believe the future of SQL is to take a lot of primitives that are Computer Science fundamentals and add them as datatypes to expand on the allowed columns today. The idea is of the ilk of a merging of noSQL and SQL for solving problems to make it easier for a new person to develop.<br />
<br />
<br />
For instance, what would be awesome is a LIST type, where the list contains a distinct number of string items mapped to a bit, much like SET yet you don't need to predefine all the items in a set.<br />
<br />
<br />
Here is a good example as how I would use a list type:<br />
<br />
<br />
Imagine you need permissions on a per row basis. Some rows are public, some are private, some are viewable by a small set of people. (Less than 64).<br />
<br />
Let's take the example of Finding all rows that are public or are viewable by only me.<br />
<br />When creating a row<br />
<br />
INSERT INTO resource_permissions (resource_id, perm_bit, list_dt) VALUES(1, 2, "dathan, sam, fred")<br />
<br />
perm_bit is 0 private, 1 = public, 2 public to a list of people<br />
<br />
When selecting rows that I "dathan" can see<br />
<br />
SELECT resource_id FROM resource_permissions WHERE perm_bit = 1 UNION SELECT resource_id FROM resource_permissiongs WHERE perm_bit = 2 AND FIND_IN_LIST(list_dt, "dathan");<br />
<br />
<br />
What the above statement says is give me all the public resource_ids and resource_ids that I "dathan" can see.<br />
<br />
<br />
Right now I can't do this, I have to use a medium_blob and a LIKE<br />
<br />
SELECT resource_id FROM resource_permissions WHERE perm_bit = 1 UNION SELECT resource_id FROM resource_permissions WHERE perm_bit = 2 AND list_dt LIKE "%:dathan:%"<br />
<br />
<br />
As you can see I'm able to simulate the desired behavior but I can't use an index, I don't want to use a FULLTEXT_INDEX due to overhead and other issues that out of scope for this post. Nor do I want to manage UDF's or Stored procedures. The last two are not desirable yet can also simulate the behavior I am looking for.<br />
<br />
<br />
Some primitives from REDIS or other noSQL solutions would be awesome additions for SQL as a hole IMHO.<br />
<br />
<br />
My two cents.<br />
<br />
Also in 5.7 maybe the <a href="https://dev.mysql.com/doc/refman/5.7/en/json.html" target="_blank">JSON</a> Column Type might be of some use.<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />Dathan Pattishallhttp://www.blogger.com/profile/00356367514107959723noreply@blogger.com0tag:blogger.com,1999:blog-31421954.post-39959600918532851812015-09-22T17:38:00.002-07:002015-09-22T17:38:42.867-07:00Golang websockets (wss) and "OOP"Golang is awesome. My 1st Language back in 1994 was C. Then the following year my Computer Science Dept. switched from C/Pascal to C++. I even like C++ but I like C more mainly because of nostalgia.<br />
<br />
Enter Go. The Syntax for me is a mix between JSON, and C. I love it. I've created 3 new servers all doing a ton of TPS. What I would like to share with you is some GO code to that handels websockets<br />
<br />
<br />
If you are building a server using websockets, over secure TCP your browser behaves slightly differently than a client side application using a websocket library. Specifically when working with wss (secure websockets) across domains.<br />
<br />
Its up to the client to respect Origin, so a client implementing a websocket doesn't have to set the Origin Header, but your browser does. This is done on purpose and its a good thing. To get websockets to work over secure sockets, let's make our assumptions consistent and do not report Origin errors with On the fly overriding methods. The power of Go.<br />
<br />
<pre style="background-color: black; color: #d0d0d0; font-family: 'Menlo'; font-size: 12.0pt;">
<span style="color: #00e000;">// going to override the handshake</span><span style="color: #79abff;">server </span>:= websocket.<span style="color: #ff8080;">Server</span>{
Handshake:<span style="color: #00d0d0;">func</span>(<span style="color: #79abff;">config </span>*websocket.<span style="color: #ff8080;">Config</span>, <span style="color: #79abff;">req </span>*http.<span style="color: #ff8080;">Request</span>) <span style="color: #ff8080;">error </span>{
<span style="color: #00d0d0;">return </span><span style="color: #ff8080;">nil</span>;
},
Handler:websocket.<span style="color: #ff8080;">Handler</span>(<span style="color: #79abff;">nsp</span>.<span style="color: #79abff;">handle</span>),
}</pre>
<br />
above says override the method in the libararies (golang.org/x/net/websocket) with the supplied local function and return nil for error - which means all is good.<br />
<br />
Anytime that ORIGIN is sent the server (non browser clients and even the browser doesn't have to do this) ignore the origin handshake<br />
<br />
<pre style="background-color: black; color: #d0d0d0; font-family: 'Menlo'; font-size: 12.0pt;">
http.<span style="color: #bed6ff;">Handle</span>(<span style="color: #79abff;">nsp</span>.<span style="color: #79abff;">path</span>, websocket.<span style="color: #ff8080;">Handler</span>(<span style="color: #79abff;">server</span>.Handler));</pre>
<pre style="background-color: black; color: #d0d0d0; font-family: 'Menlo'; font-size: 12.0pt;">
</pre>
<br />
Next we handle the websocket with the supplied handler in the server called nsp.handle. nsp.handle is a string of a function name that takes in a websocket connection. nsp.path means for a given http connected path execute the handler.<br />
<br />
<br />
This is awesome. Everything works, but what is cooler is how GOLANG handles OOP. The term used in GOLANG is embedding, and changing the type or executed method (method overriding), thats called Shadowing<br />
<br />
<br />
Here is an example<br />
<br />
<pre style="background-color: black; color: #d0d0d0; font-family: 'Menlo'; font-size: 12.0pt;"><span style="color: #00d0d0;">package </span>main
<span style="color: #00d0d0;">import </span><span style="color: #dc78dc;">"datarepo"</span><span style="color: #dc78dc;">
</span><span style="color: #00d0d0;">type </span><span style="color: #ff8080;">DataLayer </span><span style="color: #00d0d0;">struct </span>{
datarepo.<span style="color: #ff8080;">DataRepoAccess</span>}
<span style="color: #00e000;">//</span><span style="color: #00e000;">//https://github.com/luciotato/golang-notes/blob/master/OOP.md#golang-embedding-is-akin-to-multiple-inheritance-with-non-virtual-methods</span><span style="color: #00e000;">//</span><span style="color: #00d0d0;">func </span><span style="color: #bed6ff;">NewDataLayer</span>(<span style="color: #79abff;">subject </span><span style="color: #ff8080;">string</span>, <span style="color: #79abff;">class </span><span style="color: #ff8080;">string </span>) <span style="color: #ff8080;">DataLayer </span>{
<span style="color: #79abff;">ret </span>:= <span style="color: #ff8080;">DataLayer</span>{ datarepo.<span style="color: #ff8080;">DataRepoAccess</span>{Subject: <span style="color: #79abff;">subject</span>, Classof: <span style="color: #79abff;">class</span>}}
<span style="color: #79abff;">ret</span>.<span style="color: #bed6ff;">New</span>();
<span style="color: #00d0d0;">return </span><span style="color: #79abff;">ret</span>;
}
<span style="color: #00e000;">//</span><span style="color: #00e000;">// wrapper method to add in an counter</span><span style="color: #00e000;">//</span><span style="color: #00d0d0;">func</span><span style="color: #79abff;">(dl *DataLayer) </span><span style="color: #bed6ff;">Execute</span>() ([]<span style="color: #ff8080;">byte</span>, <span style="color: #ff8080;">error</span>){ <span style="color: #00e000;">// shadowed</span><span style="color: #00e000;"> </span><span style="color: #79abff;">Reporter</span>.<span style="color: #bed6ff;">increment</span>(<span style="color: #dc78dc;">"api_layer_cmd"</span>, <span style="color: yellow;">1</span>)
<span style="color: #00d0d0;">var </span><span style="color: #79abff;">base </span>= <span style="color: #79abff;">dl</span>.DataRepoAccess;
<span style="color: #00d0d0;">return </span><span style="color: #79abff;">base</span>.<span style="color: #bed6ff;">Execute</span>()
}</pre>
<br />
<br />
DataLayer is a Wrapper Design Pattern Around datarepo.DataRepoAccess a structure I wrote that handles talking to the backend. datarepo.DataRepoAccess has a method called Execute. In the example above Execute is "Shadowed" or overridden. This new method counts the number of times the base class is called.<br />
<br />
<br />
These months of coding go has been so much fun. I love learning new things but also getting my work done on time. Go enables me to do both. The analogy that I can compare learning go to is like learning to SnowBoard. In the beginning it's like getting your ass smacked with a cold wet shovel but once you get it you got it.<br />
<br />Dathan Pattishallhttp://www.blogger.com/profile/00356367514107959723noreply@blogger.com0tag:blogger.com,1999:blog-31421954.post-33404222757797348102015-08-05T10:51:00.001-07:002015-08-26T16:26:07.018-07:00San Francisco mySQL Meetup August 26 2015<h1 itemprop="name" style="background-color: white; color: rgba(0, 0, 0, 0.952941); font-family: Whitney, helvetica, arial, sans-serif; font-size: 2.375rem; letter-spacing: -0.75px; line-height: 1.1; margin: 0px; padding: 0px 0px 9px;">
<a href="http://www.meetup.com/sf-mysql/events/224404078/" target="_blank">Shots Architecture and how we handle extreme load spikes</a></h1>
<div>
<br /></div>
<div>
I invite you to come out and join me in a talk about the above heading. I will describe many things and walk through the cases of what technology is used, where, why and how. The event information is located <a href="http://www.meetup.com/sf-mysql/events/224404078/" target="_blank">here</a>. I'll also touch on, how cost is reduced, how we handle celebrity's load when they promote and what's next to make the system even more automatic and solid.</div>
<div>
<br /></div>
<div>
Thanks for sfmysql.org for all the work they do and for allowing me to give a talk.</div>
<div>
<br /></div>
Dathan Pattishallhttp://www.blogger.com/profile/00356367514107959723noreply@blogger.com0tag:blogger.com,1999:blog-31421954.post-1761597625859429972015-06-01T19:19:00.002-07:002015-06-01T19:19:27.454-07:00Reporting Across ShardsIf you have chosen to split your data across boxes, and architected your app to not query across boxes there is still a case where you will need to. Data mining, reports and data health checks require hitting all servers at some point. The case I am going over is sessions and figuring out the Session Length without taking averages of averages which is wrong.<br />
<div>
<br /></div>
<div>
<br /></div>
<div>
Let's assume you have a session table of the following</div>
<div>
<br /></div>
<pre>mysql> describe sessions;
+----------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| user_id | bigint(20) unsigned | NO | PRI | 0 | |
| added_ms | bigint(20) unsigned | NO | PRI | 0 | |
| appVer | varchar(8) | YES | | NULL | |
| device | bigint(20) unsigned | YES | MUL | NULL | |
| start | int(10) unsigned | NO | MUL | NULL | |
| stop | int(10) unsigned | NO | | NULL | |
+----------+---------------------+------+-----+---------+-------+
</pre>
<pre>
</pre>
<pre><div>
<div style="font-family: Times; white-space: normal;">
The data is federated (distributed) by user_id. This table exists across 1000s of servers. How do you get the average session length for the month of May?</div>
<div style="font-family: Times; white-space: normal;">
</div>
<br />
<ul>
<li><span style="font-family: Times;"><span style="white-space: normal;">The question already scopes the process to hit every single server</span></span></li>
<li><span style="font-family: Times;"><span style="white-space: normal;">Second we can't just take AVG((stop-start)) and then sum and divide that by the number of shards</span></span></li>
<li><span style="font-family: Times;"><span style="white-space: normal;">We can't pull all the data in memory</span></span></li>
<li><span style="font-family: Times;"><span style="white-space: normal;">We don't want to have to pull the data and upload it to BigQuery or Amazon RedShift</span></span></li>
<li><span style="font-family: Times;"><span style="white-space: normal;">We want a daily report at some point</span></span></li>
</ul>
<div>
<span style="font-family: Times;"><span style="white-space: normal;">
</span></span></div>
<div>
</div>
<div>
<span style="font-family: Times;"><span style="white-space: normal;">SELECT SUM((stop-start)) as sess_diff, count(*) as sess_sample FROM sessions WHERE start BETWEEN $start AND $stop AND stop>start</span></span></div>
<div>
<span style="font-family: Times;"><span style="white-space: normal;">
</span></span></div>
<div>
<span style="font-family: Times;"><span style="white-space: normal;">The above SQL statement says for the connection to a single server give me the sum of the session delta and count the corresponding rows in the set. In this case the SUM of SUMs (sum of session_delta) is the numerator and the sum of sess_sample is the denominator.</span></span></div>
<div>
<span style="font-family: Times;"><span style="white-space: normal;">
</span></span></div>
<div>
<span style="font-family: Times;"><span style="white-space: normal;">Now do this across all servers and finally write some client code to take a few rows < 1000 to report the number.</span></span></div>
<div>
</div>
<div>
</div>
<div>
</div>
<br />
<pre style="background-color: black; color: #d0d0d0; font-family: 'Menlo'; font-size: 12.0pt;"><span style="color: #79abff;">$total </span>= <span style="color: yellow;">0</span>;
<span style="color: #79abff;">$sessions_diff </span>= <span style="color: yellow;">0</span>;
<span style="color: #00d0d0;">foreach </span>(<span style="color: #79abff;">$rows </span><span style="color: #00d0d0;">as </span><span style="color: #79abff;">$shard_id </span>=> <span style="color: #79abff;">$result</span>) {
<span style="color: #79abff;">$sessions_diff </span>= \<span style="color: yellow;">bcadd</span>(<span style="color: #79abff;">$sessions_diff</span>, <span style="color: #79abff;">$result</span>[<span style="color: yellow;">0</span>][<span style="color: #dc78dc;">'sess_diff'</span>]);
<span style="color: #79abff;">$total </span>= \<span style="color: yellow;">bcadd</span>(<span style="color: #79abff;">$total</span>, <span style="color: #79abff;">$result</span>[<span style="color: yellow;">0</span>][<span style="color: #dc78dc;">'sess_sample'</span>]);
}</pre>
</div>
<div style="font-family: Times; white-space: normal;">
</div>
</pre>
<pre>
</pre>
<pre>
</pre>
<pre>Now the session_avg = sessions_diff/total</pre>
<pre>
</pre>
<pre>Tada a query that can take hours if done on a traditional mining server is done in ms.</pre>
<pre>
</pre>
<pre>
</pre>
Dathan Pattishallhttp://www.blogger.com/profile/00356367514107959723noreply@blogger.com0tag:blogger.com,1999:blog-31421954.post-40924762055735669592015-04-01T17:09:00.001-07:002015-04-01T17:09:28.791-07:00Federating THE friends table in a Sharded mySQL environment without downtime or users noticing<br />
A friends table is the cornerstone of social applications. Its purpose is to define relationships and help answer the question what are my friends doing.<br />
<br />
Here is an example friend’s table:<br />
<br />
<code>
CREATE TABLE `friends` (<br />
`user_id` bigint(20) unsigned NOT NULL,<br />
`friend_id` bigint(20) unsigned NOT NULL,<br />
`auto_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,<br />
PRIMARY KEY (`user_id`,`friend_id`),<br />
KEY `user_id-auto_ts` (`user_id`,`auto_ts`),<br />
KEY `friend_id-auto_ts` (`friend_id`,`auto_ts`)<br />
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci<br />
</code>
<br />
<code><br /></code>
With the table above we can get a list of user_ids a user follows (following), or a list of people who follow said user (followers), or get a list of mutual follows. This is a very simple table structure yet very powerful.<br />
<br />
The problem is this table doesn't scale on a single server, when you have millions of users, each user has many friends, all users are semi to deeply connected the table becomes a problem. Mix this with a huge request rate, with lots of concurrency a single server just doesn't scale.<br />
<br />
One can replicate the friends table but what starts to cause lag is when many users start adding or removing friends at once. So, how can we distribute this table across many servers holding a small % of the friend graph?<br />
<br />
Let's look at the friends table. It defines whom a user follows and who follows the user ordered by insertion time.<br />
<br />
Let's create two tables:<br />
<br />
<code>
CREATE TABLE `following` (<br />
`user_id` bigint(20) unsigned NOT NULL DEFAULT '0',<br />
`friend_id` bigint(20) unsigned NOT NULL DEFAULT '0',<br />
`mutual` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT 'Flag to denote mutual connections',<br />
`auto_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,<br />
PRIMARY KEY (`user_id`,`friend_id`),<br />
KEY `user_id-auto_ts` (`user_id`,`auto_ts`)<br />
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8<br />
</code>
<br />
<code><br /></code>
<code>
CREATE TABLE `followers` (<br />
`user_id` bigint(20) unsigned NOT NULL DEFAULT '0',<br />
`friend_id` bigint(20) unsigned NOT NULL DEFAULT '0',<br />
`mutual` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT 'Flag to denote mutual connections',<br />
`auto_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,<br />
PRIMARY KEY (`user_id`,`friend_id`),<br />
KEY `friend_id-auto_ts` (`friend_id`,`auto_ts`)<br />
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8<br />
</code>
<br />
<br />
The 'following' table defines whom a said user follows. The table is federated by the user_id so this table exists on the user_id's shard.<br />
<br />
The 'followers' table fines that is following the said user. On every follow instead of writing one row, we now write two rows. One write on the following user's shard. One write on the followed users shard. Thus the followers table is federated by friend_id.<br />
<br />
This can be best described by an example on reads:<br />
<br />
How many people am I user_id 3306 following?<br />
<br />
Connect to my Shard-x, execute the query<br />
<code><br /></code>
<code>
SELECT COUNT(*) FROM following WHERE user_id = 3306<br />
</code>
<br />
<br />
How many people are following me (user_id 3306)<br />
<br />
Connect to my Shard-x, execute the following query<br />
<br />
<code>
SELECT COUNT(*) FROM followers WHERE user_id = 3306<br />
</code>
<br />
<br />
Now let's look at a write, of me (user_id:3306) following friend_id:11211<br />
<br />
3306 is on Shard-x<br />
11211 is on Shard-y<br />
<br />
So, 1st we write to the fact that 3306 is following 11211. We connect to Shard-x and execute the transaction<br />
<br />
<code>
BEGIN<br />
INSERT INTO following (user_id, friend_id, mutual, auto_ts) VALUES(3306, 11211, 0, NOW());<br />
// DO NOT COMMIT YET<br />
<br />
<br />
Now connect to Shard-y to write the followers row. If the connection fails rollback the transaction on 3306's Shard-x, otherwise<br />
<br />
BEGIN<br />
INSERT INTO followers (user_id, friend_id, mutual, auto_ts) VALUES(3306, 11211, 0, NOW());<br />
if affected rows == 1 (no error)<br />
COMMIT on Shard-x<br />
COMMIT on Shard-y<br />
</code>
<br />
<br />
Now we can answer the main questions.<br />
<br />
<br />
But what about something like. Give me my friends photos sorted by last upload time 10 at a time?<br />
<br />
Well here is the magic sauce. We are going to do a FANOUT reads and hit all the shards, which my friends are on. For my environment this is much better than a FANOUT of writes, since we like to customize in real-time the feed as well as duplicating the data 10000s of times becomes very expensive quickly as servers start turning cold. We can go into this topic a bit more in another post.<br />
<br />
<br />
Now I execute the query across from friends shards<br />
<br />
<code>
SELECT p.id FROM photos p JOIN followers f ON(f.friend_id=p.user_id) WHERE f.user_id = 3306 ORDER BY p.id DESC LIMIT 10;<br />
</code>
<br />
If I have a 1000 friends and 100 shards, each friend has 10 photos I am going to get back 1000 rows.<br />
<br />
But the Order is not what I am going to display because I want to display the latest 10 photos. Thus I will need to sort in memory on the application server and take a slice of the results.<br />
<br />
<br />
But what if I want the 2nd page?<br />
<code>
SELECT p.id FROM photos p JOIN followers f ON (f.friend_id=p.user_id) WHERE f.user_id = 3306 p.id < [LAST_ID_FROM_FIRST_PAGE] ORDER BY p.id DESC LIMIT 10
</code>
<br />
<br />
In the application we pass the last_id from the 1st page and execute the same FANOUT on reads again do the same logic and return the photos.<br />
<br />
Your questions might be, but isn't this slow because people with large networks will have to hit every shard each time and you have to loop - execute - read on each connection?<br />
<br />
This can be mitigated with memory, pipelining and parallel SQL execution.<br />
<br />
If you're social graph is like twitter where all active users follows 100K users and the feed doesn't change dynamically writing the data to each shard may be for you. But, again this is out of scope for this post.<br />
<br />
What about answering the question mutual connections?<br />
<br />
On ever write of a friend relationship, do a select to see if the followed person follows the follower. Then mark the row on both shards as mutual.<br />
<br />
For all my personal cases, this distributed friends table solves all my needs. Lots of friend writes from importing friends from say an address book or email or other social network friend graph and a large concurrency is not going to affect me SINCE the table has been removed from a Single Point and is now distributed across many servers.<br />
<br />
Reads are fast because only a % of data is on each shard, 90% of the queries hit only that shard for a given user.<br />
<br />
Feed type queries are fast because the SQL is executed in parallel if we have to go to the SQL Layer. Most data is cached, reducing the need to FANOUT on reads.<br />
<div>
<br />
Finally federating without downtime or users notices requires a backfill script and writes to the old friends table as well as writes to the new friend tables. Once this is done, fix all the queries to use the new format. Then sit back and feel good that good work was done :)<br />
<br /></div>
Dathan Pattishallhttp://www.blogger.com/profile/00356367514107959723noreply@blogger.com0tag:blogger.com,1999:blog-31421954.post-53821383607045499012015-03-12T17:04:00.002-07:002015-03-12T17:04:47.612-07:00Long time since an update but great stuff coming alongSo, its been a long time since I contributed anything to my blog. That will end very soon. Things coming up is writing about the architecture of Shots, Shard optimizations, Data Organization and Grouping, Java, Golang and some cool other stuff. Also how to handle Justin Biebers traffic, which is INSANE.<br />
<br />
<br />
In the meantime if you live in the San Francisco California Bay Area, you want to work with the coolest founders on the planet, make a big difference in peoples lives, know mySQL / redis / memcache / Some C style language or want to learn contact me. I have a great job for you!<br />
<br />
<br />
<br />
<br />Dathan Pattishallhttp://www.blogger.com/profile/00356367514107959723noreply@blogger.com0tag:blogger.com,1999:blog-31421954.post-66332537552216146862014-07-10T14:41:00.001-07:002014-07-10T14:41:24.865-07:00Manually Switch Slaves to new Masters in mySQL 5.6 (XTRADB 5.6)I'm really excited about <a href="http://dev.mysql.com/doc/mysql-utilities/1.4/en/fabric.html" target="_blank">Fabric</a> which was recently announced. Everything it does has been a variety of scripts for me or manual tasks, but before I can integrate Fabric into my system I must know more about it. When dealing with live-data and moving servers around I still do things manually just because it makes me feel better to know that if data is lost, I was the cause for doing something dumb. Basically I need to know everything about Fabric including line by line execution until I will deploy it.<br />
<div>
<br /></div>
<div>
<br /></div>
<div>
Here are my steps for switching and replacing a Shard Slave.</div>
<div>
<br /></div>
<div>
Imagine having a setup in the following Config.</div>
<div>
<br /></div>
<div>
Shard Server <span style="background-color: white; color: #333333; font-family: 'Helvetica Neue', Helvetica, Arial, sans-serif; font-size: 14px; line-height: 20px;">10.0.30.123</span> - this is the master endpoint<br />
<br /></div>
<div>
The Global Shard which holds Friend Info to join against is </div>
<div>
<br />
10.0.1.1</div>
<div>
<br /></div>
<div>
<span style="background-color: white; color: #333333; font-family: 'Helvetica Neue', Helvetica, Arial, sans-serif; font-size: 14px; line-height: 20px;">10.0.30.123</span> --- replicates from ---> 10.0.1.1</div>
<div>
<br /></div>
<div>
Now the Shard Server</div>
<div>
<span style="background-color: white; color: #333333; font-family: 'Helvetica Neue', Helvetica, Arial, sans-serif; font-size: 14px; line-height: 20px;">10.0.30.123 </span>has 3 slaves, thus <span style="background-color: white; color: #333333; font-family: 'Helvetica Neue', Helvetica, Arial, sans-serif; font-size: 14px; line-height: 20px;">10.0.30.123</span> is set up to <a href="http://dev.mysql.com/doc/refman/5.6/en/replication-options-slave.html#option_mysqld_log-slave-updates" target="_blank">log-slave-updates</a></div>
<div>
The 3 slaves are 10.0.18.78, 10.0.22.76, 10.0.22.77 and I want to make 10.0.22.76 the new master for the said Shard with 10.0.22.77 as its slave. So, what I have is</div>
<div>
<br /></div>
<div>
3 slaves --- replicates from ---> <span style="background-color: white; color: #333333; font-family: 'Helvetica Neue', Helvetica, Arial, sans-serif; font-size: 14px; line-height: 20px;">10.0.30.123</span> --- replicates from ---> 10.0.1.1</div>
<div>
<br /></div>
<div>
what I will end up with is</div>
<div>
<br /></div>
<div>
10.0.22.77 -- replicates from ---> 10.0.22.76 ---> 10.0.1.1</div>
<div>
<br />
I am getting rid of 10.0.30.123 and 10.0.18.78<br />
<br /></div>
<div>
<br /></div>
<div>
Here are the steps.</div>
<div>
<br /></div>
<div>
Tell 10.0.22.77 and 10.0.22.76 to SLAVE UNTIL the next binary log in 10.0.123.1</div>
<div>
<ul>
<li>ssh to each box</li>
<li>STOP SLAVE (using mysql 5.6) on 10.0.22.7[6-7]</li>
<li>SHOW SLAVE STATUS\G -- get Master_Log_File : master-bin.000612</li>
<li>START SLAVE UNTIL MASTER_LOG_FILE='master-bin.000613', MASTER_LOG_POS=4</li>
</ul>
<div>
Now what I did here was tell the slaves to replicate until the next bin log is reached</div>
</div>
<div>
<div class="p1">
mysql> START SLAVE UNTIL MASTER_LOG_FILE='master-bin.000613', MASTER_LOG_POS=4;</div>
<div class="p1">
Query OK, 0 rows affected, 2 warnings (0.01 sec)</div>
<div class="p2">
<br /></div>
<div class="p1">
mysql> SHOW WARNINGS;</div>
<div class="p1">
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+</div>
<div class="p1">
| Level | Code | Message |</div>
<div class="p1">
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+</div>
<div class="p1">
| Note | 1278 | It is recommended to use --skip-slave-start when doing step-by-step replication with START SLAVE UNTIL; otherwise, you will get problems if you get an unexpected slave's mysqld restart |</div>
<div class="p1">
| Note | 1753 | UNTIL condtion is not supported in multi-threaded slave mode. Slave is started in the sequential execution mode. |</div>
<div class="p1">
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+</div>
<div class="p1">
2 rows in set (0.00 sec) // <i>notice the minor bug in the spelling :)</i></div>
<div class="p1">
<br /></div>
<div class="p1">
I also get a warning that says my<a href="http://dev.mysql.com/doc/refman/5.6/en/replication-options-slave.html#option_mysqld_slave-parallel-workers" target="_blank"> multiple SQL threads</a> are now a single one which is fine.</div>
<div class="p1">
<br /></div>
<div class="p1">
<br /></div>
<div class="p1">
My next step is to ssh to 10.0.30.123</div>
<div class="p1">
<br /></div>
<div class="p1">
</div>
<ul>
<li>FLUSH LOGS - this tells 10.0.30.123 to rotate all log files including mysql-bin.000613</li>
</ul>
<div>
Now on the slaves I wait until they stop</div>
<div>
<br /></div>
<div>
Once both stop, on 10.0.22.76 I issue RESET MASTER // I don't care about what was replicated at this point and saved already in the binlogs - I've already verified that they are in-sync with CHECKSUM TABLE</div>
<div>
<br /></div>
<div>
On 10.0.22.77 I issue the command</div>
<div>
</div>
<div>
STOP SLAVE; CHANGE MASTER TO MASTER_LOG_FILE='master-bin.000001', MASTER_LOG_POS=4, MASTER_HOST='10.0.22.76'; START SLAVE;</div>
<div>
<br /></div>
<div>
if you get an error </div>
<br />
<div class="p1">
Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.</div>
<div class="p1">
stop mysql, remove auto.cnf in your $DATADIR (/var/lib/mysql)</div>
<div class="p1">
<br /></div>
<div class="p1">
On 10.0.22.76 I issue </div>
<div class="p1">
<br /></div>
<div class="p1">
START SLAVE</div>
<div class="p1">
<br /></div>
<div class="p1">
<br /></div>
<div class="p1">
Now I wait until the SLAVE catches up to the MASTER 10.0.30.123 (remember this works because of log-slave-update)</div>
<div class="p1">
<br /></div>
<div class="p1">
<br /></div>
<div class="p1">
Next in my dbconfig.php file I change all references to 10.0.30.123 to 10.0.22.76</div>
<div class="p1">
<br /></div>
<div class="p1">
Verify everything is in sync (USE CHECKSUM TABLE ACROSS TABLES/SERVERS ) and push out the new config</div>
<div class="p1">
<br /></div>
<div class="p1">
After the push Make sure to restart all daemons or queue workers, they may cache the database config</div>
<div class="p1">
<br /></div>
<div class="p1">
Now do this all over again to make</div>
<div class="p1">
<br /></div>
<div class="p1">
10.0.22.76 replicate from 10.0.1.1</div>
<div class="p1">
<br /></div>
<div class="p1">
<br /></div>
<div class="p1">
In conclusion, this is just to manual and screams for automation. Soon it will be with Fabric which manages this process once I get around to rolling that out.</div>
<div class="p1">
<br /></div>
<div class="p1">
<br /></div>
<div class="p1">
<br /></div>
<div class="p1">
<br /></div>
<div class="p1">
<br /></div>
<div class="p1">
<br /></div>
<div class="p1">
<br /></div>
<div class="p1">
<br /></div>
<div class="p1">
<br /></div>
<div>
</div>
<div class="p1">
</div>
<div>
<br /></div>
<div>
<br /></div>
</div>
<div>
<br /></div>
<div>
<br /></div>
Dathan Pattishallhttp://www.blogger.com/profile/00356367514107959723noreply@blogger.com0tag:blogger.com,1999:blog-31421954.post-16986841427850584502014-06-03T13:26:00.001-07:002014-06-03T13:26:25.480-07:00CTO of Shots on Core Technology, Culture and Working on the greatest App in the WorldMySQL has opened a lot of avenues and opportunities for me. I am the <a href="http://www.linkedin.com/in/dathan/" target="_blank">CTO</a> of <a href="http://www.crunchbase.com/organization/shots" target="_blank">Shots</a> and I got here because I was a mySQL DBA, who can code in a variety of languages, understand data access, layout and design fast backends that scales to well over 100 million users, manage a team, give back to the community and prove myself through constant good work. Plus I've made every single mistake, so I know what not to do.<br />
<br />
At Shots we of course use Percona XTRA DB 5.6, with Memcache, Redis, ElasticSearch, HAProxy, FluentD with Logstash plugins, Ruby, PHP 5.4, Go, Java, Erlang and AWS which are managed via a custom CHEF build. We use chef server like chef solo :)<br />
<br />
In four months we grabbed over 1 million ACTIVE users all on IOS, which are mainly from US, UK, Australia, Canada and Brazil. We are able to handle Justin Bieber's traffic that is insane. Currently we have no downtime (yet, always plan for downtime). We Moved DC to AWS us-west-1, and S3 from S3-east crappy to S3 west. We grow organically, and are at the cusp of hitting our <a href="http://mysqldba.blogspot.com/2011/10/handling-hockey-stick-growth.html" target="_blank">hockey stick growth</a>, all on 12 Servers currently :)<br />
<br />
There are 4 of us. Everything described here is what I handle, yet I say "we" throughout the description of this post and this is a good segway for our culture. As a team we build for our consumers, our Shotties, a positive, bully free, app that works across all platforms to keep humans interacting with humans and not all the other cruft you find on social networks. Team and Community Focus is our culture, with the confidence to build the best App in the world.<br />
<br />
It's a photo status update or better known as a selfie app, which everyone from <a href="http://shots.me/shaq" target="_blank">Shaq</a>, <a href="http://shots.me/kingbach/p/fnmwiem5" target="_blank">King Bach</a>, <a href="http://shots.me/justinbieber/p/jg4fot7x" target="_blank">Justin Bieber</a> to <a href="http://shots.me/floydmayweather/p/iz6lqut3" target="_blank">Floyd Money Mayweather</a> and many others are using. Its cool to have these folks but we are not building a platform for just them we are building a platform for you. <a href="http://shots.me/chisami" target="_blank">For the Teens who are different and like Cosplay</a>, for the <a href="http://shots.me/itsjessconroy" target="_blank">teen that loves Bieber</a>, for the person <a href="http://shots.me/sammy/p/t1yo404o" target="_blank">who wants to remember the moment,</a> for you.<br />
<br />
I invite you all to use it. I invite you or for you to ask your friends if they would like to join us because now I am hiring an IOS and Andriod Dev. If you or they want to work for a startup, be apart of something cool with the purpose of changing the world and the way we interact with one another online, join us. The requirements are live in the Bay Area, can code and want to make a change :)<br />
<br />Dathan Pattishallhttp://www.blogger.com/profile/00356367514107959723noreply@blogger.com0tag:blogger.com,1999:blog-31421954.post-57454089796203211462013-10-28T14:33:00.000-07:002013-10-28T14:33:23.472-07:00MariaDB 10.0.4, BeanStalkD, Geographic Replication, Event Tracker for stats gathering at 60K stats a secondEvery company needs to see stats to understand how the application is performing, and how users are using the application(s). Typically a stat for most basic questions and even some advance questions can be summarized as "What is said event over time?". We call this EventTracker.<br />
<br />
To add to the complexity of generating stats, how do you get stat events from a DataCenter (DC) in Singapore, a DC in Western Europe, a DC in Oregon to a database for querying in West Virginia - near real-time? I used multisource replication, and the BLACKHOLE storage-engine to do so with MariaDB.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgDx-PqwjSaSwXvNe98Fi6XSJjA_jz84H1fL431UTE4HrtsSxPDnSpfIdOnEMf28B5Esicmrb7JX8Ab1VJL-23wRV4KNCadLPtyogruS01qziVIUiv8GpdhCvCLhbc3oh1kbJKoSA/s1600/TSDB+Layoit.gif" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="216" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgDx-PqwjSaSwXvNe98Fi6XSJjA_jz84H1fL431UTE4HrtsSxPDnSpfIdOnEMf28B5Esicmrb7JX8Ab1VJL-23wRV4KNCadLPtyogruS01qziVIUiv8GpdhCvCLhbc3oh1kbJKoSA/s320/TSDB+Layoit.gif" width="320" /></a></div>
<br />
Above is an image that shows a Webserver in some part of the world sends Events for tracking various interrupts to a BeanstalkD queue at time T in the same region. Each Region has a set of Python workers that grab events from BeanStalkD and writes the event to a local DB. Then the TSDB Database, a MariaDB 10.0.4 instance, replicates from each BlackHole StorageEngine BeanStalkD Worker server.<br />
<br />
The obvious question might be why not use OpenTSDB? The TSDB daemon couldn't handle the onslaught of stats/second. The current HBase TSDB structure is much larger compared to a compressed INNODB row for the same stat. Additionally a region may loose connectivity to another region for some time so I would need to queue in some form or another events until the network was available again. Thus the need for a home grown solution. Now back to my solution.<br />
<br />
The Structure for the event has the following DDL.<br />
<br />
Currently we are using 32 shards defined by each bigdata_# database. This allows us to scale per database and our capacity plan is not DISK IO but based on diskspace.<br />
<br />
<br />
<pre class="brush:sql">MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| bigdata_0 |
| bigdata_1 |
| bigdata_10 |
| bigdata_11 |
| bigdata_12 |
| bigdata_13 |
| bigdata_14 |
| bigdata_15 |
| bigdata_16 |
| bigdata_17 |
| bigdata_18 |
| bigdata_19 |
| bigdata_2 |
| bigdata_20 |
| bigdata_21 |
| bigdata_22 |
| bigdata_23 |
| bigdata_24 |
| bigdata_25 |
| bigdata_26 |
| bigdata_27 |
| bigdata_28 |
| bigdata_29 |
| bigdata_3 |
| bigdata_30 |
| bigdata_31 |
| bigdata_4 |
| bigdata_5 |
| bigdata_6 |
| bigdata_7 |
| bigdata_8 |
| bigdata_9 |
| information_schema |
| mysql |
| performance_schema |
+--------------------+
35 rows in set (0.16 sec)
</pre>
<br />
The database bigdata_0 is the only database that is slightly different than the rest. It has a table defined as EventTags that is not in the rest of the databases. EventTags is the map of eventId to tagName where eventId is just a numerical representation of a part of the md5 of the tagName. Each numerical representation falls into an address space that denotes the range of which database a tag should belong to. We use the EventTags table for the front-end to search for a stat to plot on a graph.<br />
<br />
<pre class="brush: sql">
CREATE TABLE `EventTags` (
`eventId` bigint(20) unsigned NOT NULL DEFAULT '0',
`tagName` varchar(255) NOT NULL DEFAULT '',
`popularity` bigint(20) unsigned NOT NULL DEFAULT '0',
`modifiedDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`eventId`,`tagName`(25)),
KEY `eventTag` (`tagName`(25))
) ENGINE=BLACKHOLE DEFAULT CHARSET=utf8
</pre>
<br />
EventDay contains the actual value of the stat combined for the last 1 minute (currently). Our granularity allows down to a second but we found seeing events for the last minute is fine. The SQL produced for events are the following.<br />
<br />
<br />
<pre class="brush: sql>
INSERT INTO EventTags (eventId, tagName, popularity) VALUES (<64-bit-int of tagName>, APP.METRICNAME:tags:key1=value1,keyN=valueN, 1) ON DUPLICATE KEY UPDATE popularity = popularity + VALUES(popularity);<br />
<br />
INSERT INTO EventDay (eventId, createDate, count) VALUES( <64-bit-in of tagName>, '2013-10-31 00:01:00', 1) ON DUPLICATE KEY UPDATE count = count + VALUES(count);<br />
</pre>
<br />
<br />
<pre class="brush: sql">CREATE TABLE `EventDay` (
`eventId` bigint(20) unsigned NOT NULL,
`createDate` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'min Blocks',
`count` bigint(20) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`eventId`,`createDate`)
) ENGINE=BLACKHOLE DEFAULT CHARSET=utf8
</pre>
<br />
MariaDB's multi-source replication then downloads the statement based binary logs from each of the workers in some part of the world and applies the SQL to a combined database that represents all regions. Note that the central database is of the same Structure BUT now the engine is Compressed INNODB with KEY BLOCK SIZE set to 8.<br />
<br />
The front-end sits on top of the central database and we record everything from a single item being sold to load on our auto-scaling web-farm. Which allows us to do some interesting plots like Items sold as a function of Load over time. I(L(t))<br />
<br />
<br />
Currently with this method we are producing 60K events a second that translates to a few thousand database updates a second across 4 replication threads (8 threads total). Keeping all data up to date from within the last minute near realtime.<br />
<br />
<br />Dathan Pattishallhttp://www.blogger.com/profile/00356367514107959723noreply@blogger.com0tag:blogger.com,1999:blog-31421954.post-75747605694271011412013-07-07T13:38:00.002-07:002013-07-17T11:51:43.342-07:00Speaking at RAMP: Scale Patterns and handling exponential growth without downtimeI will be in Budapest talking about Scale and Rapid Growth. I will start off with Flickr's Five minute conversation to take a direction on how to scale the backend to getting 90 million users in 3 weeks after going Viral.<br />
<br />
<a href="http://rampconf.com/main.html#schedule">http://rampconf.com/main.html#schedule</a><br />
<br />
<br />
<span style="background-color: white; color: #454545; font-family: arial, sans-serif; font-size: 13px;">RAMP will also have live streaming broadcasted at </span><a href="http://thenextweb.com/" id="yui_3_7_2_1_1373178580123_17033" rel="nofollow" style="background-color: white; color: #2862c5; font-family: arial, sans-serif; font-size: 13px; outline: 0px;" target="_blank">TNW</a><span style="background-color: white; color: #454545; font-family: arial, sans-serif; font-size: 13px;">, </span><a href="http://www.hwsw.hu/" id="yui_3_7_2_1_1373178580123_17034" rel="nofollow" style="background-color: white; color: #2862c5; font-family: arial, sans-serif; font-size: 13px; outline: 0px;" target="_blank">HWSW</a><span style="background-color: white; color: #454545; font-family: arial, sans-serif; font-size: 13px;"> and on </span><a href="http://www.ustream.com/" id="yui_3_7_2_1_1373178580123_17035" rel="nofollow" style="background-color: white; color: #2862c5; font-family: arial, sans-serif; font-size: 13px; outline: 0px;" target="_blank">USTREAM</a><span style="color: #454545; font-family: arial, sans-serif; font-size: x-small;">.</span><br />
<br />
<br />
<iframe src="http://prezi.com/embed/tqrss-zsk9ry/?bgcolor=ffffff&lock_to_path=0&autoplay=0&autohide_ctrls=0&features=undefined&disabled_features=undefined" width="550" height="400" frameBorder="0"></iframe>Dathan Pattishallhttp://www.blogger.com/profile/00356367514107959723noreply@blogger.com0tag:blogger.com,1999:blog-31421954.post-28860282421734206142013-06-24T04:44:00.000-07:002013-06-24T04:44:39.047-07:00First Week.5 in China: Part Two, Refactor PHP get 10% more capacity with one changeThe PHP code that I've experienced in China so far is pretty good. I have been in some environments where the Code is horrendous-where variables are set in one file yet used in another file via a require_once. If that magic variable is not set everything would break with side-effect galore. This is not the case here for the China Team. This team is really good not to imply the other-one wasn't just praising the current one.<br />
<div>
<br /></div>
<div>
The SQL, like many other companies I have been at requires some more extra effort, but the hunger to learn and improve is throughout the culture of the team here. Really that is the first step to improve a system, the willingness by developers and management in getting things done and fixed-fast.</div>
<div>
<br /></div>
<div>
Entering in the environment, first I read all the code. Then<a href="http://mysqldba.blogspot.com/2013/06/first-week-in-china-build-new-dev.html" target="_blank"> created a development environment</a> to play with the code. Next I profiled how the database is being interacted with, and in conjunction with the cache. All looked ok, but with some back of the envelope calculations the server farm is to big for the amount of traffic. Traffic is huge don't get me wrong (5M+ DAU)! But the farm is too big. Digging some more I found that the code spins to search for items on a map by loading all map items, and in a for-loop go through each item until that 1 item is found, then return. This is done two - four times for every api request, especially to trigger an achievement if the item is found on your map. More on the fix later.</div>
<div>
<br /></div>
<div>
Before making any changes, I wanted to get feedback of what the biggest issue was that seem to cause bugs or slow down development. The consensus was the DB Layer was mixed into the Model Layer causing fear of changing said models because there was fear that they would break the DB Layer. It was not quite clear how the code communicated with the DB, thus work was done by the team to use more of the same existing functionality to fulfill feature requests which is sub-optimal if the root functionality was slow or expensive to use in the 1st place at scale.</div>
<div>
<br /></div>
<div>
Thus the 1st recommendation was to separate out the DB Layer in a way where the data being requested is accessed through Data Access Objects (DAO). This concept encapsulates DB logic and for the most part requires only three methods: add, get, delete. Some more complex objects calling DAO had specific SQL to make getting data faster but for the most part three methods per table was all that was needed. Following the new Directory Structure backed by PHP Namespaces all SQL was easy to find and isolated away from the model.</div>
<div>
<br /></div>
<div>
The second recommendation was to remove a bunch of in PHP caches of data, because this was the cause of a vast amount of copies chewing up a ton of memory per request as well as chewing up CPU to build the caches per request. If the cache hit rate is not good don't cache-added complexity sucks to maintain-and can actually slow things down if not needed.<br />
<br />
The third recommendation was to make each Model a single instance per distinct entity (singleton-map) throughout the request which reduced the overall amount of database queries by coupling the model creation to database fetches. The database queries are reduced because instead of pulling the same data for object creation in various parts of the code, the single object was referenced.</div>
<div>
<br /></div>
<div>
So here is the new structure for models/database access/utils</div>
<div>
<br /></div>
<div>
v2</div>
<div>
v2/classes/DB/ -- DB connection logic</div>
<div>
v2/classes/DAO -- DB Access </div>
<div>
v2/classes/Models -- New Models</div>
<div>
v2/classes/Util -- common Utility classes</div>
<div>
v2/init.php -- everything is setup from this structure<br />
<br />
With this new structure, separation of responsibility has been created in the code. More people can work on the same feature. One person can optimize the SQL, while another plugs in the model and yet another handles the access logic (controller). Or a single person can do it all. Most importantly the team loves the new setup.</div>
<div>
<br /></div>
<div>
In my 1st week and 1/2)with the new model format added to the existing code base via editing 242 files for a single model's usage (the largest and one of the most important models that controls the MAP locations of the game) the result has been great, a 10% drop in the number of servers and no user complaints with still more room for improvement. The biggest change was due to removing the spin through all the map locations to find a single item. The fix was changing a O(n) method in PHP getting hit hard to a O(1).<br />
<br /></div>
<div>
60 more models to go.</div>
<div>
<br /></div>
<div>
The good note about the unoptimized code is its forced the dev ops side of things to mature quickly and the tools that they built are really robust. To deal with features being pushed out that may not be mature enough for the request load the team built this cool dashboard with Jenkins automation, home grown software, realtime server metrics and rules to launch new instances and shrink them automatically throughout the day. It works flawlessly, for the front ends that is. It's pretty cool. Its so good and works so well I am hoping one day that it could be an OpenSource Project on its own.</div>
<div>
<br /></div>
Dathan Pattishallhttp://www.blogger.com/profile/00356367514107959723noreply@blogger.com0tag:blogger.com,1999:blog-31421954.post-26075375708656913312013-06-18T19:10:00.001-07:002013-06-18T19:10:56.534-07:00First Week in China: Build a new Dev Environment<div>
I see my role as enabling others. When I was a pure awesome DBA in the early 2000s I enabled developers and customers of a companies product by making mySQL fault-tolerant and fast. As I moved up the stack as an Architect while still holding onto my roots as a DBA-I kept my DBA discipline by enabling my team and company through all the knowledge I garnered.</div>
<div>
<br /></div>
<div>
The first thing I identified in China that can really help my team-members is making a new development environment. The reason, the production and dev environments are wildly different. Dev is on Windows while production runs various flavors of Linux's 2.6 Kernel-mostly Centos-6. Additionally when the code is ready to be push to what I like to call pre-integration servers-meaning the code is not checked in but copied to a test server then checked in if the tests past. As a result developers spend time organizing which test server to use and this server can only be used while in the office.<br />
<br />
Generally as a developer you should develop in something simular to your production environment, and the integration server should serve as QA of the product and not as the post development process that by-passes all unit tests (which did not exist). Also a lot of effort was put into making this Windows to Linux environment to work-just good enough-which really is not. Since PHP behaves slightly differently under Windows, I found that time was being spent on issues that could possibly not show up on Linux's php version. Thus these issues provided enough justification to build an integrated environment, where the end developer can work from home, or from where ever even if there is no direct network connection to the outside world.</div>
<h2>
<br />The Setup</h2>
<div>
Forcing a developer to change their OS of choice, or IDE or what have you is not going to fly in any country-its just too disruptive. Thus I chose to build the environment on <a href="https://www.virtualbox.org/" target="_blank">virtualbox</a>, a free VM that works on MAC and Windows, the two primary Dev environments. I pre-built the VM and uploaded it to the local fileserver. Now all the team has to do is download the VM.<br />
<br />
Here is what is installed on the VM. (These steps follow after installing Centos-6-minimal)</div>
<div>
<br /></div>
<div>
<div>
First, I set up a shared directory from the HOST (Mac) machine to the GUEST machine (VM), which contains the code to run the site. This allows the user to use their favorite native IDE app or vim.</div>
</div>
<div>
<br /></div>
<div>
<div>
Next, I set up a host virtual network, so even if the HOST does not have a connection to the net, it can always talk to the VM via ssh, httpd or what have you. I also setup another network interface for the VM to talk to the outside world via the NAT setting so packages can be installed directly on it via yum.</div>
</div>
<div>
<br /></div>
<div>
<br /></div>
<div>
Then I configured the yum repos for Centos-6 epel for core linux utils, 10gen for mongoDB, percona for XtraDB by modifying /etc/yum.repos.d and adding the following repos to my list</div>
<div>
<br /></div>
<div>
Percona.repo</div>
<div>
epel-testing.repo</div>
<div>
epel.repo</div>
<div>
remi.repo</div>
<div>
CentOS-Vault.repo</div>
<div>
CentOS-Media.repo</div>
<div>
CentOS-Debuginfo.repo</div>
<div>
CentOS-Base.repo</div>
<div>
10gen.repo </div>
<div>
<div>
<br /></div>
</div>
<div>
<br /></div>
<div>
Additionally I installed Percona, MongoDB, php, php-cli, php-frm, nginx, apache, vim-enhanced, etc. via Yum on the VM.</div>
<div>
<br /></div>
<div>
<br /></div>
<div>
Finally I wrote documentation for the whole process and tested on a few people who have good Spoken English skills. With their feedback the documentation was improved and sent to the rest of the team, who have pretty good written english skills.</div>
<div>
<br /></div>
<div>
Now all the dev team members have to do is download the vm, configured the shared code directory and tada the entire dev environment in a box!<br />
<br />
The next step is to resolve Schema Changes, and use Chef to update configurations and packages as if the VM was a real server-this is currently an manual process.<br />
<br />
<br />
Next Post: Refactor PHP Models and add Unit Tests<br />
<br /></div>
Dathan Pattishallhttp://www.blogger.com/profile/00356367514107959723noreply@blogger.com0tag:blogger.com,1999:blog-31421954.post-85251456483062946062013-06-12T19:48:00.000-07:002013-06-12T19:48:21.872-07:00In China and Spreading mySQL/MariaDB/XtraDB Ganglia, GearmanD, Memcache, MongoDB, HAProxy, Nginx, PHP, PythonI am currently in Beijing for a month as the VP of Technology for Fun+, a US/China based gaming company, spreading the joys of open-source I have an entire team to do benchmarks, study INNODB flushing, build new technologies, which I hope to open-source I will also post the results here. Our Stack is mostly on AWS with the following.<br />
<br />
HA Proxy Load Balances the Web Tier<br />
Web-Tier runs nginX and php-frm<br />
Data is stored in a new Sharded mySQL layer, Gift platform is on MongoDB<br />
Memcache is used to cache frequently accessed items to give state to our stateless Web-tier and reduce DB load, although we can run without it.<br />
<br />
What I am focusing on is<br />
<br />Code-Style<br />
When to cache and not to Cache<br />
How to get the most out of mySQL and MongoDB especially on Index Design<br />
Tools for DevOps by DevOps<br />
Reducing cost<br />
<br />
I hope to have a lot of information to share in the next couple of weeks.Dathan Pattishallhttp://www.blogger.com/profile/00356367514107959723noreply@blogger.com0tag:blogger.com,1999:blog-31421954.post-27366165652911350622013-05-13T11:13:00.000-07:002013-05-13T11:16:13.580-07:00How to pick indexes is the same for MongoDB as mySQL<br />
I recently went to MongoDB Days,
a conference about everything MongoDB in SF. Starting my career as a Systems
Programmer then Web Developer, MySQL DB[Admin|Architect], to Software|System Architecture I like to keep an open
mind about new technology and trends. When you work with a lot of different
languages, and technology you find out that it’s basically the same Science
from about 40 years ago.<br />
<br />
An index in MongoDB is like an
index in mySQL since a Btree is a Btree regardless of what application uses it.
Just like with mySQL the best performance improvement for an application using MongoDB
as a datastore is adding the correct indexes.<br />
<br />
<br />
To create an index in MongoDB:<br />
<br />
db.<tableName>.ensureIndex({ col#1:1, col#2:-1, col#3:1 }); // note 1 means ASC -1 means DESC<br />
<br />
MongoDB follows the same left-most-prefix rule meaning<br />
<br />
col#1, col#2, col#3 is an index<br />
col#1, col#2 is an index<br />
col#1 is an index<br />
<br />
col#2, col#3 IS NOT AN INDEX<br />
<br />
So, just like with mySQL for ONE compound index you get a total of THREE indexes if you follow the left-most-prefix rule, the columns from left to right (in order) in a compound index is an index.<br />
<br />
MongoDB also gets a performance boost by using Covering indexes just like mySQL. What is a Covering index? Instead of reading from the datapage (or document store for MongoDB) which exists on disk your reading the data from the index which should be in memory for the most part. A common practice is to follow the left-most-prefix pattern, then add the columns which you are returning at the end of the compound index. For instance<br />
<br />
SELECT photoId from Photos WHERE userId=? AND dateCreate=? AND privacy=?<br />
<br />
The index in mySQL I would make is<br />
<br />
ALTER TABLE Photos ADD INDEX `userId-dateCreate-privacy-photoId` (userId,dateCreate,privacy,photoId)<br />
<br />
Thus following the left-most-prefix of a compound index I have an index on<br />
<br />
userId, dateCreate, privacy, photoId<br />
userId, dateCreate, privacy<br />
userId, dateCreate<br />
userId<br />
<br />
and a Covering Index satisfied by the query above.<br />
<br />
For mongoDB its the same<br />
<br />
db.photos.ensureIndex({ userId: 1, dateCreate: 1, privacy: 1, photoId: 1});<br />
<br />
<br />
So, in conclusion, understand the Computer Science of a <a href="http://xlinux.nist.gov/dads/HTML/btree.html" target="_blank">Btree</a>, <a href="http://xlinux.nist.gov/dads/HTML/hashtab.html" target="_blank">Hash</a>, <a href="http://xlinux.nist.gov/dads/HTML/linkedList.html" target="_blank">LinkedList</a> and you will understand how indexes work across technology and find that essentially it's the same.<a href="http://mysqldba.blogspot.com/2008/06/how-to-pick-indexes-for-order-by-and.html" target="_blank"> More info on indexes for mySQL can be found here.</a><br />
<br />
Also note:<br />
<br />
<a href="http://docs.mongodb.org/manual/reference/operator/explain/" target="_blank">Explain</a> in mongoDB is your friend just like <a href="http://dev.mysql.com/doc/refman/5.5/en/explain.html" target="_blank">Explain</a> in mySQLDathan Pattishallhttp://www.blogger.com/profile/00356367514107959723noreply@blogger.com0