[P]Delete

A few of the basic settings to get a better performance out of your MySQL server. We are assuming, of course, that the server role is limited to just running MySQL and that is has sufficient memory and disk to meet the application needs. The example in this document uses 16G of RAM. Monitoring Performance MySQL as some logging features to identify slow queries. This configuration logs queries that take longer that 2 seconds to complete. log_slow_queries = /var/log/mysql/mysql-slow.log long_query_time = 2 log-queries-not-using-indexes Then use the tools  to evaluate the logs. Using the above configuration the logs will show both slow and non-indexed queries which may be noise. mysqldumpslow /var/log/mysql/mysql-slow.log Count: 1 Time=0.42s (0s) Lock=0.00s (0s) Rows=0.0 (0), debian-sys-maint[debian-sys-maint]@localhost select count(*) into @discard from `information_schema`.`PARTITIONS` Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0), debian-sys-maint[de...

MySQL is a powerful open-source database.  With more and more database driven applications, people have been pushing MySQL to its limits.  Here are 101 tips for tuning and optimizing your MySQL install.  Some tips are specific to the environment they are installed on, but the concepts are universal.   I have divided them up into several categories to help you with getting the most out of MySQL: MySQL Server Hardware and OS Tuning: 1. Have enough physical memory to load your entire InnoDB file into memory – InnoDB is much faster when the file can be accessed in memory rather than from disk. 2. Avoid Swap at all costs – swapping is reading from disk, its slow. 3. Use Battery-Backed RAM. 4. Use an advanced RAID – preferably RAID10 or higher. 5. Avoid RAID5 – the checksum needed to ensure integrity is costly. 6. Separate your OS and data partitions, not just logically, but physically – costly OS writes and reads will impact your database performance. 7. Put...

Interviewing people for our  Job Openings  I like to ask them a basic question – if you have a server with 16GB of RAM which will be dedicated for MySQL with large Innodb database using typical Web workload what settings you would adjust and interestingly enough most people fail to come up with anything reasonable. So I decided to publish the answer I would like to hear extending it with basics of Hardware OS And Application optimization to optimize MySQL database. I call this  Innodb Performance Optimization Basics  so these are general guidelines which work well for wide range of applications, though the optimal settings of course depend on the workload. Hardware If you have large Innodb database size Memory is paramount. 16G-32G is the cost efficient value these days. From CPU standpoint 2*Dual Core CPUs seems to do very well, while with even just two Quad Core CPUs scalability issues can be observed on many workloads. Though this depends on the application a l...

I recently stumbled upon a post that Peter Zaitsev published back in 2007 titled “ Innodb Performance Optimization Basics .” It’s a great post and reading it inspired me to examine what’s changed in the nearly six years that have followed in terms of MySQL, Percona Server – as well as in all of the other now-available infrastructures. And a lot has in fact changed! In this post I am going to highlight most of the InnoDB parameters critical for InnoDB – specifically from a performance perspective. I’m a support engineer and I can tell you that Percona Support gets many questions related to the right sizing of basic InnoDB parameters. So hopefully this post will help others with similar questions and issues. Hardware: For larger datasets, nowadays memory counted in hundreds of giga- and even in terabytes is not surprising. MySQL requires significant memory amounts in order to provide optimal performance. By caching hot datasets, indexes, and ongoing changes, InnoDB is able to pr...

When we are hired for a MySQL performance audit, we are expected to review the MySQL configuration and to suggest improvements. Many people are surprised because in most cases, we only suggest to change a few settings even though hundreds of options are available. The goal of this post is to give you a list of some of the most critical settings. We already made such  suggestions  in the past here on this blog a few years ago, but things have changed a lot in the MySQL world since then! Before we start… Even experienced people can make mistakes that can cause a lot of trouble. So before blindly applying the recommendations of this post, please keep in mind the following items: Change one setting at a time! This is the only way to estimate if a change is beneficial. Most settings can be changed at runtime with  SET GLOBAL . It is very handy and it allows you to quickly revert the change if it creates any problem. But in the end, you want the setting to be adjusted...

Proper MySQL configuration is one of the most important aspects in terms of performance. Optimizing the MySQL configuration can provide up to 65% performance improvement. MySQL by default is configured to use far fewer resources than the average hardware can accommodate. InnoDB, the primary table storage engine type can use the in-memory buffer pool to cache table indexes and data. Less disk I/O is needed to get data from hard drives when the value of the in-memory buffer pool is set higher. A general recommendation is to set this parameter up to 80% of the available RAM for a dedicated database server. If you are running webserver and database server on one server it’s recommended to split the entire memory pool into two parts. Setting for the key parameter “innodb_buffer_pool_size” Server Type innodb_buffer_pool_size Combined webserver and database server, 6 GB RAM 2-3 GB Dedicated database server, 6 GB RAM 5 GB Dedicated database server, 12 GB RAM 10 GB Dedicated database ...