Optimizing MySQL and InnoDB on Solaris 10 for World's Largest Photo Blogging Community - Video

The video of one of my three sessions, "Optimizing MySQL and InnoDB on Solaris 10 for World's Largest Photo Blogging Community", presented at MySQL Conference & Expo 2008 has been uploaded by Sheeri. I am very thankful to her for doing all the hard work and making it available.

There are a few slides that were edited out of video because of reasons beyond my control. However, you should still be able to enjoy most of the video.

There is one point related to this video that I would like to make: Based on my particular experience I was leading to believe that Solaris 10 Kernel had the same issue as Linux Kernel related to swappiness and swapping where the kernel will start putting more importance on maintaining file system cache than the mysqld process. However, towards the end of the session, it was pointed out by a Sun engineer (thanks!) that there must be something else going on as UFS on Solaris 10 shouldn't depict this behavior and a process shouldn't swap in favor of maintaining file system cache. I am having this issue on 3 of my servers and I am currently working with Sun engineers to get to the bottom of the issue.

Can backup really kill performance?

Yes, if you are running backing on a large database that is also handling production traffic (not a very smart idea to begin with). This is especially important for backups created using snapshots based on copy-on-write algorithm.

Brian makes an important point in a comment to my post regarding backup. He points out "Backups are always onerous on IO" and that a better way to backup is to use slaves or a standby master (if using multi master replication).

If you *must* run backups on a production server, then ibbackup becomes very important as it doesn't affect performance as much as the evil snapshots created by snapshot tools like fssnap and LVM. I have found that in our case purchasing ibbackup licenses were worth every penny.

In our environment, running backups using copy-on-write snapshots was killing performance. Writes would start stalling several hours into the backup process. It didn't help that backups would take 27 hours to finish. I moved most systems to using ibbackup and for those systems running backups hasn't been an issue at all.

Of course, if you must backup production servers, take snapshots to backup everything except the databases. That way the snapshots will be held for a much smaller period and you can continue backing up databases using ibbackup.

What about mysqldump? I don't consider mysqldump an appropriate tool for periodic backups. I can see it working for small databases but running it on enterprise level databases for daily backups is just not going to be feasible.

I would love to discuss backups more at the conference. I also would like to evaluate some of the backup vendors exhibiting at the conference.

InnoDB Sessions at MySQL Conference

This year MySQL Conference features some of the best talks on InnoDB and I couldn't be more excited. We'll be hearing from Heikki Tuuri, Ken Jacobs, Mark Callaghan, Vadim Tkachenko, Peter Zaitsev and me :)

Kudos to conference organizers have really done a great job in balancing the sessions this year.

MySQL conference is a great venue to get up to date with what's happening with your favorite database/storage engine. Early registrations end soon so save yourself some money and register now.

If you have known me for sometime or if you are a regular blog reader, please send me a note and I will send you a coupon code to save even more when you register at the conference. You can email me at sofwareengineer99 at yahoo.

Without further ado, here are mouth watering InnoDB sessions scheduled for this year.

Mark Callaghan: Helping InnoDB Scale on Servers with Many CPU Cores and Disks

Ken Jacobs: InnoDB: Status, Architecture and New Features

Heikki Tuuri / Ken Jacobs: InnoDB: Fast, Reliable, Proven Transactional Storage for MySQL

Vadim Tkachenko and Peter Zaitsev: Investigating InnoDB Scaling Limits

Heikki Tuuri: InnoDB: Status, Architecture and New Features

Farhan "Frank" Mashraqi (Me): Optimizing MySQL and InnoDB on Solaris 10 for World's Largest Photo Blogging Community

Scaling the third most active Social Network with MySQL/InnoDB/Solaris

Sun Microsystems has published a case study, Delivering quality service to eleven million users with MySQL, InnoDB,
and the Solaris 10 Operating System, on Fotolog, the third most active social network according to ComScore and 14th most trafficked site according to Alexa.

Challenges:
• Scale to support eleven* million members and more than 100** million page views a day
• Increase performance without increase in database hardware (significant cost savings)

Solution
• MySQL database software
• InnoDB transactional storage engine for MySQL
• Solaris™ 10 Operating System
• Sun V440 and V210

Results
• Support for four times as many users with no additional hardware
• Higher percentage of working dataset in the memory with efficient schemas
• Four times the number of concurrent threads without adding servers
• Anticipated ability to double current number of threads

