Subscribe: Optim MySQL
Added By: Feedage Forager Feedage Grade B rated
Language: English
buffer pool  buffer  data  day  innodb  log  mysql manual  mysql  pool  query  server  size  time  values  variable 
Rate this Feed
Rate this feedRate this feedRate this feedRate this feedRate this feed
Rate this feed 1 starRate this feed 2 starRate this feed 3 starRate this feed 4 starRate this feed 5 star

Comments (0)

Feed Details and Statistics Feed Statistics
Preview: Optim MySQL

Optim MySQL

Keep watching this blog for interesting stuff on MySQL - Database Tuning, Traps and much more.

Updated: 2016-10-17T13:53:13.368+01:00


Changing Gears


This week I'm changing gears of my life. On Friday (5th September), I  yodeled and bled purple for one last time. It has been 2 years and 8 months when I first joined Yahoo! directly from college during which the size of Yahoo!, here at Bangalore, has almost tripled. Definitely the most rewarding and learning time for me as I worked and interacted with some really cool, supportive and intelligent people.

It was really a difficult decision to consider as I had no reasons to leave. It took me some time to convince myself. The only reason probably is that I got an opportunity to do something that always wanted to, that is working for a startup :).

Starting Monday I'm joining mKhoj, an Indian startup in the "Mobile Advertising marketplace" here at Bangalore. Looks like exciting times ahead.

Here are few things that I remembered to collect. Hooded sweatshirt and a nice clock (my parting gifts), my star, a YEFI calendar, yo yo badge holder and a gluestick. Definitely I will miss Yahoo! while enjoying at mKhoj.

Variable's Day Out #16: innodb_log_file_size


Applicable To InnoDB
Server Startup Option --innodb_log_file_size=
Scope Global
Dynamic Yes
Possible Values Integer: Range: 1M - 4G
<1M will be adjusted to 1M
Default Value 5M
Category Performance, Maintenance


This variable defines the size of each log file in a log group. While setting this variable it should be noted that combined size of all log files should be less than 4GB.

InnoDB requires these logs for recovery in case of a crash. So how come the size of these logs effect server performance? As stated in MySQL manual "The larger the value, the less checkpoint flush activity is needed in the buffer pool, saving disk I/O.", these logs help InnoDB in running more confidently as it knows that even if data is not written to the persistent storage often it can still have it.

Best Value:

A larger value helps you in performance but only up to some point. After a certain value, the performance gain will be minimal or it can be negative. Another issue to be considered is that a value too large will slow down recovery as there will be more and more logs to be scanned. But definitely the default is too small.

My usual recommendation is to set it to 256M or if you feel its big (because maybe you have too many crashes and of course crash recoveries) then 128M. Anything beyond this range should be tested properly and justified.

How to set?

If you just change the size of this variable, MySQL will crib about the changed log file size and start without the InnoDB engine. The safe way of re-setting this value is:
  1. Stop the MySQL server
  2. Backup your data and log files
  3. Delete log files
  4. Set the new value for innodb_log_file_size in my.cnf
  5. Start mysql server
Read More:

Translation is Fun!!


Morning, I saw Monty's post asking for contribution to drizzle's i18n efforts. I did checked out Hindi language and well I must say translation is a fun activity. 

If you think that will be as easy as using some online translation tool (I tried Google Translate), you may be wrong. Many sentences that make direct sense in English get completely screwed when translated word by word. Sometimes they are translated into a perfect meaningful sentence and that is when you can laugh out loudly.

As of now I'm doing Hindi (already 80 translations down) and next I'm gonna pick Punjabi. Wow! I know languages.

MySQL camp with Kaj (29th july)


I was there in the meetup and my feeling was a mix about the same. It was nice to have Kaj here (for the first time) and listen to him about Sun's acquisition. On the other hand it was disheartening to see so few people from corporates turning up. It was almost negligible. I'm still positive on this and do expect many more people to turn up. There were a total of three talks in the meet and then we had some chit chat with people. In his first talk, Kaj first greeted everybody in Hindi, Tamil and Kannada and many were delighted. Kaj touched various aspects of Sun's acquisition and also their on-boarding struggle. He also mentioned about MySQL considering Sun's liberal SCA in place of their stricter CLA. (I haven't gone through SCA to actually comment on it's benefits) Second talk by Thava was on how to contribute code to MySQL. It was a nice talk and showed various resource points and steps of doing so in a right manner. Third talk, again by Kaj, was on the different ways of contributing to MySQL and MySQL community other than code. He showed screenshots of forums, forge, planetmysql, few blogs (he forgot mine ;) ), bugs.mysql and how to use them. Interestingly, my name appeared on the planetmysql page :) . Overall it was nice that it started and we need to find out ways for more participation.

