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!
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_data_home_dir
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_flush_method
innodb_force_recovery 0
innodb_lock_wait_timeout 50
innodb_locks_unsafe_for_binlog OFF
innodb_log_arch_dir
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
More reading:
Overview of Partitioning in MySQL:
http://dev.mysql.com/doc/refman/5.5/en/partitioning-overview.html
Partition Types:
http://dev.mysql.com/doc/refman/5.5/en/partitioning-types.html
Overview of Partitioning in MySQL:
http://dev.mysql.com/doc/refman/5.5/en/partitioning-overview.html
Partition Types:
http://dev.mysql.com/doc/refman/5.5/en/partitioning-types.html
ASKER
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...
This allows you to achieve greater query throughput in virtue of spreading data seeks over multiple spaces.
likely you can span this on multiple servers or disks even -- so you don't get "query lag" :)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
good thinking woldgang_93!
innodb_buffer_pool_size 2147483648
innodb_buffer_pool_size 2147483648
ASKER
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!
ASKER
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
#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
Keep in mind that I found these setting on the internet... They were not listed in the my.cnf file anywhere...
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
#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
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_file_per_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
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_co
#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
# innodb_flush_method = O_DIRECT
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_file_per_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
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_co
#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
# innodb_flush_method = O_DIRECT
ASKER
Thanks a lot!
ASKER