Quick tip: how to convert tables to InnoDB | MySQL Performance Blog. July 30, 2008 by Baron Schwartz5 Comments I use Maatkit for a lot of grunt work and thought you might appreciate this quick tip. Suppose you have a bazillion tables to convert from MyISAM to InnoDB, but they are mixed in with other tables that are already InnoDB, or are another storage engine that you don’t want to touch. mk-find <db_name> --engine MyISAM --exec "ALTER TABLE %D. %N ENGINE=INNODB" --print Here’s a bonus tip, while I’m at it. I had a client a while back whose application creates tables as needed, so they had about 90,000 tables in a bunch of different databases, all named things like user_123_456_friends.
I wanted to add an index to them — but not to the ones named friends_123_456_user. mk-find <db_name> --tblregex '^user_\d+_\d+_friends$' --exec 'ALTER TABLE %D. Boy, is that a lot easier than adding indexes to 90k tables by hand! Ease of Switching to the InnoDB Plugin and the Numerous Benefits « Chris on MySQL. In my last post, I discussed how to troubleshoot InnoDB locks using the new InnoDB Plugin’s new Information Schema tables. However, that got me to thinking about how many MySQL 5.1 users who have still not switched to use the plugin as opposed to the built-in version of InnoDB. There are many advantages to using the plugin as opposed to the built-in version (aside from just the new I_S tables, and more importantly, numerous performance enhancements), and it’s breeze to set up, so I wanted to provide a quick start guide to using the new InnoDB plugin.
Note that the InnoDB plugin is now GA (as of plugin version 1.0.7) and as of 5.1.38 it is included with the MySQL downloads (it just needs to be enabled). If you’re running pre-5.1.38, then you may want to consider upgrading to a newer MySQL anyways (5.1.48 is the latest as of this posting). But if not, then you’ll need to download the plugin and extract it. But if you’re using 5.1.38 or newer, then just take the following steps: 1. 2. 3. Maximal write througput in MySQL | MySQL Performance Blog. February 28, 2010 by Vadim Tkachenko11 Comments I recently was asked what maximal amount transactions per second we can get using MySQL and XtraDB / InnoDB storage engine if we have high-end server. Good questions, though not easy to answer, as it depends on: - durability setting ( innodb_flush_log_at_trx_commit = 0 or 1 ) ?
- do we use binary logs ( I used ROW based replication for 5.1) - do we have sync_binlog options. So why would not take these as variable parameters and run simple benchmark. I run update key for various threads and with next parameters trx_commit=0 : innodb_flush_log_at_trx_commit = 0 and no binary logstrx_commit=1 : innodb_flush_log_at_trx_commit = 1 and no binary logstrx_commit=0 & binlog : innodb_flush_log_at_trx_commit = 0 and binary logstrx_commit=1 & binlog : innodb_flush_log_at_trx_commit = 1 and binary logstrx_commit=1 & binlog & sync_bin : innodb_flush_log_at_trx_commit = 1 and binary logs and sync_binlog=1 There are results I get:
Website » 13.2.4 InnoDB Startup Options and System Variables. Ariel MySQL configuration - Wikitech. Hardware ordered May 2004. The hardware order in May 2004 was for four 1U multipurpose machines and a 2U database machine to replace Geoffrin. It was based on the upgrade discussion in April 2004. Delivery started on 21 May 2004 when three 1U machines arrived and a fourth 1U followed a few days later. Memory tests on the four 1U machines started on 26 May 2004, shortly after adding them had caused an overload of the 30 amp power supply to the rack, which shut down most machines briefly. The rack power supply was upgraded to two 30 amp circuits. Total vendor cost for the five machines was US$20,047. The 2U machine arrived on 26 May and started memory testing on 27 May [1]. General purpose machines[edit] The 1U machines are Pentium 4 CPUs with 4GB of RAM each. Zwinger file storage replacement[edit] One, named Will, has a hardware RAID controller and pair of 200GB hard drives added so it can replace Zwinger for file storage, adding an extra measure of data protection.
Database server[edit] Named Ariel. Adapter submenu. Innodb Performance Optimization Basics | MySQL Performance Blog. November 1, 2007 by Peter Zaitsev77 Comments Note: There is an updated post on this topic here. Interviewing people for our Job Openings I like to ask them a basic question – if you have a server with 16GB of RAM which will be dedicated for MySQL with large Innodb database using typical Web workload what settings you would adjust and interestingly enough most people fail to come up with anything reasonable. So I decided to publish the answer I would like to hear extending it with basics of Hardware OS And Application optimization. I call this Innodb Performance Optimization Basics so these are general guidelines which work well for wide range of applications, though the optimal settings of course depend on the workload.
Hardware If you have large Innodb database size Memory is paramount. 16G-32G is the cost efficient value these days. From CPU standpoint 2*Dual Core CPUs seems to do very well, while with even just two Quad Core CPUs scalability issues can be observed on many workloads.