… sysbench does 9 primary key SELECTs followed by a PK UPDATE statement in each thread in a loop, which is probably a bit closer to real life workloads. It’s still the AUTOCOMMIT mode, so SELECTs are… buffer pool we use following queries: select avg(id) from sbtest$i force key (primary) select count(*) from sbtest$i WHERE k…
Post: Percona XtraDB Cluster 5.5.30-23.7.4 for MySQL now available
April 17, 2013 By 2 Comments
… to hang. This was happening due to prepared statement execution loop, which does not honor wsrep status codes correctly. Bug fixed… fixed #1158443 (Raghavendra D Prabhu). In case CREATE TABLE AS SELECT statement was running in parallel with the DDL statement on the selected table, in some cases first statement could be left hanging…
Post: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
…BY s_nationkey, l_shipmode; +—-+————-+———-+——-+—————+—————+———+————————-+——-+———-+————-+ | id | select_type | table | type | possible_keys | key | key…costly in comparison. Block Nested Loop Join would perform better than…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 4 Comments
…, MySQL always uses Nested Loop Join to join two or more tables. What this means is that, select rows from first table… as probe step. This join algorithm works best for highly selective queries, and obviously when the first operand used in the… read more about these variables here. The query used is: select l_orderkey, sum(l_extendedprice * (1 – l_discount)) as revenue…
Comment: Mass killing of MySQL Connections
May 21, 2009 By 30 Comments
… select_cursor; cursor_loop: LOOP FETCH select_cursor INTO query_id; IF iteration_complete THEN LEAVE cursor_loop; END IF; KILL QUERY query_id; END LOOP; CLOSE select_cursor…
Post: kernel_mutex problem. Or double throughput with single variable
December 2, 2011 By 17 Comments
… InnoDB starts the transaction, usually there is loop through ALL active transactions, and this loop is inside kernel_mutex. That is to… transactions. For this we will take sysbench running only simple select PK queries against 48 tables, 5,000,000 rows each… complex handling with spin loops, and there are two variables that affects mutex loops: innodb_sync_spin_loops and innodb_spin_wait_delay…
Post: Why MySQL could be slow with large tables ?
June 9, 2006 By 141 Comments
… remember if indexes are helpful or not depends on index selectivity – how large proportion of rows matches to particular index value… for full table scan vs range scan by index: mysql> select count(pad) from large; +————+ | count(pad) | +————+ | 31457280 | +————+ 1 row in… to large tables Joining of large data sets using nested loops is very expensive. Try to avoid it. Joins to smaller…
Comment: Getting around optimizer limitations with an IN() list
February 21, 2010 By 9 Comments
Spatial search = Postgres. SELECT count(*) FROM archive_data; count ——— 3063044 EXPLAIN ANALYZE SELECT count(*) FROM archive_data WHERE coords && ‘(45.64870078164,4… width=0) (actual time=3.197..5.828 rows=11686 loops=1) Recheck Cond: (coords && ‘(45.68463321836,4.8423759421357),(45.64870078164…
Comment: Edge-case behavior of INSERT...ODKU
October 28, 2012 By 6 Comments
… up a number of rows to be inserted / updated, then selecting out the current values from the table into a local…,NOW()), (‘foo2′, 3, NOW(), ‘foo3′, 3, NOW())) For the update, loop through the update array and create an “update case statement… usernames which you have inserted, then you repeat the first select which got out all the usernames requested from the original…
Post: mk-query-digest, query comments and the query cache
… then be cross-referenced between MySQL logs and Apache logs. select 1 — File: myfile.php Line: 25 Function: myFunc SessionID: a1b2c3d4e5f6a7b8c9d0aef… a simple test script to demonstrate the difference. The test loops twice over a set of query patterns. Each pattern is… with # and — comments at the start of a query(before SELECT), and in some cases, with varying white-space. These issues…