Optim MySQL

Changing Gears

2008-09-07T09:12:00.000+01:00

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

2008-08-28T09:24:00.000+01:00

Properties:

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

Description:

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:

Stop the MySQL server
Backup your data and log files
Delete log files
Set the new value for innodb_log_file_size in my.cnf
Start mysql server

Read More:

Translation is Fun!!

2008-08-12T10:03:00.000+01:00

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)

2008-07-30T10:38:00.002+01:00

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

2008-07-15T11:45:00.005+01:00

Properties:

Applicable To	MyISAM
Server Startup Option	`--bulk_insert_buffer_size=<value>`
Scope	Both
Dynamic	Yes
Possible Values	Integer: Range: 0 - 4294967295 (4G)
Default Value	8388608 (8M)
Category	Performance

Description:

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 ROWS INTO TEMPORARY TABLE
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

2008-07-08T16:18:00.004+01:00

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"

2008-07-04T11:43:00.001+01:00

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

2008-06-19T08:53:00.002+01:00

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 to know what all situations need this flag to be set. So far, it is set only when using temporary tables and with a possibility/extensibility of using with other situations in future. Other situations like transactions take care of themselves by the way they are logged.

I'm running few tests to see if it handles all the conditions related to temporary tables created inside transactions. Will publish soon if something pops up.

Go! Take the survey

2008-06-03T11:29:00.001+01:00

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

2008-05-30T10:59:00.001+01:00

Properties:

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

Description:

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.

Usage:

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

2008-05-20T20:25:00.001+01:00

Properties:

Applicable To	MySQL Server
Introduced In	5.1.5
Server Startup Option	--binlog-format=<value>
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:

Hope you enjoyed reading this.

Variable's Day Out #12: innodb_flush_method

2008-05-19T13:07:00.001+01:00

Properties:

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

Description:

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.

Recommendation:

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:

MySQL manual entry on innodb_flush_method
Heikki Tuuri recommends O_DIRECT: Check Q35 @ mysqlperformanceblog.com
man 2 fsync
man 2 open

Hope you enjoyed reading this.

Variable's Day Out #11: large_pages

2008-05-18T16:50:00.001+01:00

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 = <value>
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:

Hope you enjoyed reading this.

Memcached, but do you need it?

2008-05-15T13:12:00.001+01:00

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 :).

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.
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].
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

2008-05-14T10:08:00.001+01:00

“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

2008-05-13T07:09:00.001+01:00

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

Day Outs:

Day Out #1: delay_key_write
Day Out #2: key_buffer_size
Day Out #3: max_connect_errors
Day Out #4: innodb_buffer_pool_size
Day Out #5: innodb_thread_concurrency
Day Out #6: Innodb_buffer_pool_reads
Day Out #7: innodb_autoinc_lock_mode
Day Out #8: innodb_additional_mem_pool_size
Day Out #9: long_query_time
Day Out #10: innodb_file_per_table

(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

2008-05-12T18:48:00.001+01:00

Properties:

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

Description:

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

2008-04-28T13:01:00.001+01:00

Properties:

Applicable To	MySQL Server
Server Startup Option	--long-query-time=<value>
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

Description:

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

2008-04-16T13:30:00.002+01:00

Properties:

Applicable To	InnoDB
Server Startup Option	--innodb-additional-mem-pool-size=<value>
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:

Hope you enjoyed reading this post.

Variable's Day Out #7: innodb_autoinc_lock_mode

2008-04-15T15:48:00.000+01:00

Properties:

Applicable To

InnoDB

Introduced In

5.1.22

Server Startup Option

--innodb-autoinc-lock-mode=<value>

Scope

Global

Dynamic

Possible Values

enum(0,1,2)
Interpretation:

Value	Meaning
0	Traditional
1	Consecutive
2	Interleaved

Default Value

1 (consecutive)

Variable's Day Out #6: Innodb_buffer_pool_reads

2008-04-11T12:55:00.001+01:00

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

2008-04-08T14:24:00.000+01:00

Properties:

Applicable To

InnoDB

Server Startup Option

--innodb_thread_concurrency=<value>

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)

Variable's Day Out #4: innodb_buffer_pool_size

2008-04-03T12:55:00.001+01:00

Properties:

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

Description:

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

2008-03-29T14:49:00.000Z

Properties:

Applicable To	MySQL Server
Server Startup Option	--max_connect_errors=<value>
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:

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

Variable's Day Out #2: key_buffer_size

2008-03-28T12:57:00.001Z

Properties:

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

Description:

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.

Calculations:

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.

