Monday Mar 17, 2014

Improve MySQL Performance And Maximise System Usage

Some people think that performance tuning is all about reducing CPU or memory utilization. However CPU and memory are designed to be used. You should concentrate your efforts on improving the user experience by reducing response times. You can do this by optimizing your queries and by enabling your system to serve more requests.

To learn more about improving the performance of the MySQL database, consider taking the MySQL Performance Tuning course.

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

  • Training-on-Demand: Start training within 24-hours of registering, following lectures at your own pace via streaming video and booking time on a lab environment at a time that suits you.
  • Live-Virtual Class: Attend a live event from your own desk - no travel required. Choose from a selection of events on the schedule to suit different time zones.
  • In-Class Events: Travel to an education center to take this class. Events already on the schedule include the following:

 Location

 Date

 Delivery Language

 Brussels, Belgium

 10 June 2014

 English

 Sao Paolo, Brazil

 14 April 2014

 Brazilian Portuguese

 London, England

 14 April 2014

 English

Rome, Italy 

 23 June 2014

Italian 

 Riga, Latvia

 29 September 2014

Latvian 

 Petaling Jaya, Malaysia

 5 May 2014

 English

 Mexico City, Mexico

 24 May 2014

 English 

 Utrecht, Netherlands

 10 June 2014

 English

 Warsaw, Poland

  20 May 2014 

Polish 

 Singapore

 11 April 2014

 English

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

Monday Mar 10, 2014

DBAs, Learn about MySQL Where You Want, When You Want

If you have a busy schedule or too much going on to attend a live training session, Training-on-Demand is for you. With Training-on-Demand, you can watch recorded classroom sessions taught by top Oracle instructors and gain hands-on experience with a dedicated lab environment.

In viewing the classroom sessions, you access streaming lectures on your desktop or tablet, with the ability to fast-forward, pause, rewind and search.

MySQL for Database Administrators is available as training-on-demand. This course is designed for DBAs and other database professionals. Learn to configure the MySQL Server, set up replication and security, perform database backups and performance tuning and protect MySQL Databases. This class also helps prepare you for the MySQL 5.6 Database Administrator certification exam.

To see other MySQL courses available in the training-on-demand format, go to Training-on-Demand, click the Courses tab and choose the Database product area and the MySQL curriculum category.

You can also take MySQL courses as live-virtual events and in-class events. To learn more about the authentic MySQL curriculum, go to http://education.oracle.com/mysql.

Monday Feb 03, 2014

How Important is the Performance of Your Database

Whether you are a database administrator, database application developer, web developer, system administrator or system architect, if you are working with a database system such as the MySQL Server, you will want to ensure the best possible performance. 

To learn about the great performance you can get from a MySQL Database, visit Dimitri's blog.

If you have experience maintaining a database server, can use MySQL tools and have knowledge of general SQL statements and SQL tuning principles, consider taking the MySQL Performance Tuning course. In this 4-day instructor-led course, you will learn about:

  • What to tune and why
  • Monitoring, benchmarking and stress tools
  • Problem and pitfall identification
  • Evaluating and improving performance
  • Improving query cache results
  • InnoDB's role in MySQL performance
  • Designing for performance
  • MySQL Optimizer
  • Performance impact of choices in hardware, OS, logging and backup

You can take this course through the following three delivery methods:

  • Training-on-Demand: Train at your own pace, starting within 24 hours of registration with streaming-video of classroom delivery and full access to lab environments.
  • Live-Virtual Events: Follow a live class from your own desk, no travel required. Choose from a selection of events on the schedule to suit different timezones.
  • In-Class Events: Travel to an education center to take this course. Below is a selection of the events already on the schedule.

Location Date Delivery Language
 Brussels, Belgium
 10 June 2014
 English
 London, England
 14 April 2014
 English
 Rome, Italy
 1 April 2014
 Italian
 Tokyo, Japan
 17 March 2014
 Japanese
 Riga Latvia
 29 September 2014
 Latvian
 Petaling Jaya, Malaysia
 5 May 2014
 English
 Utrecht, Netherlands
 10 June 2014
 English