I will be presenting an updated and much more juicier version of how we achieved such scalability level from database point of view at the MySQL Conference 2008. The talk is titled, Optimizing MySQL and InnoDB on Solaris 10 for World's Largest Photo Blogging Community. So if you needed a sign to attend the conference, well you got one now :)

Notes:
* Now, Fotolog is reaching 15 million members with same number of database servers.
** Now reaching 150 million plus page views a day

MyISAM to InnoDB Conversion: Converting almost a billion records

So my challenge last week was to convert almost a billion records in 37 databases and 74 tables. Oh, and I wanted to do it only during the night (between 12:30AM and 6AM) and in the least number of nights.

This was not my first massive MyISAM to InnoDB migration, nonetheless, I still manged to learn a few things. We're at a stage where MyISAM just isn't the solution any more. Our plans are to go almost 100% InnoDB and this migration project completes yet another massive step towards that direction.

The first night was depressing. I applied every trick up my sleeve but only ended up getting 9.48% complete. Second night was only half depressing allowing me to complete ~ 33%. Third night was a charm and I got ~42.5% done. I would have finished the project that night but there was some storage issue and so I completed the last ~15% on the fourth day. Of course, optimizations and the method of conversion I selected really saved the day in the end.

Now that this has been accomplished, the next step will be to cover another 700-900 million records in a different cluster. Oh well, I will start doing that this weekend.

As far as performance is concerned, I couldn't be more happier. Load average on all servers in this cluster is well under 0.30 and each server is happily doing upto 1800 queries per second.

Note: Indexes were not dropped. Primary key did not consist of an integer. There was a composite primary key on all tables. The servers continued to handle production traffic on all databases on which migration was not being performed.

Update: What I did in a nutshell, for very large tables, was:

1. set innodb_flush_log_at_trx_commit=0 in my.cnf and restart
2. create new table tbl_innodb
3. set sort_buffer_size and read_rnd_buffer_size

SET SESSION sort_buffer_size=512*1024*1024; 
SET SESSION read_rnd_buffer_size=512*1024*1024;

4. then insert into tbl_innodb selecting from tbl_myisam and sorting by primary key
5. rename tbl_innodb to tbl

Of course, you need to have sufficient memory or adjust the buffers accordingly. But doing this ended up working great for me.

Update2: Regarding read_rnd_buffer_size, MySQL Manual says,

"If you are performing GROUP BY or ORDER BY operations on tables that are much larger than your available memory, you should increase the value of read_rnd_buffer_size to speed up the reading of rows following sorting operations."

Peter Zaitsev has also written about read_rnd_buffer_size.

Update3: My system (Solaris 10) had 10G of memory and read/writes were done to mounts residing on a SAN.

Update4: Best case scenario was 9600 records / second on average. Worst case was 500 records / second on average.

InnoDB table locks

When I first learned that having an AUTO-INC column causes a table lock in InnoDB, I was surprised. Since that day, I have been warning just about everyone regarding this.

In addition to the AUTO-INC table lock, there seem to be some other conditions where table lock is requested. I am saying this because on some of my InnoDB tables, I am seeing "tables in use 1, locked 1."

Do you know of any other conditions that can cause InnoDB to request a table lock? I have heard that using REPLACE or UNIQUE indexes on MySQL 4.1.X can cause this behavior.

If you know of any other situation where InnoDB will request a table lock, would you please leave a comment or email me (softwareengineer99 at Yahoo).

As I find information regarding this, I plan to document it here.

UPDATE:
MySQL Internals: Concurrent inserts causing table locks in InnoDB that go away when innodb_table_locks=0 is added to my.ini on Windows. The question raised, but not answered: "Do some queries implicitly add LOCK_TABLE?"

MySQL Internals: What is the intention lock check in InnoDB needed?
Heikki: "the intention lock is required: always when InnoDB sets an S-lock on a
record, there must be an IS-lock on the table."
- "InnoDB need read lock because binlog is enabled"

MySQL Bug 16229: InnoDB uses full explicit table locks in trigger processing: Fixed in 5.0.19.

Jeremy Cole encouraged Heikki and MySQL to change the way table and row locking is handled by MySQL and InnoDB, something that I totally support. I have heard Jeremy pitch this at nearly every ocassion but sadly I haven't seen any action. Jeremy says,

It seems like it would make more sense to reverse the locking procedures 
in MySQL and their handling by the storage engines.  As I understand it, 
currently MySQL asks for a table lock in all cases and the storage 
engine (InnoDB, in this case) may downgrade that to a row lock.