Variable's Day Out #1: delay_key_write

2008-03-27T11:02:00.000Z

After seeing some very good "of-the-day" series, I thought why should MySQL variables be left behind. So, my contribution in the race - "variable's day out". I won't brand it "of-the-day" series as it calls for too much of dedication and given my track record I shouldn't promise that. Henceforth, instead of calling a variable lucky enough for a day, lets call a day lucky enough for a variable. Though I will try my best to keep the lucky days rolling.

Today's variable is delay_key_write. Properties:

Engines	MyISAM
Server Startup Option	--delay-key-writes[=<OFF\|ON\|ALL>]
Scope	Global
Dynamic	Yes
Possible Values	enum('ON', 'OFF', 'ALL')
Default Value	ON
Category	Performance

Description:

For MyISAM tables, delayed key writes doesn't allow key buffers to be flushed between writes.

Values:

OFF	Disables delayed key writing.
ON	Enables for the tables created with DELAYED_KEY_WRITE option.
ALL	Enables delayed key writing for all MyISAM tables.

Pros:

Enabling delayed key writing is good for heavy load tables (read or write heavy) as it decreases disk access.

Cons:

If the server goes down, the indexes have to be rebuilt.

Read more:

MySQL manual entry on delay_key_write

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

MyEye update

2008-03-27T10:48:00.000Z

Since ages, I was busy with many projects including my marriage. Nice to be back on the blog with an update on MyEye - the project I announced in my very first post. MyEye is an (or is going to be an) open source monitoring tool for MySQL installations with handy knowledge-base and advisories for MySQL DBAs. So, I'm done with the first round of analysis and design for MyEye. As far as it is planned, it's going to be a framework that would provide interface for people to write their own monitoring rules. Meanwhile MyEye web site is also under construction and I will be publishing the documentation soon there. Will be looking forward to your contributions for creating an open knowledge base for MyEye.

GET_LOCK & FAMILY

2007-11-22T20:40:00.001Z

Again I have a well neglected and documented feature of MySQL. As we all often need and use locks in MySQL, we tend to forget (or not bother about) MySQL internals and how they cause trouble. For example, try something like LOCK TABLE ... WRITE on an InnoDB table in a transaction and see the same transaction getting timed out while waiting for a lock on one of the rows, Ref: [Bug 5998]. All these problems occur when we have difference in semantics of statement at MySQL and Engine levels. But recently, we figured out this good technique of keeping the logic with ourselves and not relying on MySQL too much. Though even this technique is not fool proof in all the cases.

The secret is: use GET_LOCK function of MySQL. GET_LOCK(str, timeout) function tries to get an exclusive lock with a name (str) using timeout seconds. The return values are tri-state, 1 if the lock was obtained successfully, 0 if there was a timeout and NULL in case of an error. For applications just bothered about getting the lock, following can be used.

   1: SELECT COALESCE(GET_LOCK('lock_string_name', 2), 0)

This is guaranteed to return either 1 or 0 meaning lock taken or otherwise respectively. Locks taken using GET_LOCK function are released in following conditions

Lock is released explicitly using RELEASE LOCK function.
Issue a new GET_LOCK request.
Connection terminates.

Other functions of this family are IS_FREE_LOCK and IS_USED_LOCK which are used to check the lock string status. IS_FREE_LOCK checks if the string is free i.e., returns 1 if the string is free, 0 if it is in use and NULL in case of an error. IS_USED_LOCK checks if the lock is used and returns the connection identifier of the clients that holds the lock otherwise 0. Again NULL is used for error cases.

USEFULLNESS:

This is one way of maintaining the mutual exclusivity of a process in a distributed environment while ensuring BCP also. Just have a named lock which will can be obtained only by one process at a time and rest of the processes just timing out and retrying for the lock after a sound sleep.
The lock is very light weight and doesn't eat up too many resources at the MySQL server end.
Lock is at MySQL level and engines are not involved at all, thus saving you to read bundles of documentation before using something.

CONS:

It requires due diligence in the application to ensure that all the conflicting transactions are taken care of. Even one single left out transaction may spoil the whole game. So, I would suggest such techniques are not good for heavily loaded (read lines of code) applications or otherwise you have a good QA team.
It actually cannot equate other locks as you can have obtain only one lock for a connection.

MISC:

These locks are at connection level and do not respect transactions. Meaning, committing transactions do not have any effect on these locks. I think the fairness of this feature depends on the use case, hence this was put under MISC category :)

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

Customized Order By Sequence - A Small Hack

2007-09-12T18:56:00.001+01:00

