Innodb is way too slow for 700,000 rows! Sometimes it's super fast?

Ok...  I have worked and worked on improving my queries...  The other day my table crashed because I'm doing a lot of updates and inserts daily.  So, I switched everything to innodb from myisam and I indexed it a lot.

Sometimes, it's crazy fast!  But, many times I can run the same query and it'll take close to 30-90 seconds!!!

I had our server guy change the innodb buffer pool size and that didn't seem to help... Any advice?  I placed the output of var for innodb related settings below...  Please help!

One more note:  I'm more worried about the speed of the read than anything...  My inserting is done late at night by a cron job...  We do have users that insert data, so insert speed is important...  But not as important as the read!
have_innodb	YES
innodb_additional_mem_pool_size	1048576
innodb_autoextend_increment	8
innodb_buffer_pool_awe_mem_mb	0
innodb_buffer_pool_size	8388608
innodb_checksums	ON
innodb_commit_concurrency	0
innodb_concurrency_tickets	500
innodb_data_file_path	ibdata1:10M:autoextend
innodb_doublewrite	ON
innodb_fast_shutdown	1
innodb_file_io_threads	4
innodb_file_per_table	OFF
innodb_flush_log_at_trx_commit	1
innodb_force_recovery	0
innodb_lock_wait_timeout	50
innodb_locks_unsafe_for_binlog	OFF
innodb_log_archive	OFF
innodb_log_buffer_size	1048576
innodb_log_file_size	5242880
innodb_log_files_in_group	2
innodb_log_group_home_dir	./
innodb_max_dirty_pages_pct	90
innodb_max_purge_lag	0
innodb_mirrored_log_groups	1
innodb_open_files	300
innodb_rollback_on_timeout	OFF
innodb_support_xa	ON
innodb_sync_spin_loops	20
innodb_table_locks	ON
innodb_thread_concurrency	8
innodb_thread_sleep_delay	10000

Open in new window

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

stephenmpAuthor Commented:
NerdsOfTechTechnology ScientistCommented:
NerdsOfTechTechnology ScientistCommented:
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

stephenmpAuthor Commented:
Ok...  I'll look into this when I get home, but I don't really think that's it....  It works fast sometimes, and other times it's crazy slow...
NerdsOfTechTechnology ScientistCommented:
This allows you to achieve greater query throughput in virtue of spreading data seeks over multiple spaces.
NerdsOfTechTechnology ScientistCommented:
likely you can span this on multiple servers or disks even -- so you don't get "query lag" :)
>innodb_buffer_pool_size      8388608

This tells me that you are setting the InnoDB buffer pool (i.e. the amount of RAM
that is allowed to be devoted to InnoDB queries) to only 8 meg!

Unless you are running a 10+ year old server, or it is so crammed with other
stuff, you ought to have way more RAM available than that. You want to set
this variable to as much as possible because the larger it is, the more pages in
a query (which determines query speed) gets fetched from RAM which is
about 1000 times faster than fetching it from disk.

If your MySQL server is mainly devoted to MySQL, and say it has 4 gig of RAM,
your sysadmin could set the buffer pool to 2 gig at least as you generally allow
1 gig for the operating system and say 1 gig for miscellaneous other stuff on the

 innodb_buffer_pool_size = 2G

Hopefully your sysadmin reboots the MySQL server to make sure the new setting is

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NerdsOfTechTechnology ScientistCommented:
good thinking woldgang_93!

innodb_buffer_pool_size 2147483648
stephenmpAuthor Commented:
Our server guy upgraded this...  That one db is 4GB...  I bet he forgot to restart mysql to allow new changes!   Thanks you!  Ill check this in a few minutes!
stephenmpAuthor Commented:
I just went into the server myself and made the changes...  They must not have set it right...  The only thing I added was the buffer pool size to 2G...  However, I know there are more settings that need to be set for innodb?  There is nothing in my.cnf for innodb except the buffer pool size that I added...

Search is fast now!!!

Which of these setting are necessary?
# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /usr/local/mysql/data/ #innodb_data_file_path = ibdata1:10M:autoextend #innodb_log_group_home_dir = /usr/local/mysql/data/
#innodb_log_arch_dir = /usr/local/mysql/data/ # You can set .._buffer_pool_size up to 50 - 80 %
 # of RAM but beware of setting memory usage too high innodb_buffer_pool_size = 2G innodb_additional_mem_pool_size = 100M # Set .._log_file_size to 25 % of buffer pool size innodb_log_file_size = 500M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50

Keep in mind that I found these setting on the internet...  They were not listed in the my.cnf file anywhere...
The innodb_buffer_pool_size is the most significant. I took a look at one of our
servers and list other significant ones:

# InnoDB settings
#The directory path to the main InnoDB tablespace file (ibdata1)
innodb_data_home_dir  = ...fill-in a path....
#Want separate workspace file for each InnoDB table
innodb_open_files  = 500
#The directory path to the InnoDB log files.
innodb_log_group_home_dir  = ...fill in a path....

#Memory pool size for InnoDB to store data dictionary info, etc
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 100M
#Buffer size that InnoDB uses to write to the log files on disk
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
#Default: The timeout in seconds an InnoDB transaction may wait
innodb_lock_wait_timeout = 50
#Useful InnoDB options to consider should circumstances dictate
#  innodb_max_dirty_pages_pct = 10
#  innodb_flush_method = O_DIRECT
NerdsOfTechTechnology ScientistCommented:
Suggesting full points to wolfgang_93

NerdsOfTechTechnology ScientistCommented:
oops i mean:

http:#33953232   accepted, for buffer size correction
stephenmpAuthor Commented:
Thanks a lot!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.