Variable's Day Out #15: bulk_insert_buffer_size



Applicable To MyISAM
Server Startup Option --bulk_insert_buffer_size=
Scope Both
Dynamic Yes
Possible Values Integer:

Range: 0 - 4294967295 (4G)

Default Value 8388608 (8M)
Category Performance


This cache is used by MyISAM to optimize bulk inserts. This cache is a special tree-like structure. Bulk inserts include statements like LOAD DATA INFILE..., INSERT ... SELECT, INSERT ... VALUES (...), (...), ..., (...)

Bulk inserts are often used to minimize disk writes etc and are very common in applications inserting lots of data. I often use bulk inserting using the following technique: (pseudocode)

  • CREATE TEMPORARY TABLE tmp_table LIKE main_table
  • LOOP:
  • INSERT INTO main_table SELECT * FROM tmp_table

If you are using any technique for bulk insertion, you should be carefully setting this parameter.

Optimal Value:

This depends on the way your bulk inserts are structured. If you are doing bulk inserts with data being close to or more than 10k, this comes really handy.

Read more:

Hope you enjoyed reading this.

MySQL command line pager & mysmartpager


Few days back, Baron re-introduced MySQL's command line pager command and described some cool tricks with maatkit's mk-visual-explain (one of my favorite tools). Soon after reading it, I wished if it was possible to describe regex based (on query) paging. I have written a small hack, christened mysmartpager, that can actually do regex based paging for you. The idea is simple, write a relay that will redirect the output based on to the desired pager. The problem was complex, there was no direct way of getting to know the original query. There are a couple of indirect ways of doing so, but of course with hurdles:
  • Run mysql client with --xml option: This will print the output of each command in xml and the command itself is included in the xml. The downside was not many pagers (including mk-visual-explain) understand the xml format and parsing the xml to create a output like string was going to be costly. This will also include writing a lot of code that I don't do for a hack. :)
  • Use the tee command: MySQL allows you to tee output to a file that contains everything from standard output including mysql> command... We could have used and easily take the last command using a simple grep. The problem is output is tee'd after pager exits.
Well, that was easy. Now since we know that MySQL will tee after the pager exits we will make the pager exit while still working. And introduce exit if fork; in our code. :) It's working fine for me with small display problems like the time spent is now displayed above the query result. That's fine for me. Files: I don't have an exact way of uploading files into blogger so got it on scribd. Install:
  • Copy mysmartpager file to your /path/to/bin directory
  • Copy .mysmartpager file to your $HOME directory
  • Copy mysmartpager.cnf file to your MySQL's config directory and make sure it is included.
  • Make appropriate changes in mysmartpager.cnf file
  • More regexs (perl regex's can be written in .mysmartpager)
I have listed three regex's in .mysmartpager file. Would like to hear more ideas flowing.

Reading "High Performance MySQL, 2nd Edition"


I haven't received my copy of the book yet, but being unable to control my temptation I have started reading it over Safari while waiting for my own very personal copy. :)

Already a fan of the first edition, you can feel the same charisma being carried over in this book also. The best part of the book is the simplicity by which you are set sailing over MySQL.

Without doubt, it is one of the best books MySQL can ask for. Certainly, I would recommend this book to anyone who is associated with the word MySQL. Or otherwise if you answer yes to any of these questions below, then go and grab a copy.

  • Are you a developer working/struggling with MySQL?
  • Are you a DBA working/struggling with MySQL?
  • Do you intend to learn MySQL?
  • Are you fascinated by databases and open-source?
  • Do you work with some other RDBMS and have an open mind?
  • Are you an Internet entrepreneur worried about your one fine day?
  • ...
  • ...
  • ...
  • Do you love dolphins?

Well, there are some blanks left for you to fill in your own reasons. :)

As for me, I would take this opportunity to thanks Baron, Peter, Vadim, and Arjen for a wonderful book and resort back to reading.

Temporary tables as seen by replication slave