What if you have to customize the sequence of order by clause on a column. Say, you have a integer column 'weight' that can have values ranging from 1 to 5 and you want to get the rows in the sequence 4,2,5,1,3. So what do you do? Here is a simple trick using the field function.

   1: mysql> SELECT * FROM table ORDER BY FIELD(weight, 4, 2, 5, 1, 3);

Now what does it do. Field function returns the index of the first argument in the rest of the list. So, effectively the order by logic will be the same, just that the values that are being ordered are generated on the fly.

Other approach can be to use CASE.. WHEN .. END statement as mentioned in the MySQL Documentation. The above code can be rewritten as:

   1: mysql> SELECT *, CASE

   2:     -> WHEN weight = 1 THEN 4

   3:     -> WHEN weight = 2 THEN 2

   4:     -> WHEN weight = 3 THEN 5

   5:     -> WHEN weight = 4 THEN 1

   6:     -> WHEN weight = 5 THEN 3

   7:     -> END AS weight_custom

   8:     -> FROM table

   9:     -> ORDER BY weight_custom;

But I'm a little biased towards the first approach due to its simplicity. Though it's a matter of personal choice.

Such requirements are rare, just a food for thought.

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

InnoDB Deadlock - Next Key Locking

2007-09-12T14:25:00.001+01:00

So you use InnoDB, have indexes on your table, think of row level locking and concurrent queries, feel good and go to sleep. All this while forgetting that even UPDATE and SELECT .... FOR UPDATE statements will (or may) also use the same index for scanning or updating. Then what? You may ask.

Well, InnoDB row level locking works in a somewhat different manner when using indexes. In this case, InnoDB locks index records in shared or exclusive mode when searching or scanning an index. Therefore, as mentioned in MySQL Documentation, the row level locks are actually index record locks.

To complicate matters (or resolve issues) further, the lock is a gap lock. A gap lock refers to a lock that only locks a gap before some index record.

As per the example in MySQL Documentation, lets say we have a query like this.

   1: SELECT * FROM child WHERE id > 100 FOR UPDATE;

And there is an index on id column. The query will lock all the index records where id is greater than 100, like 103, 107, etc. Meanwhile, the query also locks out inserts made in the gaps, say for 101,102, 104, 105, 106, 108 and above. If this is not done, there may be inserts while the transaction is under way.

Now let us consider a little tricky problem. Suppose we have a table table_name with index on colx. We will have two queries running in parallel. For sure, both the queries are working on only one row each and as per the application, they are always different queries.

The queries we have are:

   1: mysql> SELECT * FROM table_name WHERE

   2:     -> colx IS NULL

   3:     -> LIMIT 1

   4:     -> FOR UPDATE;

and

   1: mysql> UPDATE table_name SET

   2:     -> colx = NULL, coly = NULL

   3:     -> WHERE colz = 'something';

The first query starts locking the index records. But locking is not done in a single shot, it is done record by record. Meanwhile, the second query also gets lock on the record that signifies the current value of colx and proceeds towards the NULL value record. Now by the time second query gets to the NULL value record, that gets locked by the first query and first query somehow waits for the lock already grabbed by second one. Huh!

You need to be very (un)lucky to achieve this.

Workarounds

Figure out all such indexes and queries where a deadlock can happen. In most cases, theoretical investigation may lead to unnecessary paranoia, so its better to have a regression test on your system and monitor for deadlocks. If you can, part away from those indexes.
If you think you cannot live without your indexes, and the queries involved in deadlocks are mutually exclusive, you can go ahead with enabling innodb_locks_unsafe_for_binlog without citing this blog as a reference. It's all your choice.
The tests I have conducted have been done on MySQL 4.1.x. There is a claim that from MySQL 5.0.2, UPDATE and DELETE only locks rows that are going to be affected. So this should reduce the probability of deadlocks. I haven't tested it though.

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

InnoDB Repeating AUTO_INCREMENT Values

2007-08-08T17:13:00.001+01:00

This is a continuation of my earlier post with the same title. Recently while solving a problem where values of an auto_increment fields were getting repeated, a well documented and often neglected feature of InnoDB came forward. Lets first have a glimpse of the system and the problem.

We are maintaining a job queue in an InnoDB table for a distributed resource environment. This job queue is populated by distributed schedulers from the system, the job is assigned to a resource and once the job is done, it is removed from the job queue. The system runs on an assumption that the job id, which is an auto_increment field, always remain unique throughout the life of the system. I acknowledge that the usage is debatable, but lets keep it separate from this.

