Monday, September 10, 2012

SAP Business (Data) Warehouse

Hari ini hari kedua pelatihan SAP Business Warehouse. Dulu sudah pernah dapat pelatihan serupa dari SAP, kini yang membuat materi dan menjadi instruktur ialah dari salah satu vendor SAP. Bagi yang belum pernah mencoba, silakan ketik tcode RSA1 di sistem SAP BW anda. Jika bener-bener terpasang BW, maka akan muncul Data Warehousing Workbench.

Meskipun ini sudah kedua kalinya, saya temukan SAP BW itu sebenernya cukup rumit. Memang dia  flexibel. Kerumitan pertama ialah bahwa dia punya banyak istilah-istilah yang tidak umum, bahkan bagi orang yang biasa kustomisasi SAP dan develop aplikasi. Memang typical SAP ialah membuat istilah dan memaksa orang untuk memahaminya..
Tabel padanan berikut ini mungkin membantu :
-> InfoObject karakteristik = Tabel. Di SAP BW kita bisa seenaknya membuat tabel dengan istilah 'InfoObject'. Ini adalah tabel tabel yang bisa dijadikan Dimension. Tabel tabel InfoObject bisa dijadikan sasaran foreign key dari tabel fact.
-> PSA = Penampungan Sementara [Persistent Staging Area]. Hasil penarikan data dari file atau sistem lain tidak langsung diproses melainkan masuk ke PSA dulu. Dan jika anda menarik data berkali-kali, data PSA mungkin akan terdobel-dobel. Sempat bingung  ketika gak sengaja merun ekstraksi data dua kali, pengolahan data berikutnya error terus. Dan seingat saya pada pelatihan pertama saya juga menemui masalah sama (isi PSA dobel-dobel).
-> Data Transfer Process = Aliran data di DFD. Pada tool ETL lain seperti Pentaho Data Integrator atau Talend DI aliran data dilambangkan oleh garis. Namun di SAP, DTP disimpan sebagai icon yang ada di sebuah tree. DTP adalah aliran data dari PSA ke InfoObject atau entitas SAP BW lainnya.
-> InfoPackage = Penjadwalan untuk menarik data dari sumber ke  dalam PSA.
-> Transformation = Mapping. Untuk ini SAP menyediakan tool graphical untuk menarik garis dari satu kolom sumber ke kolom tujuan. Tool SAP cukup baik, dan lebih responsif daripada GUI berbasis Java milik Talend. Saya tidak tahu kalau dibandingkan dengan Pentaho.
-> Data Source analog dengan PSA. Jadi satu data source akan memiliki satu PSA.
Dari Transaction RSA1, kita bisa melakukan semua pendefinian proses extraksi data dan transformasi. Hal yang kurang jelas bagi saya ialah  bagaimana navigasi yang efektif untuk melihat isi PSA, melihat isi tabel InfoObject yang sudah diupload oleh DTP. Dan juga bahwa kita harus sering-sering melakukan aktivasi tiap kali merubah screen tertentu. Kadang tombol aktivasi tidak aktif padahal object sudah modified, dan versi aktif yang tercompile belum sama dengan versi modified. Solusinya ialah pura-pura change baru klik aktivasi.

Persistent Staging Area (PSA)

Cara melihat isi PSA ialah mengklik kanan pada Data Source dan kemudian klik Manage. Umumnya di SAP selalu ada beberapa cara untuk menjalankan sebuah fungsi, namun khusus untuk yang ini saya baru temukan satu cara.
Jika ternyata isi PSA ada lebih dari satu set data, maka kita perlu hapus yang tidak perlu. Pilih yang hendak dihapus dan klik kanan, pilih 'Delete Request from DB'. 
Menurut saya SAP memberikan tool yang cukup rumit ketika kita sampai harus menghapus PSA. Arsitektur tool Data Integration lain yang saya kenal tidak menyimpan state di elemen-elemen perantara sehingga pengguna tool tidak perlu maintain di level ini.