Few days back, one of my colleagues posted a good question. It sounds something like this; "Temporary tables are session based that means under different sessions we can create temporary tables with similar names. Now since slave thread is singleton, how does it manage to keep them separate?" He was very much right in asking this and the answer is not all that intuitive. Lets go through the binlog events to see why it is not that intuitive. 1: mysql> SHOW BINLOG EVENTS IN 'log-bin.000016'; 2: . . . 3: | log-bin.000016 | 389 | Query | 2515922453 | 488 | use `test`; CREATE TEMPORARY TABLE test.t(a int) | 4: | log-bin.000016 | 488 | Query | 2515922453 | 582 | use `test`; INSERT INTO test.t(a) VALUES(1) | 5: | log-bin.000016 | 582 | Query | 2515922453 | 676 | use `test`; INSERT INTO test.t(a) VALUES(3) | 6: | log-bin.000016 | 676 | Query | 2515922453 | 775 | use `test`; CREATE TEMPORARY TABLE test.t(a int) | 7: | log-bin.000016 | 775 | Query | 2515922453 | 869 | use `test`; INSERT INTO test.t(a) VALUES(7) | 8: | log-bin.000016 | 869 | Query | 2515922453 | 944 | use `test`; drop table t | 9: ... Under general conditions if you run these statements in sequence, you will end up with a Table `t` already exists when you put second create temporary table. But with replication this seems to just work, how? Well, the truth is SHOW BINLOG EVENTS doesn't show the full truth. The Magic Behind:For such situations, MySQL uses a special flag LOG_EVENT_THREAD_SPECIFIC_F that is set if the event is dependent on the connection it was executed on. This translates into setting a session level variable pseudo_thread_id instructing the slave thread to treat a bundle of statements in a special way and do not create any confusion. Now this is actually a very safe method of doing things and being very extra paranoid I wondered why this was not there for every session? Simple answer is; performance reasons. :) Check the outcome of mysqlbinlog: 1: $ mysqlbinlog log-bin.000016 2: ... 3: # at 389 4: #080617 2:06:11 server id -1779044843 end_log_pos 488 Query thread_id=138 exec_time=0 error_code=0 5: SET TIMESTAMP=1213693571/*!*/; 6: SET @@session.pseudo_thread_id=138/*!*/; 7: CREATE TEMPORARY TABLE test.t(a int)/*!*/; 8: # at 488 9: #080617 2:06:15 server id -1779044843 end_log_pos 582 Query thread_id=138 exec_time=0 error_code=0 10: SET TIMESTAMP=1213693575/*!*/; 11: SET @@session.pseudo_thread_id=138/*!*/; 12: INSERT INTO test.t(a) VALUES(1)/*!*/; 13: # at 582 14: #080617 2:06:36 server id -1779044843 end_log_pos 676 Query thread_id=138 exec_time=0 error_code=0 15: SET TIMESTAMP=1213693596/*!*/; 16: SET @@session.pseudo_thread_id=138/*!*/; 17: INSERT INTO test.t(a) VALUES(3)/*!*/; 18: # at 676 19: #080617 2:06:55 server id -1779044843 end_log_pos 775 Query thread_id=141 exec_time=0 error_code=0 20: SET TIMESTAMP=1213693615/*!*/; 21: SET @@session.pseudo_thread_id=141/*!*/; 22: CREATE TEMPORARY TABLE test.t(a int)/*!*/; 23: # at 775 24: #080617 2:11:07 server id -1779044843 end_log_pos 869 Query thread_id=141 exec_time=0 error_code=0 25: SET TIMESTAMP=1213693867/*!*/; 26: SET @@session.pseudo_thread_id=141/*!*/; 27: INSERT INTO test.t(a) VALUES(7)/*!*/; 28: # at 869 29: #080617 2:15:50 server id -1779044843 end_log_pos 944 Query thread_id=141 exec_time=0 error_code=0 30: SET TIMESTAMP=1213694150/*!*/; 31: SET @@session.pseudo_thread_id=141/*!*/; 32: drop table t/*!*/; 33: ... I delved into the code[...]

Go! Take the survey


Keith Murphy and Mark Schoonover have put together an excellent survey which is already doing good (seeing the numbers they have been clocking). Survey results will be out in the summer issue of MySQL magazine. I feel this is a "must take" survey for everyone in the MySQL community. And do not forget to mention my blog in the "top 5 favorite MySQL blogs" ;-). Just kidding!

Overall, this is the best survey regarding MySQL that I have ever taken, don't miss it. It will hardly take 10 minutes of your busy schedule. A busy man has the time do anything, right?

Quick Links:

Variable's Day Out #14: log_queries_not_using_indexes



Applicable To MySQL Server
Server Startup Option --log-queries-not-using-indexes
Scope Global
Dynamic Yes
Possible Values Boolean
Default False
Category Performance, Monitoring, Best Practices


If you have slow query logs enabled (with --log-slow-queries), this variable will help interpret all those queries that are not using indexes as slow queries.


While designing a MySQL oriented application, I generally design my schema first without introducing any index (only exception being the PRIMARY ones). Get the application ready. Next enable log_queries_not_using_indexes and start index usage testing. Analyze all queries, introduce indexes one by one and you are all set to go. This helps a lot in rewriting queries, figuring out the best possible index etc. Many times I have seen the need for swapping WHERE clause CONDITIONS for making it possible for the query to use an existing index.