The problem: The operations team restarted the MySQL server in between. After that restart, some job id's got repeated. This broke the basic assumption of unique job id's and affected the whole business at large.

What caused this: After some hours of debugging and hypothesizing we figured out that the way InnoDB handles auto_increment values at server start is completely different from how MyISAM does and somewhat against the basic assumption of auto_increment values. InnoDB doesn't persists the auto_increment value between two MySQL instances. Rather when the server starts, for the first insert into the table, InnoDB executes the equivalent of this statement.

SELECT MAX(ai_col) FROM T FOR UPDATE;

As I said, this is a well documented feature, but often neglected. In our case, it happened that before the server shutdown, few of the values from the upper bracket were deleted, as normally they would have been.

Workarounds:

If you have such a system, where you rely too much on the auto_increment values from InnoDB, delete values frequently and assume them to be unique throughout the system, beware!

Try and give up the auto_increment column, if you can afford to, and maintain the logic of incrementing values in your code. There might be other concerns, but then its all your own choice.
Have another table which has only one auto_increment column, reference this from your original table and make sure you don't delete values from the upper bracket. You can safely expire the older entries in order to keep this table small enough.

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

del.icio.us Tags: MySQL, InnoDB, AUTO_INCREMENT

OLAP Paradise - WITH ROLLUP

2007-07-22T16:09:00.001+01:00

Though this is an age old concept, but after seeing the ignorance factor of many regarding this, I thought of writing a bit about it. ROLLUP is a GROUP BY modifier that adds extra rows to the output which contain summary data. At each level of aggregation the aggregated column is filled with a NULL value.

