…MySQL configuration. MySQL Configuration I used Percona Server 5.5.22-55 for the tests. Following is the configuration that I used: ## InnoDB…innodb_io_capacity = 800 innodb_read_io_threads = 8 innodb_write_io_threads = 4 innodb_file_per_table = 1 ## Disabling query cache query_cache_size… increase in…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
…Tables MEMORY tables can take memory. There are implicit MEMORY tables which are allocated for query execution, which size can be controlled by tmp_table_size…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
… and ran it on TPC-H dataset (InnoDB tables) with a Scale Factor of 2 (InnoDB dataset size ~5G). Note that query cache is…_size increased to 6M, the query time was ~300s while when both the join_buffer_size and the read_rnd_buffer_size/mrr_buffer_size… manual either for MariaDB or MySQL, but you need to appropriately increase read_rnd_buffer_size/mrr_buffer_size because these have an impact…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… example. Suppose you have the following query executed on the InnoDB table: SELECT non_key_column FROM tbl WHERE key_column=x…-H dataset (InnoDB tables) with a Scale Factor of 2 (InnoDB dataset size ~5G). I did not use Scale Factor of 40 (InnoDB dataset size ~95G… MySQL 5.6, increase of 0.02s. But the query times for MariaDB 5.5 are greater than both MySQL 5.5 and MySQL…
Post: Innodb Caching (part 2)
May 10, 2011 By 7 Comments
…primary key values from sysbench table and reading them over and…increasing number of rows being tested 20% per step. I’m trying standard Innodb page size, 4KB page size as 16K page size…size without recompiling the server, though I expect results for 16K and 16K Compressed to be very similar on MySQL…
Post: What to tune in MySQL Server after installation
September 29, 2006 By 116 Comments
…tables which are created on disk. innodb_buffer_pool_size This is very important variable to tune if you’re using Innodb tables. Innodb tables…sizes offer better performance but increase recovery times so be careful. I normally use values 64M-512M depending on server size. innodb_log_buffer_size…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
…size, for full table scan or index scan read-ahead may be performed which can increase average read size…Innodb builds for some pages to speed up row lookup replacing btree search with hash search. This section shows hash table size…
Post: MySQL Server Memory Usage
May 17, 2006 By 18 Comments
…increase memory consumption for given thread, however if table will perform complex operations such as full table scans, sorts, or need temporary tables… tmp_table_size is rather maximum amount of memory MySQL will …Innodb Table Cache. Innodb has its own table cache in which meta data about each table…
Post: Heikki Tuuri answers to Innodb questions, Part II
November 4, 2007 By 13 Comments
… these non-mysql processes drops rapidly when you increase the amount of system memory. We just deployed a new Linux/Mysql server for…. Q37: We have a fairly large Innodb table (150GB) that is showing poor performance for full table scans. using O_DIRECT, we are… not showed up in “show innodb statusâ€. select * from table where id=5 show innodb status: Hash table size 10624987, used cells 1, node…
Post: Modeling MySQL Capacity by Measuring Resource Consumptions
March 8, 2011 By 1 Comment
… we’re running Innodb with MySQL we can use Innodb_data_reads , Innodb_data_writes, Innodb_os_log_…01 0 0.11 0 # Tmp tbl size 554.04M 0 12.83M 324….0% yes, 99% no # Tmp table 1% yes, 98% no # Tmp table on 1% yes, 98% …handle it or whenever it needs an increase. The query time distribution histogram is …
