sorted by: recent | see : popular

Content Tagged with MySQL + databases

message Schema {
   required string name = 1;
   optional string characterset = 2;
   optional string collation = 3;
}

If you deal with databases for a living, eventually you’ll come across cases where you’ll need to migrate a lot of data from one schema to another. I am not just talking about migrating from one different type of database to another, like from Oracle to MySQL, but from, for instance, a badly-designed schema to one more expertly crafted.

If there are minor differences between the source and target schema, this is a trivial affair. On the other hand, if the schema is completely different, this can be quite a challenge. Moreover, the database being migrated might represent a high-demand website that will need to be done with little or no downtime, with lots of planning and preparation to boot. You may be interacting with the application developers, the systems crew, and juggling tight deadlines as well.

Well, as you may have guessed, I have described some of the roles I now play at a leading social networking company. We are indeed in the midst of creating the “NextGen” product — a complete rewrite and redesign. The new system is designed with modularity and scalability in mind. The old system we are transitioning from was created when the company was much smaller and had 2 orders of magnitude or more less demand. Suffice it to say, it has all the appearances of being crafted by a bunch of “juniors” that just quickly browsed through “PHP for Dummies”, “Database Design for Idiots”, and the like the night prior. That the aging application still works at all is seen as the “8th Wonder of the World”, but to it’s credit it brings in millions in revenue despite all of its faults.

I am an “old veteran” when it comes to software development. In my “advance age”, I’ve decided to do databases as something that I’ve not done before in my 30-year career as software developer. The nice thing is that I find much I’ve learned about algorithms and data structures can also be applied to schema design. It also helps with interacting with the applications development team as I can relate to what their needs are and “bridge the gap”, as it were between the code and the database.

I have chosen Ruby out of all the languages I know — Python, Perl, PHP, C++,Java, etc. — because of it’s expressive power and meta-programming capabilities that most of the other languages don’t either do well, or lack  a clean syntax to accomplish the same.

First, let me speak of my general approach to data migration. You have your source and destination databases. Of your source databases, you will obviously have the main database containing the enterprise’s lifeblood information. Some of that data will relate directly to customer/account activity; some may relate to configuration of how that data is handled; other data may serve as a reference, such as a zip-code database.

Similarly, you will also have target databases, with the same type of data, but organized differently — hopefully more efficiently. Also, what may have been denormalized in the source database you might choose to normalize it in the target, or vice-versa. Perhaps password for user accounts were in plaintext in the source and now you need to md5 them in the target.  Perhaps there were a fixed number of columns in the source tables representing some resource that you wish to store as separate rows in the target for added flexibility and expandability. Again, if you are only dealing with a couple of tables, it’s trivial to do the migration. If, on the other hand, you are dealing with dozens of tables, the problem explodes in complexity.

Since I want to illustrate doing a migration, I don’t want to bog you down with a complex schema; instead, I will take a simple example. Suppose you have a picture display site where each picture was represented by a column in the users table, and you need to migrate this to a more flexible system that will allow any number of pictures per user. If you have 10 million users in this table, doing a ALTER TABLE every  time needed to expand on the number of pictures would be just plain silly.

1
2
3
4
5
6
7
8
9
10

CREATE TABLE old_accounts (
  id INT auto_increment primary key,
  name varchar(100) not null,
  email varchar(100),
  picture1 varchar(100),
  picture2  varchar(100),
  picture3  varchar(100),
  picture4  varchar(100),
  picture5  varchar(100)
) ENGINE=MyISAM;

And here is the new schema we wish to migrate this to:

1
2
3
4
5
6
7
8
9
10
11
12
13

CREATE TABLE new_account(
  userID INT auto_increment primary key,
  given varchar(50) not null,
  sur varchar(50) not null,
  email varchar(100)
  ) ENGINE=InnoDB;
 
CREATE TABLE pictures (
  pictureID int not null auto_increment,
  userID INT not null,
  url varchar(100) not null,
  unique index(userID, url)
) ENGINE = InnoDB;

I have deliberately left out the foreign key specifications for clarity — and some would argue it would be a nasty performance hit under some circumstances, though I’ve not run into that problem personally.

I have written a complete Ruby framework specifically for migration, but as of the time of this writing, that code is proprietary and not yet released to open-source, though eventually I may do that if I get clearance. But basically, I use Ruby classes to represent a “unit” of migration — normally a single source table to one or more target tables. So, using my Migration framework, here’s what this migration would look like in Ruby:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

class UserMigration < Migration
    def migrate_map
        @src_table = {
             :old_accounts => {:PK => :id}
        }
        @dest_table = {
            :new_account = {
                :PK => :userID,
                :id => :userID,
                :name => :given,
                :email => :email,
            },
 
            :pictures => {
                :PK => :pictureID,
                :FK => {:new_account => {:userID => :userID}}
                :picture1 => :url,
            },
 
           :pictures => {
                :PK => :pictureID,
                :FK => {:new_account => {:userID => :userID}}
                :picture2 => :url,
            }, ... 
        }
    end
