Monday Nov 04, 2013

Optimizing MySQL Database Operations for Better Performance

If you are responsible for a MySQL Database, you make choices based on your priorities; cost, security and performance.

To learn more about improving performance, take the MySQL Performance Tuning course

In this 4-day instructor-led course you will learn practical, safe and highly efficient ways to optimize performance for the MySQL Server. It will help you develop the skills needed to use tools for monitoring, evaluating and tuning MySQL.

You can take this course via the following delivery methods:Training-on-Demand:

  • Take this course at your own pace, starting training within 24 hours of registration.
  • Live-Virtual Event: Follow a live-event from your own desk; no travel required. You can choose from a selection of events to suit your timezone.
  • In-Class Event: Travel to an education center to take this course. Below is a selection of events already on the schedule.

 Location

 Date

 Delivery Language

 London, England

 26 November 2013

 English

 Toulouse, France

 18 November 2013

French 

 Rome, Italy

 2 December 2013

 Italian

 Riga, Latvia

 3 March 2014

 Latvian

 Jakarta Barat, Indonesia

10 December 2013 

English 

 Tokyo, Japan

 17 April 2014

 Japanese

 Pasig City, Philippines

9 December 2013 

 English

 Bangkok, Thailand

 4 November 2013

 English

To register for this course or to learn more about the authentic MySQL curriculum, go to http://education.oracle.com/mysql.

To see what an expert has to say about MySQL Performance, read Dimitri's blog.

Thursday Oct 31, 2013

After 10 Years, MySQL Still the Right Choice for ScienceLogic's "Best Network Monitoring System on the Planet"

ScienceLogic has a pretty fantastic network monitoring appliance.  So good in fact that InfoWorld gave it their "2013 Best Network Monitoring System on the Planet" award.  Inside their "ultraflexible, ultrascalable, carrier-grade" enterprise appliance, ScienceLogic relies on MySQL and has since their start in 2003.  Check out some of the things they've been able to do with MySQL and their reasons for continuing to use MySQL in these highlights from our new MySQL ScienceLogic case study.
  • Science Logic's larger customers use their appliance to monitor and manage  20,000+ devices, each of which generates a steady stream of data and a workload that is 85% write. On a large system, the MySQL database:
    • Averages 8,000 queries every second or about 1 billion queries a day
    • Can reach 175,000 tables and up to 20 million rows in a single table
    • Is 2 terabytes on average and up to 6 terabytes
    • "We told our customers they could add more and more devices. With MySQL, we haven't had any problems. When our customers have problems, we get calls. Not getting calls is a huge benefit."
      Matt Luebke, ScienceLogic Chief Software Architect.


  • ScienceLogic was approached by a number of Big Data / NoSQL vendors, but decided against using a NoSQL-only solution. Said Matt, "There are times when you really need SQL. NoSQL can't show me the top 10 users of CPU, or show me the bottom ten consumer of hard disk. That's why we weren't interested in changing and why we are very interested in MySQL 5.6. It's great that it can do relational and key-value using memcached."
  • The ScienceLogic team is very cautious about putting only very stable technology into their product, and according to Matt, MySQL has been very stable: "We've been using MySQL for 10 years and we have never had any reliability problems. Ever."
  • ScienceLogic now uses SSDs for their write-intensive appliance and that change alone has helped them achieve a 5x performance increase.

Learn more>>




Friday Sep 20, 2013

Tuning MySQL 5.6 for Great Product Performance: FAQs

“Will you expand the next webcast to 90 minutes? This one was too interesting to last only one hour” was one of the questions we got during the “Tuning MySQL for Great Product Performance: The Fundamentals, Updated for MySQL 5.6” webinar on August 27th.  The engineers on Q&A got a lot of good (and more technical) questions during the webcast.  For those of you who were unable to join us live, I’ve posted the questions and answers below, and you can listen to a recording of the webinar and get a .pdf of slides at this link.

The webinar was created specifically for the software and hardware vendors that include MySQL with their products, often “deeply embedded” so their customers do no separate installation, tuning, or admin. Their focus is great out of the box performance, but whatever your use case, you’ll get useful performance tips with an emphasis on some of the newer capabilities in MySQL 5.5 and 5.6.

Following are some related and new resources, and below that, the Q&A.  
•    "Developer and DBA Guide to What's New in MySQL 5.6" white paper
•    “What’s New in MySQL Workbench” on-demand webinar
•    “MySQL Workbench: Database Design. Development. Administration. Migration” white paper

And, don’t forget!  MySQL Connect starts on tomorrow!

FAQs
Performance Benchmarks
Q.  What tool did you use to perform the benchmarks?
A. We primarily use Sysbench. Here are more details regarding the benchmarks: http://dev.mysql.com/tech-resources/articles/mysql-5.6.html

Q. Does MySQL 5.6 Community Edition include the performance gains shown in the benchmarks?
A. Yes, everything that John has been discussing is in both releases of MySQL: Community Edition and the Commercial Editions.

Q. Do these performance results hold for NDB as well?
A. These graphs are based upon InnoDB. To better understand the performance characteristics of NDB, please review this information: http://www.mysql.com/why-mysql/benchmarks/mysql-cluster/

Buffer Pool

Q. Buffer Pool % of Available Memory: Does this mean RAM or virtual memory or combination? For 32-bit MySQL, would the maximum be 3 to 4 GB?   

A. We recommend basing this upon RAM, mapping to Virtual Memory might create some performance issues. Find more details here: http://dev.mysql.com/doc/refman/5.6/en/innodb-buffer-pool.html 

For 32-bit MySQL, the max is (2^32 -1). Here are the details: http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_buffer_pool_size

Q. What’s the benefit of having multiple buffer pools instead of one large one? 