For already deployed systems' performance glitches and production outages, this flag do serve as a starting point.

Though as always, usage of a variable is subject to circumstances. For my conditions and usage pattern, this variable comes very handy.

Read more:

Hope you enjoyed reading this.

Variable's Day Out #13: binlog_format


Properties: Applicable To MySQL Server Introduced In 5.1.5 Server Startup Option --binlog-format= Scope Both Dynamic Yes Possible Values enum(ROW, STATEMENT, MIXED) Default < 5.1.12: STATEMENT >= 5.1.12: MIXED Categories Replication, Performance Description: Starting with 5.1.5, MySQL has implemented ROW based replication format which logs the physical changes to individual row changes. This looks like the most optimal way to many users. But it is not always, rather not optimal most of the times. E.g. consider a statement that does bulk insert of thousands of rows. In ROW based logging, there will be those many entries in binlog and otherwise it would have been just one single statement. STATEMENT based replication, the old good historical way, propagates SQL statements from master to slave and has been working good all those years except for few cases like statements using non deterministic UDFs. In MIXED format, MySQL uses STATEMENT based replication by default other than a few cases like when USER(), CURRENT_USER() are used when a call to UDF is involved when 2 or more tables with AUTO_INCREMENT columns are updated. ... For full list of all such cases, refer the official documentation. What about UDF's? A user defined function or stored procedure is very hard to predict. In such cases, statement based replication can create inconsistencies. Few days back, I saw a case where a table  included in Replicate_Ignore_Table list was propagating statements from a procedure. If one has procedures and such cases, consider using ROW or MIXED mode replication. What to use? Though ROW based replication is the safest in case of creating inconsistencies, it may lead to sheer performance degradation thanks to binlog size. On the other hand STATEMENT based replication has drawbacks like with UDFs etc. IMHO, MIXED mode is the best way out unless you have some very special case. The only problem is that there might be more cases that need to be handled by mixed mode than currently being served. We need time's stamp on it. :) Read more: MySQL manual entry on binlog_format MySQL manual on Replication Formats MySQL manual: Mixed Binary Logging Format Statement based vs Row based replication   Hope you enjoyed reading this. [...]

Variable's Day Out #12: innodb_flush_method



Applicable To InnoDB on Unix like OS
Server Startup Option --innodb_flush_method=
Scope Global
Dynamic No
Possible Values enum(O_DSYNC, O_DIRECT, <> )
Default Value <>
Category Performance


This variable changes the way InnoDB open files and flush data to disk and is should be considered as very important for InnoDB performance. By default, InnoDB uses fsync() (without O_DSYNC) to flush both log and data files.

Setting this variable to O_DIRECT will result in InnoDB using O_DIRECT while opening files and fsync() to flush both data and log files. O_DIRECT is useful when an application maintains it's own caching mechanism which is very well true for MySQL/InnoDB. O_DIRECT is the option that should be used in most of the cases as it takes the overhead of double buffering and reduces swap pressure. You should be careful if you are not using a battery backed up RAID cache as it may lead to data loss in case of a crash. Also, the MySQL documentation warns against using O_DIRECT when files are located on a SAN, I don't have any idea on that.

O_DSYNC makes InnoDB to use O_SYNC to open and flush the log files, and uses fsync() to flush data files.

For Windows, the flush method is always async_unbuffered.


If you are not doing anything unusual like SAN storage etc (which otherwise also you should reconsider before doing), always use O_DIRECT for this. This leads to a significant improvement in InnoDB performance by removing double buffering.

Read More:


Hope you enjoyed reading this.

Variable's Day Out #11: large_pages


Properties: Applicable To MySQL/Linux Server Startup Option --large-pages Scope Global Dynamic No way Possible Values True|False flag Default Value False Category Performance, Administration Description: This option, currently available only for Linux, if set enables the usage of large pages by MySQL. Many operating systems/system architectures support optional memory pages bigger than the default size (4 KB). Usually the large page size is 2 MB, this can be checked in the large_page_size variable in MySQL. For applications that do a lot of memory accesses, as MySQL can, reduced TLB (Translation Lookaside Buffer) misses lead to better performance. Also having large pages, makes MySQL buffers less susceptible of being swapped out. When to Use? Try to keep it enabled, I have never heard of any side effects (In other words if you find any do let me know, I will update the blog post :) ). Other things that can be considered are: How much of swapping is happening on the system? What are the other applications running on that system? Though it is a bad idea to run other heavy applications on the same box as MySQL (at least in production), but if you can't get rid of them, using large-pages will make MySQL avoid swapping. How to configure? Before large pages can be used on Linux, it is necessary to configure the HugeTLB memory pool. Check your total memory (MemTotal), huge page size (Hugepagesize) etc and decide how many huge pages you want to run with. This information is available in /proc/meminfo file. Set the number of huge pages to be used by the system. use sysctl -w vm.nr_hugepages = Ensure to set memlock for mysql user in /etc/security/limits.conf to allow this user using large memory. Stop MySQL. Restart your system. Start MySQL server with large-pages option. If nothing goes wrong, you should be all set and going. Read more: MySQL manual entry on large-pages Swap file discussion @ Large page support in the Linux kernel Using Very Large Memory MySQL manual entry on large_page_size   Hope you enjoyed reading this. [...]