InfoPackage dan DTP

SAP membedakan InfoPackage dan DTP, karena keberadaan PSA. Ini membuat keadaan jadi lebih rumit. Padahal kedua-duanya sama-sama aliran data, yang dalam tool lain cuma digambar sebagai garis. InfoPackage adalah aliran dari data sumber menuju PSA. DTP ialah aliran dari PSA menuju tabel-tabel data warehouse seperti InfoObject.
SAP memberikan monitoring yang cukup lengkap. Namun memang jadi banyak layar yang harus dilihat. Untuk monitoring InfoPackage dan DTP dapat kita klik icon Monitor (gambar osiloskop) di toolbar baris kedua (ketiga kalau baris navigasi TCode kita hitung) ketika kita sedang memilih InfoPackage/DTP.
Dari Monitoring DTP misalnya, kita dapat mendobel klik hasil proses terakhir, dan di Toolbar akan muncul icon "Administer Data Target". Ini adalah jalur navigasi yang cukup bagus namun kurang obvious, maka setelah kita jalankan DTP, dan melihat hasilnya sukses, kita dapat langsung melihat tabel InfoObject hasil proses DTP tersebut.

Sekian bagian pertama tentang SAP data warehouse. Mungkin saya akan menulis yang kedua, tapi tidak janji ya..

Recovering from Deleted Row Disaster in MySQL

This post is dedicated to accidents that every so often resulting in deleted rows of data.
Of course everyone should have backups. But things do happen when the backups nowhere to be found, or not being done often enough, or we're storing data in a forgotten database server.
In the event of such incident.. deactivate your apps. Prevent unnecessary writes to db that could make your data overwritten. In case of oracle database, you could try flashback query that assume the transaction is still fresh in the redo log. In case of mysql database, copy the whole data directory into a safe location, where we would try to dump deleted rows using Percona Innodb recovery tool.
But such methods failed miserably when we found out about the accident much too late. I think every person should be responsible for their actions, even if the action is about deleting data. But the consequence of mass-delete actions is very different from single-row deletes. That is why mass delete function must be avoided at all cost.. just kidding. There are few legitimate reasons to provide mass delete functionality, and we should always try to help our users to do their tasks. But if there is such functionality, we must : a. ensure undo mass delete functionality is implemented as well, or b. ensure informations deleted is saved into some other tables ( folders) upon mass delete executions, or c. ensure the informations deleted is still remained in the system in another form (another table stores similar data and not affected by delete command in the first table). The functionality to actually recover the mass deleted data could be left unimplemented until the event of the accident, but you must be certain that the information stored during mass delete are correct and sufficient for recovery.
The similar rules apply to mass update functionality. The safeguards must be implemented, snapshot of changed data should be serialized and stored in a different database table. 
I hope if there ever such an accident to your app, recovery could be done and no work would be lost.

Saturday, September 1, 2012

Troubleshooting Enterprise App Performance

Have you ever deployed an application, and  find out that its real world performance is less than what you expect? Maybe you haven't got time to do proper load testing, or maybe the production environment have different performance characteristic. Fear not because for some common issues we could still improve performance even when time running out. In this blog post I will try to create conceptual framework for troubleshooting enterprise application performance. Enterprise application in this context is actually synonymous to information system (as Martin Fowler has said in his Patterns in Enterprise Application Architecture). I will use three application that I have engineered as examples. The first, application P, is primarily used for viewing the result of complex business logic calculation, have transactional features but the transactional function usage is 1 : 10 to the view/report function. The second, application C, is primarily used in transactions. The third, application S, is connected to three external systems and three database systems. I will explain what steps I have done to improve the performance of these 3 apps. All three is in production, application P have about 20000 potential users, application C have about 20000 users, application S have only about 50 users. Daily data volume for P is extremely small, for app C is small, for app S is medium.

Performance Hypotheses

