Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Previous 30 Newer Entries Showing entries 31 to 60 of 742 Next 30 Older Entries

Displaying posts with tag: performance (reset)

A new dimension to MySQL query optimizations – part 1
Employee_Team +4 Vote Up -0Vote Down

It’s not radical to claim that one of the most important tasks of any DBMS query optimizer is to find the best join order for the tables participating in a query. Is it, e.g., better to read country or city first in

SELECT * 
FROM country JOIN city ON country.id=city.countryid 
WHERE city.population > 1000000 AND 
      country.region="EMEA"

employee or department first in

SELECT *
FROM employee JOIN department ON employee.dept_no=department.dept_no
WHERE employee.first_name="John" AND
      employee.hire_date BETWEEN "2012-01-01" AND "2012-06-01" AND
      department.location="Paris"

If the optimizer gets this wrong, the resulting response time may be disastrous (or hilarious, depending on your sense of humour).

Simply put (and ignoring some edge cases), the MySQL optimizer does the following to find the cheapest

  [Read more...]
MySQL Cluster 7.4.0 Labs Release
Employee +3 Vote Up -0Vote Down

The first version of MySQL Cluster 7.4 has now been released on MySQL Labs. Note that labs loads are not suitable for production use (in fact they’re even less mature than Development Milestone Releases); their purpose is to give users a chance to see what’s in the works, try it for themselves and then provide feedback. Having read that, if you’d like to try it out then Download MySQL Cluster 7.4 from MySQL Labs.

The focus of this first Cluster 7.4 load is performance and data node restart

  [Read more...]
InnoDB Crash Recovery Improvements in MySQL 5.7
Employee_Team +2 Vote Up -0Vote Down

Background

InnoDB is a transactional storage engine. Two parts of the acronym ACID (atomicity and durability) are guaranteed by write-ahead logging (WAL) implemented by the InnoDB redo log.

A statement within a user transaction can consist of multiple operations, such as inserting a record into an index B-tree. Each low-level operation is encapsulated in a mini-transaction that groups page-level locking and redo logging. For example, if an insert would cause a page to be split, a mini-transaction will lock and modify multiple B-tree pages, splitting the needed pages, and finally inserting the record.

On mini-transaction commit, the local mini-transaction log will be appended to the global redo log buffer, the page locks will be released and the modified pages will be inserted into the

  [Read more...]
Presenting MySQL 5.7 Performance & Benchmarks at Percona Live
Employee_Team +2 Vote Up -0Vote Down

I’ll speak about MySQL 5.7 Performance & Benchmarks during the incoming Percona Live and will be happy to share with you all our latest finding, improvements, benchmark results, open issues and many other stuff keeping our brains in constant activity ) As you know, there is no a “silver bullet” solution for MySQL Performance tuning.. – only by a good understanding of what is going inside of MySQL and InnoDB you may configure your MySQL server in the most optimal way for your workloads. And this topic is endless, and progressing every month with new solutions and features – all parties are working hard here, and I’m very

  [Read more...]
Presenting MySQL/InnoDB at Percona Live 2014
Employee_Team +1 Vote Up -0Vote Down

I will be presenting at Percona Live 2014 and I’m excited to share and discuss the latest and greatest features and improvements that we have made to MySQL/InnoDB in 5.7. Great performance improvements, there are some new exciting compression features that we are working on,  GIS support,  temporary table performance etc.. There is a long list. Also, we are always interested to hear about user issues and priorities so that we can address them and/or work them into our plan. Your feedback is very important for us, if you want to influence the direction of InnoDB development then you need to talk to me .

Improve MySQL Performance And Maximise System Usage
Employee_Team +1 Vote Up -0Vote Down

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
  [Read more...]
March 20 Webinar: How to Scale MySQL for Big Data Applications
+0 Vote Up -0Vote Down

You may think that you have to buy, install, and get up to speed on a new database if you want to work with large amounts of data, but you can do more than you think with the MySQL you already have.
Register Now!

SPEAKER: Jon Tobin, Tokutek
DATE: Thursday, March 20th
TIME: 1pm ET

Without having to change your application or do special tuning you can increase performance and save significant time and money when you need to scale.

Join Tokutek’s Jon Tobin as he demonstrates how to use MySQL or MariaDB in Big Data applications by simply upgrading the storage engine with TokuDB, and how to effectively evaluate TokuDB for increased performance, compression and agility.

During this webinar you will learn:

  • How to dramatically increase performance without having to rewrite



  [Read more...]
No application changes needed: 10 times faster slave with MariaDB 10 parallel replication
+3 Vote Up -1Vote Down