Wouldn't it make far more sense for MySQL to ask for a row lock, and the 
storage engine (MyISAM, in this case) to upgrade that to a page lock, 
table lock, etc. as support is available?  Basically MySQL should ask 
the storage engine for the most granular lock possible, and the storage 
engine should upgrade that lock if it is unable to lock with that 
granularity.

MySQL Internals: Heikki pushed a change into 4.1.9 (just a comment). The changes were primarily made in ha_innodb.cc:

...no InnoDB table lock is now acquired in LOCK TABLES if
AUTOCOMMIT=1. This helps to avoid deadlocks when porting old MyISAM applications to
InnoDB

+  /* Starting from 4.1.9, no InnoDB table lock is taken in LOCK
+  TABLES if AUTOCOMMIT=1. It does not make much sense to acquire
+  an InnoDB table lock if it is released immediately at the end
+  of LOCK TABLES, and InnoDB's table locks in that case cause
+  VERY easily deadlocks. */

InnoDB Performance Optimization

I am sitting in the session "InnoDB Performance Optimization" by Heikki Tuuri and Peter Zaitsev. This is going to be the last session of a very enjoyable and informative MySQL Conference and Expo 2007.

General Application Design is Paramount
Of course, you should design your schema, indexes and queries right. Storage engines aspects are often fine tuning. Storage engine selection may affect your schema layout and indexes.

Each storage engine has unique design and operating properties. App written for 1 storage engine may not perform best with other storage engines. Special optimizations exist for each storage engine.

There are always transactions with InnoDB, even if you don't use them explicitly. There is a cost with transactions. If running in autocommit mode, each transaction commit overhead for each statement.