There is a rule of thumb when improving performance, namely the pareto rule : that 80% of application time is spent in 20% part of the entire system. That means our first objective is to identify system bottlenecks. Emphasis here, there might be more than one bottlenecks and removing all of them might not be feasible in terms of time or cost.
For simple systems, such as when the app only connects to a single database, we might put one hypothesis that the bottleneck is in the database. And write another hypothesis that the bottleneck is in the application server.
For other systems, it is not that simple, such as with application S which has more than one external system interface and more than one database connected.
So for first step in the framework we enumerate all systems involved in the operation of the aplication. All databases, all services, pools, etc. Each system is one bottleneck candidate.

Step 1. Enumerate all system involved, for each system create a hypothesis that the system is the one causing performance bottleneck

I have 3 application as an example. Let me write the hypothesis for each application.
Application P. Only connected to an Oracle database and LDAP directory. so the hypotheses are :
P1. Bottleneck in the PHP Web server
P2. Bottleneck in the Oracle DB
P3. Bottleneck in the LDAP directory
P4. Bottleneck in interconnection between systems (networking)
Application C. Only connected to a MySQL database that replicated back to back. The hypotheses are :
C1. Bottleneck in the PHP web server
C2. Bottleneck in the MySQL DB
C3. Bottleneck in the LDAP directory
C4. Bottleneck in interconnection between systems (networking)
Application S. Connected to SAP ERP system, Joget workflow service, two MySQL database, one Oracle database.
S1. Bottleneck in PHP web serverS2. Bottleneck in SAP ERP system
S3. Bottleneck in Joget workflow service
S4. Bottleneck in MySQL A
S5. Bottleneck in MySQL B
S6. Bottleneck in Oracle DB
S7. Bottleneck in interconnection between systems (networking)
Each host/system has each performance characteristics that might contribute as application bottleneck. In general for each host, we have :
  1. CPU usage. CPU bottleneck might occured if CPU usage > 70 or 80%
  2. Memory usage. Memory could became bottleneck if swap is > 0 AND swap activity per second is high.
  3. Disk usage -> this is much less related to performance. If free is 0, the host is disabled in some aspect.
  4. Disk IO activity -> this is more likely related to performance. 20 .. 40% IO wait already indicates some kind of disk IO bottleneck.
  5. Network usage -> in some conditions this could impact performance 
For database hosts, in addition to these 5 characteristics, we have :
  • query execution bottlenecks. High disk IO is an indication of such bottleneck.
  • query parsing bottlenecks. High CPU is an indication of such bottleneck
Each database system might have tools, built in or additional, that help us to detect bottlenecks.

Diagnosis

From each hypothesis we could do test or checks that could strengthen or weaken the hyphotheses. Example, do repeated load testing while watching sql queries from a dba session. If there is a query bottleneck then we would find out from the most often sql text shown in database sessions monitoring. If the web server CPU is high then the bottleneck is more likely in the application script parsing.

Step 2. Enlist more specific performance bottlenecks and how could we test or check for such bottleneck.