A. When the InnoDB buffer pool is large, many data requests can be satisfied by retrieving from memory. But, you might encounter bottlenecks from multiple threads trying to access the buffer pool at once.
Starting in InnoDB 1.1 and MySQL 5.5, you can enable multiple buffer pools to minimize this contention. Each page that is stored in or read from the buffer pool is assigned to one of the buffer pools randomly, using a hashing function. Each buffer pool manages its own free lists, flush lists, LRUs, and all other data structures connected to a buffer pool, and is protected by its own buffer pool mutex.

Q. How can I calculate the buffer pool size if we have 250 connections running simultaneously?
A. Ideally, you set the size of the buffer pool to as large a value as practical, leaving enough memory for other processes on the server to run without excessive paging. The larger the buffer pool, the more InnoDB acts like an in-memory database, reading data from disk once and then accessing the data from memory during subsequent reads. The buffer pool even caches data changed by insert and update operations, so that disk writes can be grouped together for better performance.

Q. When using JPA (e.g. EclipseLink) it has a cache for database objects; should that cache more or less replace the InnoDB pool?  
A.  Since every application's requirements are unique, we recommend that you architect your solution based upon your business objectives. As far as MySQL goes, I would still recommend scaling the size of the InnoDB buffer pool as large as possible. Application caching is very unique to your goals.

Performance Schema
Q.  How can I get the number of “hits”(query select/insert/update/delete) in a table in a day?
A. The Performance Schema in MySQL 5.6 will give you that insight. The counters can be reset at the start of the day and then examine the contents at the end of the day.
Here is the query that I use: SELECT object_schema.object_name, count_fetch AS SELECTS, sum_timer_fetch, count_insert AS INSERTS, sum_timer_insert, count_delete AS DELETES, sum_timer_delete, FROM performance_schema.table_io_waits_summary_by_table ORDER BY sum_table_wait DESC;

Q. How do you diagnose and analyze a Query (SELECT) statement that has a very short duration reported but very long fetch time reported. How do you find out the cause of a very slow fetch time? What is a slow fetch time a symptom of?    
A. If the EXPLAIN statement doesn't help, then with MySQL 5.6 you can try using the Optimizer Trace for more details on the cost of the SELECT statement. Or, I would recommend using the Performance Schema. This allows you to look at stages of a query and understand exactly where time is spent. http://dev.mysql.com/doc/refman/5.6/en/performance-schema.html

Q. Can you please elaborate on performance schema usage? 
A. Performance Schema is designed to provide you with deeper details into how the MySQL server performs.  Details are located at: http://dev.mysql.com/doc/refman/5.6/en/performance-schema.html If you want to discuss it further, please contact your local Sales Technical Consultant and we can dive into the details.

Other MySQL Storage Engines (not InnoDB)

Q. Which of the MySQL storage engines support built-in procedures etc.? 
A. The general new features such as views, stored procedures, triggers, INFORMATION_SCHEMA, precision math (DECIMAL column type), and the BIT column type, apply to all storage engines.

Q. Can index condition pushdown be used with NDB? 

A. This optimization can be used only by the NDB storage engine. See: http://dev.mysql.com/doc/refman/5.6/en/condition-pushdown-optimization.html

Q. Is that index is being internally created by the server while executing any query using order by in internal temp table?   
A. You must manually create the index; an index is not automatically created by a query using ORDER BY.

Q. Do you have any tuning suggestions or references for those of us who are heavily dependent on the Archive storage engine?  
A. Since you can't index tables in the ARCHIVE engine, there isn't much you can do - see: http://dev.mysql.com/doc/refman/5.6/en/archive-storage-engine.html 
You might want to take a look at using a different storage engine if performance is an issue.

OLTP

Q.  What disk types should be used for an OLTP environment?  
A.  That depends on the number of transactions per second, size of each transaction, etc. But you should at least have disks with high speeds (10,000 RPM), high access rates and with a large disk cache. But SSD's would be the best choice - if available.

Monday Sep 09, 2013

MySQL Brings Huge Performance Improvements with Each Release

The MySQL engineering team constantly works to bring you huge performance improvements with each new release of MySQL. Here are four ways to help you get the most from these improvements

Tap into Sveta Smirnova's MySQL performance expertise on October 1st 2013 at 10am Central European Time, by attending the 1-day virtual seminar, Troubleshooting MySQL Performance with Sveta Smirnova. Sveta starts with basics, working towards more advanced cases that DBAs usually need years of experience to identify or solve. Click here to learn more about this seminar and to register for the event.

The MySQL Performance Tuning training course teaches you to:

  • Understand the basics of performance tuning
  • Use performance tuning tools
  • Tune the MySQL Server instance to improve performance
  • Improve performance of tables based on the storage engine being used
  • Implement proper Schema Design to improve performance
  • Improve the performance of MySQL Queries
  • Describe additional items related to performance tuning

You can take this 4-day instructor-led course through the following delivery methods:

  • Training-on-Demand: Start training within 24 hours of registration and take this course at your own convenience.
  • Live-Virtual Event: Attend a live event from your own desk - no travel required. Choose from a selection of events on the schedule to suit different timezones.
  • In-Class Event: Travel to an education center to take this class. Below is a selection of events already on the schedule:

 Location

 Date

 Delivery Language

 Brussels, Belgium

 18 November 2013

 English

 Strasbourg, France

 22 October 2013

 French

 Budapest, Hungary

 15 October 2013

 Hungarian

 Rome, Italy

 2 December 2013

 Italian

 Riga, Latvia

 11 November 2013

 Latvian

 Utrecht, Netherlands

 18 November 2013

 Dutch

 Waraaw, Poland

 8 October 2013

 Polish

 Barcelona, Spain

 7 October 2013

 Spanish

 Madrid, Span

 25 November 2013

 Spanish

 Tokyo, Japan

 17 September 2013

 Japanese

 Jakarta Barat, Indonesia

 9 September 2013

 English

 Pasig City, Philippines

 16 September 2013

 English

 Sao Paulo, Brazil

 15 October 2013

 Portugese

To register for a course or to learn more about the authentic MySQL curriculum, go to http://oracle.com/education/mysql.