Parallel replication is in MariaDB 10.0. I did some benchmarks on the code in 10.0.9. The results are quite good! Here is a graph that shows a 10-times improvement when enabling parallel replication: The graph shows the transaction per second as a function of number of slave worker threads, when the slave is executing events from the master at full speed. The master binlog was generated with sysbench oltp.lua. When the binlog is enabled on the slave and made crash-safe (--sync-binlog=1 --innodb-flush-log-at-trx-commit=1), the slave is about ten times faster at 12 worker threads and above compared to the old single-threaded replication.

These results are for

  [Read more...]
DBAs, Learn about MySQL Where You Want, When You Want
Employee_Team +0 Vote Up -0Vote Down

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

  [Read more...]
Optimizing InnoDB Transactions
Employee +2 Vote Up -0Vote Down

(This is a cross-post from the Engine Yard blog. The original article appears here.)

Here is a question I've actually been asked a few times:

"I am writing a batch processing script which modifies data as part of an ongoing process that is scheduled by cron. I have the ability to group a number of modifications together into a transaction, but I'm not sure what the correct number is?"

First off, I think that this question is interesting not just in the context of batch processing, but it equally applies to all parts of the application. If you are designing a high throughput system for MySQL, there are actually some potential pain points that you can design your way around.

Potential Pain Points

Here are the situations where the

  [Read more...]
Translated slides from my seminar about using Performance Schema for MySQL troubleshooting at Devconf 2013
Employee_Team +2 Vote Up -0Vote Down
Few weeks ago I asked my friends who speak both English and Russian if it is worth translating slides about Performance Schema which I prepared for a seminar at Devconf 2013. They said it is. Today I finished translation and uploaded slides to SlideShare.

Strictly speaking simple translation of slides is not enough, because they were created for the seminar where I was going to explain what they mean. I think I need to repeat same seminar, this time in English language. But if you have rough imagination about what Performance Schema is and need hints for practical use you will find such suggestions in the slides. You will also find ready-to-use queries which you can use to troubleshoot most frequent performance issues.

Enjoy!
The MySQL Server team is looking for a Windows performance architect
Employee_Team +2 Vote Up -0Vote Down

MySQL is very popular and growing on the Windows platform. As a consequence we want to further staff our Windows efforts with a full time Windows performance architect. The Windows performance architect will be an integral part of the MySQL Server development team and also work closely with our Windows Experience team. Work will be centered around profiling and tuning large multi-threaded service applications on the Windows platform, debug and remove limitations and develop new code.

Key qualifications are:

  • 5+ years C/C++ software development experience on Windows platform
  • Ability to understand large pieces of code fast
  • Strong understanding of long running programs (for example Operating System Kernels, Filesystems, high end networking equipment)
  • Strong understanding of multi-threaded service
  [Read more...]
Today’s practical use-case for Performance Schema
Employee +2 Vote Up -0Vote Down

Today’s blog post starts with a question:

“In my continuous integration environment, running tests currently takes 30 minutes. I
believe that a large percentage of time is spent in MySQL, and I want to know if putting MySQL
on tmpfs will cut that time down?”.

I have previously written about how to configure MySQL to be less durable in testing environments, but today I wanted to write about how we can really test the assumption that the question is based around. That is to say:

  • We know that total time is 30 minutes.
  • We assume MySQL is a large percentage of this.
  • We know that this optimization will potentially allow us to avoid IO.
  • We then want to know what


  [Read more...]
Another 10 Performance Wins
+1 Vote Up -0Vote Down

Following on from my earlier 10 performance wins post, here is another group of 10 I have worked on.

# Target Analyzed Key Tool Fixed Specific Improvement 11 redis System DTrace System scheduler 41% 12 rsync System DTrace Application app config 5x 13 mongoperf System DTrace System ZFS tuning up to 8x 14 backups System iostat System OS tuning 2x – 4x 15 Percona System taskset System OS tuning 16x 16 ZFS System DTrace System OS tuning up to 100x 17 Sphinx System Flame Graphs Build compiler options 75% 18 rsync System methodology Application system tuning 2.3x 19 ab System DTrace System TCP issue 5x 20 Elasticsearch System arcstat Benchmark config 50%

Longer summaries for each below. See the

  [Read more...]
Using MASTER_GTID_WAIT() to avoid stale reads from slaves in replication
+2 Vote Up -0Vote Down

I have just implemented MASTER_GTID_WAIT() in MariaDB 10.0. This can be used to give a very elegant solution to the problem of stale reads in replication read-scaleout, without incuring the overheads normally associated with synchronous replication techniques. This idea came up recently in a discussion with Stephane Varoqui, and is similar to the concept of Lamport logical clock described in this Wikipedia article.

I wanted to describe this, hoping to induce people to test and maybe start using this, as it is a simple but very neat idea, actually.