Let's see a small example. I know the values are too small for the data presented, but just to keep it readable.

   1: mysql> CREATE TABLE `rollup_1` (

   2:   `url` varchar(255) DEFAULT NULL,

   3:   `year` int(11) DEFAULT NULL,

   4:   `country` char(2) DEFAULT NULL,

   5:   `visit` int(11) DEFAULT NULL

   6: ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

   7: Query OK, 0 rows affected (0.01 sec)

8:

   9: mysql> SELECT * FROM rollup_1;

  10: +----------------------+------+---------+-------+

  11: | url                  | year | country | visit |

  12: +----------------------+------+---------+-------+

  13: | http://www.yahoo.com | 2005 | US      | 12000 |

  14: | http://www.yahoo.com | 2005 | IN      | 10000 |

  15: | http://www.yahoo.com | 2006 | US      | 16000 |

  16: | http://www.yahoo.com | 2006 | IN      | 10700 |

  17: | http://www.yahoo.com | 2007 | US      | 21000 |

  18: | http://www.yahoo.com | 2007 | IN      | 13200 |

  19: +----------------------+------+---------+-------+

  20: 6 rows in set (0.00 sec)

21:

  22: mysql> SELECT IFNULL(url, 'TOTAL') AS url, SUM(visit) AS visit FROM rollup_1 GROUP BY url WITH ROLLUP;

  23: +----------------------+-------+

  24: | url                  | visit |

  25: +----------------------+-------+

  26: | http://www.yahoo.com | 82900 |

  27: | TOTAL                | 82900 |

  28: +----------------------+-------+

  29: 2 rows in set, 1 warning (0.00 sec)

30:

Complete analysis:

   1: mysql> SELECT IFNULL(url, 'ALL_URLS') AS url,

   2:     -> IFNULL(year, 'ALL_YEARS') AS year,

   3:     -> IFNULL(country, 'ALL_COUNTRIES') AS country,

   4:     -> SUM(visit) FROM rollup_1

   5:     -> GROUP BY url, year, country WITH ROLLUP;

   6: +----------------------+-----------+---------------+------------+

   7: | url                  | year      | country       | SUM(visit) |

   8: +----------------------+-----------+---------------+------------+

   9: | http://www.yahoo.com | 2005      | IN            |      10000 |

  10: | http://www.yahoo.com | 2005      | US            |      12000 |

  11: | http://www.yahoo.com | 2005      | ALL_COUNTRIES |      22000 |

  12: | http://www.yahoo.com | 2006      | IN            |      10700 |

  13: | http://www.yahoo.com | 2006      | US            |      16000 |

  14: | http://www.yahoo.com | 2006      | ALL_COUNTRIES |      26700 |

  15: | http://www.yahoo.com | 2007      | IN            |      13200 |

  16: | http://www.yahoo.com | 2007      | US            |      21000 |

  17: | http://www.yahoo.com | 2007      | ALL_COUNTRIES |      34200 |

  18: | http://www.yahoo.com | ALL_YEARS | ALL_COUNTRIES |      82900 |

  19: | ALL_URLS             | ALL_YEARS | ALL_COUNTRIES |      82900 |

  20: +----------------------+-----------+---------------+------------+

  21: 11 rows in set, 3 warnings (0.00 sec)

There is a small Gotcha while using ROLLUP. As you can see, my table definition does not put a NOT NULL constraint on any of the fields. Though you might have guessed what I'm pointing to, let's see this example.

   1: mysql> INSERT INTO rollup_1 values ('http://www.yahoo.com', 2007, null, 12000);

   2: Query OK, 1 row affected (0.00 sec)

3:

   4: mysql> SELECT IFNULL(url, 'ALL_URLS') AS url,

   5:     -> IFNULL(year, 'ALL_YEARS') AS year,

   6:     -> IFNULL(country, 'ALL_COUNTRIES') AS country,

   7:     -> SUM(visit) FROM rollup_1

   8:     -> GROUP BY url, year, country WITH ROLLUP;

   9: +----------------------+-----------+---------------+------------+

  10: | url                  | year      | country       | SUM(visit) |

  11: +----------------------+-----------+---------------+------------+

  12: | http://www.yahoo.com | 2005      | IN            |      10000 |

  13: | http://www.yahoo.com | 2005      | US            |      12000 |

  14: | http://www.yahoo.com | 2005      | ALL_COUNTRIES |      22000 |

  15: | http://www.yahoo.com | 2006      | IN            |      10700 |

  16: | http://www.yahoo.com | 2006      | US            |      16000 |

  17: | http://www.yahoo.com | 2006      | ALL_COUNTRIES |      26700 |

  18: | http://www.yahoo.com | 2007      | ALL_COUNTRIES |      12000 |

  19: | http://www.yahoo.com | 2007      | IN            |      13200 |

  20: | http://www.yahoo.com | 2007      | US            |      21000 |

  21: | http://www.yahoo.com | 2007      | ALL_COUNTRIES |      46200 |

  22: | http://www.yahoo.com | ALL_YEARS | ALL_COUNTRIES |      94900 |

  23: | ALL_URLS             | ALL_YEARS | ALL_COUNTRIES |      94900 |

  24: +----------------------+-----------+---------------+------------+

  25: 12 rows in set, 3 warnings (0.00 sec)

As you can see in the output, there are two rows claiming to provide data for url='http://www.yahoo.com' and year=2007, question is which one is which one? Few good answers are:

One that appears late in the result set.
One that has a higher value for SUM(visit) field. This is arguable in general as there might be negative values, for our example we can say that's impossible.

The best answer is of course this. For using ROLLUP with data like this, please make sure that you have fields marked as NOT NULL and instead specify another DEFAULT like 'not set' or something else.

While using ROLLUP, you may further like to consider few points.

ROLLUP and ORDER BY are mutually exclusive, they cannot be used in the same statement.
If you are using LIMIT clause along with ROLLUP, remember LIMIT clause is applied after ROLLUP, so it may include the aggregated rows.
In MySQL documentation, it is mentioned that "Because the NULL values in the super-aggregate rows are placed into the result set at such a late stage in query processing, you cannot test them as NULL values within the query itself.". You can see in the above queries, we are actually testing this value to be replaced by TOTAL_* string. Also if your data itself does not have NULL values in it, it works wonders. Look at the output below, I have removed the row inserted above.

   1: mysql> SELECT IFNULL(url, 'ALL_URLS') AS url,

   2:     -> IFNULL(year, 'ALL_YEARS') AS year,

   3:     -> IFNULL(country, 'ALL_COUNTRIES') AS country,

   4:     -> SUM(visit) FROM rollup_1

   5:     -> GROUP BY url, year, country WITH ROLLUP HAVING country is null;

   6: +----------------------+-----------+---------------+------------+

   7: | url                  | year      | country       | SUM(visit) |

   8: +----------------------+-----------+---------------+------------+

   9: | http://www.yahoo.com | 2005      | ALL_COUNTRIES |      22000 |

  10: | http://www.yahoo.com | 2006      | ALL_COUNTRIES |      26700 |

  11: | http://www.yahoo.com | 2007      | ALL_COUNTRIES |      34200 |

  12: | http://www.yahoo.com | ALL_YEARS | ALL_COUNTRIES |      82900 |

  13: | ALL_URLS             | ALL_YEARS | ALL_COUNTRIES |      82900 |

  14: +----------------------+-----------+---------------+------------+

  15: 5 rows in set, 4 warnings (0.00 sec)

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

InnoDB Row Counting using Indexes

2007-07-19T18:13:00.001+01:00

This is always mentioned that InnoDB is slower in giving results for COUNT(*) as compared to MyISAM. But as Peter points out in his blog that this fact only applies to COUNT(*) queries without WHERE clause. This text is from Peter's blog only - "If you have query like SELECT COUNT(*) FROM IMAGE WHERE USER_ID=5 this query will be executed same way both for MyISAM and Innodb tables by performing index rage scan. This can be faster or slower both for MyISAM and Innodb depending on various conditions." Let's see what EXPLAIN has in store for us.

   1: mysql> CREATE TABLE `test_index` (

   2:   `id` int(11) NOT NULL AUTO_INCREMENT,

   3:   `x` int(11) DEFAULT NULL,

   4:   `y` int(11) DEFAULT NULL,

   5:   `z` varchar(255) NOT NULL DEFAULT 'testing',

   6:   PRIMARY KEY (`id`),

   7:   KEY `x` (`x`,`y`)

   8: ) ENGINE=InnoDB;

9:

  10: mysql> EXPLAIN SELECT COUNT(*) FROM test_index \G

  11: *************************** 1. row ***************************

  12:            id: 1

  13:   select_type: SIMPLE

  14:         table: test_index

  15:          type: index

  16: possible_keys: NULL

  17:           key: PRIMARY

  18:       key_len: 4

  19:           ref: NULL

  20:          rows: 4875772

  21:         Extra: Using index

  22: 1 row in set (0.01 sec)

23:

  24: mysql> SELECT COUNT(*) FROM test_index;

  25: +----------+

  26: | COUNT(*) |

  27: +----------+

  28: |  4915200 |

  29: +----------+

  30: 1 row in set (2.61 sec)

The explain states that the counting is going to be done on PRIMARY index and Using Index. The best part is that since it is going to use PRIMARY index and since it is NOT NULL, MySQL will actually count the values from the index itself. So, contrary to the thought that something like COUNT(1) will work faster is not true in this case. Here is an interesting case from a bug.

   1: mysql> CREATE TABLE `test` (

   2:   `id` int(11) NOT NULL,

   3:   `int_k` int(11) NOT NULL,

   4:   `data1` varchar(255) NOT NULL,

   5:   `data2` varchar(255) NOT NULL,

   6:   PRIMARY KEY (`id`),

   7:   KEY `int_k` (`int_k`)

   8: ) ENGINE=InnoDB DEFAULT CHARSET=latin1

   9: 1 row in set (0.00 sec)

10:

  11: mysql> CREATE PROCEDURE populate()

  12: begin

  13:   declare i int;

  14:   set i = 0;

  15:   start transaction;

  16:   while i < 300000 do

  17:     insert into test (id, int_k, data1, data2)

  18:       values (i, i, repeat("-", 250), repeat("-", 250));

  19:     set i = i + 1;

  20:     if i % 1000 = 0 then

  21:       start transaction;

  22:     end if;

  23:   end while;

  24:   commit;

  25: end

  26: 1 row in set (0.00 sec)

27:

  28: mysql> call populate;

  29: Query OK, 0 rows affected (1 min 0.65 sec)

30:

  31: mysql> SELECT COUNT(*) FROM test;

  32: +----------+

  33: | COUNT(*) |

  34: +----------+

  35: |   300000 |

  36: +----------+

  37: 1 row in set (1.08 sec)

38:

  39: mysql> SELECT COUNT(*) FROM test use index (int_k);

  40: +----------+

  41: | COUNT(*) |

  42: +----------+

  43: |   300000 |

  44: +----------+

  45: 1 row in set (0.12 sec)

Using a secondary index is faster. But why? Generally speaking, the PRIMARY index should be faster because it is usually in order and can be read with sequential I/O at around 15 times more speed than generally fragmented secondary index. Actually this is a special case, since the secondary index is inserted into the table in perfect order, which is very rare. Also, as Heikki points out in the bug

"Since the minimum record size of InnoDB is about 20 bytes, and the fill-factor of a secondary index is typically 70 %, we can calculate that if the row length is > 15 * 1.5 * 20 = 450 bytes, then scanning the secondary index would probably be a better option." Till this feature is implemented and we have a much better optimized count(*) for InnoDB, please use secondary index explicitly for counting rows if you satisfy any of above conditions. Hope this blog was helpful to you. Keep posting your comments.

del.icio.us Tags: InnoDB, MySQL, Performance, Indexes

Implicit Commits and Transactions

2007-07-16T20:13:00.001+01:00

We think that whatever statements are there in a transaction can be rolled-back. Right? No! If by any chance you have statements that cause an implicit commit, then no. These statements commit the transaction mid-air. And if you think that these statements are only DDL, beware. There are many statements in MySQL that implicitly commit like LOAD DATA INFILE was an implicit commit statement for all storage engines until 5.1.12. Even now, if you are using NDB, this is still a implicit commit statement.

Say, we have statements like this:

1. START TRANSACTION
2. SOME INSERT STATEMENT
3. ONE MORE INSERT STATEMENT
4. ONE IMPLICIT COMMIT STATEMENT
5. ONE MORE INSERT STATEMENT
6. SOME MORE STATEMENTS
7. COMMIT

The implicit commit statement (statement number 4) commits the statements above it and aborts the transaction, as already mentioned. So, what happens to the statements 5 and 6. They run as normal statements outside transaction, and beat the whole purpose of a transaction. As MySQL documentation points out that this behavior occurs because we cannot have nested transactions. The same link also provides the list of all the implicit commit statements.

There is an open bug (I saw lately) requesting a feature on this, as most of the users take transactions for granted.

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

del.icio.us Tags: InnoDB, Performance, Transaction

AUTO_INCREMENT in InnoDB

2007-07-14T19:09:00.001+01:00

It's very easy to have an auto_increment column in a table and maintain primary keys. Many people think so, and also they think that MySQL will do the best job for them. With InnoDB, the case is a little different as supposed to be. Basic assumption is that since InnoDB works with row-level locking, combining it with auto_increment should ward off worries. But it is just the beginning.

Let me clarify how auto_increment works. InnoDB maintains the auto_increment value in memory. Good approach. When an insert statement requests the auto_increment value, InnoDB takes the stored value, increment it by one and return. The value in memory is not incremented at this time. This is not bad, because who knows how many rows the insert statement has.

But, this means that InnoDB cannot provide auto_increment values to other threads until this finishes, hence we cannot have concurrent inserts even in InnoDB when using an auto_increment value. This is assured by a special AUTO-INC lock by InnoDB, which is an exclusive table level lock. We will see how this can affect your application and some workarounds.

Application may suffer

If you have concurrent inserts into an InnoDB table, start worrying. Single row inserts won't affect the performance noticeably. But, if you are doing bulk inserts and your inserts are taking enough time, one of your insert threads may exit because of lock wait timeout. Also, this will cause problems for other components of your application (even if they are read only) as these insert statements will keep connected and you may hit your max_connections.

Workarounds

As you might think, moving to MyISAM won't help. MyISAM by default takes a table lock while writing.
Do some magic and make sure your inserts do not execute concurrently. This is also not much possible in the case of web applications, where one doesn't have the control over the inserts, but neither web applications do bulk inserts. This can be managed where one is using a cron job to do bulk inserts, say from a log file.
Get rid of auto_increment. If your application actually needs to do bulk inserts from multiple sources at the same time and the above solution doesn't make sense, figure out your own primary key. Something like a combination of MD5 checksum over data combined with the length of the data may be a good option in most cases, but do not rely on it. Also, remember a multi-column primary key is not always a good option.

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

del.icio.us Tags: MySQL, InnoDB, AUTO_INCREMENT

InnoDB Tablespace Size

2007-07-12T17:38:00.001+01:00

Unlike MyISAM where data for individual tables is stored in their respective files, InnoDB stores data in a tablespace. By default, there is one single tablespace and data of all the databases is stored in one file. This file has data dictionary, tables, as well as indexes in it. There is a global parameter innodb_data_file_path that defines this tablespace file. It has a syntax like ibdata1:256M:autoextend, this means at the beginning a file of size 256 MB will be created and then whenever the data size exceeds this, the file will be auto-extended. The innodb_autoextend_increment variable defines in MB's that by how much each increment should be.

Let's see how well can we play around:

Inserts: Suppose you have too many inserts and InnoDB is extending the file too frequently. It makes sense to increase the value of innodb_autoextend_increment. Say we increase it to 16MB, then obviously the number of attempts to autoextend tablespace comes down by a factor of 2, hence performance. But beware before you take it too easy and increase the value too much. There is a big trap, we will come to it shortly.
Deletes: Here is the trap. You have a 10 GB tablespace (after too many autoextends), delete some 5 GB data (data + indexes) and think now the tablespace is 5 GB. Wrong, InnoDB doesn't have the notion of giving back space to the file system. Though, it will make sure to use the freed up space for further inserts. So, this method directly cannot be used to free disk space. So, in case you have data which you can get rid of, get rid of quickly before the next autoextend is done. One thing that can be done to reclaim space is to use OPTIMIZE TABLE frequently enough on tables that have high volume of inserts and deletes. But again remember, MySQL locks a table during the time OPTIMIZE TABLE is running. Another Gotcha, right? OPTIMIZE TABLE does several other things for which it makes sense to run it, though not that frequently. I will be posting a blog soon on it.
Separate Files per Table: InnoDB provides this option where data (data + indexes) for each table can be stored in a separate file through a global variable innodb_file_per_table. Though still a shared tablespace will be created for storing the likes of data dictionary et al. But still this approach makes sense as having data in small chunks (separate files) will improve the scope of managing them well and may increase performance in general.
Fixed Tablespace size: One way to work around with the tablespace file size problems is to fix the tablespace size (remove autoextend) to an extrapolated value. So, when you hit the limit, you know it is time to cleanup. This is not that viable with all the applications, as extrapolation is not always possible. And also it increases the complexity of the application, which then needs to take care of all such error conditions and not lose any data.

So, where does this end? You need to figure out what your data is, how critical it is, what all you want to do with it, what all you want your data to do. Then take some of the following steps.

Move to MyISAM: For all the tables (or even databases), for which you feel data is not that critical to have transactions et al, move them to MyISAM. So, for the problem we can't solve completely, we destroy the problem.
Separate Tablespace: Its a lot easier to maintain 10 small problems than a single big one.
Delete data/OPTIMIZE TABLE: Figure out how soon you can get rid of data. You actually don't need to delete data as it is. Transfer it to a MyISAM table, compress the file and archive it somewhere else and then delete it from the main table. Likewise there are many ways to do it. Run OPTIMIZE TABLE frequently enough so that it doesn't bother your reads and writes too much and also it doesn't take too much time to run.

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

Table Status

2007-07-09T18:21:00.001+01:00

Checking out the state of tables of your MySQL setup in production is important for fine tuning server parameters. MySQL's SHOW TABLE STATUS command comes very handy in determining table states from a running environment. Here, I explain the output of the command.

mysql> SHOW TABLE STATUS LIKE 'mytable' \G
****************** 1. row ******************
           Name: mytable
         Engine: InnoDB
        Version: 9
     Row_format: Dynamic
           Rows: 2465222
 Avg_row_length: 370
    Data_length: 913326080
Max_data_length: NULL
   Index_length: 146817024
      Data_free: 0
 Auto_increment: 18357110
    Create_time: 2007-05-11 22:27:56
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment: InnoDB free: 869376 kB
1 row in set (0.34 sec)

Leaving behind the obvious parameters, we shall see only the more relevant ones and ones that look like more interesting.

Row_format: This describes the length behavior of the rows in this table. Can have one value out of Dynamic, Fixed or Compressed. Dynamic rows have variable length due to fields having type like BLOB, VARCHAR etc. Fixed rows are made up of fields that do not vary in length. Compressed rows exist for compressed tables as those in Compressed MyISAM.

Rows: This describes the number of rows in a table. This number is always accurate for non-transactional engines like MyISAM, but an approximate value for transactional tables like InnoDB. Ever wondered why select count(*) is faster on MyISAM than InnoDB.

Avg_row_length: The name says it all. Anyhow, it's in bytes.

Data_length: This is the total data (in bytes) in the table. For InnoDB tables, which tend to use a single tablespace, this can be a deterministic factor for splitting tables in their individual tablespaces.

Max_data_lenth: This is generally not applicable for all the engines. This describes the maximum data that a table can hold. MyISAM tables generally have this restriction.

Index_length: This parameter should be of utmost interest. This describes the total space (in bytes) taken up by the index of the table. The aggregate value of this field over all the tables under the same engine (or the engines that use same buffer for index data) can actually be a deterministic value for such buffers. For InnoDB tables, this can also play a role in splitting tables in their individual tablespaces.

As far as this moment is concerned, this much of explanation is enough. Users should take care of not going by the instinct of a single snap shot for this data. Rather this data should be collected periodically to actually see the change and decisions be made accordingly.

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

del.icio.us Tags: MyISAM, InnoDB, MySQL, Table Status, Tablespaces, Tuning

Introduction

2007-07-08T16:36:00.000+01:00

Would appreciate your comments and love to work on MySQL related problems if you are facing any, that will also keep me motivated about posting stuff here. My current project is MyEye, which will be soon available. It is a tool that will keep a vigilant eye on your MySQL installation, provide valuable feedback and helps in monitoring too.