There are also lots of opportunites to learn about MySQL Performance during the performance tuning sessions at MySQL Connect.

And to learn more about MySQL performance, why not consult http://dimitrik.free.fr/blog/.

Monday Jul 29, 2013

Tune MySQL for Top-Level Performance

In 4 days, the MySQL Performance Tuning training teaches you practical, safe and highly efficient ways to optimize performance for the MySQL Server. It will help you:

  • Evaluate the architecture
  • Understand and use the tools.
  • Configure the database for performance.
  • Tune application and SQL code.
  • Tune the server.
  • Examine the storage engines.
  • Assess the application architecture.
  • Understand general tuning concepts.

You can take this instructor-led course as a:

  • Training-on-Demand offering: Start training within 24 hours of regsitration, taking this course at your own pace through streaming video of instructor delivery and booking time to perform lab exercises at your own convenience.
  • Live-Virtual event: Take this course live from your own desk, choosing from a selection of events on the schedule to suit different timezones.
  • In-Class event: Travel to an education center to take this course. Below is a selection of events already on the schedule.

 Location

 Date

 Delivery Language

 Brussels,  Belgium

 26 August 2013

 English

 London, England

 1 October 2013

 English

 Bordeaux, France

 27 August 2013

 French

 Nice, France

 9 September 2013

 French

 Strasbourg, France

 23 October 2013

 French

 Milan, Italy

 16 September 2013

Italian 

 Rome, Italy

 2 December 2013

 Italian

 Budapest, Hungary

 15 October 2013

 Hungarian

 Riga, Latvia

 11 November 2013

 Latvian

 Utrecht, Netherlands

 26 August 2013

 English

Warsaw, Poland 

8 October 2013 

Polish 

 Lisbon, Portugal

 23 September 2013

 European Portugese

 Porto, Portugal

 23 September 2013

 European Portugese

 Barcelona, Spain

 7 October 2013

Spanish 

 Madrid, Spain

25 November 2013

Spanish

Jakarta, Indonesia 

 19 August 2013

 English

 Tokyo, Japan

 19 September 2013

Japanese 

 Pasig City, Philippines

 5 August 2013

 English

 Sao Paolo, Brazil

15 October 2013 

 Brazilian Portugese

To register for this course or to learn more about the authentic MySQL curriculum, go to http://oracle.com/education/mysql.

To learn more about MySQL performance, attend the 1-day seminar: Troubleshooting MySQL Performance with Sveta Smirnova on October 1st, 2013. For more information go to http://oracle.com/education.

Monday Jun 10, 2013

Tuning MySQL Database to Access Really High Performance

MySQL continues to improve in terms of performance. To get the best out of MySQL, you must learn to analyze your system and use tools to monitor, evaluate and tune the MySQL Server.

The MySQL Performance Tuning training is a 4-day instructor-led course which teaches you to configure the database for performance, tune application and SQL code, tune the server, examine storage engines and assess the application architecture.

You can take this course through the following event types:

  • Training-on-Demand: Start training within 24 hours of registration. Follow this course at your own pace through streaming video of instructor delivery and scheduling time to do lab exercises at a time that suits you.
  • Live-Virtual: Attend a live event from your own desk. Choose from a selection of events on the schedule to suit different timezones.
  • In-Class: Travel to an education center to follow this course. Below are some of the events already on the schedule:

 Location

 Date

 Delivery Language

 Bordeaux, France

 27 August, 2013

 French

 Nice, France

 9 September 2013

 French

 Strasbourg, France

 23 October 2013

 French

 Budapest, Hungary

 15 October 2013

 Hungarian

 Milan, Italy

 16 September 2013

 Italian

 Rome, Italy

 2 December 2013

 Italian

 Riga, Latvia

 11 November 2013

 Latvian

 Utrecht, Netherlands

 26 August 2013

English 

 Utrecht, Netherlands

 18 November 2013

 Dutch

 Warsaw, Poland

 1 July 2013

 Polish

 Lisbon, Porgual

 23 September 2013

 European Portugese

 Porto, Portugal

 23 September 2013

 European Portugese

 Barcelona, Spain

 15 July 2013

 Spanish

 Madrid, Spain

 15 July 2013

 Spanish

 Tokyo, Japan

 17 September 2013

 Japanese

 Mexico City, Mexico

 24 June 2013

 Spanish

 Sao Paolo, Brazil

 17 June 2013

 Brazilian Portugese

To register for this course or to learn more about the authentic MySQL curriculum, go to http://oracle.com/education/mysql.

To go further in your learning, attend the one-day seminar: Troubleshooting MySQL Performance with Sveta Smirnova.

Sunday Apr 14, 2013

Tap into Top-Level MySQL Performance with MySQL Training

MySQL, the most popular open source database brings you great performance. You can see the performance details in http://dimitrik.free.fr/blog/. To help you get the most from MySQL's performance, take the MySQL for Performance Tuning course.

This four day instructor-led training course covers:

  • Performance Tuning Basic
  • Performance Tuning Tools
  • MySQL Server Tuning
  • MySQL Query Cache
  • Storage Engines
  • Schema Design and Performance
  • Performance Tuning Extras

This course is available in three delivery types:

  • Training-on-Demand: Take this course at your own pace via streaming video and access to lab environments and start training within 24 hours of registering.
  • Live-Virtual Event: Follow a live event from your own desk - no travel required. Choose from a selection of events already on the schedule suiting different timezones.
  • In-Class Event: Travel to an education center to follow this course. Below is a selection of events already on the schedule.

 Location

 Date

 Delivery Language

 Mechelen, Belgium

 13 May 2013

 English

 Aix-en-Provence, France

 10 June 2013

 French

 Budapest, Hungary

 6 May 2013

 Hungarian

 Rome, Italy

 24 June 2013

 Italian

 Riga, Latvia

 11 November 2013

 Latvian

 Amsterdam, Netherlands

 10 June 2013

 Dutch

 Warsaw, Poland

 13 May 2013

 Polish

 Barcelona, Spain

 27 May 2013

 Spanish

 Madrid, Spain

 27 May 2013

 Spanish

 Tokyo, Japan

