Narendra Dhami

My Site

Archive for the ‘MySQL’ Category

Tips on MySQL

Posted by Narendra Dhami on January 18, 2010

  • Posted in MySQL | Leave a Comment »

    Top 20+ MySQL Best Practices

    Posted by Narendra Dhami on November 28, 2009

    Database operations often tend to be the main bottleneck for most web applications today. It’s not only the DBA’s (database administrators) that have to worry about these performance issues. We as programmers need to do our part by structuring tables properly, writing optimized queries and better code. Here are some MySQL optimization techniques for programmers. More …

    Posted in MySQL | Leave a Comment »

    Analyzing Queries for Speed with EXPLAIN

    Posted by Narendra Dhami on November 12, 2009

    When you are trying to optimize your queries to run quickly and efficiently, you may encounter queries that really should run faster. That’s where EXPLAIN comes in handy. This article shows you how to use EXPLAIN in query analysis. It is excerpted from chapter 13 of the MySQL Certification Guide, written by Paul Dubois et al. (Sams, 2005; ISBN: 0672328127). More …

    Posted in MySQL | Leave a Comment »

    MySQL Cluster: Two webserver setup

    Posted by Narendra Dhami on October 1, 2009


    This HOWTO was designed for a classic setup of two servers behind a loadbalancer. The aim is to have true redundancy – either server can be unplugged and yet the site will remain up.

    You MUST have a third server as a managment node but this can be shut down after the cluster starts. Also note that I do not recommend shutting down the managment server (see the extra notes at the bottom of this document for more information). You can not run a MySQL Cluster with just two servers And have true redundancy.

    Although it is possible to set the cluster up on two physical servers you WILL NOT GET the ability to “kill” one server and for the cluster to continue as normal. For this you need a third server running the managment node.

    I am going to talk about three servers:

    Servers 1 and 2 will be the two that end up “clustered”. This would be perfect for two servers behind a loadbalancer or using round robin DNS and is a good replacement for replication. Server 3 needs to have only minor changes made to it and does NOT require a MySQL install. It can be a low-end machine and can be carrying out other tasks. More …

    Posted in MySQL | Leave a Comment »

    InnoDB Configuration and Optimization

    Posted by Narendra Dhami on September 24, 2009

    Just thought i should have a quick guide for configuring an optimized for innodb my.cnf

    use innodb_data_home_dir & innodb_data_file_path to specify the location and size of the tablespace.

    innodb_data_home_dir = /path/to/data
    innodb_data_file_path = ibdata1:50M;ibdata2:50M:autoextend:max:10G
    innodb_autoextend_increment = 10

    This asks innodb to create two files ibdata1 & ibdata2 (as tablespace) and keep them in the /path/to/data directory. Both would be of 50 MB initially, but ibdata2 would expand automatically by innodb_autoextend_increment MBs (yes the value is in MB) till the max size of 10 GB. InnoDB forms the directory path for each data file by textually concatenating the value of innodb_data_home_dir to the data file name, adding a path name separator (slash or backslash) between values if necessary.

    By default innodb creates all its tables in the tablespace specified by the innodb_data_file_path directory – in a single file – as specified. But there is another option using which you can ask innodb to create tablespace for each table.


    Innodb would now store each newly created table in its own table_name.ibd file – which would contain both the data and the index.

    Lets look at some optimization and performance settings for innodb


    If you have a large number of innodb tables, you will need to increase this value. It is used to store data dictionary information and other internal data structures. If the value is low, you might see warning messages in the mysql error log.


    Innodb uses some table level locking for generating auto increment values – which is configurable. If the value is set to 0 (traditional mode), for all insert statements, a table level auto_inc lock is obtained and maintained till the end of the insert statement. If the value is set to 1(consecutive lock mode), all bulk insert-like statements obtain and maintain the auto_inc lock on the whole table. But simple inserts use a new locking method where a lightweight mutex is used during the allocation of auto-increment values, and no table-level AUTO-INC lock is used. If the value is set to 2 (interleaved lock mode), no insert-like statements use the table-level AUTO-INC lock, and multiple statements can execute at the same time. This is the fastest and most scalable lock mode, but it is not safe when using statement-based replication or recovery scenarios when SQL statements are replayed from the binary log. Here auto-increment values are guaranteed to be unique and monotonically increasing across all concurrently executing insert-like statements. However, because multiple statements can be generating numbers at the same time (that is, allocation of numbers is interleaved across statements), the values generated for the rows inserted by any given statement may not be consecutive.


    The larger the buffer pool the less disk I/O would be needed to access data in tables. Generally buffer pool should be around 50% of available memory size. Very large values can result in paging and slow down the system.


    The number of threads that can commit at the same time. Should be equal to the number of available cpus or cores.


    If the value is 0, the log buffer is written out to the log file once per second and the flush to disk operation is performed on the log file, but nothing is done at a transaction commit. When the value is 1 (the default), the log buffer is written out to the log file at each transaction commit and the flush to disk operation is performed on the log file. When the value is 2, the log buffer is written out to the file at each commit, but the flush to disk operation is not performed on it. However, the flushing on the log file takes place once per second also when the value is 2. Note that the once-per-second flushing is not 100% guaranteed to happen every second, due to process scheduling issues.

    The default value of 1 is the value required for ACID compliance. Better performance can be achieved by setting the value different from 1, but then you can lose at most one second worth of transactions in a crash. With a value of 0, any mysqld process crash can erase the last second of transactions. With a value of 2, then only an operating system crash or a power outage can erase the last second of transactions.


    The size of buffer that innodb uses to write log files on disk. A large buffer allows large transactions to run without a need to write the log to disk before the transactions commit.


    The size of each log file in a log group. The combined size of log files must be less than 4GB. Sensible values range from 1MB to 1/N-th of the size of the buffer pool, where N is the number of log files in the group. The larger the value, the less checkpoint flush activity is needed in the buffer pool, saving disk I/O. But larger log files also mean that recovery is slower in case of a crash.


    The number of log files in the log group.


    The maximum no of tablespaces that innodb can keep open at one time.


    If this is enabled, InnoDB support for two-phase commit in XA transactions is enabled, which causes an extra disk flush for transaction preparation. If you do not wish to use XA transactions, you can disable this variable to reduce the number of disk flushes and get better InnoDB performance.


    InnoDB tries to keep the number of operating system threads concurrently inside the engine less than or equal to the limit given by this variable. Once the number of threads reaches this limit, additional threads are placed into a wait state within a FIFO queue for execution. Threads waiting for locks are not counted in the number of concurrently executing threads. By default this value should be twice the number of available cpus/cores.

    Using some of the data here, you can easily setup a machine to perform better using innodb. You will always have to choose between performance and reliability as both are inversely proportional. As performance increases the reliability of data would decrease. The variables that i have set here are for high performance only.

    Posted in MySQL | Leave a Comment »

    Quick and Dirty MySQL Performance Troubleshooting

    Posted by Narendra Dhami on September 17, 2009

    What are the first things you should look at after learning of a sudden change in MySQL server performance?

    Sooner or later, you’re going to get that phone call (or email, SMS, instant message, tweet, or whatever): The database is slow! Fix ASAP! And you’ll be expected to very quickly figure out what’s slowing things down–often wither very little context, background, or sense of what may have changed recently at the application layer.

    It’s just a server. How hard could it be?

    Well, as we all know “that depends.” It depends on a lot of things that you likely have little time to investigate in the middle of a crisis. So where should you focus your time and attention?

    This article assumes that you have little to no good monitoring on the server and virtually no idea of what “typical” usage looks like either. Sadly, that’s a real-world situation for a lot of MySQL administators–especially those in smaller organizations who are expected to wear half a dozen different hats on any given day.

    In other words, let’s go back to basics and talk about high-level performance troubleshooting–not fancy high end hardware or new releases of software that you haven’t had a chance to try yet.

    Hardware Bottlenecks

    The first thing I tend to do is check for hardware bottlenecks. That means logging into the box and running a small list of commands to get a quick idea of what’s happening. Mainly I’m looking for stress points. Which resources are most constrained right now? CPU? Memory? Disk I/O? Something else?

    There are three main utilities I’ll run to in a situation like this:

    • top
    • vmstat
    • iostat

    First I’m going to use top to see if anything is hogging CPU on the machine. If there are non-mysql processes using a substantial percentage of the CPU cores, I’m going to want to have a look at what that is and see about limiting its use or moving it a dedicated server. If I see mysqld using up a lot of CPU, I know it’s working hard and will have to drill into what’s happening inside of MySQL (maybe some poorly written queries). If nothing is apparently chewing up the CPU time, I know that the problem is likely elsewhere.

    Next I’ll run vmstat over a fairly short interval–typically 5 or 10 seconds.

    $ vmstat 5

    ‘ll generally run this for at least two or three minutes to get a sense of what the CPU and memory use are like. I’m also watching to see how much time the CPU is stalled waiting for I/O requests. Doing this for several minutes will make the occasional spikes really stand out and also allow for more time to catch those cron jobs that fire up every few minutes.

    The last thing I’ll check before poking at MySQL itself is iostat. Just as with vmstat, I’m going to run it with a short interval (5 or 10 seconds) and do so for several minutes. I’ll likely filter the output so that I only see the output for the most active disk or array (the one where all of MySQL’s data lives).

    $ iostat -x 5 | grep sdb

    I’m looking closely at the % busy and tps to get a qualitative feel for how “busy” the I/O subsystem is, and I’ll watch both Blk_wrtn/s and Blk_read/s to figure out how many blocks are being written and read every second.

    Once I have a basic feel for what the system is doing, I’ll start digging into MySQL itself a bit.

    Probing MySQL

    Looking inside MySQL is a sort of two-layer problem for me. First I want a high-level picture of what it seems to be doing and then I want to dig into the storage engine(s) doing all the I/O work, since I/O is a very common bottleneck.

    For the highest level view, I want to see a number of things quickly:

    • how many queries per second is the server handling
    • how many clients are connected (and active)
    • are there many “slow” queries being executed
    • what, if any, unusual errors are being logged

    The first few items can be answered by looking at the results of a few SHOW PROCESSLIST commands along with some SHOW GLOBAL STATUS. Or, better yet, by using a tool that is able to summarize and prevent that data in a more friendly and efficient manner. innotop and mytop both do that. (I wrote mytop but fully admit that innotop is more feature rich and frequently maintained. But either will handle the basics.) Sorting the running queries by execution time is often revealing.

    To find out about slow queries I’m going hope that the slow query log is enabled and the server has a sane long_query_time. But even the default of 10 seconds is helpful in truly bad situations.

    I’ll also want to glance through MySQL’s error log to make sure nothing bad-looking has started to appear.

    With that out of the way, I’ll generally dive into the storage engine statistics. And nowadays that almost always means asking InnoDB to cough up some information with SHOW ENGINE INNODB STATUS. While there’s a wealth of information to process in that output, I’m mainly interested in a few high-level stats at the end.

    Total memory allocated 23904405026; in additional pool allocated 37084160
    Buffer pool size   1310720
    Free buffers       1
    Database pages     1274443
    Modified db pages  770518
    Pending reads 1
    Pending writes: LRU 0, flush list 1, single page 0
    Pages read 733227814, created 65128628, written 1679994934
    98.63 reads/s, 3.09 creates/s, 227.34 writes/s
    Buffer pool hit rate 999 / 1000

    I’m usually keen to see that the buffer pool hit rate is good. The closer to 1000 / 1000 things get, the happier I am. I also want to see how mange pages are being read and written per second. If either of those seems high, it should correspond to a high volume of I/O seen earlier in iostat.

    The Culprit?

    In nine out of ten cases, the culprit is obvious by now–or was obvious half way through this exercise. The reality is that most of the time changes that are very easy to spot are responsible for a sudden decline in performance. The trick is this: you have to be looking in order to see them!

    Silly as that sounds, it’s been my experience that most problems go undetected until someone complains–especially in smaller IT groups where there isn’t someone regularly looking after the MySQL server that runs just fine 99.9% of the time. And since it fails so infrequently, nobody bothers to setup decent monitoring or performance logging infrastructure to detect problems before end users are impacted.

    Sound familiar?

    Just a Start…

    The tasks I’ve presented here are just the beginning. Once you’ve got a good sense of what’s happening on a MySQL server, you can really start to dig in and think about how to improve the situation. Do you add or change hardware? Modify the application? Adjust some of MySQL or InnoDB’s run-time or start-time parameters?

    Original From

    Posted in MySQL | Leave a Comment »

    How To Set Up Database Replication In MySQL

    Posted by Narendra Dhami on September 7, 2009

    This tutorial describes how to set up database replication in MySQL. MySQL replication allows you to have an exact copy of a database from a master server on another server (slave), and all updates to the database on the master server are immediately replicated to the database on the slave server so that both databases are in sync. This is not a backup policy because an accidentally issued DELETE command will also be carried out on the slave; but replication can help protect against hardware failures though. More …

    Adding More Slaves …

    Switching Masters During Fail-over …

    Master to Slave and Master to Master ….

    Posted in MySQL | 1 Comment »

    MySQL Stored Procedures

    Posted by Narendra Dhami on September 3, 2009

    MySQL 5.0 finally introduces functionality for Stored Procedures. So what exactly are stored procedures? That is the kind of question that gets database professionals who use other DBMS’s raising their eyebrows. Stored procedures have been integral to Oracle, PostgreSQL, DB-2, MS-SQL server and others for years, and it has long been a sore point that MySQL has not had them. But there is no snobbery here – if you are a MySQL newbie, or have been using MySQL for years and want to find out what all the fuss is about, read on. If it is your eyebrows that are raised, and you just want to know how MySQL implements them, you will be relieved to know MySQL stored procedures are very similar to the DB2 implementation, as both are based on the SQL:2003 standard.

    A stored procedure is simply a procedure that is stored on the database server. MySQL developers have to date unthinkingly written and stored their procedures on the application (or web) server, mainly because there hasn’t been an option. That has been limiting. Some have claimed that there are two schools of thought – one claiming that logic should be in the application, the other saying it should reside in the database. However, most professionals would not bind themselves to one or other viewpoint at all times. As always, there are times when doing either makes sense. Unfortunately, some of the staunchest adherents of the in the application school are only there because until now they have had no choice, and it is what they are used to doing. So why would we want to place logic on the database server?

    More …
    Part 1  
    Part 2  
    Part 3

    Posted in MySQL | Leave a Comment »

    An Overview of the MySQL Server Architecture

    Posted by Narendra Dhami on September 2, 2009

    Posted in MySQL | Leave a Comment »

    INNODB Performance TODO List

    Posted by Narendra Dhami on August 7, 2009

    These are my plans for making InnoDB faster on SMP and high-IOPs servers. I think we can double throughput at high levels of concurrency.

    Future work:

    1. Reduce the size of mutex and rw-lock structures
    2. Reduce contention on the sync array mutex
    3. Reduce contention on kernel_mutex
    4. Reduce contention on commit_prepare_mutex
    5. Reduce the number of mutex lock/unlock calls used when a thread is put on the sync array
    6. Name all events, rw-locks and mutexes in InnoDB to make contention statistics output useful
    7. Add optional support to time all operations that may block
    8. Introduce dulint to native 64-bit integer types
    9. Make BUF_READ_AHEAD_AREA a compile-time constant
    10. Prevent full table scans from wiping out the InnoDB buffer cache
    11. Make prefetching smarter
    12. Get feedback from Dimitri, Domas, Mikael and Percona
    13. Use prefetch with MRR/BKA to get parallel IO in InnoDB
    14. Investigate larger doublewrite buffer to allow for more concurrent IOs
    15. Make Innodb work with a 4kb page size
    16. Make trx_purge() faster when called by the main background thread
    17. Use crc32 for Innodb page checksums with hardware support or otherwise make checksum faster.
    18. Reduce the per-page overhead for sync objects
    19. Repeat

    Current work:

    1. Add my.cnf options to disable InnoDB prefetch reads
    2. Put more output in SHOW INNODB STATUS and SHOW STATUS
    3. Reduce the overhead from buf_flush_free_margin()
    4. Change background IO threads to use available IO capacity
    5. Use more IO to merge insert buffer records when the insert buffer is full

    Non-InnoDB work:

    1. Fix mutex contention for the HEAP engine
    2. Fix mutex contention for the MyISAM engine
    3. Fix mutex contention for the query cache
    4. Give priority (CPU, disk) to the replication SQL thread to minimize replication delay.
    5. Push changes for –oltp-secondary-index to public sysbench branch
    6. Add support to sysbench fileio for transaction log and doublewrite buffer IO patterns

    Originally from

    Posted in MySQL | Leave a Comment »