Memcached, but do you need it?


With all due respect to the technology and it's advocates (myself included), after a surge in articles describing the merits of using memcached I'm just pushing a thought breakpoint for developers to think whether they actually need it or not?

Recently, I ran into cases where the developers have decided to use memcached over MySQL style architecture after reading some/many good/nice articles about it without giving a damn to their requirements. I would like to list few things as a checklist for developers to decide on their architecture. There is still no precise answer but sometimes few cases can be just ruled out :).

  1. What is the total size of your data? It might be a possibility that you can keep the data in memory in each node, or MySQL can just keep the whole thing (data+indexes) in a buffer.
  2. How frequently your data is updated? Very frequent updates may lead to low cache hit ratio for memcached data. And refreshing memcached too many times may lead to unnecessary overhead. Remember doing [get,get,set] vs [get].
  3. What is the peak load on your system? Consider if MySQL itself can handle the peak load or otherwise if even memcached cannot handle the peak load with given infrastructure.

I generally ask people a simple question, Why they think they should be using memcached (or something else even)? To shock them, I even ask them "Why they think they should be using MySQL?". And believe me, this is what I believe developers should be asking themselves.

There is only one good argument against this, what if tomorrow you need to suddenly scale or what if your projections need memcached? In such cases, I suggest people to design their data layers in a flexible way, flexible enough to allow things in and out.

Long Live Open Source


“Don’t worry about people stealing an idea. If it’s original, you will have to ram it down their throats.”
— Howard Aiken

MySQL is back on Open Source track and that is definitely the best news for all (including community, MySQL and Sun as well). I think that now Sun/MySQL have agreed to the importance of community, it becomes community's responsibility to give them more reasons to believe so. Let's participate like never before.

Kaj, in his post says "...model to be useful for both those who spend money to save time, and those who spend time to save money". This is what Open Source is, isn't it?

All in all, a decision most awaited and most welcome is taken. Thanks a ton to everybody who made this happen.

Last 10 Variable Day Outs


Well, the first summary of last 10 variable day outs.

Day Outs:

(If you want some specific variable to be discussed here, do let me know. Contact me by posting a comment on the blog or send me a mail.)

Variable's Day Out #10: innodb_file_per_table



Applicable To InnoDB
Server Startup Option --innodb-file-per-table
Scope Global
Dynamic General InnoDB Engine: No
InnoDB plug-in: Yes
Possible Values Enable|Disable Flag
Default Value Disabled
Category Maintenance


This variable if enabled, makes InnoDB to create a separate .ibd file for storing indexes and data. Setting this variable makes it easy to manage disks with huge tables. Having this option enabled, makes it a lot easier to move bigger (or smaller) .ibd files to separate physical disks and/or have backups of certain tables without affecting others.

As I have mentioned in my earlier post, that InnoDB does not return back the space once claimed for a tablespace, this variable comes handy in managing such situations. Though I have not seen any performance gain while using this option, people do cite such metrics. Again, I would say as always that it may be case dependent.

It should be noted that this option only effects tables that are created after this option is enabled (and server is restarted). Tables that are created with this option disabled are created in the shared tablespace and remain there even if this option is enabled in between. Also, even if all the tables are created with this option enabled, InnoDB still needs the shared tablespace to store its internal data dictionary and undo logs.

Speical case: InnoDB plug-in

With the introduction of InnoDB plug-in, this variable becomes dynamic in nature and holds much more importance than just disk maintenance. You should have this variable enabled to be able to use the new file format. But even in InnoDB plug-in the restriction on tablespaces not returning disk space is intact.

Read More:

Variable's Day Out #9: long_query_time



Applicable To MySQL Server
Server Startup Option --long-query-time=
Scope Both
Dynamic Yes
Possible Values (< 5.1.21): Integer
(>=5.1.21): Numeric
Minimum Value (< 5.1.21): 1
(>=5.1.21): 0
Default Value 10
Category Monitoring