end

Well, that’s it — almost, and there’s a problem in the Ruby code that you will catch right off the bat if you know Ruby — and I think that if you look at it for a bit, you can figure out what’s going on here. So I’ll leave that as an exercise for you to mull over. You don’t really need to know Ruby at all to understand what’s going on here, and that’s the bit I like about Ruby. You can use it as a type of “meta-language” if you know what you’re doing.

Today I was doing some work on one of our database servers (each of them has 4 SAS disks in RAID10 on an Adaptec controller) and it required huge multi-thread I/O-bound read load. Basically it was a set of parallel full-scan reads from a 300Gb compressed innodb table (yes, we use innodb plugin). Looking at the iostat I saw pretty expected results: 90-100% disk utilization and lots of read operations per second. Then I decided to play around with linux I/O schedulers and try to increase disk subsystem throughput. Here are the results:

(more…)

I’ve been doing some research and evaluation of more cloud computing. Specifically my focus has been on data store, and considering how to augment an existing operation using a popular database such as MySQL.

I’ve been looking first at Google App Engine and now I have my SimpleDB Beta today will be looking here next.

Some observations I’ve struggled with are:

• No Native CLI, say for basic data setup. You can do some programmatic input for SELECT statements in a Query object in a SQL like syntax called GQL, but you can’t do DML
• No simple data viewer. In production you would not do this, but I’m in evaluation and still looking at functionality, verification of results etc. A phpmyadmin clone is what I’m seeking for example. I suspect this would have been a good Google Summer of Code project.
• Python only. While this was great for me to need to spend a 1/2 day to learn about Python syntax, it was just another small starting hurdle. If your organization doesn’t do or use Python, this is another skill or resource needed.

But the biggest concern and hurdle I’m understanding from my traditional principles is loss of control. Loss of control for monitoring and instrumentation, performance, availability and backup and recovery.

Recent issues in performance and unavailability have highlighted that App Engine is not suitable for mission critical web sites, not as your primary focus. I see huge benefits in augmenting access to information, perhaps more historical for example, a well define API within your application could easily support options to consider cloud storage as a secondary storage and primary retrieval of less important data.

My focus when revisiting here will be looking at means of object translate between tables and the Data Store and maybe an API for data transfer etc.

I suspect that when I get to evaluating EC2/S3 more I will have much more support by being able to leverage existing tools and techniques.

I spent this past weekend writing a Paper for a project I’ve been playing with. It is a simplified distributed processing system loosely based on Google’s MapReduce, except rather than focusing on larger batch jobs, it prototypes out some common database application uses. The model is currently very basic, but I plan on exploring this further (possibly with a performance-enhanced implementation in C). I’ve also been reading up on other interesting projects like Hadoop, HyperTable, Amazon’s SimpleDB, and of course the DB interface for Google’s AppEngine. I’m wondering how these distributed models can apply to relational databases (the Pig project has some relational constructs).

For those that have my existing blog bookmarked, or use any RSS/Atom feeds please update your information now.

My new blog can be found at http://ronaldbradford.com/blog/

RSS 2 is http://ronaldbradford.com/blog/feed/rss2, Atom is http://ronaldbradford.com/blog/feed/atom

Ok, so before you read on please change now. While all old links will redirect I encourage you to take a few moments in your browser and RSS reader.

So what’s new. Well as Baron stated in I moved this blog to pairLite with zero downtime, and it was easy I moved my old blog site blog.arabx.com.au and my temporary staging site ronaldbradford.com and transitioning my passworded beta site all with no downtime. Even with all my testing over several weeks, and several test users, my production migration had one hitch with some internal url re-directs not working correctly, but my overall site main links were not affected. You would have had to been quick at 1:30am on Saturday to catch it.

Other features is a consolidation of information from various sites now all under my brand ronaldbradford.com and allowing me now to expand on that. I’ve also split my professional blog from my personal views, photos and comments as well as new SEO/SEM optimizations. More on these experiences later. My theme was actually inspired by some business cards I had made in Australia while home last week, quality 300GSM color cards that were designed and printed in a just a few hours and are very good quality (which was very impressive).

I’m now free to work on so many other projects including many MySQL draft posts now Version 1.0 of my new site is out. As with all release early, release often sites, expect 1.1 soon!

I’m migrating my existing WordPress run blog site at blog.arabx.com.au to a my new site ronaldbradford.com (which is not yet publically available)

As part of this process I’ll be doing a number of upgrades/changes including:

1. Update blog software to 2.5.1 from 2.0.2 (I’d previously done a 2.0.2 upgrade to 2.3.2, but not deployed)
2. Migrate to new domain
3. Upgrade existing MySQL 5.1 version from 5.1.11 to 5.1.24
4. Migrate database to using MySQL 5.1.24, from 5.0.22 (my server runs 5.0 and 5.1 instances)
5. Split my blog into Professional & Personal

Upgrading
The upgrade is straightforward, backup database, download latest wordpress software. I run full revision to older versions via directories + symlinks so my installation is more complicated, but fully recoverable. Install, and run upgrade script. That all works, but my site breaks. Suspecting is my heavily customized them, by disabling that my site is up. One to add to the TODO list.

Migrate to new domain
Dump + Reload data into new schema. Copy Wordpress install. I had to make two data changes to correctly use the new domain.


update wp_options set option_value='http://ronaldbradford.com/blog' where option_name='siteurl';
update wp_options set option_value='http://ronaldbradford.com/blog' where option_name='home';


Upgrade MySQL 5.1 version
That was also relatively straightforward. I was surprised I was running such an old 5.1.11, but I remember originally using 5.1.6 in production use before that.

Migrate database to 5.1 from 5.0
This is where my problems have begun. Wordpress does not appear to like host+port stuff. I can confirm access via MySQL client. At 9:30pm Sunday night, this may have to remain in the unresolved bin for a few days.

I was prompted following a few key words by Marten Mickos at the Sun Dinner on Wednesday evening, and subsequent one on one discussion with Marten, to post my thoughts of some significant news this week announced at the MySQL Conference. The decision to provide as it’s been termed is “Enterprise only features”. It is unfortunate this was not discussed in Marten’s opening keynote, having been exposed the evening before in the Partner’s meeting and hitting the blog sphere before the conference officially started.

MySQL, past, present and future as an Open Source company requires a functional business model to succeed. This includes the funding of resources and the technology progression. It is also necessary in this business climate to build a successful business quickly. How do you do this? Well that’s probably the difference between a successful CEO and an unsuccessful one, and what Marten Mickos has produced is clearly very successful.

I may not necessarily agree with the decisions made, more specifically not understanding at this time the rationale of which features are free and which are likely to be commercial, but I respect the decision made by Marten Mickos. These new feature considerations are in a future release of MySQL, they are also I’m sure not yet set in stone, however MySQL can not be all things to all people, no software can. It reminds me of the Homer Simpson car, designed to do everything Homer wanted in a car, but it bankrupted the previously successful company due the views of one individual to solve all their own needs, but not the needs of the majority. Who is affected by this decision, who will benefit, again it’s too early to tell.

Monty indicates this is a MySQL decision, not a Sun decision. This indicates the transition of MySQL to being under the Sun banner of the largest open source company is well, still in transition. Today it was again confirmed to me, that the MySQL database will always be GPL and MySQL will not never revoke the functionality that powers the world’s largest websites as free software for the database server.

As an advocate for the MySQL community, I’d like to consider myself one of the pulses, a thought in the MySQL conscious and even a vocal lobbyist. I am however not interested in being a disruptor in the MySQL ecosystem. A Communications Lesson on Slashdot I believe correctly states “Sun to Begin Close Sourcing MySQL.” The headline is wrong.

A number of people have posted their comments, let’s stop bickering about it, and let’s see something positive happen for the benefit of the community. For example, as I’ve mentioned previously regarding the lack of differentiation for the Community version, again mentioned by Mark Callaghan in A better (community) HEAP engine where a worthwhile patch can’t be of benefit to the community in a binary release for the lay person.

As new COO of PrimeBase Technologies and in charge of the Business Solutions division, I am responsible for contributing to the success of the company, to play a significant role in the functional business model, to ensure funding for resources and technology progression. In other terms, how can revenue generation be achieved to fund prominently salaries of staff, including my own. How am I going to do this? This will be the difference between my huge success or not.

The 2008 Google Summer of Code MySQL Projects are now available. MySQL has 14 listed projects, one of the ~190 different Open Source products listed. Unfortunately there is no summary to see the total number of projects being sponsored across all products.

Media Blob Streaming actually has the luxury of two approved projects, so we will have plenty of mentoring work at PrimeBase Technologies.

Raj Kissu Rajandran will be working on BLOB Streaming Support for phpMyAdmin and KishoreKumar Bairi on Streaming Enabled MySQL Driver for PHP. Welcome to our team for your respective projects.

Jay’s opening lines regarding the final MySQL Conference keynote speaker was: “I work with a lot of data. I think peta-bytes, maybe exa-bytes”. This was relating to Jacek Becla from the Stanford Linear Accelerator Center, giving his presentation on “The Science and Fiction of Petascale Analytics”.

