…MySQL will allocate for this operation. Note it is more complicated than once may think – multiple… you know memory requirements with current settings and increase/decrease values appropriately. Some …in practice and how memory consumption reacts to changing various variables. For example you may find…
Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark
March 11, 2013 By 7 Comments
… MySQL 5.5.30 and testing multiple buffer pools on MySQL 5.5.30. Finally, MySQL …” of queries represents a set of drill-down queries to find an anomaly. I am … 10 rows in set (0.00 sec) Here are the contents of the buffer pool in pages afterwards: mysql> select sq.*,…
Post: Researching your MySQL table sizes
March 17, 2008 By 28 Comments
…it goes: Find total number of tables, rows, total data in index size for given MySQL Instance … row in set (52.56 sec) Find the same data using some filter I often use similar queries to find space used by particular table “type” in sharded environment when multiple tables with…
Post: Why MySQL could be slow with large tables ?
June 9, 2006 By 141 Comments
…mysql> select count(pad) from large; +————+ | count(pad) | +————+ | 31457280 | +————+ 1 row in set (4 min 58.63 sec) mysql… complex queries finding relationships between objects…multiple servers to host portions of data set. Store portion of data you’re going to work with in…
Post: MySQL Connection Timeouts
April 19, 2011 By 4 Comments
… the case by running netstat -s and finding something like: 38409 times the listen queue …From this point on MySQL processes network communication in multiple threads and can benefit from multiple cores but this …multiplier”. This means they should be set to at least 600. Setting it to cover much more…
Post: Investigating MySQL Replication Latency in Percona XtraDB Cluster
March 3, 2013 By 13 Comments
… a simple script (you can find it in the end of the post) which connects to one node in the cluster, performs an… are done on multiple nodes. There is an option you can enable to get fully synchronous replication behavior: mysql> set global wsrep_causal… my test to see how the latency is impacted: mysql> update sbtest2 set k=k+1; Query OK, 1000000 rows affected (1…
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
…null 2>&1 < /dev/null done; Where to find the images Amazon ID Name …and/or loading among multiple nodes as well. Note that in the demonstration each…from 1988 to 2010′; mysql> use ontime1; Database changed mysql> show table status like ‘ontime… from 1988 to 2010 1 row in set (0.00 sec) With ICE, after…
Post: Moving Subtrees in Closure Table Hierarchies
February 14, 2011 By 18 Comments
… a subtree in this design? Moving subtrees can be tricky in both Closure Table and the Nested Sets model….. When you insert a subtree, you’re inserting multiple nodes, and you want to create new paths… same table in a single query in MySQL. But we can use MySQL‘s multi-table DELETE syntax, to find any…
Post: MySQL: what read_buffer_size value is optimal ?
September 17, 2007 By 25 Comments
…MySQL 5.1.21-beta was used for tests. mysql> select count(*) from dt2 where slack like “a%”; +———-+ | count(*) | +———-+ | 4705992 | +———-+ 1 row in set… into multiple ones…in best performance – experiment with realistic load and realistic data (including fragmentation) to find…
Post: Edge-case behavior of INSERT...ODKU
October 27, 2012 By 6 Comments
…in which they were defined, and the PRIMARY KEY is always going to be considered as being defined first. So, MySQL… finds a… 10 | NULL | +————+———-+———+———————+ 2 rows in set (0.00 …multiple unique indexes such as this one would be equivalent to “UPDATE update_test SET…