In case (as generally the case is) one wants to know about the bottlenecks in their system, MySQL's has a small answer to this in "Slow Query Logs". Any query that takes more time than specified by long_query_time, is labeled as a slow query and logged. Time measurement for a query is done in real time and not CPU time. So, whether a query is slow or not depends on the system that you are running on and the load the system is running under.

For many people's delight, as of MySQL 5.1.21, the long_query_time can be specified in microseconds and minimum value has been changed to 0. Earlier, the minimum value was 1.  If set to "0", it will log all the queries under slow query log. However, the new microsecond resolution in 5.1.21 is only supported when logging to a file and microsecond part is ignored when logging to table. The table logging was introduced in MySQL 5.1.6.

How to Use:

This is completely dependent on your use-case, SLA's etc. I have two systems, one of them is running with long_query_time being 5 seconds. Whereas for the second one, we are planning to upgrade so to become capable of using microsecond resolution (actually we need up-to millisecond resolution ;) ).

Related Variables:

  • log_slow_queries
  • log_queries_not_using_indexes
  • min_examined_row_limits
  • log_output
  • slow_query_log_file

Read More:


Hope you enjoyed reading this.

Variable's Day Out #8: innodb_additional_mem_pool_size


Properties: Applicable To InnoDB Server Startup Option --innodb-additional-mem-pool-size= Scope Global Dynamic No Possible Values Integer: Range: 524288 (512K) - 4294967295 (4G) Default Value 1048576 (1M) Category Performance Description: As per MySQL documentation, this pool is used to store data dictionary information and other internal data structures. If InnoDB runs out of memory on this pool, it starts allocating from OS. Most of the additional memory pool usage goes to tables in the data dictionary and connections. Normally, with increasing number of tables you may need to allocate more memory to this pool. But as modern OS'es have good and fast memory allocating functions, this variable does not hit performance. Anyhow, if you need to see how much of additional memory pool is being used, it's available via SHOW ENGINE INNODB STATUS. 1: SHOW ENGINE INNODB STATUS \G 2: . 3: . 4: ---------------------- 5: BUFFER POOL AND MEMORY 6: ---------------------- 7: Total memory allocated XXXXXXXXXXXXX; in additional pool allocated XXXXXXX 8: Dictionary memory allocated XXXXX 9: . 10: . additional pool allocated will show amount of memory allocated in additional memory pool, the usage. And out of usage, Dictionary memory allocated will show amount of memory being used for data dictionary. Best value: There is hardly any impact of a smaller value for this variable. Still it is better to have a value which around 20% more than what SHOW ENGINE INNODB STATUS shows for additional pool allocated. It's proportional to (number of tables + number of concurrent connections). Read more: MySQL manual entry on innodb_additional_mem_pool_size What to tune in MySQL ... Heikki Tuuri on "Calculating innodb_additional_mem_pool_size?" Hope you enjoyed reading this post.[...]

Variable's Day Out #7: innodb_autoinc_lock_mode


Properties: Applicable To InnoDB Introduced In 5.1.22 Server Startup Option --innodb-autoinc-lock-mode= Scope Global Dynamic No Possible Values enum(0,1,2) Interpretation: Value Meaning 0 Traditional 1 Consecutive 2 Interleaved Default Value 1 (consecutive) Categories Scalability, Performance Description: This variable was introduced in 5.1.22 as a result of the [Bug 16979] and comes very handy when stuck with auto_increment scalability issue, also mentioned in my previous post. So what do traditional, consecutive and interleaved mean? Traditional is "traditional", this takes back InnoDB to pre-innodb_autoinc_lock_mode and a table level AUTO-INC lock is obtained and held until the statement is done. This ensures consecutive auto-increment values by a single statement. Remember, this lock is scoped for a statement and not transaction and hence is not equivalent to serializing transactions as someone raised a question to me recently. Consecutive, the default lock mode, works in context switching method. For inserts where the number of rows is not known (bulk inserts), ie INSERT ... SELECT, REPLACE ... SELECT, LOAD DATA, it takes a table level AUTO-INC lock. Otherwise for inserts where the number of rows is known in advance (simple inserts), it uses a light weight mutex during the allocation of auto-increment values. The mutex is of course checked for only if no other transaction holds the AUTO-INC lock. However for inserts where user provides auto-increment values for some rows (mixed mode inserts), InnoDB tends to allocate more values and lose them. Interleaved mode just ensures uniqueness for each generated auto-incremented value. This mode never takes an AUTO-INC lock and multiple statements can keep generating values simultaneously. How to use? My overall recommendation is not to change this variable and keep it to default. And if you are having mixed mode insert statements that contradict the usage, better look into them. Otherwise, following are the constraints on usage. Use interleaved only when your tables don't have auto-increment columns. Also if you don't know/don't care if they have, then you have more issues to resolve. :) "mixed mode inserts" can lead to losing values with consecutive mode. It's not safe to use statement based or mixed replication with interleaved mode. Traditional mode has scalability issues, but is safe when used with mixed mode inserts. Read more: MySQL manual entry on innodb_autoinc_lock_mode Configurable InnoDB Auto-Increment Locking [Bug 16979] Hope you enjoyed reading this. [...]

