Databases and interfacing with them - DB Help Thread

We'll see if this flies.

 

You have two problems you need to solve: database development, and building an appliance.

The second is much harder than the first.

DBs are easy to learn. Start with the general SQL language that is, for the most part, shared by all relational databases. This is the core of what you need to learn in order to develop simple DB-based applications.

<a class="postlink" href="http://shop.oreilly.com/product/9780596526849.do" onclick="window.open(this.href);return false;">http://shop.oreilly.com/product/9780596526849.do</a>

That's a great book to figure out basic stuff.

Now, if you are doing complex reporting, you might be touching on DB warehousing, which is fucking terrifying (to me, anyway). It all depends on your specific use cases, but if you're just doing simple shit, then you shouldn't have any problems. Pro-tip though, define all of your reports up front and reverse engineer them into the data model / sql queries you'll need, or you'll get lost pretty easily.

Next, peruse the MySQL online docs, specifically around the features and administration stuff, to get an idea of the concepts. Don't focus on learning the low-level shit and how to implement it, skim it to get an idea of the concepts. "Oh... there are replication options. Oh, partitioned tables, what are they?" Focus on finding the "gotchas"... they're pretty good about explaining situations where things WON'T work as expected, so pay attention, and steer clear of those situations.

Then Google for "MySQL DBA best practices" to get an idea of the type of ongoing, periodic maintenance you need to do to keep a DB running, as well as the type of monitoring you need to put in place to alert you when something isn't working. This is where you're getting into "building an appliance".

Of course, I'm assuming you really are building an appliance, in the true sense of the word...


The quickest way you can learn a DB is to have a specific project... define the requirements up front, and get a good understanding of what you'll need the DB to do. Even a rough idea of the data model will help. Then keep that shit in mind while you're investigating DB's, so you can make note of when something will apply to what you're doing.

As to what DB's after MySQL, I'd say PostgreSQL... it's so much better than MySQL, and is much closer to commercial offerings, like Oracle. Learn that, and your skills will transfer quite well.

MySQL isn't a good database, it's just EVERYWHERE. Most people don't realize that MySQL isn't even fully ACID compliant, just the default storage engine (innodb) is... the actual DBMS isn't. (MySQL has two main parts, the DB Management System, or "head", and the storage system, of which there are 10 or so main options... InnoDB is the default). It's nice and fast and simple for a few use cases, but I wouldn't build a bank around it.

 

which is why they bought mysql.

 

I'm trying to figure out the most efficient way to duplicate a SQL database using remote mysql commands.

I'm regularly standing up and tearing down test instance for my website, and since the database + file directories total nearly 10gb, this can take some time and I'd like to just be able to run a script and walk away. I have the script built but the DB copy is slooow.

Previously, I've been using PHPMyAdmin, running on the web server, to duplicate the database. I have no idea what query it uses to do the duplication, but it seems to be adequately fast. When I run this query from the web server:
mysqldump -h db01 -u root -pPASSWORD source_database | mysql -h db01 -u root -pPASSWORD destination_database

It is functional but far slower and more impactful to the functionality of the website than whatever PHPMyAdmin does.

Thoughts?

 

Look at using binary logs, especially if you want it to be current. It's an incremental backup process designed to keep standby DB's up to date with as minimal overhead as possible.

http://dev.mysql.com/doc/refman/5.0/en/ ... overy.html

Basically, do the following:

• take a data file backup of your source db
• copy the big files to your target and recover DB there. This is done once a week/month/whatever depending on your DB's rate of change, time to copy, and other elements that are specific to your environment and use case. You have to figure out the ROI and adjust accordingly.
• enable binary logs on the source DB, which record changes to your DB since the base backup has been made
• when you want to do a copy of the DB to your target, just copy those binary logs to your target and apply to your target
• redo process every so often when binary logs get too big to copy quickly

 

To further expand on this, you could do the following:

Have a local/test/target DB that is a duplicate (logically) of PROD, and is kept up to date with reasonable frequency using the binary log approach.

When you need to take a copy, treat that replica as the source, not your PROD (assuming PROD is off over the internet, and your replica is local drive).

When you need a new TEST db to play with, make a local data-file copy of the 10GB DB, which is way, way faster than doing mysqldump/restore (which basically just executes a shit-ton of sql statements, and does index maintenance after each one unless you specify "no indexes" when you're dumping it).

You'll just have to figure out the DB admin stuff (getting the DB to be usable under a new instance name) which is rather fast compared to running 10GB worth of SQL statements.


The only real issue is the added management on the PROD db, which means you have to then be worried about cleaning up / managing the binary logs, taking a new base every so often, etc.)

Would still be way, way faster.

 

I had thought about using binary logs but to be honest, the servers are kind of on the hairy edge of being acceptably performant as it is and the company that owns them is basically unwilling to do upgrades. While binary logging isn't an enormous hit, I'm a little leery of doing anything to upset the balance, especially to solve a relatively minor convenience problem.

The DB itself is only ~4gb, the rest are filesystem items that need to be duplicated and obviously that's the easy part. I'm just trying to figure out why I can do a database duplication in PHPMyAdmin in about 10 minutes with seemingly little impact on the website, whereas the SQL dump command basically brings the website down to molasses and sits there and spins for 30 minutes. My assumption is that's because the mysqldump command I'm using is doing the transfer of the DB twice (dump from DB > web server where piped command is running > back to new DB). I'm speculating that PMA avoids that redundant transfer and keeps the data local to the DB server... I just don't actually know how, since PMA is hosted remotely as well and has no access to the filesystem of the DB server.

 

It's the mysql command... whenever you do raw sql commands to rebuild the server, especially without disabling keys, it's expensive as hell. Odds are phpmyadmin is using --disable-keys option (on the dump part) to make the insert much faster, which you're not doing.

If they're using commercial mysql, then do a hot backup and copy the raw files, not process the sql statements.

 

Didn't think about disabling keys - that's a good idea, thanks.

They're not running a hot backup because it's just a vanilla install of free MySQL.

 

Focus on simple "hello world" php code that does basic CRUD actions on a single table. There are tons of tutorials out there that do that.

Here's the first of thousands that popped up in Google: http://www.startutorial.com/articles/vi ... ial-part-1

Work your way through that, and then see where you need to go from there to get it to work for your specific use case.

Then start learning about blobs, clobs, exception handling, transactions, sql injections, etc., and wonder what the fuck you've gotten yourself into.

Always better to start with a simple Proof Of Concept and then slowly morph it into what you need.

 

Depending on where the scope of the project and where your database requirements lead you, you may want to consider using a noSQL database like Mongo for something like this. They are designed to be a simple key/value data store... no transactions, no joins, etc. Very fast, very simple... as long as you stay within the scope of what it does.

http://php.net/manual/en/mongo.tutorial.php

Be careful, though. I'm in the middle of converting 2 years of development with Mongo to PostgreSQL due to the fact that they started off using Mongo for something very lightweight and perfect for what it does, only to have the application morph into something that uses joins, transactions, and other things that Mongo doesn't do. It's hell.

But we're still doing all of our BI instrumentation and metric collection using Mongo.

It's about using the right tool for the right job.

 

Tell me about it. I knew the problems they were having 30 seconds into the interview when they told me their technology stack.

They looked at me like I was some kind of psychic.