The goal of the Large Synoptics Survey Telescope (LSST) is the storage of 50+ PB of images and 20+ PB data.
Let’s just clarify the size. 20 PB of data = 20 years of HD Movies = 2000 years of 128kb MP3

The next database frontier is obviously building huge databases. What part will MySQL or other relational databases play? Some interesting facts were.

• The Digital Universe Created 161 Exabytes of data last year.
• Google, processes 20 petabytes of data per day.

The Operational plan for LSST Project Timeline is 10 years, only starting in 2014. The timeline:

• 2009 Choosing Technology
• 2010-2014 constructions
• 2014-2023 production

The primary goals are: Scale, parallelize, fault tolerant.

• http://en.oreilly.com/mysql2008/public/schedule/detail/874
• presented by Charles Lee of Hyperic
• Hyperic has the best performance with MySQL out of MySQL, Oracle, and Postgres in their application
• I suddenly remember hyperic was highly recommended above nagios in MySQL Conference Liveblogging: Monitoring Tools (Wednesday 5:15PM)
• performance bottleneck
• the database
• CPU
• memory
• IO
• disk latency
• network latency
• slow queries
• media size deployment example
• 300 platforms (300 remote agents collecting data)
• 2,100 servers
• 21,000 services (10 services per server), sounds feasible
• 468,000 metrics (20 metrics per service)
• 28,800,000 metric data rows per day
• larger deployments have a lot more of these (sounds crazy)
• data
• measurement_id
• timestamp
• value
• primary key (timestamp, measurement_id)
• data flow
• agent collects data and sends reports to server with multiple data points
• server batch inserts metric data points
• if network connection fails, agent continues to collect but server "backfills" unavailable
• when agent reconnects, spooled data overwrite backfilled data points (why not use REPLACE for all inserts?)
• things are very basic so far
• batch insert
• INSERT INTO TABLE (a,b,c) VALUES (0,0,0), (1,1,1),…
• using MySQL batch insert statements vs prepared statements with multiple queries in other databases seems to improve overall performance by 30%
• batch inserts are limited by 'max_allowed_packet'
• other options for increasing insert speed
• set unique_checks=0, insert, set unique_checks=1 (definitely need to make sure data is valid first)
• set foreign_key_checks=0, insert, set foreign_key_checks=1 (same concerns as above)
• Hyperic doesn't use the 2 above
• INSERT … ON DUPLICATE KEY UPDATE
• when regular INSERT fails, retry batch with INSERT ON DUPLICATE KEY syntax
• it's much slower but it allows
• this is all basic, where are the performance tweaks?!
• batch aggregate inserter
• queue metric data from separate agent reports
• minimize number of inserts, connections, CPU load
• maximize workload efficiency
• optimal configuration for 700 agents
• 3 workers
• 2000 batch size seems to work best
• queue size of 4,000,000
• this seems to peak at 2.2mil metric data inserts per minute
• data consolidation
• inspired by rrdtool
• lower resolution tables track min, avg, and max
• data compression runs hourly
• size limit 2 days
• every hour, data is rolled up into another table that holds hourly aggregated values with size limit 14 days, then that one gets rolled up into a monthly table, etc
• this is is a good approach if you don't care about each data point
• I'm overwhelmed by the amount of "you know"s from the speaker. Parasite words, ahh! Sorry Charles
• software partitioning
• measurement data split into 18 tables, representing 9 days (2 per day)
• they didn't want to do more than 2 SELECTs to get data per day, hence such sharding
• oddly, Charles didn't actually use the word 'shard' once
• tables truncated, rather than deleting rows => huge performance boost
• truncation vs deletion
• deletion causes contention on rows
• truncation doesn't produce fragmentation
• truncation just drops and recreates the table - single DDL operation
• indexes
• every InnoDB table has a special index called the clustered index (based on primary key) where the physical data for the rows is stored
• advantages
• selects faster - row data is on the same page where the index search leads
• inserts in (timestamp) order - avoid page splits and fragmentation
• shows comparison between non-clustered index and clustered index (see slides)
• still no mention of configuration tweaks
• UNION ALL works better than inner SELECTS because the optimizer didn't optimize them enough (at least in the version these guys are using, not sure which)
• recommended server options are on the very last slide, I was waiting for those the most! I guess I'll look up the slides after

• MySQL Conference Liveblogging: EXPLAIN Demystified (Tuesday 2:00PM)
• MySQL Conference Liveblogging: Performance Guide For MySQL Cluster (Tuesday 10:50AM)
• MySQL Conference Liveblogging: Applied Partitioning And Scaling your (OLTP) Database System (Wednesday 11:55AM)
• MySQL Conference Liveblogging: Introduction To The BLOB Streaming Project (Wednesday 3:00PM)
• MySQL Conference Liveblogging: MySQL Performance Under A Microscope: The Tobias And Jay Show (Wednesday 2:00PM)