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.

 

Monday Apr 30, 2012

Meet The MySQL Experts Podcast: MySQL Thread Pool

In the latest episode of our “Meet The MySQL Experts” podcast, Mikael Ronstrom, senior MySQL Architect, explains us how the MySQL Thread Pool improves MySQL Scalability.

You can try out the MySQL Thread Pool via our MySQL Enterprise Edition Trial.

And…MySQL being of Nordic origin, Hyvää Vappua/Glada Vappen to all the Finns and Swedes among us!

Enjoy the podcast!

Tuesday Apr 10, 2012

Benchmarking MySQL Replication with Multi-Threaded Slaves

The objective of this benchmark is to measure the performance improvement achieved when enabling the Multi-Threaded Slave enhancement delivered as a part MySQL 5.6.

As the results demonstrate, Multi-Threaded Slaves delivers 5x higher replication performance based on a configuration with 10 databases/schemas. For real-world deployments, higher replication performance directly translates to:

· Improved consistency of reads from slaves (i.e. reduced risk of reading "stale" data)

· Reduced risk of data loss should the master fail before replicating all events in its binary log (binlog)

The multi-threaded slave splits processing 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.

Multi-Threaded Slaves are just one of many enhancements to replication previewed as part of the MySQL 5.6 Development Release, which include:

· Global Transaction Identifiers coupled with MySQL utilities for automatic failover / switchover and slave promotion

· Crash Safe Slaves and Binlog

· Optimized Row Based Replication

· Replication Event Checksums

· Time Delayed Replication

These and many more are discussed in the “MySQL 5.6 Replication: Enabling the Next Generation of Web & Cloud Services” Developer Zone article 

Back to the benchmark - details are as follows.


Environment
The test environment consisted of two Linux servers:

· one running the replication master

· one running the replication slave.

Only the slave was involved in the actual measurements, and was based on the following configuration:

- Hardware: Oracle Sun Fire X4170 M2 Server

- CPU: 2 sockets, 6 cores with hyper-threading, 2930 MHz.

- OS: 64-bit Oracle Enterprise Linux 6.1
- Memory: 48 GB

Test Procedure
Initial Setup:

Two MySQL servers were started on two different hosts, configured as replication master and slave.

10 sysbench schemas were created, each with a single table:

CREATE TABLE `sbtest` (
   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
   `k` int(10) unsigned NOT NULL DEFAULT '0',
   `c` char(120) NOT NULL DEFAULT '',
   `pad` char(60) NOT NULL DEFAULT '',
   PRIMARY KEY (`id`),
   KEY `k` (`k`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

10,000 rows were inserted in each of the 10 tables, for a total of 100,000 rows. When the inserts had replicated to the slave, the slave threads were stopped. The slave data directory was copied to a backup location and the slave threads position in the master binlog noted.

10 sysbench clients, each configured with 10 threads, were spawned at the same time to generate a random schema load against each of the 10 schemas on the master. Each sysbench client executed 10,000 "update key" statements:

UPDATE sbtest set k=k+1 WHERE id = <random row>

In total, this generated 100,000 update statements to later replicate during the test itself.

Test Methodology:
The number of slave workers to test with was configured using:

SET GLOBAL slave_parallel_workers=<workers>

Then the slave IO thread was started and the test waited for all the update queries to be copied over to the relay log on the slave.

The benchmark clock was started and then the slave SQL thread was started. The test waited for the slave SQL thread to finish executing the 100k update queries, doing "select master_pos_wait()". When master_pos_wait() returned, the benchmark clock was stopped and the duration calculated.

The calculated duration from the benchmark clock should be close to the time it took for the SQL thread to execute the 100,000 update queries. The 100k queries divided by this duration gave the benchmark metric, reported as Queries Per Second (QPS).

Test Reset:

The test-reset cycle was implemented as follows:

· the slave was stopped

· the slave data directory replaced with the previous backup

· the slave restarted with the slave threads replication pointer repositioned to the point before the update queries in the binlog.

The test could then be repeated with identical set of queries but a different number of slave worker threads, enabling a fair comparison.

The Test-Reset cycle was repeated 3 times for 0-24 number of workers and the QPS metric calculated and averaged for each worker count.

MySQL Configuration
The relevant configuration settings used for MySQL are as follows:

binlog-format=STATEMENT
relay-log-info-repository=TABLE
master-info-repository=TABLE

As described in the test procedure, the
slave_parallel_workers setting was modified as part of the test logic. The consequence of changing this setting is:

0 worker threads:
   - current (i.e. single threaded) sequential mode
   - 1 x IO thread and 1 x SQL thread
   - SQL thread both reads and executes the events

1 worker thread:
   - sequential mode
   - 1 x IO thread, 1 x Coordinator SQL thread and 1 x Worker thread
   - coordinator reads the event and hands it to the worker who executes

2+ worker threads:
   - parallel execution
   - 1 x IO thread, 1 x Coordinator SQL thread and 2+ Worker threads
   - coordinator reads events and hands them to the workers who execute them

Results
Figure 1 below shows that Multi-Threaded Slaves deliver ~5x higher replication performance when configured with 10 worker threads, with the load evenly distributed across our 10 x schemas. This result is compared to the current replication implementation which is based on a single SQL thread only (i.e. zero worker threads).

Figure 1: 5x Higher Performance with Multi-Threaded Slaves

The following figure shows more detailed results, with QPS sampled and reported as the worker threads are incremented.

The raw numbers behind this graph are reported in the Appendix section of this post.



Figure 2: Detailed Results

As the results above show, the configuration does not scale noticably from 5 to 9 worker threads. When configured with 10 worker threads however, scalability increases significantly. The conclusion therefore is that it is desirable to configure the same number of worker threads as schemas.

Other conclusions from the results:

· Running with 1 worker compared to zero workers just introduces overhead without the benefit of parallel execution.

· As expected, having more workers than schemas adds no visible benefit.

Aside from what is shown in the results above, testing also demonstrated that the following settings had a very positive effect on slave performance:


relay-log-info-repository=TABLE
master-info-repository=TABLE

For 5+ workers, it was up to 2.3 times as fast to run with TABLE compared to FILE.

Conclusion

As the results demonstrate, Multi-Threaded Slaves deliver significant performance increases to MySQL replication when handling multiple schemas.

This, and the other replication enhancements introduced in MySQL 5.6 are fully available for you to download and evaluate now from the MySQL Developer site (select Development Release tab).

You can learn more about MySQL 5.6 from the documentation 

Please don’t hesitate to comment on this or other replication blogs with feedback and questions.

Appendix – Detailed Results

Wednesday Feb 29, 2012

Meet The MySQL Experts Podcast: MySQL Performance

In the latest episode of our “Meet The MySQL Experts” podcast, Dimitri Kravtchuk talks to us about MySQL Performance. He reviews at a high level improvements made over the past few years, including in MySQL 5.5, as well as MySQL Enterprise Scalability, some of the new MySQL 5.6 features improving performance and more.

Enjoy the podcast!

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