17 September 2013 

 Japanese

 Montreal, Canada

 17 June 2013

 English

 Ottawa, Canada

 17 June 2013

 English

 Toronto, Canada

 17 June 2013

 English

 Sao Paolo, Brazil

 17 June 2013

 Brazilian Portugese

 Sao Paolo, Brazil

 15 October 2013

 Brazilian Portugese

For more information about this course or other courses on the authentic MySQL curriculum, go to http://oracle.com/education/mysql

Monday Mar 04, 2013

Get Even More from MySQL With MySQL Performance Tuning Training

With each release, the MySQL database brings you even better performance. To get the most from your MySQL servers, performance tuning is important. And what better route than to take the MySQL for Performance Tuning training course.

In this four-day instructor-led class you will learn to:

  • Understand the basics of Performance Tuning
  • Use the tools of performance tuning
  • Tune the MySQL server instance to improve performance
  • Improve performance of tables
  • Implement proper Schema Design to improve performance
  • Improve the performance of MySQL queries

This course is available as:

  • Training-on-Demand: Start training within 24 hrs of registering and follow the course at your own pace and from your own desk.
  • Live-Virtual Class. Follow this live event from your own desk. Choose from a wide selection of events already on the schedule to suit different timezones.
  • In-Class: Travel to an education center to take this course. Below is a selection of events already on the schedule.

 Location

 Date

 Delivery Language

 Mechelen, Belgium

 13 May 2013

 English

 London, England

 9 April 2013

 English

Aix-en-Provence, France 

10 June 2013 

 French

Rennes, France 

23 April 2013 

 French

Budapest, Hungary 

21 May 2013 

 Hungarian

Rome, Italy

 2 April 2013

Italian 

Riga, Latvia 

 11 November 2013

 Latvian 

 Warsaw, Poland

13 May 2013 

Polish

 Lisbon, Portugal

 6 May 2013

 European Portugese

 Porto, Portugal

 6 May 2013

 European Portugese

 Amsterdam, Netherlands

 10 June 2013

 Dutch

 Barcelona, Spain

 27 May 2013

 Spanish

 Madrid Spain

 27 May 2013

 Spanish

Edmonton, Canada 

 15 April 2013

  English 

Montreal, Canada

17 June 2013 

  English 

Ottawa, Canada  

 17 June 2013

  English 

Toronto, Canada 

 17 June 2013

 English

 Sao Paolo, Brazil

15 April 2013 

 Brazilian Portugese

Note, this course is also available in Japanese. To register for this course or to learn more about the authentic MySQL curriculum, go to http://oracle.com/education/mysql.

You can get in-depth MySQL 5.6 performance and feature specific benchmarks by following related blogs by Mikael Ronstrom and Dimitri Kravtchuk

Thursday Feb 07, 2013

MySQL 5.6 Replication Performance

With data volumes and user populations growing, its no wonder that database performance is a hot topic in developer and DBA circles.  

Its also no surprise that continued performance improvements were one of the top design goals of the new MySQL 5.6 release which was declared GA on February 5th (note: GA means “Generally Available”, not “Gypsy Approved” @mysqlborat)

And the performance gains haven’t disappointed:

- Dimitri Kravtchuk’s Sysbench tests showed MySQL delivering up to 4x higher performance than the previous 5.5 release.

- Mikael Ronstrom’s testing showed up to 4x better scalability as thread counts rose to 48 and 60 threads (not so uncommon in commodity systems today)

Of course, YMMV (Your Mileage May Vary) in real-world workloads, but you can be reasonably certain you will see performance gains by upgrading to MySQL 5.6.  It is up to you whether you invest these gains to serve more applications and users from your MySQL databases, or you consolidate to fewer instances to reduce operational costs.

How about if you are using MySQL replication in order to scale out your database across commodity nodes, and as a foundation for High Availability (HA)? Performance here has also been improved through a combination of:

- Binary Log Group Commit

- Multi-Threaded Slaves

- Optimized Row-Based Replication

As well as giving you the benefits above; higher replication performance directly translates to:

- Reduced risk of losing data in the event of a failure on the master

- Improved read consistency from slaves

- Resource-efficient binlogs traversing the replication cluster

We will look at each of these in more detail.

Binlog Group Commit

Rather than applying writes one at a time, Binary Log Group Commit batches writes to the Binlog, significantly reducing overhead to the master. This is demonstrated by the benchmark results below.


Using the SysBench RW tests, enabling the binlog and using the default sync_binlog=0 reduces the throughput of the master by around 10%. With sync_binlog=1 (where the MySQL server synchronizes its binary log to disk after every write to the binlog, thereby giving maximum data safety), throughput is reduced by a further 5%.

To understand the difference this makes, the tests were repeated comparing MySQL 5.6 to MySQL 5.5 


Even with sync_binlog=1, MySQL 5.6 was still up to 4.5x faster than 5.5 with sync_binlog=0

Gone are the days when configuring replication resulted in a 50% or more hit to performance of your master.

The result of Binary Log Group Commit is that MySQL replication is much better able to keep pace with the demands of write-intensive workloads, imposing much less overhead on the master, even when the binlog is flushed to disk after each commit!

Details of the configurations used for the benchmark are in the Appendix at the end of this post.

You can learn more about the implementation of Binlog Group Commit from Mats Kindahl’s blog.

Multi-Threaded Slave

Looking beyond the replication master, it is also necessary to bring performance enhancements to the replication slaves.

Using Multi-Threaded Slaves, processing is split between worker threads based on schema, allowing updates to be applied in parallel, rather than sequentially. This delivers benefits to those workloads that isolate application data using databases - e.g. multi-tenant systems deployed in cloud environments.

