Showing entries 91 to 100 of 1206
« 10 Newer Entries | 10 Older Entries »
Displaying posts with tag: Databases (reset)
Designing Multi-Tenanted Databases – Hierarchies

In the previous article, we learned how you can design your database schema so that many customers can share one database and one set of tables. The examples I used were fairly simple: they consisted of single tables or simple hierarchies like the order and line item tables. In those examples, you had a single tenant id that segmented the tables. Now I’d like to introduce the idea of hierarchical segmentation. This is a pretty big term for the idea of allowing customers to see data based on where they are in a hierarchy, like an org chart.

Let’s say you have three sales teams each with its own manager for three regions in the US: East, Central, and West. A salesman in the East only gets to see his own prospects, while the East sales manager sees the prospects of the entire East sales team. At the very top, the VP of US Sales can access the …

[Read more]
Designing Multi-Tenanted Databases

Designing database tables for many customers is a surprisingly foreign concept for some programmers. I’ve been in interviews where a programmer will sketch out a normal object model, and then proceed to suggest that for the database, each customer has their own set of tables, each prefixed with the customer name. What I’d like to do in this post is introduce the concept of multi-tenancy and then show methods you can use to do it right, instead of hacky ways like the one I just mentioned. Multi-tenancy is when several customers (tenants) share the same database and codebase but can only see their own data.

The basic idea

The core method of adding multi-tenancy to your database is adding a column to every table you’d like to segment that indicates the owner of the data. I’ll call this column a tenant id. We’ll start with a basic example: a todo app. Let’s say you write a program that you’re going to use on your own to …

[Read more]
Null Columns in MySQL – Part II

I’ve covered some strange default behavior around nulls in MySQL. There’s another nuance to this issue: you can still insert rows without specifying values for not null columns. MySQL will helpfully give you a default value (this is not really helpful – true help would be a quick failure with a descriptive error message). Let’s walk through this example.

mysql> Create Table NullTable (a Int Not Null, b Int Not Null);
Query OK, 0 rows affected (0.01 sec)

Here’s a table with two not null columns.

mysql> Insert Into NullTable (a) Values (1);
Query OK, 1 row affected, 1 warning (0.01 sec)

We can totally insert, even though we didn’t specify a value for b and b is not null.

mysql> Show Warnings;
+---------+------+----------------------------------------+
| Level   | Code | Message …
[Read more]
MySQL Simplified

MySQL is the little engine that could. It powers sites like Facebook, YouTube, Twitter, and thousands of blogs, CMSes, and e-commerce sites. Its value to the world and to the development community could be measured in the hundreds of billions, and yet it’s free, and you can use it just by downloading it. Almost every programming language has drivers for it and it can run on so many operating systems and architectures, there’s really no limit on it.

Yet there’s a dark side. MySQL is full of gotchas and bugs, and it lacks features that sometimes call into question its status as a real database. The documentation is often open-ended and confusing, with gaps in key parts. If you want to run it, you have the option of using it on Linux, Mac, Solaris, or Windows and every hosting company or provider like Amazon AWS has their own managed service, each with its own quirks and limitations. The user community has also produced thousands of …

[Read more]
MongoDB in 2013 -- A Year in Review

It's again that time of the year. Analysts are spending oceans of words to predict the future, companies are making plans for the next year and people are resting and enjoying the break with their families. To me, this is the perfect time to reflect on my choices, the direction I'm headed to and consider if I still love what I do.

At the beginning of the year I decided to join MongoDB (formerly 10gen). The more I think about it, the more I realize I've been wrong. Yes, it's been the worst decision in my life not to join MongoDB when I was first offered the opportunity years ago. At that time an insightful friend asked me to consider the opportunity. At that time I didn't see what I see today.

I didn't see the …

[Read more]
MongoDB in 2013 -- A Year in Review

It's again that time of the year. Analysts are spending oceans of words to predict the future, companies are making plans for the next year and people are resting and enjoying the break with their families. To me, this is the perfect time to reflect on my choices, the direction I'm headed to and consider if I still love what I do.

At the beginning of the year I decided to join MongoDB (formerly 10gen). The more I think about it, the more I realize I've been wrong. Yes, it's been the worst decision in my life not to join MongoDB when I was first offered the opportunity years ago. At that time an insightful friend asked me to consider the opportunity. At that time I didn't see what I see today.

I didn't see the …

[Read more]
Upgrading from MySQL 5.1 to MariaDB 5.5

In my last post, a tale of two MySQL upgrades, a few folks asked if I would outline the process we used to upgrade, and what kind of downtime we had.

Well, the processes were different for each upgrade, so I will tackle them in separate blog posts. The first step was to upgrade all our MySQL 5.1 machines to MariaDB 5.5. As mentioned in the previous post, MariaDBs superior performance for subqueries is why we switched and we switched back to MySQL for 5.6 to take full advantage of the performance_schema.

It is not difficult to blog about our procedure, as we have documentation on each process. My first tip would be to do that in your own environment. This also enables other folks to help, even if they are sysadmins and not normally DBAs. You may notice the steps contain items that might be obvious to someone who has done maintenance before we try to …

[Read more]
A Tale of Two MySQL Upgrades

At the beginning of 2013, Mozillas MySQL databases were a mix of MySQL 5.0, Perconas patched MySQL 5.1, Perconas patched MySQL 5.5 and MariaDB 5.5. MySQL 5.1 was released in November 2008 so at the beginning of the year, we still had databases with no new major features in 4 years. Currently we have almost all our databases at Oracle’s MySQL 5.6 — the only stragglers are our cluster running TokuDB and a few machines that are no longer in use. Heres a graph showing the state of our machines you can see that in the first half of the year we concentrated on upgrading our 5.0 and 5.1 servers to 5.5, and then in the second half of the year we upgraded everything to MySQL 5.6 (click on the image to get the .png file):

[Read more]
Simple Settings for a Saner MySQL – InnoDB

Within MySQL, there’s a piece called a storage engine that reads and writes to disk on your behalf when you execute a query. It controls the way that your data is stored on disk. With MySQL, you can change what storage engine you use, which is helpful since every engine has different advantages and downsides, and you can select which engine to use based on your workload. There are two main engines: MyISAM and InnoDB. MyISAM was the default engine before MySQL 5.5 and it’s been there since the beginning. It’s also not crash-proof, it doesn’t have foreign keys, and it’s not transactional. InnoDB, on the other hand, has all these features.


What happens if you use MyISAM

No Safety Belt

Grooveshark (a popular music site) in …

[Read more]
Unexplained halts using mysql command line client

I recently came across an issue trying to connect to a MySQL server using the mysql client. It appeared as through the connection was hanging.

A subsequent connection using the -A option highlighted the problem with the previous connection stuck in the state “Waiting for table metadata lock”.

mysql> SHOW PROCESSLISTG
*************************** 1. row ***************************
     Id: 37
   User: root
   Host: localhost
     db: tmp
Command: Query
   Time: 90
  State: preparing
   Info: create table missing as select id from AK where id not in (select id ..
*************************** 2. row ***************************
     Id: 38
   User: root
   Host: localhost
     db: tmp
Command: Field List
   Time: 50
  State: Waiting for table metadata lock
   Info:
*************************** 3. row ***************************
     Id: 39
   User: root
   Host: localhost
     db: tmp
Command: Query
   Time: 0
  State: init
   Info: show processlist …
[Read more]
Showing entries 91 to 100 of 1206
« 10 Newer Entries | 10 Older Entries »