Variable's Day Out #6: Innodb_buffer_pool_reads


Properties: Applicable To InnoDB Type Status Variable Scope GLOBAL | SESSION Dynamic NA Possible Values Integer Default Value NA Category Performance Description: According to the official documentation, this variable defines "The number of logical reads that InnoDB could not satisfy from the buffer pool and had to do a single-page read.". But we can go a little further to see the what happens in and around this variable. As per the MySQL Internals documentation, a buffer is defined as a memory area that contains copies of pages that ordinarily are in the main data file (read innodb tablespace). And "buffer pool" is simply a set of all the buffers. Now, when InnoDB needs to access a new page it looks first in the buffer pool, in case page isn't there InnoDB reads it from the disk to a new buffer. InnoDB chucks old buffers (basing its decision on LRU algorithm) when it has to make space for a new buffer. This is the time when this variable gets incremented. Best value: Without doubt, 0 (zero). But that is only possible if you are not using InnoDB. :) So, while using InnoDB, you have to see that this variable remains as small as possible when compared to Innodb_buffer_pool_read_requests. In case you are seeing a high value for this, you might like to increase your innodb_buffer_pool_size and/or look at the size of your data/indexes at large. What else: I always used to wonder the difference between Innodb_buffer_pool_reads and Innodb_pages_read, but never too seriously until I read Jay Janssen's query about it. And then thought of figuring out myself. As I stated there also "Innodb_pages_read are the pages read from buffer pool and Innodb_buffer_pool_reads are the pages read for the buffer pool", so no confusion. Read more: MySQL manual entry on Innodb_buffer_pool_reads MySQL Internals documentation of InnoDB source files - read [\buf (BUFFERING)] section. Jay Janssen's post asking differnence and many answers Jay Janssen's own answer on the same.   Hope you enjoyed reading this. [...]

Variable's Day Out #5: innodb_thread_concurrency


Properties: Applicable To InnoDB Server Startup Option --innodb_thread_concurrency= Scope Global Dynamic Yes Possible Values Integer: Range: 0 - 1000 Interpretation: MySQL Version Value 4.x 0 - sets it to 1 >500 - Infinite Concurrency < 5.0.19 0 - sets it to 1 >= 20 - Infinite Concurrency >= 5.0.19 0 - Infinite Concurrency Default Value MySQL Version Default Value < 5.0.8 8 (Finite) 5.0.8 - 5.0.18 20 (Infinite) 5.0.19 - 5.0.20 0 (Infinite) >= 5.0.21 8 (Finite) Category Performance Description: innodb_thread_concurrency is the variable that limits the number of operating system threads that can run concurrently inside the InnoDB engine. Rest of the threads have to wait in a FIFO queue for execution. Also, threads waiting for locks are not counted in the number of concurrently executing threads. In the beginning of a thread execution, InnoDB checks the count of already running threads. If it is greater than or equal to the limit set by this variable, then the thread waits for some time and gives a new try. In case the second try also fails, the thread enters a FIFO queue. The sleep time was 50 ms and later on changed to 10 ms, and is managed by innodb_thread_sleep_delay from 5.0.3 onwards. On entering the InnoDB, the thread is given number of "free tickets", number as defined by innodb_concurrency_tickets. Optimal Setting: Depends on your environment. The value largely depends on the version of MySQL, number of CPUs, number of Disks, and the kind of load that you are running. As a general principle, you may like to set it to twice the number of CPUs and Disks. And may consider only Disks for Disk IO intensive load and only CPUs for CPU intensive load. Also, if you have greater than or equal to 4 CPUs, you may consider lowering this variable, courtesy [Bug 15815], for un-patched MySQL versions (read the bug for the discussion). For machines with 1 or 2 CPUs, go for the kill, set it to infinite. Hyperthreading is worse than anything else, it is in many ways different than multi-CPU problems. Because hyperthreading tries to make a single processor work like two while making the instances share a lot of pipelines and cache. You might consider turning hyperthreading off when running MySQL. In my experience, I have seen a big enough innodb_buffer_pool_size to complement this variable on a multi-CPU machine, of course with the bug resolved. I will be publishing some numbers on it soon. Read more: MySQL manual entry on innodb_thread_concurrency InnoDB thread concurrency at MySQL InnoDB performance tuning on Solaris 10 OS [Bug 15815]   Hope this post was helpful to you. [...]

