… for more details, check out detailed guide on tuning innodb buffer pool innodb_log_file_size – This depends on your recovery speed… one of our MySQL Presentations. Application tuning for Innodb Especially when coming from MyISAM background there would be some changes you would… to use it in joins), large unpacked indexes (try to be easy on indexes). With these basic innodb performance tunings you will…
Post: INSERT INTO ... SELECT Performance with Innodb tables.
July 12, 2006 By 22 Comments
… clause and joins. It is important for tables which is being read to be Innodb – even if writes are done in MyISAM table… not used you can enable innodb_locks_unsafe_for_binlog option, which will relax locks which Innodb sets on statement execution, which… unsafe fore replication and point in time recovery, so use innodb_locks_unsafe_for_binlog option with caution. Note disabling binary…
Post: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
… WITH READ LOCK is waiting for that very nasty “full join” select to complete. What is worse as the statement started…) Reads though would not be blocked, ie there is no MYISAM like table lock priority problem with pending WRITE query blocks…’re just using Innodb tables and you’re not actively changing users, stored procedures etc (which are stored in MyISAM tables anyway…
Post: XtraDB/InnoDB CPU bound benchmarks on 24cores server
December 29, 2008 By 6 Comments
…=MYISAM ft_min_word_len=4 innodb_additional_mem_pool_size=16M innodb_buffer_pool_size=15G innodb_data_file_path=ibdata1:10M:autoextend innodb…_size=512M innodb_status_file=0 innodb_thread_concurrency=0 innodb_io_capacity=1000 innodb_write_io_threads = 16 innodb_read_io_threads = 16 join_buffer…
Post: New OLAP Wikistat benchmark: Introduction and call for feedbacks
January 28, 2010 By 16 Comments
…, sum(page_count) sm FROM pagestat JOIN datesinfo di ON ( di.id=date_id ) JOIN projects p ON (p.id=project_id… to load data and run queries against available engines: MySQL MyISAM / InnoDB (to have reference results) InfoBright InfiniDB MonetDB LucidDB Greenplum and…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…innodb_use_native_aioblogpercona.commanual innodb_use_sys_mallocblogpercona.commanual innodb_versionblogpercona.commanual innodb_write_io_threadsblogpercona.commanual insert_idblogpercona.commanual interactive_timeoutblogpercona.commanual join…
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
…cost of repairing huge MyISAM tables and taking downtime, I would not use MyISAM for anything but read…What does this have to do with InnoDB? Data clustering. InnoDB‘s primary keys define the physical order…workload. The nested-loop joins are not all that fast on big joins; the query optimizer can…
Comment: New Forum Categories: Help Wanted, For Hire
March 5, 2013 By 1 Comment
… = 128M join_buffer_size = 8M long_query_time=300 key_buffer_size = 500M concurrent_insert=2 myisam_sort_buffer_size = 64M # innodb innodb_additional… = 128M join_buffer_size = 8M long_query_time=300 key_buffer_size = 500M concurrent_insert=2 myisam_sort_buffer_size = 64M # innodb innodb_additional…
Post: How fast is FLUSH TABLES WITH READ LOCK?
April 24, 2010 By 30 Comments
… | 80 | Sending data | select count(*) from t t1 join t t2 join t t3 join t t4 where t1.b=0 | | 5 | root… data. This does not apply to all storage engines. However, MyISAM does not attempt to flush its own data to the… is unavoidable. This includes backing up a mixture of MyISAM and InnoDB data. But many installations do not mix their data this…
Post: MySQL 5.6.7-RC in tpcc-mysql benchmark
October 8, 2012 By 22 Comments
…innodb_read_io_threads = 16 innodb_write_io_threads = 16 innodb_io_capacity = 20000 innodb_io_capacity_max = 40000 #not innodb…table_size = 64M sort_buffer_size = 4M join_buffer_size = 4M thread_cache_size … myisam_sort_buffer_size = 8M myisam_max_sort_file_size = 10G myisam_repair_threads = 1 myisam…