Not all hypotheses I could check because limitation of what is being allowed for me to do in each system. Let me enlist some diagnosis steps I have done :
P1. check :  do a 'top' in the linux web server to check for CPU bottleneck while load testing the application using apache bench ('ab'). watch for high cpu (%us), memory use, and I/O waits (%sy and %wa). I have to change the application to ignore authentication to make the test easier.
P2. check :  monitor database sessions while load testing running repeatedly. The most often SQL query shown is identified as query bottleneck.
For application C, is similar:
C1. check : use 'top' command. Because web and database  in the same system, watch for high cpu in the php-cgi process and mysqld process. For other configurations might want to watch for apache processes.
C2. check a: connect using mysql's root user, do a show full processlist while the application is being used by more than 100 users. Actual queries that frequently shown is identified, but unable to acted upon because there is too many query, and no query specific improvement could be done only by examining them.
C2. check b: enumerate pages being used by user, configure the PHP framework to display timing results and sql query executed in each page. Thus the bottleneck identified : the pages execution is not written in optimal way, inefficiently doing a lot for query for simple displays. Part of the page executes a query which fetch a lot of data that is not being used at all. Another part is identified to fetch a lot of data to determine simple fact.
For application P, is a lot more complicated. Because the user is too few, performance indicators will be weak (user will perceive terrible performance but by the time we go hunting for bottlenecks the performance indicator will already return to normal) , so we must simulate a larger load to get better indications. In the past we used testing tools like the Grinder or JMeter to simulate large load. But I must confess that I simply refreshed my Firefox page repeatedly to simulate load during application P's troubleshooting.
S1.  check using top command  in the web server. With apc activated in the PHP web server, CPU usage is low enough, bottleneck hypothesis is weakened.
S2.  In the SAP server, OS-level measurements are simply off limits for me or not informative enough. So I did a timing measurement each time SAP is called. This is done by using PHP profiling functions to mark start and end each SAP RFC execution, the profiling functions being used is provided by the PHP framework we used (Yii::beginProfile and Yii::endProfile). Activating profile component in the PHP application's configuration will show the profiling result in the lower part of application page.
S3.  Joget workflow is a tomcat-based Java application. Doing a 'top' in the joget server and joget's database server shows a lot of CPU activity by MySQL and also Java process. Thus the hypothesis is strenghtened. Further bottleneck identification is done by using jvisualvm. It was a hassle to setup (it has a bug that caused me unable to monitor remote apps), in short I used the thread dump functionality repeatedly to identify what part of the joget workflow service that became bottleneck.
For S4, and S6, no checks done but conceptually checks that I did in P2  and C2 could be done.
For S5, I did a show full processlist repeatedly, and find out some queries indeed became a query bottleneck by appearing often in the list.
For P4, C4, and S7 I didn't do any checks yet. Conceptually we could use iperf (wikiref, jperf) to test for end to end network bandwidth and packet loss. We could also plot network traffic using Munin (ref) in each host to determine whether traffic saturation occured or not. Or better yet, network traffic graph in network switches involved could help us strengthen or weaken these network related hypothesis.
These actions I summarize as :

Step 3. Check each bottleneck hypothesis. If the check confirms (strengthens) the hypothesis, breakdown the bottleneck into a smaller hypotheses relating smaller part of the system. Check the smaller hypothesis, if it is found to be true, breakdown it further into smaller parts if possible. 

Step 3-A. The check&breakdown step should be applied recursively (repeatedly, for you non programmers) if possible.

Step 3-B. For database bottleneck, could be breakdown into :

  • SQL query (or queries). Frequently shown SQL queries could became a clue that such SQL became a bottleneck.

  • SQL Parse or SQL Execution. SQL parse problem could be fixed by changing the application to use query binding.

  • Database capability problem. If there is a problem with almost all SQL queries, then database capability is identified as bottleneck. This is further breakdown into: A) Database CPU bottleneck B) Database IO bottleneck C) Other Database Problems

Step 3-C. For bottlenecks in application script, identify which part of entire application is bigger bottleneck by using sampling or profiling techniques (choose one from the two different approach). Execute a repeated load testing in parallel with execution sampling/profiling.  If using manual profiling, examine timing results and breakdown the larger part further.

Sampling techniques is essentially finding out what part of application logic is being run at one time. The most often part that shows up in the stack trace is identified as a bottleneck. 
Profiling is essentially measure time taken to do some part of application logic. If we found, for example, one page is slow, then we apply profiling functions (change the application) to find out the time related accesses to external systems, and also time taken for parts of our application logic. If the result is, from application logic part A,B,and C, the time taken by part C is 90%, then we should profile parts of application logic C, breaking it into C1, C2, C3. Repeatedly checking the results and apply  further profiling on the largest part will identify the smallest part of the application responsible for larger part of execution time.
For application using C/C++ programming language, execution sampling could be done by using GDB (gnu debugger). The application should be compiled with debug information enabled. Do 3x - 5x consecutive thread dumps  with 2 seconds between each dump using thread apply all backtrace command. In single-threaded multiprocess configuration we are to execute backtrace command in each and every processes.
For application using Java programming language,  execution sampling could be done using thread dump functionality in jvisualvm tool. Java automated profiling tools also could be used, but because of the performance degradation associated with automated profiling techniques I seldom use any of them.
For application using PHP programming language, one of my previous blog post could be implemented to do stack dump for PHP pages that are running. Profiling could be done by modifying application to call profiling functions. These will degrade performance but selective implementation will ensure negligible performance impact.
The reader is encouraged to find out what kind of method works for other languages.