You should wrap multiple updates in the same transaction for efficient operation (SET AUTOCOMMIT=0; ... COMMIT; ... COMMIT'_

HT points that InnoDB cannot purge deleted rows if transactions are open. Transaction commit doesn't affect query caching. If you run SELECT inside transaction Query cache may not be used.

You should make sure that you're catching deadlocks and wait timeouts. Transactions should not be too large. Deadlocks are normal and you should expect them. In app, try to reduce them but they will occur. If you run SELECTs you don't get deadlocks normally. Your application should deal with deadlocks appropriately.

Do not use LOCK tables as LOCK TABLES is designed to work with table level locking storage engines. With tow level lock storage engines, transactions are better choice. The behavior of LOCK TABLES is different in MySQL versions and depends on --innodb_table_locks. It can give problems for portable applications if you port from MySQL4 to later. Behavior may not be what you expect from MyISAM.

PRIMARY KEY CLUSTERING: It is very important. Accessing data by PK is faster than other keys and is true for both in-memory and disk based accesses. You should try to do most lookups by primary key.

In InnoDB data is clustered by PK and sequential PK values will likely have data on the same page. PK range and prefix lookups are also very efficient. It can be used to cluster data that is accessed together.

If storing user messages, you can use (user_id, message_id) primary key to keep all users messages in a few pages.

PK creates a "covering index" for any set of fields in the PK because PK holds all the data

What is the cost of clustered primary key:
PK in random order are costly and lead to table fragmentation. PK inserts should normally be in an ASC order. If you can, load data in PK order. At times, you may find that changing primary key to auto_increment is a good idea.

If you don't specify one, then InnoDB will create a clustered key internally, so you are better off defining one and using it.

Primary Key updates are very expensive and requires row data physically to be moved from one place in the index to another. The row has to be deleted and then inserted after which all secondary keys are updated. Never update primary key, change it to AUTO-INCREMENT. HT: If you need to update primary key then there is something wrong with your schema. Generally not a good schema/application design either.

What is a huge PK? It all depends on the access patterns of your application. Benchmark different keys.

What about 32-bit hash? That's like shooting yourself. Its really long and will be in non-sequential order.

If really large primary key then use AUTO-INC and demote your original primary key to unique key.

Keep your primary key short as secondary indexes use primary key to refer to the clustering index which makes PK value part of any/every index.

Long PK makes your indexes slow and long so you are better off reducing them.

Go easy on UNIQUE indexes as they do not use the "insert buffer" which can speed up index updates significantly. Indexes in InnoDB are not prefix compressed so they can take much more space then for MyISAM indexes (upto 10 times larger for the same structure). You should really avoid excessive or unneeded indexes. Remember MySQL will at most use one index for a query, not two.

Keep your updates fully indexed or you can see unexpected locking problems. A statement such as DELETE FROM users WHERE name="peter" may have to lock all rows in the table if the column name is not indexed. HT: MySQL 5.1 read-committed then only those rows are locked that are actually deleted.

AUTO-INCREMENT may limit scalability: AUTO-INC INSERTS may use table level locks (but only to the end of the INSERT statement, not transaction) even if you specify the AUTO-INC column value.


AUTO-INCREMENT limits scalability for concurrent inserts. HT: they have a patch and they are deciding on whether to implement it in 5.1.

MVCC: Complements row level locking to get even better concurrency. Standard SELECT statements set no locks, just reads appropriate row version.

LOCK IN SHARE MODE, FOR UPDATE modifiers can be done to do locking reads.

Even long running selects do not block updates to the table or other selects. Overly long queries (transactions) are bad for performance as a lot of unpurged versions accumulate. InnoDB can only purge rows when no active transactions are running. READ COMMITTED can ease the problem in such cases. InnoDB can only remove a row version when no transactions are open which can read it. HT: READ COMMITTED releases the snapshots at SELECT statements and doesn't keep it for the duration of transaction. READ COMMITTED is better for long transactions as it will let InnoDB purge transactions in background.

How MVCC plays with indexes? When you have index, reference is stored in index. When you update an index more and more versions are in the index that can slow index operations as well.

For UPDATE and LOCK IN SHARE MODE: Locking selects are executed in read committed mode because you cannot lock a row which doesn't exist. Because of this the results of these queries can be different than for standard SELECTs.

SELECT ... FOR UPDATE always has to access row data page to set the lock, so it can't run index covered queries which can further slow down queries significantly.

Reducing deadlocks: Deadlocks are normal for a transactional database. Non-locking SELECT statements do not deadlock with InnoDB. You should make sure to handle deadlocks and lock wait timeouts in your application. You should also make sure that your transactions lock data in the same order when possible.

Your update chunks should be smaller. You can achieve that by chopping transactions.

If you are going to update most of the selected rows then you are better off using SELECT ... FOR UPDATE. External locking can be used to avoid problem such as application level locks, SELECT GET_LOCK('mylock') etc.

Affect of isolation modes over performance:
Isolation modes in InnoDB can be set globally, per connection, or per transaction.

READ-UNCOMMITTED: If you are fine with dirty reads. Performance improvement is still limited.

Foreign Keys Performance: InnoDB checks foreign keys as soon as a row is updated, no batching is performed or checks delayed till transaction commit. FKs have their own cost and often have serious performance overhead. They are ideal for maintaining data consistency.

FKs increase amount of row level locking done and can make it spread to a lot of tables besides the one being updated.

Restrict number of open transactions: InnoDB performs best with a limited number of opern transactions and running queries. Multiple running queries may cause a lot of thrashing bumping into each other. Work is being done to improve performance in such cases. InnoDB_thread_concurrency can be used to restrict number of threads in InnoDB kernel. Many open transactions make lock graph building more cplicated and increase some other overhead. Whenever possible, you should keep a limited number of queries running at the same time by queuing on application side.

Guidelines for setting concurrency level: Manual says set it higher or lower. It really depends on application and number of hard drives.

HK: if you are worried about thread thrashing then set thread concurrency to 1.

INSERT...SELECT statement runs locking select. It is required for logical level replication to work properly. The problem goes away with MySQL 5.1 row level replication and the READ COMMITTED isolation level. Behavior is the same whenever you have log-bin enabled or not, to keep things consistent. innodb_locks_unsafe_for_binlog helps in 5.0 but your replication may get broken so do it if you know what you are doing. It also disables next-key locking.

SELECT...INTO OUTFILE + LOAD DATA INFILE can be often use as non-blocking safe alternative.

COUNT(*) facts and myths:
MyTH: "InnoDB doesn't handle count(*) queries well": Most count(*) queries are executed same way by all storage engines if you have a WHERE clause, otherwise you InnoDB will have to perform a full table scan.

FACT: InnoDB doesn't optimize count(*) queries without the where clause

You can use triggers and counter table to work it around or use SHOW table status like 'users' to get an approximation.

InnoDB and GROUP COMMIT: GC lets you commit several outstanding transactions with single log write. You can improve performance dramatically especially if no RAID with BBU. In 5.0, group commit does not work with binary logging due to a way XA (distributed transactions) support was implemented. Watch out for it if upgrading from MySQL 4.0.

Heikki will now talk further about server tuning settings:: Everything starts with innodb_buffer_pool_size which specifies main InnoDB buffer: data and index pages, insert buffer and locks are stored here. Most activity of InnoDB runs inside buffer pool. Why innoDB has its own cache? It's more efficient than OS cache. THe buffer pool is very important for performance on large data sets and much more efficient than OS cache especially for writes. InnoDB has to bypass OS buffering for writes. You can set buffer pool to 70-80% of memory for dedicated InnoDB-Only MySQL. Default value is just 8M, independent of available memory, just make sure to configure it.

InnoDB_additional_mem_pool: just stores dictionary information and is automatically increased, do not set too high. HK just uses 10MB for it. In future they may remove it altogether.

InnoDB_log_file_size: dramatically affects write performance. You should keep it high. However, high values increase recovery time. Check how large logs you can afford. Combined log file size should be 50% of the buffer pool memory.there is a 4GB total size limit.

InnoDB_log_files_in_group: this is the number of files of specified size that are used for log. There is usually no need to change default value.

InnoDB_log_buffer_size: You shouldn't set over 2-8MB unless you use huge BLOBs. Log file is flushed at least once per second anyway. Check innodb_os_log_written growth to see how actively your logs are written. innoDB logs are physio-logical, not page based so they are very compact.

InnoDB_flush_logs_at_trx_commit: By default logs are flushed to the disk at each transaction commit. This is required for ACID guarantees and is expensive. You can set to 2 or 0 if you can afford losing transactions for last one second or so (ie if you are using it as MyISAM tables replacement). Value 2 is nice if mysqld crashes then all your transactions are preserved, it is only bad in case of power crash or OS crash.

InnoDB log resizing: is not as simple as changing option and restarting. You need to shut down MySQL server and make sure it shuts down normally (check error log for errors to confirm). Then move away InnoDB log files ib_log*. Then start the MySQL server and check error log files to see if it successfully created new log files.

InnoDB_flush_method: specifies a way InnoDB will work with OS file system. On Windows unbuffered IO mode is always used. On Unix you can use fsync() or O_SYNC/O_DSYNC for flushing files. fsync() is usually faster. Always use default one or O_DIRECT (Peter).

InnoDB_file_per_table: allows InnoDB to store each table in its own file and makes disk management easier. Main tablespace is still needed for system needs. It can help to spread tables to multiple disks. It also allows you to reclaim space if a table is dropped. Sometimes slower for writes as fsync() is called sequentially. Can increase startup/shutdown time with large number of tables. I asked Heikki but unfortunately there are no plans to remove dependency on main tablespace as there are rollback segments that need to be there :((

Will there be InnoDB tablespace per database? No plans. Flexible tablespaces are similar to Oracle.

Other file IO settings:
innodb_autoextend_increment: HT doesn't think this is important. It specifies growth increment for shared tablespace (not for per table tablespaces). Larger values allow to reduce fragmentation.

InnoDB_file_io_threads: changes number of IO threads on Windows only. Note that all 4 threads are doing different jobs. In future it may change?

InnoDB_open_files: number of files used for per table tablespaces. You should increase it you have a lot of tables. No stats are available so far to show number of re-opens InnoDB needs to do.

innodb_support_xa: if set to 0 it reduces work InnoDB should do on transaction commit, however binlog can get out of sync.

So how do you minimize restart times: InnoDB buffer pool may have a lot of unflushed data and shutdown may take very long time. If you need to minimize downtime then

SET GLOBAL innodb_max_dirty_pages_pct=0

Watch for InnoDB_buffer_pool_pages_dirty in SHOW STATUS. As it gets close to 0, shut down the server.

InnoDB doesn't remove rows on delete and old row versions on update because these may be needed by other transactions. Purge thread is used to clean up these unused rows. In some workloads, the purge thread may not be able to keep up and the tablespace will grow witout bounds. CHECK TRANSACTIONS section in SHOW INNODB STATUS.

innodb_max_purge_lag limits number of transactions which have updated/deleted rows. This will delay insert/updates so purge thread can keep up. What is a good value of innodb_max_purge_lag? HT will set it to 100,000. It is measured in history list which contains transactions. Million transactions for this is too much.

innodb_commit_concurrency: umber of threads that are allowed at commit stage at the same time.

Questions: Are there any plans to make InnoDB's output to error log in a more defined/structured format?
Why does InnoDB show status sometimes only shows X transactions running and then truncates.