Warsaw, Poland
24 February 2014
 Polish
 Barcelona, Spain
 3 March 2014
 Spanish
 Istanbul, Turkey
 3 March 2014
 Turkish
To learn more about this course, register for an event, request an additional event or learn more about the authentic MySQL curriculum, go to http://education.oracle.com/mysql.

Monday Jan 27, 2014

Configuring MySQL Server for Optimal Performance

Configuring MySQL Server, with the dozens of options available, has always been perceived as a bit of a black art. In MySQL Database 5.6, configuring your MySQL server for optimal performance is easier than ever before.

In the MySQL for Database Administrators course you can learn more.

MySQL 5.6 is configured to work faster out of the box in a wide range of installation scenarios and it is easier than ever to set up the server by adjusting only a small number of settings which cause others to be set. In addition, you get a new default my.cnf file with instructions guiding your through adjusting key settings. The MySQL for Database Administrators course describes many of these settings, allowing you to make an informed choice on how best to configure your server.

You can take this instructor-led course through different delivery methods:

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

 Location  Date  Delivery Language
 Brussels, Belgium
 31 March 2014
 English
 London, England
 3 February 2014
 English
 Belfast, Ireland
 17 March 2014
 English
 Turkey, Istanbul
 24 March 2014
 Turkish
 Milan, Italy
 12 May 2014
 Italian
 Rome, Italy
 17 February 2014
 Italian
 Turin, Italy
 24 March 2014
 Italian
 Seoul, Korea
 3 February 2014
 Korean
 Petaling Jaya, Malaysia
 3 March 2014
 English
 Mexico City, Mexico
 17 February 2014
 Spanish
 San Pedro Garca Garcia, Mexico
 17 February 2014
 Spanish
 Utrecht, Netherlands
 12 May 2014
 English
 Lagos, Nigeria
 7 April 2014
 English
 Makati City, Philippines
 28 April 2014
 English
 Warsaw, Poland
 7 April 2014
 Polish
 Tunis, Tunisia
 24 March 2014
 French
 Singapore  10 February 2014
 English
 Barcelona, Spain
 7 April 2014
 Spanish
 Madrid, Spain
 9 June 2014
 Spanish

To register for an event, request an additional event or learn more about the authentic MySQL curriculum, go to http://education.oracle.com/mysql.

Those who have acquired the knowledge in the MySQL for Database Administrators course and have MySQL hands-on experience can prove their skills by taking the MySQL 5.6 Database Administrators certification exam. This exam is now available for preregistration for appointments at a later date.

Monday Dec 16, 2013

Analyze and Optimize MySQL Performance

If the performance of your MySQL servers is important to you, performance tuning is key.

In the 4-day MySQL Performance Tuning course, you learn to analyze and optimize the performance of the MySQL Database, using the tools necessary for monitoring, evaluating and tuning.

You can take this course as a:

  • Training-on-Demand event: Start training within 24 hours of registration and take this training at your own pace.
  • Live-Virtual event: Attend a live event from your own desk - no travel required. You can choose from a selection of events already on the schedule.
  • In-Class event: Travel to an education center to take this training. Below is a selection of the events already on the schedule.

 Location

 Date

 Delivery Language

 Brussels, Belgium

 17 February 2014

English 

 Milan, Itlay

20 January 2014 

Italian

 Rome, Italy

1 April 2014 

Italian

 Tokyo, Japan

 17 March 2014

 Japanese

 Riga, Latvia

2 March 2014 

 Latvian

 Utrecht, Netherlands

17 February 2014 

 English

 Barcelona, Spain

3 March 2014 

 Spanish

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

For more information on MySQL performance, see: 

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

About

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

Twitter


Facebook

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
2
5
6
9
10
11
12
13
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today