Benchmarks demonstrate that Multi-Threaded Slaves increase performance by 5x.  


This performance enhancement translates to improved read consistency for clients accessing the replication cluster. Slaves are better able to keep up with the master, and so users are much less likely to need to throttle the sustained throughput of writes, just so that the slaves don't indefinitely fall further and further behind (previously some users had to reduce the capacity of their systems in order to reduce slave lag).

You can get all of the details on this benchmark and the configurations used in this earlier blog posting

Optimized Row-Based Replication

The final piece in improving replication performance is to introduce efficiencies to the binary log itself.

By only replicating those elements of the row image that have changed following INSERT, UPDATE and DELETE operations, replication throughput for both the master and slave(s) can be increased while binary log disk space, network resource and server memory footprint are all reduced.

This is especially useful when replicating across datacenters or cloud availability zones.

Another performance enhancements is the was Row-Based Replication events are handled on the slave against tables without Primary Keys. Updates would be made via multiple table scans. In MySQL 5.6, no matter size of the event, only one table scan is performed, significantly reducing the apply time.

You can learn more about Optimized Row Based Replication from the MySQL documentation.  

Crash-Safe Slaves and Binlog

Aka “Transactional Replication” this is more of an availability than a performance feature, but there is a nice performance angle to it.

The key concept behind crash safe replication is that replication positions are stored in tables rather than files and can therefore be updated transactionally, together with the data. This enables the slave or master to automatically roll back replication to the last committed event before a crash, and resume replication without administrator intervention. Not only does this reduce operational overhead, it also eliminates the risk of data loss or corruption.

From a performance perspective, it also means there is one less disk sync, since we leverage the storage engine's fsync and don't need an additional fsync for the file, giving users a performance gain.

Wrapping Up

So with a faster MySQL Server, InnoDB storage engine and replication, developers and DBAs can get ahead of performance demands. Bear in mind there is more to MySQL 5.6 replication than performance – for example Global Transaction Identifiers (GTIDs), replication event checksums, time-delayed replication and more.

To learn more about all of the new replication features in MySQL 5.6, download the Replication Introduction guide

To get up and running with MySQL replication, download the new Replication Tutorial Guide 


Appendix – Binary Log Group Commit Benchmarks

System Under Test:

5 x 12 thread Intel Xeon E7540 CPUs @2.00 GHz

512 GB memory

Oracle Linux 6.1


Configuration file:

1) --innodb_purge_threads=1

2) --innodb_file_format=barracuda

3) --innodb-buffer-pool-size=8192M

4) --innodb-support-xa=FALSE

5) --innodb-thread-concurrency=0

6) --innodb-flush-log-at-trx-commit=2

7) --innodb-log-file-size=8000M

8) --innodb-log-buffer-size=256M

9) --innodb-io-capacity=2000

10) --innodb-io-capacity-max=4000

11) --innodb-flush-neighbors=0

12) --skip-innodb-adaptive-hash-index

13) --innodb-read-io-threads=8

14) --innodb-write-io-threads=8

15) --innodb_change_buffering=all

16) --innodb-spin-wait-delay=48

17) --innodb-stats-on-metadata=off

18) --innodb-buffer-pool-instances=12

19) --innodb-monitor-enable='%'

20) --max-tmp-tables=100

21) --performance-schema

22) --performance-schema-instrument='%=on'

23) --query-cache-size=0

24) --query-cache-type=0

25) --max-connections=4000

26) --max-prepared-stmt-count=1048576

27) --sort-buffer-size=32768

28) --table-open-cache=4000

29) --table-definition-cache=4000

30) --table-open-cache-instances=16

31) --tmp-table-size=100M

32) --max-heap-table-size=1000M

33) --key-buffer-size=50M

34) --join-buffer-size=1000000



Monday Jan 21, 2013

Optimize MySQL Performance with the Authentic MySQL Training

The MySQL database provides top-level performance. Here are two courses to help you get the most out of your MySQL system.

Troubleshooting MySQL Performance With Sveta Smirnova

This 1 day seminar is an opportunity to interact with Sveta Smirnova, MySQL engineer, expert on MySQL Performance and author of MySQL Troubleshooting.

Sign up for this seminar scheduled for February 28th, 2013 in London, England

Sveta will start from basics, working towards more advanced cases that DBAs would usually need years of experience to identify or solve.

This seminar covers performance problems in MySQL applications. You learn why issues happen in the first place and how to react to and solve situations including:

  • Query runs slow
  • Query execution time increases during life of the application
  • MySQL server uses more and more system resources
  • Slave cannot catch up the master

MySQL Performance Tuning Course

In this 4 day courseyou learn practical, safe, highly efficient ways to optimize performance for the MySQL Server. This course prepares each student with the skills needed to utilize tools for monitoring, evaluating and tuning. Students evaluate the architecture, learn to use the tools, configure the database for performance, tune application and SQL code, tune the server, examine the storage engines, assess the application architecture, and learn general tuning concepts.

Take this class through one of the following delivery types:

  • Training-on-Demand: You can start training within 24 hours of registering, following lecture material via streaming video at your own pace and scheduling time to do lab exercises at your convenience.
  • Live-Virtual Class: Follow a live instructor-led event without the inconvenience or cost of travel. Take the course from your own desk, connecting with the instructor and lab environment virtually. There is already a wide range of events on the schedule.
  • In-Class: Travel to a classroom to take this class. Below is a sample of events already on the schedule:

 Location

 Date

 Delivery Language

 Mechelen, Belgium

 13 May 2013

English 

 London, England

 9 April 2013

English 

 Aix-en-Provence, France

 10 June 2013

French 

 Rennes, France

 23 April 2013

French 

 Budapest, Hungary

 5 February 2013

Hungarian 

 Milan, Italy

 18 February 2013

Italian 

 Rome, Italy

 2 April 2013

Italian 

 Riga, Latvia

 11 March 2013

Latvia 

 Amsterdam, Netherlands

 10 June 2013

