I had earlier written a post on tuning the MySQL server configuration which was more geared towards the MyISAM storage engine. While that is not because I didn't intend on ignoring InnoDB but because I had planned a whole post on tuning InnoDB related configuration. So this post is the post that I had planned, I have discussed the major configuration parameters in here that should help you out most of the times.
« 10 Newer Entries
There are three types of FUD: the first and more genuine is
(#1) the intentional spreading of falsehood, mostly to gain
some marketing advantage over a competing product. While I
despise this practice, I understand it.
Then there is (#2) FUD spread by ignorance, when the originators are so blindly enraged by their hatred for a product that they don't care about getting the facts straight.
And finally, there is a third kind, not less dangerous, which is (#3) the spreading of FUD with good intentions, when the authors believe that they have the facts straight and they want to help.
I have recently come across two examples of unintentional FUD. For different reasons, my …[Read more]
Although MyISAM has been the default storage engine for MySQL but its soon going to change with the release of MySQL server 5.5. Not only that, more and more people are shifting over to the Innodb storage engine and the reasons for that is the tremendous benefits, not only in terms of performance, concurrency, ACID-transactions, foreign key constraints, but also because of the way it helps out the DBA with hot-backups support, automatic crash recovery and avoiding data inconsistencies which can prove to be a pain with MyISAM. In this article I try to hammer out the reasons why you should move on to using Innodb instead of MyISAM.
Clients often ask what the differences are between the various InnoDB isolation levels, or what ACID means. Here are some simple explanations for those that have not yet read the manual and committed it to memory.
Every select operates without locks so you don’t get consistency and might have dirt reads, which are potentially earlier versions of data. So, no ACID support here.
Has consistent reads without locks. Each consistent read, even within the same transaction, sets and reads its own fresh snapshot.
The InnoDB default isolation level for ACID compliance. All reads within the same transaction will be consistent between each other – ie, the C in ACID. All writes will be durable, etc etc.
Same as REPEATABLE READ but MySQL converts regular select …
A couple of question I get a lot from MySQL customers is “how will this hardware upgrade improve my transactions per second (TPS)” and “what level of TPS will MySQL perform on this hardware if I’m running ACID settings?” Running sysbench against MySQL with different values for per-thread and global memory buffer sizes, ACID settings, and other settings gives me concrete values to bring to the customer to show the impact that more RAM, faster CPUs, faster disks, or cnf changes have on the server. Here are some examples for a common question: “If I’m using full ACID settings vs non-ACID settings what performance am I going to get from this server?”
Let’s find out by running sysbench with the following settings (most are self explanatory – if not the man page can explain them):
- sysbench –test=oltp –db-driver=mysql –oltp-table-size=1000000 –mysql-engine-trx=yes –oltp-test-mode=complex …
Tokutek is pleased to announce immediate availability of TokuDB for MySQL, version 3.0. It is designed for continuous querying and analysis of large volumes of rapidly arriving and changing data, while maintaining full ACID properties.
TokuDB v3.0 combines our long-standing performance advantages with ACID compliant transactional integrity:
- 10x-50x faster indexing for faster querying
- Full support for ACID transactions
- Fast recovery time (seconds or minutes, not hours or days)
- Immunity to database aging to eliminate performance degradation and maintenance headaches
- 5x-15x data compression for reduced disk use and lower storage costs
Because of its high indexing performance and transaction support, TokuDB is well suited to Web applications that must simultaneously store and query …[Read more]
We recently made transactions in TokuDB 3.0 durable. We write table changes into a log file so that in the event of a crash, the table changes up to the last checkpoint can be replayed. Durability requires the log file to be fsync’ed when a transaction is committed. Unfortunately, fsync’s are not free, and may cost 10’s of milliseconds of time. This may seriously affect the insertion rate into a TokuDB table. How can one achieve high insertion rates in TokuDB with durable transactions?
Decrease the fsync cost
The fsync of the TokuDB log file writes all of the dirty log file data that is cached in memory by the operating system to the underlying storage system. The fsync time can be modeled with a simple linear equation: fsync time = N/R + K, where N is the amount of dirty data that needs to by written to disk, R is the disk write rate, and K is a constant time defined by the …[Read more]
My editor Andy Oram recently sent me an ACM article on BASE, a technique for improving scalability by being willing to give up some other properties of traditional transactional systems.
It’s a really good read. In many ways it is the same religion everyone who’s successfully scaled a system Really Really Big has advocated. But this is different: it’s a very clear article, with a great writing style that really cuts out the fat and teaches the principles without being specific to any environment or sounding egotistical.
He mentions a lot of current thinking in the field, including the CAP principle, which Robert Hodges of Continuent first turned me onto a couple months ago. …[Read more]
Until now PBXT has been ACId (with a lower-case d). This is soon
to change as I have had some weeks to work on a fully durable
version of the transactional engine (http://www.primebase.com/xt).
My first concern in making PBXT fully durable was to what extent I would have to abandon the original "write-once" design. While there are a number of ways to implement durability, the only method used by databases (as far as I know) is the write-ahead log.
The obvious advantage of this method is that all changes can be flushed at once. However, this requires that all data be written twice: once to the log and after that, to the database itself.
My solution to this problem is a compromise, but I think it is a good one. In a nutshell: short records are written twice, and long records are written once. When it comes to durability, this compromise, I …
« 10 Newer Entries