Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 10 of 753 10 Older Entries

Displaying posts with tag: performance (reset)

Removing Scalability Bottlenecks in the Metadata Locking and THR_LOCK Subsystems in MySQL 5.7
Employee_Team +1 Vote Up -0Vote Down

The MySQL Server 5.7.5 Development Milestone Release, which was published recently, contains some significant changes to the metadata locking (MDL) subsystem and to the usage of the THR_LOCK manager for InnoDB tables. This post provides more information about these changes, which resulted in nice improvements in both scalability and performance.

Sometime during the development cycle of MySQL 5.6 we realized that locks used in the metadata locking subsystem (MDL) implementation can become a scalability bottleneck when many short statements were executed in @autocommit=1 mode against InnoDB tables.

Bug #66473 is a good example (8-table Sysbench

  [Read more...]
More EXT4 vs XFS IO Testing
+1 Vote Up -0Vote Down

Following my previous post, I got some excellent feedback in the forms of comments, tweets and other chat. In no particular order:

  • Commenter Tibi noted that ensuring I’m mounting with noatime, nodiratime and nobarrier should all improve performance.
  • Commenter benbradley pointed out a missing flag on some of my sysbench tests which will necessitate re-testing.
  • Former co-worker @preston4tw suggests looking at different IO schedulers. For all tests past, I used deadline which seems to be best, but re-testing with noop could be useful.
  • Fellow DBA @kormoc encouraged me to try many smaller partitions to limit the number of concurrent fsyncs.

There

  [Read more...]
Testing the Fastest Way to Import a Table into MySQL (and some interesting 5.7 performance results)
+2 Vote Up -0Vote Down

As I mentioned on my last post, where I compared the default configurations options in 5.6 and 5.7, I have been doing some testing for a particular load in several versions of MySQL. What I have been checking is different ways to load a CSV file (the same file I used for testing the compression tools) into MySQL. For those seasoned MySQL DBAs and programmers, you probably know the answer, so you can jump over to my 5.6 versus 5.7 results. However, the first part of this post is dedicated for developers and MySQL beginners that want to know the answer to the title question, in a step-by-step fashion. I must say I

  [Read more...]
The Query Rewrite Plugins
Employee_Team +1 Vote Up -0Vote Down
Why Query Rewrites?

Now that the cost model project is progressing, most of you are going to notice execution plan changes. In the vast majority of the cases, the changes will be for the better, and some bugs with a long history will finally be closed. In some cases, however, you will notice that your queries run slower. This is inevitable: even if the MySQL optimizer is doing a much better job with the information it has, it may still be the case that the information was incomplete and that the best plan was, in fact, found by not trusting that information! Normally, we would just say “add an optimizer hint” and be over with it. But sometimes you can’t do that. For instance your query could be auto-generated from an application that you have no control over.

  [Read more...]
Optimizer Cost Model Improvements in MySQL 5.7.5 DMR
Employee_Team +0 Vote Up -0Vote Down

In a previous blog post we presented some of the issues with the current optimizer cost model and listed several ideas for improvements. The new 5.7.5 DMR contains the result of our initial work on improving the optimizer’s cost model:

  • Cost Model for WHERE Conditions. In previous versions of MySQL, the estimated number of rows from a table that will be joined with the next table only takes into account the conditions used by the access method. This often led to record estimates that were far too high and thus to a very wrong cost estimate for the join. With wrong cost estimates, the join optimizer might not find and choose the best
  [Read more...]
Hard Drive Reliability
+0 Vote Up -0Vote Down

Cloud service provider Backblaze has updated its earlier study of hard drive failure rates (Nov 2013) in its own infrastructure – from 27,000 to more than 34,000 drives, and the new report (Sep 2014) is quite informative. Hitachi comes out pretty high, Western Digital has produced some good drives, but Seagate tends to come out worst. Each brand does have good and not-so-good models so there’s no single right answer, and for any new model you’ll always be dealing with an unknown factor.

Backblaze also found that consumer drives actually perform well compared to enterprise grade drives,

  [Read more...]
Better Performance for JOINs Not Using Indexes
Employee_Team +1 Vote Up -0Vote Down

In some cases it is not possible to use an index to optimize a JOIN. This may for example happen when you query the Performance Schema. As a result these kind of queries can be very slow; however in MySQL 5.6 and later you can use a trick to improve the performance considerably.

As a working example in this post, I will use the schema_table_statistics view in the sys schema. Since the view involves the schema, I will create a reasonable large number of databases and tables for the test:

shell$ for ((i=0; i<100; i++)); do
>    echo "Database ${i}"
>    mysql -e "CREATE DATABASE db${i}"
>    for ((j=0; j<100; j++)); do
>        mysql -e "CREATE TABLE
  [Read more...]
Which Compression Tool Should I Use for my Database Backups? (Part II: Decompression)
+0 Vote Up -0Vote Down

On my post last week, I analysed some of the most common compression tools and formats, and its compression speed and ratio. While that could give us a good idea of the performance of those tools, the analysis would be incomplete without researching the decompression. This is particularly true for database backups as, for those cases where the compression process is performed outside of the production boxes, you may not care too much about compression times. In that case, even if it is relatively slow, it will not affect the performance of your MySQL server (or whatever you are using). The decompression time, however, can be critical, as it may influence in many cases the

  [Read more...]
Items Affecting Performance of the MySQL Database
Employee_Team +1 Vote Up -0Vote Down

To learn about the many factors that can affect the performance of the MySQL Database, take the MySQL Performance Tuning course.

You will learn:

  • How your hardware and operating system can affect performance
  • How to set up and logging to improve performance
  • Best practices for backup and recovery
  • And much more

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

  • Training-on-Demand: Start training within 24 hours of registering for training, following lectures at your own pace through streaming video and booking time on a lab environment to suit your schedule.
  • Live-Virtual Event: Attend a live
  [Read more...]
The Road to MySQL 5.6 -- A DBA Perspective
+4 Vote Up -0Vote Down
We've all heard the hype.  MySQL 5.6 is packed with amazing new features that address all our database problems.  5.6 deals with replication and HA and performance and monitoring and security and features.  It just may cure cancer.

In fact it's been out for ages.  It went GA 

  [Read more...]
Showing entries 1 to 10 of 753 10 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.