Dutch 

 Nieuwegein, Netherlands

 11 March 2013

Dutch 

 Warsaw, Poland

 11 February 2013

Polish 

 Lisbon, Portugal

 6 May 2013

European Portugese 

 Porto, Portugal

 6 May 2013

European Portugese 

 Barcelona, Spain

 25 March 2013

Spanish

 Madrid, Spain

 27 May 2013

Spanish 

 Tokyo, Japan

 18 June 2013

Japanese 

 Tokyo, Japan

 25 February 2013

Japanese 

 Edmonton, Canada

 15 April 2013

English 

 Ottawa, Canada

 19 February 2013

English 

 Toronto, Canada

 19 February 2013

English 

 Montreal, Canada

 19 February 2013

English 

 Mexico City, Mexico

 5 February 2013

Spanish 

 Sao Paolo, Brazil

 15 April 2013

Brazilian Portugese 

To register for or learn more about this course, to express interest in additional events or learn more about the authentic MySQL curriculum, go to http://oracle.com/education/mysql

Monday Dec 03, 2012

Get The Most From MySQL Database With MySQL Performance Tuning Training

Get the most from MySQL Server's top-level performance by improving your understanding of performance tuning techniques.

MySQL Performance Tuning Class

In this 4 day class, you'll learn practical, safe, highly efficient ways to optimize performance for the MySQL Server. You can take this class as:

  • Training-on-Demand: Start training within 24 hours of registering and follow the instructor-led lecture material through streaming video at your own pace. Schedule time lab-time to perform the hands-on exercises at your convenience.
  • Live-Virtual Class: Follow the live instructor led class from your own desk - no travel required. There are already a range of events on the schedule to suit different timezones and with delivery in languages including English and German.
  • In-Class Event: Travel to a training center to follow this class.

For more information on this class, to see the schedule or register interest in additional events, go to http://oracle.com/education/mysql

Troubleshooting MySQL Performance with Sveta Smirnova 

During this one-day, live-virtual event, you get a unique opportunity to hear Sveta Smirnova, author of MySQL Troubleshooting, share her in-depth experience of identifying and solving performance problems with a MySQL Database. And you can benefit from this opportunity without incurring any travel costs!

Dimitri's Blog

If MySQL Performance is a topic that interests you, then you should be following Dimitri Kravtchuk's blog.

For more information on any aspect of the Authentic MySQL Curriculum, go to http://oracle.com/education/mysql.

Thursday Nov 08, 2012

Election 2012: Twitter Breaks Records with MySQL

Twitter VP of Infrastructure Operations Engineering Mazen Rawashdeh shared news and numbers yesterday on his blog:

"Last night, the world tuned in to Twitter to share the election results as U.S. voters chose a president and settled many other campaigns. Throughout the day, people sent more than 31 million election-related Tweets (which contained certain key terms and relevant hashtags). And as results rolled in, we tracked the surge in election-related Tweets at 327,452 Tweets per minute (TPM). These numbers reflect the largest election-related Twitter conversation during our 6 years of existence, though they don’t capture the total volume of all Tweets yesterday."

"Last night, Twitter averaged about 9,965 TPS from 8:11pm to 9:11pm PT, with a one-second peak of 15,107 TPS at 8:20pm PT and a one-minute peak of 874,560 TPM. Seeing a sustained peak over the course of an entire event is a change from the way people have previously turned to Twitter during live events. Now, rather than brief spikes, we are seeing sustained peaks for hours."

Congrats to Jeremy Cole, Davi Arnaut and the rest of the team at Twitter for their excellent work!

Jeremy recently held a keynote presentation at MySQL Connect describing how MySQL powers Twitter, and why they chose and continue to rely on MySQL for their operations. You can watch the presentation here. He also went into more details during another presentation later that day and you can access the slides here.

Below a couple of tweets from Jeremy after what have surely been hectic days...

 Keep up the good work guys!




Monday Oct 29, 2012

Harness MySQL's Continued Performance Tuning Improvements

To fully harness the continued improvements in performance tuning you get with MySQL, take the MySQL Performance Tuning course.

This 4 day class teaches you practical, safe, highly efficient ways to optimize performance for the MySQL Server. You will learn the skills needed to use tools for monitoring, evaluating and tuning. 

You can take this course in the following three ways:

  • Training-on-Demand: Follow this course at your own pace and from your own desk with streaming video of instructor delivery and booking time to follow hands-on exercises at your own convenience.
  • Live-Virtual: Attend a live instructor-led event from your own desk. Choose from the numerous events on the schedule.
  • In-Class:  Travel to an education center to follow this class. A sample of events on the schedule is shown below:

 Location

 Date

 Delivery Language

 Tokyo, Japan

 19 November 2012

 Japanese

 Mechelen, Belgium

 4 February 2013

 English

 London, England

 19 November 2012

 English

 Budapest, Hungary

 21 May 2013

 Hungarian

 Milan, Italy

 14 January 2013

 Italian

 Rome, Italy

 3 December 2012

 Italian

 Riga, Latvia

 10 December 2012

 Latvian

 Amsterdam, Netherlands

 7 January 2013

 Dutch

 Nieuwegein, Netherlands

 26 November 2012

 Dutch

 Warsaw, Poland

 3 December 2012

 Polish

 Lisbon, Portugal

 4 February 2013

 European Portugese

 Porto, Portugal

 4 February 2013

 European Portugese

 Barcelona, Spain

 25 March 2013

 Spanish

 Madrid, Spain

 17 December 2012

 Spanish

 Sydney, Australia

 26 November 2012

 English

 Edmonton, Canada

 10 December 2012

 English

 Montreal, Canada

 26 November 2012

 English

 Ottawa, Canada

 26 November 2012

 English

 Toronto, Canada

 26 November 2012

 English

 Vancouver, Canada

 10 December 2012

 English

 Sao Paolo, Brazil

 26 November 2012

 Brazilan Portugese

