Posted by djudd on May 21, 2010 at 12:49pm
Any write to my database, be it UPDATE, INSERT, or DELETE, is very slow and spikes my CPU very very high, usually above 100%. And it doesn't matter at all if that table is INNODB or MYISAM, happens every time.
I have a quad core system with 8GB of RAM running both Apache and MySQL for a single server solution. My OS runs on the main drive, and my database is on a separate drive.
Any tips on where to begin looking for the problem?
Comments
The main problem likely has
The main problem likely has to do with keys, if changes are slow but selects are fast. Excessive or bad keys will cause slow reindexing.
You probably still want to use InnoDB for the row-level locking. If you can easily modify the query, there may be improvements to be made there as well.
Ken Winters
I'm using INNODB selectively
I'm using INNODB selectively at this point for tables like users, sessions, boost_cache and boost_cache_relationships, etc. This were table locking can really slow things down.
As for my keys, it doesn't matter which table I'm updating. It can be a huge table or a tiny one, the minute any kind of update happens to that table, I get a CPU spike.
I have thrown 2GB into my innodb_buffer_pool to see if that would help, and it did not. I'm nervous about going higher since I have to dedicate a portion of RAM to memcache, apc, and apache as well as mysql.
I take it back Ken, I very
I take it back Ken, I very well could be wrong on that assumption.
I have a view built that will search for a node by title, mostly because our publisher doesn't remember normal information when looking up old stories, but the man never forgets a title.
I built an index on node.title to help the search. After reading your thoughts about keys, I went back and deleted that index.
When my FeedAPI runs (on cron) it doesn't tie things up as long now, or at least seemingly it doesn't, but it does still spike the cpu pretty high.
Well, it seems that updates
Well, it seems that updates to any INNODB table are still pretty slow. MYISAM tables seem much faster.
Anyone know of any reason off the top of their heads that INNODB would be so slow for writes and could make the CPU spike so high?
You may want take a look to
You may want take a look to the key_buffer_size settings in the my.cnf
Usually is set to 8 mb by default ... If you do excessive writing and updating you might want to make this number significantily.
Drupal Rocks !!!
I have my key_buffer_size set
I have my key_buffer_size set to 300M. Thanks for the tip, but that one is covered.
I added the following line to
I added the following line to the my.cnf file and restarted mysqld, and it seems to have made a solid impact. My CPU is not spiking anymore when writing to INNODB tables, and my server is running more uniform.
innodb_data_home_dir = /var/lib/mysql/
innodb_data_file_path = ibdata1:2048M:autoextend
I'm not a MySQL expert, so maybe this one was obvious to the more educated among us. Hopefully this helps someone else out there having a similar issue.
And if any of the smarter guys can explain why this would have this effect, I'd appreciate it.
innodb
I use innodb_file_per_table
Found this via google
http://www.meetup.com/mysqlbos/messages/boards/thread/6141618
http://www.pythian.com/news/1067/
I'm going to have to explore
I'm going to have to explore that option a little more I guess. I'm pretty nervous about having to delete my InnoDB dataspace to implement it, but the benefits seem sound.
Well mikeytown2, I sucked it
Well mikeytown2, I sucked it up, followed the instructions, and set up innodb_file_per_table.
AND... I screwed it up. Thank goodness for backups.
I struggled getting everything right, but I finally have it all set and working. I've pushed my cache tables, boost_cache_relationships, users, sessions, and xmlsitemap tables to INNODB, and so far, everything seems all right.
Thanks for the tip.
innodb_file_per_table
yes, innodb_file_per_table is a risky maneuver; glad to hear you got it converted over.