Posted by djudd on May 25, 2010 at 11:40pm
I've been considering converting my node and node_revisions tables to INNODB to avoid locking, but I've heard that putting those tables, or the search tables into INNODB is a very bad idea.
The problem is, I'm not sure why it's a bad idea. Can anyone shed some light on this one for me?
Comments
It's only a bad idea if: -
It's only a bad idea if:
- You don't tune MySQL to have proper resources for the InnoDB stuff (I'm a fan of mysqltuner2 https://launchpad.net/mysqltuner ).
- You've tuned MySQL to the best of your ability, but you just don't have enough RAM to give the InnoDB Buffer Pool what it needs. Check this article out:
http://www.mysqlperformanceblog.com/2010/04/08/fast-ssd-or-more-memory/
--
Dave Hansen-Lange
Director of Technical Strategy, Advomatic.com
Pronouns: he/him/his
dalin, when I run mysqlruner
dalin, when I run mysqlruner I get the following output, which scares me...
Current InnoDB index space = 0 bytes
Current InnoDB data space = 0 bytes
Current InnoDB buffer pool free = 0 %
Current innodb_buffer_pool_size = 1.00 G
Depending on how much space your innodb indexes take up it may be safe
to increase this value to up to 2 / 3 of total system memory
Any idea why my index space and data space are zero?
The version of mysqltuner
The version of mysqltuner that I'm using doesn't give me any output like that. But it's just a straightforward Perl script. You should be able to open it up and take a look at what variables it's reading and work back from there.
--
Dave Hansen-Lange
Director of Technical Strategy, Advomatic.com
Pronouns: he/him/his
Dalin, you seem like a pretty
Dalin, you seem like a pretty knowledgeable guy with INNODB. I can't seem to find a good formula for determining the innodb_log_file_size. The tuner you linked me says that my innodb_log_file_size is inappropriate in relation to my innodb_buffer_pool_size.
Is there a good way to figure out how big the log file size should be?
Here's a good description of
Here's a good description of how the log file works and how big you should make it:
http://www.mysqlperformanceblog.com/2008/11/21/how-to-calculate-a-good-i...
--
Dave Hansen-Lange
Director of Technical Strategy, Advomatic.com
Pronouns: he/him/his
Obviously, I'm learning about
Obviously, I'm learning about how to optimize a MySQL server, so I hope my rookie questions aren't annoying to folks.
In doing some more reading, it's come to my attention that the reason my server may be so slow when using INNODB tables is because of autocommit. It seems, if I understand this right, that unless autocommit is turned off, the INNODB tables will be locked anyway. Am I correct in that understanding?
And if so, believe right configuration switch (according to what I have read anyway) to fix that would be innodb_flush_log_at_trx_commit=0.
Am I correct in that as well?
Yes I
Yes I use
innodb_flush_log_at_trx_commit = 0
I'm not a bank so if power goes out at my hosting facility and I loose as much as the last two seconds of transactions it's not the end of the world.
--
Dave Hansen-Lange
Director of Technical Strategy, Advomatic.com
Pronouns: he/him/his
So far the only tables I have
So far the only tables I have pushed to INNODB are my cache style tables. Primarily boost_cache_relationships and sessions.
I really do appreciate your advice on this one, and your expertise and experience on the subject. I'm learning a lot as I go. Do you think that might have been the reason I was getting extremely slow writes on my INNODB tables?
I have innodb_flush_log_at_trx_commit = 0 enabled now, but it's too early in the game (and late at night) for me to tell if it's making any real difference yet.
Yes it's possible. Writing
Yes it's possible. Writing to disk every transaction as it happens vs. in batches every two seconds can make a big difference if you are getting a lot of writes. It can sometimes take a lot of detective work to find out what's going on.
--
Dave Hansen-Lange
Director of Technical Strategy, Advomatic.com
Pronouns: he/him/his
I'm guessing there is quite a
I'm guessing there is quite a bit of writing happening to my INNODB tables. My site does about 20,000 page views per day, but it's very heavily Views dependent. As a result, my boost_cache_relationships table often has a few million rows in it.
Seems to me that's a pretty fair amount of writing happening for the amount of traffic the site gets.
My CPU is spiking really high when the boost cron runs, or when feedapi cron runs. Those are doing a lot of writing to the DB and that's where I am having my problems.