chronicdb's blog
Database Metadata Made Easy
Submitted by chronicdb on Thu, 2012-02-16 08:48
When you work as a DBA you often need to synchronize two databases together. It's often between a development database and a live, production database, and the pressure of a release push has you reaching for a quick script to make ends meet.
If it only it were that easy. The reality is managing changes to databases is the
primary causal factor of database downtime. What a DBA battles most of the time is getting accurate metadata information to plan changes.
We hope the metadata API we are releasing today will make life a little easier for the DBA.
Data Are Not Code
Syncing databases is important because it gives you confidence that what is tested in development matches what's in production. You wouldn't want thorough application testing to happen using invalid data or the wrong database schema.
Fundamentally, database syncing first requires 'diffing' data, similar to diffing source code. The two seem so similar that it's hard to imagine why databases get into such trouble in deployment compared to source code. But there is a critical difference between the two. Whereas source code is stateless, a database is stateful.
You can find precisely which lines of code changed in source code, but you cannot always find precisely which rows of data changed in a database. By the time you diff the data, the data has changed.
Additionally, the data has structure. Data are connected together with relations much like data-structures in memory are connected together with pointers. Missing one property of those data relations is analogous to a crash of a program. It can be worse than a crash actually. When a program crashes you at least know about it: the program isn't running and someone is complaining. But when a relation becomes corrupt the database continues to stealthily run without a visible sign of error.
Finally, a hard but neglected part of database synchronization is that you often need the flexibility to choose what to synchronize. When testing a migration to a different database or schema, you first cautiously test against a single table, then a few tables, then tables with more relations. The need to be in charge of synchronization programmatically soon becomes obvious.
We know by now that data, schemas, and metadata are important in making databases easier to work with. That's why ChronicDB versions data like a TV-recorder, changes schemas live and seamlessly, and makes metadata accessible.
Metadata Cloud API
After experiencing the pain of needing frequent access to database metadata we are opening our metadata API for free public access. It is build atop the core engine that drives ChronicDB's versioning and replication, so it has grown out of our needs and has been battle tested.
The API gives you metadata about:
-
Tables, including details on
- Column naming, typing, and ordering
- Primary keys
- Foreign keys
- Size information
- Sequences
- Views
- Stored procedures
- Triggers
- Size information of database objects
The API is available through our CLI as chd meta and through our public SQL gateway over HTTP. It supports PostgreSQL and MySQL to start, returns results as JSON and is conveniently accessible as shown below:
$ curl -d '{ "command": "meta postgresql://chdroot/urimarusin@vdi1.chronicdb.com:30084/pamella" }' https://api.chronicdb.com { "message": { "procedures": { "languages": {}, "procedures": {} }, "sequences": {}, "tables": { "dynass": { "columnnumbers_by_name": { "begyr1": 4, "begyr2": 8, "begyr3": 12, "begyr4": 16, "begyr5": 20, "endyr1": 6, "endyr2": 10, "endyr3": 14, "endyr4": 18, "endyr5": 22, "gvkey1": 5, "gvkey2": 9, "gvkey3": 13, "gvkey4": 17, "gvkey5": 21, "pdpass": 1, "pdpco1": 2, "pdpco2": 7, "pdpco3": 11, "pdpco4": 15, "pdpco5": 19, "source": 3 }, "columns": { "begyr1": { "number": 4, "type": "integer" }, "begyr2": { "number": 8, "type": "integer" }, "begyr3": { "number": 12, "type": "integer" }, "begyr4": { "number": 16, "type": "integer" }, "begyr5": { "number": 20, "type": "integer" }, "endyr1": { "number": 6, "type": "integer" }, "endyr2": { "number": 10, "type": "integer" }, "endyr3": { "number": 14, "type": "integer" }, "endyr4": { "number": 18, "type": "integer" }, "endyr5": { "number": 22, "type": "integer" }, "gvkey1": { "number": 5, "type": "integer" }, "gvkey2": { "number": 9, "type": "integer" }, "gvkey3": { "number": 13, "type": "integer" }, "gvkey4": { "number": 17, "type": "integer" }, "gvkey5": { "number": 21, "type": "integer" }, "pdpass": { "number": 1, "type": "integer" }, "pdpco1": { "number": 2, "type": "integer" }, "pdpco2": { "number": 7, "type": "integer" }, "pdpco3": { "number": 11, "type": "integer" }, "pdpco4": { "number": 15, "type": "integer" }, "pdpco5": { "number": 19, "type": "integer" }, "source": { "number": 3, "type": "character varying(512)" } }, "columns_by_number": { "1": "pdpass", "2": "pdpco1", "3": "source", "4": "begyr1", "5": "gvkey1", "6": "endyr1", "7": "pdpco2", "8": "begyr2", "9": "gvkey2", "10": "endyr2", "11": "pdpco3", "12": "begyr3", "13": "gvkey3", "14": "endyr3", "15": "pdpco4", "16": "begyr4", "17": "gvkey4", "18": "endyr4", "19": "pdpco5", "20": "begyr5", "21": "gvkey5", "22": "endyr5" }, "foreign_keys": {}, "primary_keys": [], "size": 966656 }, "patentdata0506": { "columnnumbers_by_name": { "claimspat": 5, "coname": 1, "nclaims": 4, "npats": 3, "yearap": 2 }, "columns": { "claimspat": { "number": 5, "type": "character varying(512)" }, "coname": { "number": 1, "type": "character varying(512)" }, "nclaims": { "number": 4, "type": "character varying(512)" }, "npats": { "number": 3, "type": "character varying(512)" }, "yearap": { "number": 2, "type": "character varying(512)" } }, "columns_by_number": { "1": "coname", "2": "yearap", "3": "npats", "4": "nclaims", "5": "claimspat" }, "foreign_keys": {}, "primary_keys": [], "size": 139264 } }, "triggers": {}, "views": {} }, "value": 0 }
This one goes to the DBAs out there. Enjoy!
Install PostGIS 2.0 Easily
Submitted by chronicdb on Wed, 2012-02-15 09:54
PostGIS 2.0 is now available on ChronicDB.
Besides seamlessly adding PostGIS on our hosted databases, we also make it easier to enable PostGIS in databases hosted elsewhere. Our module loader installs the .sql files needed to enable PostGIS, assuming the relevant PostGIS libraries were compiled in the remote database. It's a simple 'chd add' followed by a 'chd mod'.
In short:
-
Create a brand new database
$ chd create postgresql udi-a791f642: postgresql://chdroot/arisahakot@vdi1.chronicdb.com:14390/bonita export PGPASSWORD='arisahakot'; psql -h vdi1.chronicdb.com -p 14390 -U chdroot bonita
-
Enable PostGIS
$ chd mod bonita postgis:on Module postgis has been set to on.
-
Try it out
$ export PGPASSWORD='arisahakot'; psql -h vdi1.chronicdb.com -p 14390 -U chdroot bonita psql (9.0.4) Type "help" for help. bonita=# SELECT PostGIS_full_version(); postgis_full_version ---------------------------------------------------------------------------------------------------------------------------- --------------------- POSTGIS="2.0.0SVN" GEOS="3.3.2-CAPI-1.7.2" PROJ="Rel. 4.7.1, 23 September 2009" GDAL="GDAL 1.6.3, released 2009/11/19" LIBX ML="2.7.8" USE_STATS (1 row) bonita=#
Developing geospatial applications is not for the faint of heart. It takes some time upfront to familiarize oneself with the various geometries possible before a meaningful application can be created. If you are just starting out check out the wonderful tutorial from PGCon 2009.
SQL-over-HTTP
Submitted by chronicdb on Fri, 2012-01-13 16:18
At ChronicDB we are building the missing tools that simplify movement of data. Today, we are opening our SQL-over-HTTP service to the world.
HTTP API
First, access to databases provisioned by ChronicDB is now available through HTTP. This is in complement to directly accessing the database port with ordinary database tools. The HTTP interface gives you database connectivity from a web-application or command-line terminal using ordinary POST commands without the schlep of drivers.
The HTTP interface lets you issue all the SQL commands your database accepts and retrieve results back as JSON. This simplifies the integration of data results into your existing workflow. The complete HTTP API, which reflects the command-line chd tool, also simplifies management of the database.
If you are already using ChronicDB, the HTTP POST call accepts your platform credentials. The call also takes an "sql dbname" command and your raw SQL query under "data":
$ curl -d '{
"access_id": "your_access_id",
"secret_key": "your_secret_key"
"command": "sql leaflet",
"data": "select pdpass, pdpco1, source from dynass limit 1;"
}' https://api.chronicdb.com
{
"message": {
"data": [
[
-19702,
66448,
"m2006"
]
]
},
"value": 0
}Anonymous SQL-over-HTTP
Second, for ChronicDB users that are not hosted on our platform, anonymous SQL-over-HTTP access is also available. This is resource limited based on demand, but essentially lets you issue raw SQL to any database if a complete DSN is provided, without having a ChronicDB user account:
$ curl -d '{
"command":
"sql postgresql://chdroot/urimarusin@vdi1.chronicdb.com:30084/pamella",
"data": "select avg(pdpass), source from dynass group by source;"
}' https://api.chronicdb.com
{
"message": {
"data": [
[
10703419.654784240150,
"NBER"
],
[
11653087.993193717277,
"PASS1"
],
[
11666499.518774475927,
"MTCH1"
],
[
11743333.748478701826,
"UPDT2"
],
[
11642403.865139949109,
"m2006"
],
[
11180665.317757009346,
"PHRM"
],
[
10720777.905555555556,
"FRAT"
]
]
},
"value": 0
}Anonymous SQL? How anonymous is access if I still need to provide the DSN I hear you ask? Truly anonymous SQL would be running SQL on databases without knowing the credentials at all.
And how safe can this be? Can one run harmless SQL like SELECT/INSERT, while guarding against bad SQL that corrupts or deletes data? Could on store data without fully knowing the data structures?
Safe anonymous SQL almost sounds like REST itself.
Stay tuned for our SQL virtualization and happy hacking!
Hours Instead of Seconds
Submitted by chronicdb on Thu, 2011-10-13 16:45
We recently experienced downtime.
As a result of a database issue that occurred during a normal maintenance procedure, our phone system was down for more than a day.
Worded this way, it almost sounds like we are trying hard to dance around the issue. In fact here is what else the phone system vendor had to report:
We've made several changes to our systems in an effort to prevent outages from happening. Unfortunately, as a part of this effort an undocumented Oracle bug was introduced a week ago and it only presented itself when we restarted our systems completely on Oct 8 as a part of a scheduled maintenance that was also required for network improvements. The bug prevented all of our telephony equipment from coming back as planned. We began rolling back changes until we returned our equipment to the state they were in before last week's changes.
Though not directly disclosing the nature of the bug, the final sentence may be enough:
In some ways, it's not unlike hitting the undo button in any word processor, except that in our case, every undo effort takes hours instead of seconds.
Worded this way, it almost sounds like the vendor could have used database version control.
Change Is Not The Enemy
Submitted by chronicdb on Sat, 2011-10-08 08:21
ALTER TABLE in SQL leaves a lot to be desired. It seems to perform poorly, which is somewhat known. But it may not be known that changing schemas is a bigger problem that neither ALTER TABLE nor NoSQL solve.
The bigger problem is something like the generation gap, but between a database and an application. When a database grows old the app doesn't talk to her anymore. Either the app doesn't know how to talk to the database or the database doesn't shift its viewpoint on what the app wants. The communication breakdown could be avoided, if only they could understand where each other is coming from.
Performance
The performance problem is that ALTER TABLE often locks the table, forcing incoming reads to block. This is so common that the DBMS you are using right now likely does it. More often than not, it is only constraints that ALTER TABLE modifies lock-free, not the table itself. In fact here is what our survey revealed:
-
In DB2 9:
"Any ALTER TABLE statement requires exclusive access to a table, as it modifies in-memory structures. For certain statement options in particular, ALTER TYPE and DROP COLUMN—rows in table catalogs will be locked exclusively for UPDATE or DELETE." -
In MS SQL Server 2008:
"ALTER TABLE acquires a schema modify lock on the table to make sure that no other connections reference even the metadata for the table during the change [...] In an ALTER TABLE…SWITCH operation, the lock is acquired on both the source and target tables [...] Changes that affect all the rows in very large tables, such as dropping a column or adding a NOT NULL column with a default, can take a long time to complete and generate many log records [...] Additionally, you cannot change the data type of a column of a partitioned table." -
In MySQL 5.1:
"The table being altered is not locked with respect to API nodes other than the one on which an online ALTER TABLE, ADD COLUMN, CREATE INDEX or DROP INDEX statement is run. However, the table is locked against any other operations originating on the same API node while the online operation is being executed. " -
In Oracle 11g:
"Oracle Database permits DDL operations on a table only if the table can be locked during the operation." -
In PostgreSQL 9.0:
"ALTER TABLE cannot safely be executed concurrently with other operations on the same table, so it obtains an exclusive lock on the table to enforce that." -
In Sybase ASE 12.5:
"alter table acquires an exclusive table lock while it is modifying the table schema. This lock is released as soon as the command is done."
Clearly ALTER TABLE is still maturing and will improve its performance, like most software features. DBMSs will eventually provide an implementation that gradually prepares a new table and swaps it online without locking.
Mind The Gap
Unlike performance, what ALTER TABLE will not improve is its intrusiveness to applications. Apply a schema change and the old version of your web app breaks. Deploy a new version of the app and it doesn't know how to use the old schema, so the app breaks.
Somehow, the change to schema and application must happen in a way that lets both code and data get along. The release process that deploys a new version of the app in production should also deploy the new version of the schema, or at least one that doesn't break. It's not ALTER TABLE's job to release apps, so it doesn't. But the problem of upgrading applications without breaking anything remains.
Similarly a NoSQL database with flexible schemas has to solve the same problem. Unfortunately it doesn't just as a non-blocking ALTER TABLE doesn't. Changing either a dynamic or fixed schema involves both code and data, not just data.
To better understand what's involved in upgrading code and data together, let's consider a typical database+web-app scenario and examine our options:
- A schema change is backward compatible with the old app.
We run both old and new app concurrently with no problem, and then stop the old app. Success! That was easy.
- A schema change is not backward compatible with the old app.
This is where things get adventurous depending on how much control we have over the upgrade process:
- We are in control of both the old and the new app.
We stop the old app, upgrade the schema, and start the new app. Success, but with downtime proportional to the time it takes to upgrade the schema, which is proportional to the size of the database. Non-blocking or not, while
ALTER TABLEis running the app is down. Further, if the upgrade failed or needs to be reverted more downtime is in order.We could prepare the schema change on a replica and then stop the old app. But manually coordinating the replication of individual pieces, and doing so safely without data inconsistency is a fair amount of work. And it is even more work to also prepare a means for reverting a schema change that might go wrong (which hopefully we are preparing for.)
- We are not in control of both the old and the new app.
Changing the schema breaks the old app, possibly permanently. Failure, operator. If the schema serves multiple apps, we either have an immutable schema or dead apps.
Unless schema and apps could shift their viewpoints.
- We are in control of both the old and the new app.
Stepping back for a moment, we realize that it is backward compatibility that leads to success, and backward incompatibility that leads to downtime/failure. The obstacle to upgrading code and data gracefully is handling backward incompatibility.
If we've learned anything from the generation gap, it must be that closing the gap involves getting the two generations to understand each other's viewpoint. If what they say is read between the lines, translated, and understood, they might find they like talking to each other and not breaking anything. It is not controlling each other's viewpoint that leads to success but seeing the intention behind it. It involves doing a fair amount of work to change and possibly shifting viewpoint.
Conclusion
Another way of saying the obstacle is backward incompatibility is saying the enemy is change. As if, were databases and applications to never change, there would be no obstacle. But change is continuous, and in the case of the generation gap it leads to a gap.
If your business workflow never changed, which is what a database and application serve, would you call that success? The ability of a business to quickly change direction is a competitive advantage. Rather than avoiding change, seeking out and embracing change puts technology barriers between a business and its competitors.
So change is more like an opportunity, not an enemy. It's simply hard to change.
UNDELETE FROM Whoops;
Submitted by chronicdb on Thu, 2011-09-01 10:14
Bad news. That last DELETE statement you typed had too wide of a WHERE clause and it wiped out records it shouldn't have with a cascading effect. Or worse, a programming bug has been wiping database records for a while and you only just noticed...
What do you do?
Backups. You have last night's backup, can't that help? Unfortunately backups capture only a snapshot of the database in time, omitting the changes between that snapshot and now. So if you were to restore, you would lose valid changes between backup-time and whoops-time.
Replication. You have a slave replica, can't that help? It turns out that just as replication continually propagates intentional data addition, it also continually propagates accidental data deletion. By the time you notice, the replica probably wiped records too.
Binary logging. You may have enabled bin-logging though, can't that help? It can, if you make the database unavailable until you recover the data, which could be expensive, or adventurous enough to lead to data loss.
It doesn't take long to realize that backups, replication, and binary logging are incomplete approaches to managing database changes, as they don't readily offer historical data changes.
What If
If such a data versioning capability existed, what would the benefits be?
First, you would avoid the nightmare of data resuscitation, which could either be impossible or require surgery via binary log parsing and replay. Accidentally issued transactions would be rolled-backed instantaneously, just like older revisions of source code are restored instantaneously from your Git or Subversion repositories.
Second, temporal events for investigative analytics could be captured, rather than ignored. Consider a case where a customer adds multiple items in their cart, and then removes all but the one they purchase. Understanding the thought process of the customer during the selection phase clearly conveys information about both product and customer. With data versioning, such actionable events could be captured automatically, without code changes in an application, and expose what hides behind mouse-clicks (in the database), which is something web-analytics software does not track.
Compared to accidentally losing data, automatically gathering data suddenly sounds attractive, doesn't it?
Database Version Control
Well, time for the good news! We are happy to announce the ChronicDB data versioning capability through the chd version command. Besides tracing data changes, it offers the ability to roll-back individual, accidentally issued transactions.
Let's run through adding versioning to the booktown sample database for Postgres:
- Load up the database:
$ wget http://examples.oreilly.com/9781565928466/booktown.sql $ psql -h campbell.chronicdb.com -U postgres booktown -W < booktown.sql Password for user postgres: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "books_id_pkey" for table "books" CREATE TABLE ... setval -------- 25044 (1 row)
- Add the booktown database in ChronicDB and add versioning:
$ chd add postgresql://postgres/mypassword@campbell.chronicdb.com/booktown udi-2b6981fb: postgresql://postgres/mypassword@campbell.chronicdb.com:36873/booktown psql -h campbell.chronicdb.com -p 36873 -U postgres booktown -W $ chd version booktown Versioning enabled.
- Take data versioning for a test drive.
(For data versioning — as opposed to schema versioning — you can get away with connecting to the database directly, rather than connecting to the ChronicDB VM.)
$ psql -h campbell.chronicdb.com -p 36873 -U postgres booktown -W Password for user example: psql (9.0.4) Type "help" for help. booktown=# -- A whoops moment awaits... booktown=# DELETE FROM books; DELETE 15 booktown=# -- Oh NO! How do I undo a delete in SQL?
We "accidentally" deleted some data we would now like to recover. We can rollback a
DELETEwith ChronicDB if we find the transaction number corresponding to the deletion. In frequently changing databases, having a general idea of the time of the mishap helps, although looking for more accurate information such as specific values of columns in the ChronicDB journal is possible.To find the transaction number we simply issue an SQL statement:
booktown=# SELECT DISTINCT COUNT(*),global_id,global_time FROM __chdjournal_books GROUP BY global_id,global_time ORDER BY global_time DESC LIMIT 5; count | global_id | global_time -------+-----------+------------------------------- 15 | 789 | 2011-08-29 09:38:29.464475+00 (1 row)
In the
__chdjournal_booksjournaling table there isn't much recent activity. Transaction789is the only one that stands out, and now we can run what effectively is an "UNDELETE" statement:booktown=# SELECT __chdcall_revert(789); __chdcall_revert ------------------ 15 (1 row) booktown=# -- Undeleting a table has never been easier. booktown=# -- Let's verify it worked. booktown=# SELECT COUNT(*) FROM books; count ------- 15 (1 row)
The
15books we accidentally deleted are now back! And the undelete operation is versioned too, in case we reverted the wrong transaction:
booktown=# SELECT DISTINCT COUNT(*),global_id,global_time FROM __chdjournal_books GROUP BY global_id,global_time ORDER BY global_time; count | global_id | global_time -------+-----------+------------------------------- 15 | 789 | 2011-08-29 09:38:29.464475+00 15 | 790 | 2011-08-29 09:41:06.772839+00 (2 rows)
Finally, ChronicDB protects us from an accidental double undo.
booktown=# SELECT __chdcall_revert(789); ERROR: Cannot revert delete transaction 789 since the record(s) affected in table books changed further.
We can disable data versioning at any time of course with a chd unversion command or even permanently destroy data versioning with a chd burn. But after gaining such a powerful capability, why should we?
We have just showed a critical, yet often overlooked component of database change management. The data versioning capability offered by ChronicDB can be applied to a stock database unobtrusively and safely: it does not interfere with existing triggers, it is applied while existing queries are running live, and can also remove itself completely and live at any time. Adding data versioning requires no modifications to an existing application and automatically adds a temporal parameter useful for analytics, which application developers would otherwise need to develop manually.
Lack of best practice for change control remains the number one causal factor of database unavailability. But don't version data merely as a step towards best practice. Version data as a step away from disaster, as it may be the only thing you can do.
How To Hot-Move an SQL Database
Submitted by chronicdb on Tue, 2011-08-23 08:42
If you missed the buzz we recently rolled out in Private Beta the ChronicDB evolution platform. This means that DBAs and developers now gain the ability to automate key data management tasks using a programmable API. We are still handing out invites to select requests, and will admit to having been blown away so far by people's war stories, so don't shy away from telling us yours.
Besides solving the stressful part of moving production data around, what else could moving a database "evolve" into might you ask? Well, imagine your database is now free to open its Fodor travel guide and pick where it wants to visit next. It can book a few Airbnb nights to the next cool pad in the cloud, say move from RightScale to DotCloud, from EngineYard to Heroku, or from Windows Azure to AppHarbor.
Now let's have a look at what this practically means for a DBA. When you need to move a live database to a different machine, you can now do so in three easy steps, without downtime to an application, and without having to run around installing dependencies and rpms:
- You add a database to ChronicDB:
$ sudo easy_install chronicdb $ chd init $ chd add postgresql://postgres/mypassword@mydb.example.org/mydb udi-0dcfec81: udi4.chronicdb.com:64334 vdi-999c94f7: postgresql://postgres/mypassword@mydb.example.org:5432/mydb
- You point your application to the ChronicDB Virtual Machine, in this case
udi4.chronicdb.comat port64334:$ psql -h udi4.chronicdb.com -p 64334 -U postgres mydb -W Password for user postgres: psql (9.0.4) Type "help" for help. postgres=#
- You can now manage your database with the
chdcommand-line tool, just like a version control system. The possibilities are suddenly multiplying here in terms of versioning, so to just move a database live with zero-downtime:$ chd move mydb postgresql://surfer/pass@acting.example.org/mydb
If the database relocation doesn't need to run live or you would prefer to not depend on the ChronicDB Virtual Machine, a
chd clonecan move databases directly, although you need to be sure that there's no possibility of a data inconsistency (be really sure no-one is using the source database.)
We tried to make no assumptions on how brave database administrators may be, so ChronicDB assumes it can connect to the database directly. But as you already imagine we plan on making the access path from ChronicDB to your database even easier and more secure without you having to mock around with your iptables firewall (shh.)
This is certainly the beginning of moving databases. But just as we were blown away by people's war stories so far, we suspect we may be blown away by what else you might imagine our technology could do. Don't shy away now!
(Edit: We were delighted to hear that, though lacking transactional guarantees, the Facebook team moved 20PB of data to a different data center.)