A very typical use of MariaDB/MySQL replication is

  [Read more...]
Filesort optimization in 5.7.3: pack values in the sort buffer
Employee_Team +4 Vote Up -0Vote Down

In MySQL, filesort is the catch-all algorithm for producing sorted results for ORDER-BY or GROUP-BY queries. MySQL has two algorithms for filesort, both the original and the modified algorithms are described in the user manual.  The most commonly used algorithm is the so called modified algorithm, it is used for all cases except when BLOB and TEXT column are involved.

In 5.7.3, Tor Didriksen in the optimizer team introduced one more optimization that applies to the modified algorithm.  Let us first take a look at how MySQL´s modified filesort algorithm worked up to 5.7.2.

  • Read the rows that match the WHERE clause.
  • For each row, record a tuple of values consisting of the sort key value and the additional fields referenced by the query.
  • When
  •   [Read more...]
    MaxScale, ProxySQL and MySQL Proxy
    +2 Vote Up -0Vote Down

    At FOSDEM 2014 ProxySQL and MaxScale were both presented. Both are proxy that can help build sophisticated MariaDB/MySQL architectures. But currently what is the most used proxy with MySQL? It is HAproxy. HAproxy is a level 4 proxy that has no knowledge of the MySQL protocol. Being low level makes it very fast but it [...]

    How Important is the Performance of Your Database
    Employee_Team +0 Vote Up -0Vote Down

    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
      [Read more...]
    Configuring MySQL Server for Optimal Performance
    Employee_Team +0 Vote Up -0Vote Down

    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

      [Read more...]
    More on 40% better single-threaded performance in MariaDB
    +1 Vote Up -0Vote Down

    In my previous post I wrote about how I achived a >40% speedup on sysbench read-only using profile-guided optimisation (PGO). While this is a preliminary result, I though it was so interesting that it deserved early mention. The fact that any benchmark can be improved that much shows clearly that PGO is something worth looking into. Even if we will probably not improve all workloads by 40%, it seems highly likely that we can obtain significant gains also for many real workloads.

    I had one or two interesting comments on the post that raise valid concerns, so I wanted to write a follow-up here, explaining some of the points in more details and going deeper into the performance counter measurements. As I wrote before, actual observations and measurements are crucial to fully understand performance of

      [Read more...]
    Performance Schema implementation Internals: Registering instruments
    Employee_Team +0 Vote Up -0Vote Down

    This is the very first post in the series of Performance Schema Implementation Internals. This series is for MySQL Developers to understand implementation of Performance Schema. For user point of view of performance Schema, please refer to mysql documentation link : http://dev.mysql.com/doc/refman/5.7/en/performance-schema-quick-start.html

    This post talks about the mechanism to register instruments in Performance Schema (referred as P_S from hereon) so that their statistics could be collected at runtime.

    To understand this, lets first understand different instrument classes in P_S.

    Instrument Classes in P_S:

    An enum PFS_class_type is there to keep track of all possible classes of

      [Read more...]
    January 28 Webinar: Get More Out of MySQL with TokuDB
    +0 Vote Up -0Vote Down

    You love MySQL and MariaDB for its ease of deployment, but what if you could increase performance and save significant time and money when your application starts to scale without having to change your applications?
    Register Now!

    SPEAKER: Tim Callaghan, VP of Engineering at Tokutek
    DATE: Tuesday, January 28th
    TIME: 1pm ET

    Join this interactive webinar with Tokutek’s VP of Engineering, Tim Callaghan, as he walks through the potential pitfalls when using MySQL or MariaDB for Big Data applications, and how to effectively use TokuDB to increase performance, reduce database size and achieve true schema agility.

    Attend this webinar to learn:

  • How easy it is to install and configure TokuDB with MySQL or MariaDB
  • How to dramatically increase performance without having to rewrite code



  •   [Read more...]
    40% better single-threaded performance in MariaDB
    +5 Vote Up -0Vote Down

    Continuing my investigation of single-threaded performance in the MariaDB server, I managed to increase throughput of single-threaded read-only sysbench by more than 40% so far:

    I use read-only sysbench 0.4.12 run like this:

        sysbench --num-threads=1 --test=oltp --oltp-test-mode=simple --oltp-read-only --oltp-skip-trx run
    

    And mysqld is run with minimal options:

        sql/mysqld --no-defaults --basedir=X --datadir=Y --innodb-buffer-pool-size=128M
    

    With modern high-performance CPUs, it is necessary to do detailed measurements using the built-in performance counters in order to get any kind of understanding of how an application performs and what the bottlenecks are. Forget about looking at the code and counting instructions or cycles as

      [Read more...]
    Transaction life cycle improvements in 5.7.3
    Employee_Team +3 Vote Up -0Vote Down
    This is part of the ongoing work on improving the transaction life cycle management. In 5.7.2 we split the transaction list into two. The read-only transaction list and the read-write transaction list. There was another "virtual" list, the auto-commit non-locking  read-only (AC-NL-RO) transaction list. The change in 5.7.2 was that by default a transaction was treated as read only and added to the read-only transaction list. Only when it was determined that the transaction was going to do an update we removed the transaction from the read-only list and moved it to the read-write transaction list. This initial add to the the read-only list forced the acquisition of the trx_sys_t::mutex. Acquiring the mutex during transaction start/begin has a cost. Promoting a transaction from read-only to read-write we had to  [Read more...]
    Benchmarking the Cloud
    +0 Vote Up -1Vote Down

    Benchmarking, and benchmarking the cloud, is incredibly error prone. I provided guidance though this minefield in the benchmarking chapter of my book (Systems Performance: Enterprise and the Cloud); that chapter can be read online on the InformIT site. I also gave a lightning talk about benchmarking gone wrong at Surge last year. In this post, I’m going to cut to the chase and show you the tools I commonly use for basic cloud benchmarking.

    As explained in the benchmarking chapter, I do not run these tools passively. I perform Active Benchmarking, where I use a variety of other

      [Read more...]
    Improved Performance of Data Export/Import for MySQL Utilities 1.3.6 GA
    +0 Vote Up -0Vote Down
    The performance of the mysqldbcopy, mysqldbexport, and mysqldbimport utilities has been optimized in MySQL Utilities 1.3.6. In the case of export/import there have been significant improvements. In particular, multiprocessing support has been added to these utilities and can be enabled with the new --multiprocess option. The option permits concurrent execution and makes the most of the CPU resources available (number of cores).

    Multiprocessing is applied at different levels according to the operating system. For non-POSIX systems, multiprocessing is limited to the database-level whereas POSIX systems can make multiprocess at the table level.

    More specifically, the mysqldbcopy and mysqldbexport utilities allow multiprocessing at the table level for non- Windows systems and database level for Windows system. The mysqldbimport utility allows multiprocessing at



      [Read more...]
    Warm-up InnoDB Buffer Pool
    +4 Vote Up -0Vote Down
    Send to Kindle

    As we know, one of the most important config for InnoDB is the innodb_buffer_pool_size, it basically store the innodb data and indexes in memory, when MySQL receives a query and the InnoDB pages involved on that query are stored in the buffer, it does not need to go to the disk to return the result, which is much faster (memory speed vs disk speed).

    As it is stored in memory, every time you restart your MySQL server it starts with a clean/empty buffer pool and usually it take some time to warm-up the buffer.
    To speed up this process, we can configure 2 variables that will dump and reload the pages reference stored in the buffer, this is a new functionality added on MySQL 5.6 (it was presented on previous versions of Percona

      [Read more...]
    Transaction life cycle improvements in 5.7.3
    Employee_Team +1 Vote Up -0Vote Down

    This is part of the ongoing work on improving the transaction life cycle management. In 5.7.2 we split the transaction list into two. The read-only transaction list and the read-write transaction list. There was another “virtual” list, the auto-commit non-locking read-only (AC-NL-RO) transaction list. The change in 5.7.2 was that by default a transaction was treated as read only and added to the read-only transaction list. Only when it was determined that the transaction was going to do an update we removed the transaction from the read-only list and moved it to the read-write transaction list. This initial add to the the read-only list forced the acquisition of the trx_sys_t::mutex. Acquiring the mutex during transaction start/begin has a cost. Promoting a transaction from read-only to read-write we had to acquire the trx_sys_t::mutex to add to the read-write

      [Read more...]
    Multiple masters : attraction to the stars
    +0 Vote Up -0Vote Down

    In the last 10 years I have worked a lot with replication systems, and I have developed a keen interest in the topic of multiple masters in a single cluster. My interest has a two distinct origins:

    • On one hand, I have interacted countless times with users who want to use a replication system as a drop-in replacement for a single server. In many cases, especially when users are dealing with applications that are not much flexible or modular, this means that the replication system must have several points of data entry, and such points must work independently and in symbiosis with the rest of the nodes.
    • On the other hand, I am a technology lover (look it up in the dictionary: it is spelled geek), and as such I get my curiosity stirred whenever I discover a new possibility of implementing multi-master systems.

    The double nature of

      [Read more...]
    Analyze and Optimize MySQL Performance
    Employee_Team +0 Vote Up -0Vote Down

    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
      [Read more...]
    Previous 30 Newer Entries Showing entries 31 to 60 of 742 Next 30 Older Entries

    Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

    Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.