For more information on this class or to know more about other courses on the authentic MySQL curriculum. see http://oracle.com/education/mysql.

Note, many organizations deploy both Oracle Database and MySQL side by side to serve different needs, and as a database professional you can find training courses on both topics at Oracle University! Check out the upcoming Oracle Database training courses and MySQL training courses. Even if you're only managing Oracle Databases at this point of time, getting familiar with MySQL will broaden your career path with growing job demand.

Tuesday Jul 31, 2012

MySQL Cluster Performance Best Practices: Q & A

With its distributed, shared-nothing, real-time design, MySQL Cluster has attracted a lot of attention from developers who need to scale both read and write traffic with ultra-low latency and fault-tolerance, using commodity hardware. With many proven deployments in web, gaming, telecoms and mobile use-cases, MySQL Cluster is certainly able to meet these sorts of requirements.

But, as a distributed database, developers do need to think a little differently about data access patterns along with schema and query optimisations in order to get the best possible performance.

Sharing best practices developed by working with MySQL Cluster's largest users, we recently ran a Performance Essentials webinar, and the replay is now available, on-demand, for you to listen to in the comfort of your own office.

The webinar also accompanies a newly published Guide to optimizing the performance of MySQL Cluster.

We received a number of great questions over the course of the webinar, and I thought it would be useful to share a selection of those:

Q. How do I calculate and then monitor memory usage with MySQL Cluster?

A. If designing a completely new database, the following calculations can be used to help determine the approximate memory sizing requirements for the data nodes:

(in memory) Data Size * Replicas * 1.25 = Total Database Memory Requirements

Example: 50 GB * 2 * 1.25 = 125 GB

(Data Size * Replicas * 1.25)/Nodes = RAM Per Node

Example: (2 GB * 2 * 1.25)/4 = 31.25 GB

To see how much of the configured memory is currently in use by the database, you can query the ndbinfo.memory usage table

If using MySQL Cluster CGE then you can view this information over time in a MySQL Enterprise Monitor graph.


Q. Would enabling Disk space Table Space be an impact on the Query Performance ?

A. It can do. The only reason to use Disk based table spaces is when you do not have sufficient memory to store all data in-memory. Therefore some of your disk based data will be uncached at some time, and reads or writes which access this data will stall while the necessary pages are read into the page buffer. This can reduce throughput.


Q. I've seen that MySQL Cluster 7.2 can speed up JOIN operations by 70x. How does it do this?

A. There are two new features in MySQL Cluster 7.2, which when combined, can significantly improve the performance of joins over previous versions of MySQL Cluster:

- The Index Statistics function enables the SQL optimizer to build a better execution plan for each query. In the past, non-optimal query plans required a manual enforcement of indexes via USE INDEX or FORCE INDEX to alter the execution plan. ANALYZE TABLE must first be run on each table to take advantage of this.

- Adaptive Query Localization (AQL) allows the work of the join to be distributed across the data nodes (local to the data it’s working with) rather than up in the MySQL Server; this allows more computing power to be applied to calculating the join as well as dramatically reducing the number of messages being passed around the system.

You can learn more about AQL and a sample query here


Q. Can all JOINs use AQL?

A. In order for a join to be able to exploit AQL (in other words be “pushed down” to the data nodes), it must meet the following conditions:

1. Any columns to be joined must use exactly the same data type. (For example, if an INT and a BIGINT column are joined, the join cannot be pushed down). This includes the lengths of any VARCHAR columns.

2. Joins referencing BLOB or TEXT columns will not be pushed down.

3. Explicit locking is not supported; however, the NDB (MySQL Cluster) storage engine's characteristic implicit row-based locking is enforced.

4. In order for a join to be pushed down, child tables in the Join must be accessed using one of the ref, eq_ref, or const access methods, or some combination of these methods. These access methods are described in the documentation

5. Joins referencing tables explicitly partitioned by [LINEAR] HASH, LIST, or RANGE currently cannot be pushed down

6. If the query plan decides to 'Using join buffer' for a candidate child table, that table cannot be pushed as child. However, it might be the root of another set of pushed tables.

7. If the root of the pushed Join is an eq_ref or const, only child tables joined by eq_ref can be appended. (A ref joined table will then likely become a root of another pushed Join)

These conditions should be considered when designing your schema and application queries – for example, to comply with constraint 4, attempt to make any table scan that is part of the Join be the first clause.

Where a query involves multiple levels of Joins, it is perfectly possible for some levels to be pushed down while others continue to be executed within the MySQL Server.

If your application consists of many of these types of JOIN operations which cannot be made to exploit AQL, other MySQL storage engines such as InnoDB will present a better option for your workload.


Q. What are best practices for data model and query design?

A. The data model and queries should be designed to minimize network roundtrips between hosts. Ensuring that joins meet the requirements for

AQL and avoiding full table scans can help with this.

Looking up data in a hash table is a constant time operation, unaffected by the size of the data set

Looking up data in a tree (T-tree, B-tree etc) structure is logarithmic (O (log n)).

For a database designer this means it is very important to choose the right index structure and access method to retrieve data. We strongly recommend application requests with high requirements on performance be designed as primary key lookups. This is because looking up data in a hash structure is faster than from a tree structure and can be satisfied by a single data node. Therefore, it is very important that the data model takes this into account. It also follows that choosing a good primary key definition is extremely important.

If ordered index lookups are required then tables should be partitioned such that only one data node will be scanned.

The distributed nature of the Cluster and the ability to exploit multiple CPUs, cores or threads within nodes means that the maximum performance will be achieved if the application is architected to run many transactions in parallel. Alternatively you should run many instances of the application simultaneously to ensure that the Cluster is always able to work on many transactions in parallel.

Take a look at the Guide to optimizing the performance of MySQL Cluster for more detail


Q. What are best practices for parallelising my application and access to MySQL Cluster?

A. As mentioned MySQL Cluster is a distributed, auto-sharded database. This means that there is often more than one Data Node that can work in parallel to satisfy application requests.

