… how hash join algorithm works. Suppose you have two tables …size=6M mrr_buffer_size=6M Note that I have turned off ICP because I would like to see the affect…size and the read_rnd_buffer_size/mrr_buffer_size and both of these buffers should be increased appropriately for the best possible performance…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
…benchmark, because we want to see the individual affect of an optimization (where possible). Also note that…scans had to be performed. As in the table above you can with default buffer size of 256K, MariaDB…me. Probably its due to a bug in how counter is calculated inside MariaDB? Other Observations …
Post: InnoDB's gap locks
March 27, 2012 By 14 Comments
… of data to other sessions. How to troubleshoot gap locks? Is … 755 sec 4 lock struct(s), heap size 1248, 3 row lock(s), … 0x7f84a78ba700, query id 163 localhost msandbox TABLE LOCK table `test`.`t` trx id …locks in your transactions affecting the concurrency and the performance you can disable …
Post: How innodb_open_files affects performance
November 18, 2009 By 4 Comments
Recently I looked at table_cache sizing which showed larger table cache does not always provides the best performance. So I decided to look… data dictionary. More complicated tables can take a bit more. So innodb_open_files does not affect performance a lot on reads – what… a lot depending on hardware log file size workload and even when crash happen (how many unflushed changes we had) Repeating test…
Post: How much overhead is caused by on disk temporary tables
August 16, 2007 By 26 Comments
…table_size=1000000000; Query OK, 0 rows affected (0.00 sec) mysql> set tmp_table_size=1000000000; Query OK, 0 rows affected…Performance of Disk MyISAM tables may not scale proportionally with table size, even when all data fits in memory MEMORY temporary tables…
Post: How fast can you sort data with MySQL ?
August 18, 2007 By 26 Comments
… the system. Lets now increase sort_buffer_size and see how performance is affected: set sort_buffer_size=100000; mysql> select * from gt order by…_size to 100K gives quite expected performance benefit, now we’re just 2 times slower than table scan of the query and considering table size…
Post: Predicting how long data load would take
May 24, 2007 By 12 Comments
… order in which entries are inserted in the index can affect performance dramatically. Table Size This is the worst trouble maker. Typically load starts…_sort_buffer_size or key_buffer_size increase. Innodb typically needs large innodb_buffer_pool_size and large innodb_log_file_size to perform load… 3600; du -sh” in the database data directory to see how much data is loaded per hour (assuming your Innodb tablespace…
Post: Why MySQL could be slow with large tables ?
June 9, 2006 By 139 Comments
…important differences which can affect performance dramatically. The three …size and expect gradual performance decrease as database size grows you may be surprised by serve drop in performance… on index selectivity – how large proportion of… normalized to several tables or perform complex queries …
Post: Predicting Performance improvements from memory increase
August 7, 2008 By 6 Comments
… if your main queries do full table scan of 100GB table it does not matter if you…possible to see performance to go down as you increase buffer pool size. Some threads which…performance. By far the best method is trying, if you can afford it – just get memory upgrade and see how it affects performance…
Post: Evaluating IO subsystem performance for MySQL Needs
March 5, 2008 By 23 Comments
… themselves which will affect performance significantly. In any case especially playing with data size a bit you will well learn how your cache behaves… better performance by having many outstanding requests. Running with more than one threads you may also take a look at another table…
