…sort buffers to be allocated. You can’t control that or calculate it at a global level….( select * from ( select 1 as a ) as b ) as c; You can have many of those per query…* 100); Query OK, 0 rows affected (2.34 sec) mysql> select get_lock(@a, 1); +—————–+ | get_lock(@a, 1) | …
Post: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
… MariaDB includes a new variable ‘join_cache_level’, this variable controls which Join Algorithms are allowed to be used, a value… introduces another variable to control the size of the buffer ‘join_buffer_space_limit’. This variable controls the maximum allowed size of… more time as compared to BKA algorithm of MySQL 5.6. Test Case C – Join a small table with a large table…
Post: ANALYZE: MyISAM vs Innodb
September 3, 2008 By 9 Comments
… | 1 | i | A | 245900 | NULL | NULL | | BTREE | | | antest_innodb | 1 | c | 1 | c | A | 245900 | NULL | NULL | YES | BTREE | | | antest_innodb | 1…. MyISAM has a special variable which controls if NULLs should be considered equal when computing stats: mysql> show variables like “myisam_stats…
Post: The performance effects of new patches
September 9, 2008 By 14 Comments
… between transactions (no thinking time, no keying time), it uses MySQL C API and the server side prepared statement. The server has… following parameters are added. innodb_read_ahead (default 3) This controls [enable/disable] of read-ahead. 3: normal 2: enable linear… value innodb_buf_flush_burst (default 100) * Burst value These control the number of blocks flushed at once. (flushing: writing modified…
Post: To pack or not to pack - MyISAM Key compression
May 13, 2006 By 4 Comments
… to be treated differently. For uncompressed index blocks MySQL can do binary search inside the page – kind …average to find the key value So how you control which indexes are packed and which are not ? …: select count(*) from t1, t1 t2 where t1.c=t2.c – simple join by string column select count(*) …
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
…, which can potentially speed them up greatly. This feature is controlled by the expand_fast_index_creation system variable which is….00 sec) mysql> INSERT INTO t(c) VALUES (RAND()); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO t(c) SELECT RAND… OK, 0 rows affected (0.00 sec) mysql> ALTER TABLE t ADD KEY (c), ADD KEY(c); Query OK, 0 rows affected (36…
Post: Test Drive of Solid
September 22, 2006 By 5 Comments
…this test. Test 1: Solid, deadlock detection, default (OPTIMISTIC) concurrency control. CREATE TABLE `test2` ( `id` int(11) NOT NULL, `names` …sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> UPDATE sbtests set c=’773425598-640282370-898784553-626619839-555294498-279281971-788986238-202873246-…
Post: Pacemaker, please meet NDB Cluster or using Pacemaker/Heartbeat to start a NDB Cluster
May 19, 2010 By 10 Comments
…-value false At this point, we have a running cluster controlling nothing. The trick with NDB Cluster is that Heartbeat is… behavior, I created fake resource scripts that can be fully controlled by Heartbeat but allowing the one way behavior I wanted…_mgmd to start /bin/sleep 10 nohup /usr/local/mysql/libexec/ndbd -c 10.2.2.139 > /dev/null & while [ 1 ] do…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
… semaphore: Mutex at 0x2a957858b8 created file buf0buf.c line 517, lock var 0 waiters flag …thread declared inside InnoDB 442 mysql tables in use 1, locked 0 MySQL thread id 8079, query … active, state of Innodb main thread which controls scheduling of number of system operations – flushing dirty…
Post: Living with backups
November 6, 2008 By 28 Comments
… can play some tricks, but it’s mostly beyond your control. Otherwise, when copying physical data files, in order not to…: /root/backup-tools/tar –read-rate=15000000 -C /mnt/snapshot -c -z -v -f – mysql | ssh backup@storage-host /root/backup-tools/write_backup.sh This will read /mnt/snapshot/mysql directory…