Variable's Day Out #4: innodb_buffer_pool_size



Applicable To InnoDB
Server Startup Option --innodb_buffer_pool_size=
Scope Global
Dynamic No
Possible Values Integer
Range: 1048576 (1MB) - 4294967295 (4GB)
(more for 64 bit machines)
Default Value 8388608 (8MB)
Category Performance


One of the Peter's "what to tune?" parameters, this innodb buffer pool is a cache for both indexes and data and should be considered as the most important option for InnoDB performance. In case your server is hosting InnoDB heavy database, you can allocate up to 70-80% of the physical memory to this buffer. Anything more than this might cause paging in the operating system. Obviously if your server is not InnoDB heavy and you are using a mix of certain engines, dividing memory can be lot more complicated. This may also be taken as an argument against mixing engine types. :)

Optimal Setting:

As always, there is no magic number and the optimal value for you depends on your requirements. In case your data size is too much, you can always chose to provide as much as possible to this buffer pool. While doing so always remember that InnoDB will eat up some space for data structures related to your buffer pool also. On the other hand, if your projected database size (data+indexes) is small enough to fit in the memory, allocate around 10-15% more than your data-size.

Read more:

Hope this post was helpful to you. Keep posting your comments.

Variable's Day Out #3: max_connect_errors


 Properties: Applicable To MySQL Server Server Startup Option --max_connect_errors= Scope Global Dynamic Yes Possible Values Integer Range: 1 - 4294967295 Default Value 10 Category Security Description: This variable determines how many interrupted connections can occur from a host. If the number of interrupted connections from this host surpasses this number, that host is blocked from further connections. All of the following, as listed here, will cause the counter to be incremented. Client program did not call mysql_close() before exiting. Client had been sleeping more than wait_timeout or interactive_timeout without issuing any requests to the server. Client program ended abruptly in the middle of a data transfer. Client doesn't have privileges to connect to a database. Client uses incorrect password. It takes more than connect_timeout seconds to get a connect packet. You can always unblock the hosts with a FLUSH HOSTS statement. Prior to 5.1, any error would just increment the counter for a host. In 5.1, if a host is not yet blocked, any successful handshake resets the counter to 0. Usage: It depends on the system admin, how they want to use this variable. For example, If the MySQL server is at a physically distant location from it's clients, one might not want unnecessary network issues to get a host blocked. Otherwise, if the expected client machines are somewhat known, one might not want to invite hackers. I generally consider a higher value for this variable to be a security threat, as some unfriendly people may use it for a DOS attack. It's better to keep the value low and system admin aware of the hosts being blocked. Read More: MySQL manual entry on max_connect_errors Communication Errors and Aborted Connections Nice discussion on resetting counter Possible feature in future Host 'host_name' is blocked error explained   Hope this post was helpful to you. Keep posting your comments. [...]

Variable's Day Out #2: key_buffer_size



Engine(s) MyISAM
Server Startup Option --key_buffer_size=
Scope Global
Dynamic Yes
Possible Values Integer
Range: 8 - 4294967295 (4 GB)
Default Value 131072 (128 KB)
Category Performance


This is a global buffer where MySQL caches frequently used blocks of index data for MyISAM data. Maximum allowed size is 4GB on a 32 bit platform. Greater values are permitted for 64-bit platforms beyond MySQL 5.0.52.

Keeping this buffer to an optimal value (neither too low nor too high) contributes heavily to the performance of your MySQL server. As given in the MySQL Documentation, "using a value that is 25% of total memory on a machine that mainly runs MySQL is quite common". As suggested by Peter, "Set up to 30-40% of available memory if you use MyISAM tables exclusively. Right size depends on amount of indexes, data size and workload".

Starting with 25%, one can keep monitoring key cache hit ratio and increase it over time. A key cache hit ratio of more than 95% should be good enough. Also, for preventing oneself from allocating too much memory to this buffer, monitoring key buffer usage should be helpful. I feel one should target towards more than 80% of buffer usage.

If you are not using very few MyISAM tables, still key_buffer can be useful for MySQL's tables and temporary tables as well.


  • key_cache_hit_ratio = (1 - Key_reads/Key_read_requests) * 100
  • key_buffer_usage    = (1 - (Key_blocks_unused * key_cache_block_size)/key_buffer_size) * 100

Read More:

MySQL manual entry on key_buffer_size

What to tune in MySQL server after installation

MyISAM Key Cache - MySQL Manual


Hope this post was helpful to you.