Performance Improvement Techniques

After specific bottlenecks is found out, we need to try improve condition by various means. Because of the many factors involved, not all bottlenecks could be avoided.

Step 4. For each positively identified bottleneck, try to improve performance by some means

For application P, I improved the application by adding index for tables that being used, by examining what column is used in the bottleneck queries. Note that adding unnecessary index will slow down data manipulation activity (insert, delete, updates). 
For application C, improvement is done by rewriting PHP logic in the slow pages found out during testing.  Because we use Yii's ActiveRecord classes, embedding a few with() function calls to some find() in the controller will result in automatic join to reduce query count done to the database. In some other find() we add more condition criteria so the rows retrieved is only the one we need.
In other part of the application, a flag column is add into one of tables. This flag is a substitute to querying another table. Called performance-improving denormalization, we add another place to store some data that normally obtained by aggregating data in another table, change each and every activity that modify the source data table to automatically update the flag, and replace the aggregation query to source table with simple query to the table containing the flag. The logic is that we trade the reduced time required for aggregation query with increased transaction time relating to the source table.
For application S, it is much more complex. Examining S2 profiling results, it is found out that excessive SAP RFC calls is being done for data that slowly changing. The step taken is to add a caching layer for such RFC calls. The Yii's cache component is found to be a good solution for this problem.
S3 bottleneck is shown to be existed, and from the thread dumps it is shown that SLA calculation is done by Joget while we only need general workflow activity information. The solution we take is by modifying the Joget workflow service so there is optional parameter called nosla that will prevent SLA (service level agreement) calculation. The PHP application is also modified to include parameter nosla=1 during specific calls to Joget workflow.
While S4 bottleneck hypothesis  is found to be false, S5 hypothesis is shown to be strong.
Analyzing query bottleneck from S5 checks, it seems that MySQL 5.1 have troubles optimizing IN query which actually have only one element in the 'IN clause'. The solution is replacing the MySQL 5.1 with MariaDB 5.3.

These explanations is summarised as follows:

Step 4-A. Consider upgrading your database server if the bottleneck is shown to be fixed in the newer version 

Step 4-B.  Consider using caches. Data caches can be used to reduce calls to external system that didn't manipulate data.  PHP Opcode caches speed up PHP parsing.

Step 4-C. Consider changing your data schema, denormalize data if necessary.  

Step 4-D. Consider changing application logic  implementation to access less data. Less rows is more impact than less columns. Avoid unnecessary query to BLOB columns when we only need data from other columns. 

Step 4-E. Consider creating database indexes to enhance select query performance

For completeness, I include other ideas that I have done when optimizing another  application not discussed in this post :

Step 4-F. Consider implement part of application logic in other faster language, such as Java or PL-SQL, when CPU bottleneck in web app is an issue. But try to improve the algorithm first before porting logic to other language.

Step 4-G. Consider increase memory pool configuration in the database server if buffer hit ratio is small (example, less than 95%).

Step 4-H. Consider increase parallel processes in the web server if there is a large amount of concurrent user and memory use is below 70%. This should only be done if we are certain that no database bottleneck exists and no CPU bottleneck in web server. Interpolate memory usage correctly (see maximum memory used for each php-cgi or httpd process, multiply by maximum process expected). Remember that OS will automatically use free memory as disk cache, and too small free memory will degrade IO performance.

And also some ideas that haven't been tested:

Step 4-I. Consider add more disk, enable striping with large blocks if there is I/O bottleneck. Consider add more CPU if the CPU bottleneck could not be reduced in other ways.


Summary

In this blog post I wrote steps that I have done to identify performance bottlenecks, and what steps I take to improve performance. I also tried to write a troubleshooting framework as a 4 step process:

Step 1. Enumerate all system involved, for each system create a hypothesis that the system is the one causing performance bottleneck

Step 2. Enlist more specific performance bottlenecks and how could we test or check for such bottleneck.

Step 3. Check each bottleneck hypothesis. If the check confirms (strengthens) the hypothesis, breakdown the bottleneck into a smaller hypotheses relating smaller part of the system. Check the smaller hypothesis, if it is found to be true, breakdown it further into smaller parts if possible.

Step 4. For each positively identified bottleneck, try to improve performance by some means

The practical implementation of each step is explained for each of three example application that chosen for this blog step. I hope these could serve as a guidelines for others (and myself) when the need to troubleshoot application performance arises.

HA Storage Cost Comparison : NetApp MetroCluster vs DRBD

One of the cost saving techniques being used by Google is that they used commodity SATA drives in their GFS Clusters, as opposed to a specialized storage cluster. That makes me wonder just how much saving that could be realized by using SATA drives.
For our comparison case, lets build a highly availably MySQL server. This MySQL is not clustered, but will be failover onto the second host upon first host's failure. Our core solution primarily depends on two servers, one server runs mysqld and the other will run mysqld upon failure. Both mysqld is configured to save data to the same storage, so there only can be one mysqld server allowed to run at a time. The storage solution being used is NetApp storage solution described in http://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=1001783, that will supply storage redundancy and availability zone redundancy. NetApp's MetroCluster will do synchronous mirroring between two separate NetApp disk shelves. For comparison we will use DRBD synchronous storage replication on commodity SATA drives (http://dev.mysql.com/doc/refman/5.5/en/ha-drbd.html) with similar effect. Heartbeat open source cluster software will be our means to failover in our DRBD solution.

Requirement 

High Available MySQL server with 5-6 TB storage.
Modes of failure that need to be supported :

HA Scenario :

  • Host Failure -> Hardware problem on one of the server
  • Storage Failure -> Hardware problem on one of database storage  drives
  • Availability zone Failure -> Entire zone disabled by some problem, such as total network loss or total power disruption. We assume we have two availability zone, maybe one zone in one building and the other is in the building across the street.

Note: Network failure not discussed here because network redundancy is a blog topic for another day :), And disaster recovery not being a requirement because cannot be fulfilled using MetroCluster nor DRBD. MySQL master slave replication is a better solution for disaster recovery.

Expected failover time is in one minute or less. In practice this will be achievable depending on InnoDB Log size configured in the MySQL server, the larger the log size will result longer failover time.

Solution Design

Virtual Machine or physical servers could be used interchangeably. Performance implications of the choice of physical or virtual server might be significant but lets assume its already decided by IT organization's policy. VM servers notably easier to configure onto HA because we could configure Fault Tolerant feature into VMs, but in the open source solution we deliberately choose not to because Heartbeat cluster software installed into two machine will provide fault tolerance onto our solution.
This solution requires two servers. If we require to support availability zone failure, then the server will be deployed across two availability zone, with the consequence of some performance impact.  If we are not required to support such failure, then deploying the two servers in the same room connected to the same switch will give us better performance.

A. NetApp solution