Additionally, MySQL Cluster 7.2 enhances multi-threading so data nodes can now effectively exploit multiple threads / cores. To use this functionality, the data nodes should be started using the ndbmtd binary rather than ndb and config.ini should be configured correctly

Parallelization can be achieved in MySQL Cluster in several ways:

- Adding more Application Nodes

- Use of multi-threaded data nodes

- Batching of requests

- Parallelizing work on different Application Nodes connected to the Data Nodes

- Utilizing multiple connections between each Application Node and the Data Nodes (connection pooling)

How many threads and how many applications are needed to drive the desired load has to be studied by benchmarks. One approach of doing this is to connect one Application Node at a time and increment the number of threads. When one Application Node cannot generate any more load, add another one. It is advisable to start studying this on a two Data Node cluster, and then grow the number of Data Nodes to understand how your system is scaling.

If you have designed your application queries, and data model according to best practices presented in the Performance Guide, you can expect close to double the throughput on a four Data Node system compared to a two Data Node system, given that the application can generate the load.

Try to multi-thread whenever possible and load balance over more MySQL servers.

In MySQL Cluster you have access to additional performance enhancements that allow better utilization on multi-core / thread CPUs, including:

- Reduced lock contention by having multiple connections from one MySQL Server to the Data Nodes (--ndb-cluster-connection-pool=X):

- Setting threads to real-time priority

- Locking Data Node threads (kernel thread and maintenance threads to a CPU)


Q. Does MySQL Cluster’s data distribution add complexity to my application and limit the types of queries I can run?

A. No, it doesn't. By default, tables are automatically partitioned (sharded) across data nodes by hashing the primary key. Other partitioning methods are supported, but in most instances the default is acceptable.

As the sharding is automatic and implemented at the database layer, application developers do not need to modify their applications to control data distribution – which significantly simplifies scaling.

In addition, applications are free to run complex queries such as JOIN operations across the shards, therefore users do not need to trade functionality for scalability.


Q. What hardware would you recommend to get the best performance from MySQL Cluster?

A. It varies by node type. For data nodes:

- Up to 32 x x86-64 bit CPU cores. Use as high a frequency as possible as this will enable faster processing of messages between nodes;

- Large CPU caches assist in delivering optimal performance;

- 64-bit hosts with enough RAM to store your in-memory data set

- Linux, Solaris or Windows operating systems.

- 2 x Network Interface Cards and 2 x Power Supply Units for hardware redundancy.

It is important to ensure systems are configured to reduce swapping to disk whenever possible.

As a rule of thumb, have 7x times the amount of DataMemory configured for disk space for each data node. This space is needed for storing 2 Local Checkpoints (LCPs), the Redo log and 3 backups. You will also want to allocate space for table spaces if you are making use of disk-based data – including allowing extra space for the backups.

Having a fast, low-latency disk subsystem is very important and will affect check pointing and backups.

Download the MySQL Cluster Evaluation Guide for more recommendations 

The hardware requirements for MySQL Servers would be a little less:

- 4 - 32 x86-64 bit CPU cores

- Minimum 4GB of RAM. Memory is not as critical at this layer, and requirements will be influenced by connections and buffers.

- 2 x Network Interface Cards and 2 x Power Supply Units for hardware redundancy.


Q. I heard that MySQL Cluster doesn't support Foreign Keys, how can I get around that?

A. Foreign keys are previewed in MySQL Cluster 7.3 Early Access release which you can download and evaluate now. In MySQL Cluster 7.2 and earlier, you can emulate foreign keys programmatically via triggers.


Summary

If you are thinking about using MySQL Cluster for your next project, it is worth investing a little bit of time to get familiar with these performance best practices. The Webinar replay, the MySQL Cluster Performance Guide and the MySQL Cluster Evaluation Guide will give you pretty much everything you need to build high performance, high availability services with MySQL Cluster. 




Monday Jun 25, 2012

Honing Performance Tuning Skills on MySQL

Get hands-on experience with techniques for tuning a MySQL Server with the Authorized MySQL Performance Tuning course. 

This course is designed for database administrators, database developers and system administrators who are responsible for managing, optimizing, and tuning a MySQL Server.

You can follow this live instructor led training:

  • From your desk. Choose from among the 800+ events on the live-virtual training schedule.
  • In a classroom. A selection of events/locations listed below

 Location

 Date

 Delivery Language

 Prague, Czech Republic

 1 October 2012

 Czech

 Warsaw, Poland

 9 July 2012

 Polish

 London, UK

 19 November 2012

 English

 Rome, Italy

 23 October 2012

 Italian

 Lisbon, Portugal

 17 September 2012

 European Portugese

 Aix-en-Provence, France

 4 September 2012

 French

 Strasbourg, France

 16 October 2012

 French

 Nieuwegein, Netherlands

 3 September 2012

 Dutch

 Madrid, Spain

 6 August 2012

 Spanish

 Mechelen, Belgium

 1 October 2012

 English

 Riga, Latvia

 10 December 2012

 Latvian

 Petaling Jaya, Malaysia

 10 September 2012

 English

 Edmonton, Canada

 27 August 2012

 English

 Vancouver, Canada

 27 August 2012

 English

 Ottawa, Canada

 26 November 2012

 English

 Toronto, Canada

 26 November 2012

 English

 Montreal, Canada

 26 November 2012

 English

 Mexico City, Mexico

 9 July 2012

 Spanish

 Sao Paulo, Brazil

 2 July 2012

 Brazilian Portugese

To find a virtual or in-class event that suits you, go or http://oracle.com/education and choose a course and delivery type in your location.

 

About

Get the latest updates on products, technology, news, events, webcasts, customers and more.

Twitter


Facebook

Search

Archives
« May 2015
SunMonTueWedThuFriSat
     
2
3
4
5
6
8
9
10
13
14
15
16
17
19
20
21
22
24
25
26
27
29
30
31
      
Today