Thursday, August 07, 2008

Capacity Planning, Architecture, Scaling, Response time, Throughput

First of all let me start off saying that I learned a lot of Capacity Planning from two people. Jozo Dujmovic, and John Allspaw-who by the way is coming out with a book.

Capacity != Performance
. You may have the capacity to do a bubble sort but a bubble sort is still a bubble sort.

Really to Scale you need to know when your application will break. I have a tool set to help determine what application is producing what SQL and use that to figure out which SQL is producing the most load on the system. Some common tricks I do is put the execution path automatically as a SQL comment, then sample the FULL Processlist to build a graph on what application, function, SQL pattern is the top load.

On top of that I use Ganglia to trend the use of each mysql box. Key metrics that I use to determine capacity.

From iostat -x

I/O wait
atime
svctm

If the service average is trending towards 20% I/O wait I know that that is a hard-limit for my server configuration that will cause slave lag.

Jeremy Cole has a good write up and a tool for getting I/O stats that iostat itself does not expose.

If the atime (Response time) is growing, I know that the overall SAN LUNS are saturated. On top of that SAN LUNS typically have larger svctm cutting overall throughput with how MYSQL works. On a side NOTE I despise using SANs for mySQL. Why well that's another post.


Then I have ganglia configured to monitor everything for SHOW GLOBAL STATUS, but really I only look at the following

Com_delete
Com_insert
Com_replace
Com_select
Com_update
Key_reads
Questions
Connections
Threads_created
Slow_queries
Handler_read_next
Handler_read_rnd
Handler_read_rnd_next
Handler_rollback
Innodb_buffer_pool_read_requests
Innodb_buffer_pool_reads
Innodb_pages_created
Innodb_data_pending_reads
Innodb_buffer_pool_read_ahead_rnd
Innodb_data_read
Innodb_data_written
Innodb_row_lock_time_avg
Innodb_row_lock_time
Table_locks_immediate
Table_locks_waited
Sort_merge_passes
Sort_range
Sort_rows
Sort_scan


Next I take the techniques I learned from John Allspaw and build a 3rd order Polynomial and verify that my R^2 is in the 98%tile to see when I need to add more servers. So far so good. Now I have a rough idea when I need to add more servers-a capacity plan. (The techniques involved are various ratios of Users per Application per Shard, busy time, more junk like that)

Now your Architecture allows you to Scale, by ensuring a High Throughput at a low Response Time.

I personally use a architecture that I've started on since 1999-Shard'ing. Brad Fitzpatrick when building Live-Journal really made this concept popular.

With my Federation strategy I've been able to scale some of the most toughest dynamic applications linearly by just arbitrary adding more servers. It takes 5 min. to deploy new DB servers.

So, in summary to capacity plan you need to know how the system works, monitor it and trend it. To scale: your database architecture needs to meet the needs of the app. Are you read or write heavy or both? Do you have a lot of concurrency? Does your app do a lot of sorts? Does your app do a lot of ranges? Is it all of the above? Design to meet the needs, benchmark, know when it will break, and have a plan to recover before it does.

6 comments:

Ronald Bradford said...

You might like to update your title to "Architecture"

Dathan Pattishall said...

haha yea I'll do that

Anonymous said...

"NOTE I despise using SANs for mySQL. Why well that's another post."

Your thinking on that would be very interesting!

Unknown said...

Hello Dathan,
Our client has a fulltime position available in San Francisco for a MySQL DBA. This opportunity is for a start-up that has a lot of growth potential. They can offer a salary in the $100k – 140k range. We had a candidate interview and was offered the position at $130k but he ended up taking another opportunity. I thought I would reach out to see if you might know anyone interested in this role. We can certainly pay a referral fee if successfully place anyone you recommend. I appreciate any guidance you can offer.

Job Responsibilities:

• Database administration tasks such as setup new databases, backups/restores (validation), MySQL db replication, Security.

• Tuning and optimizing the databases configuration.

• Performance tuning the sqls.

• Provide support on data analysis.

• Should be able to setup Linux servers and manage storage for the databases.

• Learn client applications and provide applications support.

• Investigate into the application issues or failures and develop fixes for them.

• Work with the developers to provide DB support

• Can-do-attitude to be successful in startup environment.

TOP Skills

1) MySQL (Very Strong skills)
2) Linux
3) Storage
4) Sys Admin (nice to have)
5) Application support experience and ability to work closely with development team.

Looking for a very strong MySQL DBA who offers a breadth of experience in Application support and storage. Work is currently being done by development and they need to hire this DBA so other engineers can perform their core work. This position would work extremely closely with their current engineering team, primarily developers, who are all pitching in to do this work now.

Although requirements are not specific to Versions of MySQL, Current MySQL Versions in use are 4.0, 5.0, and they are just about to start using 5.1, which has been out for approx 6 Mos.

Client needs candidates who are senior and offer a broad range of experience. Since this is a start-up, everybody pitches in when they need to and each is expected to help out if needed. Communication skills are also extremely important. Although they are working only in US right now, they plan on going global soon. They need candidates who can articulate their thoughts in a succinct and clear way.

This is a traditional Lamp Stack environment: Java based, PHP, etc.

If you would like to learn more about this opportunity, please send me a copy of your current resume and salary request.

Respectfully,
Megan

Megan Dodson
Resource Manager
Phoenix, AZ
480.775.8482
Cell 602.363.3852
Fax 866.620.4300
www.exdir.com

Anonymous said...

Hi

Good article. Did you consider releasing your ganglia mysql metrics as mentioned on: http://www.xaprb.com/blog/2008/05/25/screenshots-of-improved-mysql-cacti-templates

Many ganglia users would find it useful

Unknown said...

After I finish with my next project which is awesome btw, I'll clean up the ganglia package.