NetApp disk shelves with one controller and configured in a MetroCluster is enough to provide storage failure and site failure protection. It still has local SPOF on the controller but lets ignore it for now because NetApp stuffs are pricey :). MetroCluster configuration ensure that each disk is mirrored in the other availability zone. 
Host failure will prompt VMWare to restart the VM, and if not possible, start the MySQL VM in the other host. Storage mirroring and storage failover in event of failure will be taken care of by NetApp MetroCluster. Availability zone failure requires manual intervention to do a forced takeover in the surviving VM site (see http://communities.vmware.com/message/1394567)
Product required :
Two disk shelf with 5.4 TB total capacity, Est price @$32,002.00 (ref : http://storagemojo.com/storagemojos-pricing-guide/netapp-price-guide/)
(DSK SHLF,12x450GB,10K,6Gb SAS,IOM6,-C,R5).
Two Brocade 200E Distance Bundle @ $750
Two FAS 3170 FAS3170A-CL-BASE-R5 @ $51,224
Note: For the Brocade I don't know which one to buy so I choose the lower price one.
Total  : $167,952,00 (Not included : VMWare licenses and VM server nodes)

Storage specific price: Price per redundant GB is 2x$32002 / 5400 = $11.8/GB

B. DRBD solution

Our DRBD solution uses commodity SATA drives in place of NetApp disk shelves. Server's price (A HP Proliant DL 180) will be counted with storage price because 4 drive bay limitation for the server. If we need more storage we need to buy another couple of servers and share the additional storage via NFS or iSCSI (and also mirror them via DRBD).
Product required : (ref: HP Proliant 100 product line)
2x HP Proliant DL 180 G6  with 4 GB memory and Xeon E5606 quad core: $1,824.00
8 x  HP 2TB 3G SATA 7.2K Hot Plug 3.5 MDL Hard Drive - 1-year warranty  @ $ 509.00
(4 drive in each server)
2 TB configured as root filesystem
6 TB configured as mysql data, giving 6TB mirrored storage
Total : $18,664.00
Upper bound Storage specific cost, price per redundant GB :  $18664 / 6000 = $3.11

The DRBD solution typically failover in under 1 minutes, this is mostly the time taken to start MySQL because the Heartbeat system have < 1 second response. (see note above regarding InnoDB Log size impact). When Heartbeat detects that filesystem failure occured on the primary disk, it will initate failover. This also occurs when it detects primary host's no longer has a heartbeat, that could be caused by host failure or site failure. Heartbeat will start the MySQL server in the secondary node and could also take over the primary's IP if necessary.
HA MySQL configuration with DRBD



Short Summary

Comparing upper bound storage cost of our DRBD with SATA solution with NetApp solution (disk shelf only), we have comparison of $3.11 / Gb versus $11.8 / GB. Tremendous cost savings indeed when choosing SATA, even if we take the whole system cost of DRBD solution into account.
Comparing total system cost for similar capacity (6 TB vs 5.4 TB redundant storage), we have $18,664.00 vs $167,952.00 .. a difference of almost 1 : 9 between NetApp solution and our DRBD solution. This primarily caused by costs of synchronization support devices.

What does the large amount price difference buy us for the NetApp? 12 disk performance vs 4 disk performance for one. But that many disk could give us no performance gain if the condition not right (such as when striping and data spread not good). Vendor support for another. We could buy linbit's Support for DRBD solutions but I haven't found a price list yet. But from the point of view of High Availability features seems that the DRBD solution is adequate by giving us failover time under 1 minute.

Other Scenarios

This configuration could be modified to support disaster recovery scenario by adding another Mysql server, with the same redundant storage, located in remote location. These two Mysql server is configured as slave to the original server, and by giving this server the same capability as the first, it will be ready to perform with full performance and availability when the need arise.

More IO performance could be given by configuring the DRBD solution with smaller capacity (but more performant) disks and more servers, sharing storage using iSCSI or NFS (maybe following HA NFS configuration in my previous post). Thus we trade cost with performance, but keeping the system cost below NetApp's.

Another idea for next article is for implementing HA on Oracle database server .. while I look for solutions I found these reference docs :  http://eval.symantec.com/mktginfo/enterprise/white_papers/b-ha_for_oracle_db_vcs_hadr_WP_14216725.en-us.pdf and http://www.vmware.com/files/pdf/partners/oracle/Oracle_Databases_on_VMware_-_High_Availability_Guidelines.pdf. Seems Symantec got two product for lower cost oracle HA : Symantec ApplicationHA and Veritas Cluster Server.