MySQL BluePrints

{section:border=true}
{include:Left Column}
{column}
{panel:title=Sun Systems for MySQL Enterprise Announcement|titleBGColor=#F8D583|bgColor=white|borderStyle=solid}
*[*Sun Systems for MySQL Enterprise*|http://www.sun.com/systems/solutions/mysql/]* is the powerful combination of MySQL and Sun systems platforms, in tuned, tested and proven solutions.

*[*Web infrastructure*|http://wikis.sun.com/display/BluePrints/Web+2.0+Scalable+Performance+Toolkit]* is one of the fastest-growing areas for new hardware deployments. Today's hardware market for databases is $15 billion, of which the open source component has grown by an annual average of 42% over the last two years. According to Gartner, 16% of enterprise database users have replaced proprietary databases with open source databases, with nearly 70% of enterprise database users planning to use open source databases by 2008. IDC estimates that databases account for nearly 20% of all server sale


Related Videos

* *[*Olio: A Toolkit for Evaluating Web Technologies*|http://link.brightcove.com/services/player/bcpid1659850592?bctid=1900386921]*


* *[*A Look Inside Sun's MySQL Optimization Lab*|http://link.brightcove.com/services/player/bcpid1683878780?bctid=1900386930]*

* *[*Intro to MySQL Deployments*|http://link.brightcove.com/services/player/bcpid1683878780?bctid=1892182235]*

* *[*Tuning & Monitoring MySQL*|http://link.brightcove.com/services/player/bcpid1683878780?bctid=1892230546]*

* *[*Future Directions for MySQL*|http://link.brightcove.com/services/player/bcpid1683878780?bctid=1892187731]*

Benchmarks

* *[*Open Source Leader in Application and Database tiers of SPECjAppServer2004 benchmark*|http://www.sun.com/servers/x64/x4150/benchmarks.jsp#3]*


{panel}
!mysql.gif!
h3. MySQL Papers
{contentbylabel:MySQL|key=BluePrints|maxResults=99}

by Neelakanth Nadgir
{quote}

UFS is a buffered filesystem because by default it uses free memory to cache data. While buffering is useful for a large number of applications, database users have generally stayed away from it by using DirectIO. If you are using UFS with MySQL, you have the option of using UFS buffered or UFS DirectIO. In this blog I will try to describe some of the reasoning behind why UFS DirectIO should be used with MySQL.


*[*MySQL 5.1 v 5.0*|http://blogs.sun.com/cwebster/entry/mysql_5_1_v_5]*
{quote}
by Christopher Webster
{quote}

We saw a better than 25% improvement. Nice work guys!

*[*What's going on in MySQL land?*|http://blogs.sun.com/datacharmer/entry/what_s_going_on_in]*
{quote}
by Giuseppe Maxia
{quote}

Enough of one-sided stories. Let's see a different angle of MySQL 5.1.

*[*MySQL import progress*|http://blogs.sun.com/LinuxJedi/entry/mysql_import_progress]*
{quote}
by LinuxJedi
{quote}

Sometimes when importing data into MySQL using the following method it can take a very long time, especially if the file is very large in size.

I have therefore developed a tiny patch to the mysql client which uses stderr to show the progress of the import in number of lines every 1000 lines of import.

*[*Scaling MySQL on a 256-way T5440 server using Solaris ZFS and Java 1.7*|http://blogs.sun.com/MrBenchmark/]*
{quote}
by Mr Benchmark
{quote}

This summer, in our 26 Sun Solution Centers worldwide, I observed a shift. Yes, we were still seeing older solutions based on DB2, Oracle, Sybase or Informix being evaluated on new Sun hardware. But every customer project manager, every partner, every software engineer working on a new information system design asked us : Can we architect this solution with MySQL ?

In many cases, if you dared to reply YES to this question, the next interrogation would be about the scalability of the MySQL engine.

*[*T5440 World Record Specjappserver Single Application Server*|http://blogs.sun.com/bmseer/entry/t5440_world_record_specjappserver_single]*
{quote}
by BM Seer
{quote}

One Sun SPARC Enterprise T5440 server with four UltraSPARC T2 Plus processors at 1.4GHz, delivered a single system World Record result of 6334.86 SPECjAppServer2004 JOPS@Standard. The Sun SPARC Enterprise T5440 consumed an average of 1578 Watts of power to obtain this result for a power-performance rating of 0.25 Watts/JOP.

The Sun SPARC Enterprise T5440 server (four 1.4 GHz UltraSPARC T2 Plus chips) demonstrated 32% better performance over the HP DL580 G5 result of 4410.07 SPECjAppServer2004 JOPS@Standard, which used four 2.66 GHz Intel 6-core Xeon processors.

The Sun SPARC Enterprise T5440 server (four 1.4 GHz UltraSPARC T2 Plus chips) demonstrated 75% better performance over the HP DL580 G5 result of 3339.94 SPECjAppServer2004 JOPS@Standard, which used four 2.93 GHz Intel 4-core Xeon processors.



*[*The Rise of The Open Source Software Stack*|http://blogs.sun.com/isve/entry/open_source_stack_benchmarks_time]*
{quote}
by Daniel Powers
{quote}

Sun released a SPECjAppServer2004 performance result using top-to-bottom Open Source Software stack components: OpenSolaris/MySQL/Glassfish. At 1197.10 JOPS, the performance is highly respectable - not setting any land speed records, but it is in the ball park with other results using the same class of hardware. The difference (of course) is in the price/performance.

*[*Introduction to the Innodb IO subsystem*|http://blogs.sun.com/realneel/entry/introduction_to_the_innodb_io]*
{quote}
by Neelakanth Nadgir
{quote}

When a client connects to MySQL, it creates a thread to handle it. This thread executes SQL queries and interacts with the storage engine (for simplicity, lets call them user threads). Innodb uses a four (4) additional threads to implement asynchronous io1. Although Innodb has an option innodb_file_io_threads to control the number of IO handler threads, it has no effect on how many IO handler threads are actually created. These IO handler threads wait and process events in a loop. Each IO handler thread processes different kinds of events. (Insert buffer writes, log writes, datafile writes, and read-ahead or prefetch) Let us now see how different types of IO are handled by Innodb.

*[*MySQL Consolidation on Sun SPARC Enterprise T5220*|http://blogs.sun.com/bmseer/entry/mysql_consolidation_on_sun_sparc]*
{quote}
by BM Seer
{quote}

Sun's CMT provides huge savings in Cost, Energy, and Datacenter Space!

Twenty-four Sun SPARC Enterprise T5220 servers (UltraSPARC T2 processors) were able to consolidate a customer's workload of MySQL databases which were running on over 250 Dell 2950 servers. For this medium-weight OLTP customer workload, the solution was architected to handle query distribution at the application layer. In each of the over 700 instances of MySQL, up to a 4 GB database was used.

Now this isn't an official benchmark (SPEC, TPC,...) but it was a workload comparisons that those benchmark consortiums do not cover. All vendors sometimes do this to show capabilities. (sidenote: it is funny to see IBM bloggers smear Sun for doing this and then you see IBM do this same thing as well - duplicitous behavior?). Anyway if you want to see official CMT results on standard benchmarks go to: T5220 benchmarks on sun.com

This following test was run as a customer request to do a fair an complete comparison to judge the effect of a possible upgrade. They upgraded!

*[*Sun Demonstrates Compelling Price Performance*|http://blogs.sun.com/tomdaly/entry/sun_compelling_price_performance]*
{quote}
by Tom Daly
{quote}
Using Glassfish V2 U2 / MySQL 5.0/ OpenSolaris on commodity Sun Intel hardware (i.e. SunFire X4150 Intel based servers) [*Sun has achieved a result of 1197.50 SPECjAppServer2004JOPS@Standard*|http://www.spec.org/jAppServer2004/results/res2008q4/jAppServer2004-20081008-00118.html] , you can see a lot more detail on this result on the [*Sun benchmarkspage*|http://www.sun.com/servers/x64/x4150/benchmarks.jsp#3] and also a nice write up by the [*bmseer*|http://blog.sun.com/bmseer/entry/opensolaris_specjappserver_sun_fire_x4150] and also checkout the Sun ISV blog [*here*|http://blogs.sun.com/isve]

*[*Price Substantiation for SPECjAppServer2004 results*|http://blogs.sun.com/tomdaly/entry/specjpricing]*
{quote}
by Tom Daly
{quote}
This blog entry is intended to disclose and document the price of a number of selected SPECjAppServer2004 submissions and to demonstrate the big price advantages to consumers when using enterprise supported Open Source software in as much of the (enterprise) application stack as possible. The SPECjAppServer2004 benchmark (despite it's name) is a full system benchmark and tests most system components i.e. application server, JVM, hardware, network, operating system and (especially) the database. Pricing SPECjAppServer2004 submissions therefore assists end users and developers to compare the costs of a system benchmark using proprietary software and using Open Source software running the same (unchanged) applications.

*[*MySQL Performance Optimizations*|http://blogs.sun.com/allanp/entry/mysql_performance_optimizations]*
{quote}
by Allan Packer
{quote}
You might be wondering what's been happening with MySQL performance since Sun arrived on the scene. The good news is that we haven't been idle. There's been general recognition that MySQL could benefit from some performance and scalability enhancements, and Sun assembled a cross-organizational team immediately after the acquisition to get started on it. We've enjoyed excellent cooperation between the engineers from both organizations.

*[*Toward a More Scalable MySQL Replication Master*|http://blogs.sun.com/dlutz/entry/toward_a_more_scalable_mysql]*
{quote}
by David Lutz
{quote}
If you are a MySQL 5.x/6.0 InnoDB replication user, right now you take a significant performance hit on the replication master simply by turning on the binlog. The good news is that we've taken a big step toward eliminating that performance gap. I'll describe the problem, how I was able to track down the root cause, and point to a patch that fixes the problem. Since the changes are in the InnoDB code, right now we're waiting on Oracle/Innobase to review the fix and formally commit it. Once that happens, you should see it show up in binary releases. In the meantime, if you build your own binaries you can test the prototype patch yourself.


*[*Advantages of deploying MySQL database with Solaris Cluster*|http://blogs.sun.com/ritu/entry/advantages_of_deploying_mysql_database]*
{quote}
by Ritu Kamboj
{quote}
The primary advantage of deploying the MySQL database in a Solaris Cluster environment is high availability. The Solaris Cluster environment provides fault monitoring and failover capabilities not only for the MySQL software, but also for the entire infrastructure including servers, storage, interconnects, and the operating system. If any component of the entire infrastructure fails, that failure is isolated and managed independently with no impact on availability.

*[*Why isn't MySQL using the my.cnf settings I've specified?*|http://blogs.sun.com/rainy/category/MySQL]*
{quote}
by Vince Carbone
{quote}


You are just getting started with MySQL on OpenSolaris. You've installed the OpenSolaris Community Edition and Cool Stack MySQL. To explore this new environment you decide to run some tests using the sysbench benchmark. After running a number of tests you realize that for some reason the options you are setting in /etc/my.cnf are not getting used.
What is going on? Not to worry, you've just hit a problem common to new users of MySQL on OpenSolaris.

*[*Scale-out MySQL Study on Solaris Nevada*|http://blogs.sun.com/luojiach/entry/scale_out_mysql_study_on]*
{quote}
by Jenny Chen
{quote}
Read on if you'd like to know how to improve MySQL performance using two mysql servers with amoeba proxy.

*[*Installation and Configuration Sun Cluster Data Service For MySQL*|http://blogs.sun.com/luojiach/entry/installation_and_configuration_sun_cluster1]*
{quote}
by Jenny Chen
{quote}
MySQL data service running on a clustered-server model provide orderly startup, shutdown, fault monitoring, and high availability(failover) mechanism compared to the single server model. It can be free donwloaded, and Sun also released Open HA cluster, derived from the Sun Cluster 3.2 agents including HA MySQL data service. The open HA cluster also provide build tools necessary to develop new features, build and use the code.

*[Optimizing the DTrace logger for MySQL queries |http://blogs.sun.com/angelo/entry/optimizing_the_dtrace_logger_for]*

{quote}
by Angelo Rajadurai
{quote}
The Data Charmer Giuseppe Maxia did some quick tests on the DTrace script and was wondering if I could optimize it a little.

One issue with the old script is that it prints every SQL statement and this can be pretty expensive. This can be minimized by printing to a file. Here is a script that will do just that. The freopen() is not documented but it opens a file and sends all prints to the file. Giuseppe reports a 30% improvement in logging performance with this improved script.

*[Using DTrace to observe the SQL statements on a live running MySQL database |http://blogs.sun.com/angelo/entry/observing_mysql_sql_statements_in]*

{quote}
by Angelo Rajadurai
{quote}
FDTrace allows you to instrument any live running application in production without the need of extra coding, application recompile or even an application restart. All you need is that the application is running on an OS that supports DTrace. Today Solaris, OpenSolaris, OS X and FreeBSD are a few that have DTrace built in.




*[MySQL in production: looking for security? (continue) |http://blogs.sun.com/startups/entry/mysql_in_production_looking_for1]*

{quote}
by Thierry Manfe
{quote}
Following up on the previous entry, here are some more best practices to secure MySQL in a production environment.


*[MySQL in production: looking for security? |http://blogs.sun.com/startups/entry/mysql_in_production_looking_for]*

{quote}
by Thierry Manfe
{quote}
You finished the development phase of your project, and you are now heading to put it in production. This means that your web-site will be soon on-line, visible on the Internet, and may become a potential target for attacks.

*[Installing MySQL 5.1 on Solaris 10 using MySQL Optimal Configuration Architecture |http://blogs.sun.com/GeorgeTrujillo/entry/installing_mysql_5_1_on]*

{quote}
by George Trujillo
{quote}
The following instructions will lay out an installation of MySQL on Solaris using the MySQL Optimal Configuration Architecture (MOCA) for someone knowledgeable in MySQL/Solaris administration.

*[Top Things to Know About MySQL Database on Solaris Operating System |http://blogs.sun.com/GeorgeTrujillo/entry/top_ten_things_to_know]*
{quote}
by George Trujillo
{quote}
New to MySQL database on Solaris OS? Here are the top ten things to know about My SQL installation and configuration.

*[Memcached UDF for MySQL on OpenSolaris|http://blogs.sun.com/trond/entry/memcached_udf_for_mysql_on]*
{quote}
by Trond Norbye
{quote}
I have been hearing about the Memcached UDF for MySQL for a while now, so I decided to spend some time playing with them. Being the geek I am, playing for me is to get my hands dirty with the code so I cloned the source repository from: http://hg.tangent.org/memcached_functions_mysql/.

{column}
{column:width=20%}
{panel}
!Main^community.gif|align=center!
* [*mySQL Developer Community*|http://dev.mysql.com/]
{panel}
\\
{panel:title=Contents}{toc}{panel}
\\

{panel:title=Other Resources of Interest|bgColor=#F8D583}
[MySQL on sun.com|http://www.sun.com/software/products/mysql/index.jsp]
{panel}
\\


{column}
{section}