On Friday I gave a presentation on “MySQL Query Patterns, Optimized” for Percona MySQL Webinars. If you missed it, you can still… form. Q: Doesn’t the primary key solution for random selection only work when the IDs for movies are distributed uniformly…_id. It was more efficient in this case to force MySQL to scan the `title` table first, grouping by kind_id in…
Post: Migrating between MySQL schemas with Percona Xtrabackup
June 10, 2013 By 2 Comments
…. One of the downsides to mysqldump is the need to scan the full tables and in turn, load that data into… steps 7 and 10 in two scripts: mysql -N -B <<'EOF' > discard-ddl.sql SELECT CONCAT(‘ALTER TABLE `’, table_name, ‘` DISCARD TABLESPACE… TABLE_SCHEMA=’orig’ AND ENGINE=’InnoDB’; EOF mysql -N -B <<'EOF' > import-ddl.sql SELECT CONCAT(‘ALTER TABLE `’, table_name, ‘` IMPORT TABLESPACE…
Post: trx descriptors: MySQL performance improvements in Percona Server 5.5.30-30.2
April 12, 2013 By 3 Comments
… created for each SELECT, even within the same transaction. Why is that a major problem for MySQL scalability? The scan is performed under… kinds of transactions: MySQL transactions started with START TRANSACTION READ ONLY MySQL transactions created by a non-locking SELECT statement in the autocommit…
Post: More on MySQL transaction descriptors optimization
April 26, 2013 By Leave a Comment
… SELECTs are read-only transactions, but it doesn’t help much, because e.g. for 1024 concurrent threads each SELECT has to scan…-requests=0 –percentile=99 –mysql-user=root –mysql-db=sbtest8t1M –mysql-table-engine=INNODB –mysql-socket=/tmp/mysql.sock –oltp-point-selects=1 –oltp-simple-ranges…
Post: Memory allocators: MySQL performance improvements in Percona Server 5.5.30-30.2
April 15, 2013 By
… have run sysbench POINT_SELECT test for glibc and jemalloc allocators. Observations: = MySQL 5.5.30 – throughput of MySQL 5.5.30 with… helps MySQL 5.5.30 to fix issues with malloc scalability but still scanning of the transaction list causes performance drop = MySQL 5.6.10 – in autocommit mode 5.6.10 has no problem with transaction list scanning (due to the…
Comment: MySQL Partitioning - can save you or kill you
April 30, 2013 By 45 Comments
… a single connection) anywhere in any ‘free’ variant of MySQL. PARTITIONs are scanned one at a time. (Please provide specific references if you find otherwise.) I would argue that single-row queries (SELECT… PARTITIONing. I go into more details (and code) here: http://mysql.rjweb.org/doc.php/partitionmaint The third use case for…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
… referring to the amazing MySQL manual, especially the option and variable reference table….privblogpercona.commanual Select_full_joinblogpercona.commanual Select_full_range_joinblogpercona.commanual Select_rangeblogpercona.commanual Select_range_checkblogpercona.commanual Select_scanblogpercona….
Post: Eventual Consistency in MySQL
November 18, 2011 By 8 Comments
… too high. But if you later restore enforcement, MySQL does not immediately scan all your data to verify that you haven’t… generate an exclusion-join query for each foreign key relationship: mysql> SELECT CONCAT( ‘SELECT ‘, GROUP_CONCAT(DISTINCT CONCAT(K.CONSTRAINT_NAME, ‘.’, P.COLUMN…); mysql> SET FOREIGN_KEY_CHECKS=0; mysql> INSERT INTO Bar (ID,X,Y) VALUES (42,555,666); — THIS IS AN ORPHAN mysql> SELECT Bar…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 4 Comments
… Access Traditionally, MySQL always uses Nested Loop Join to join two or more tables. What this means is that, select rows from… a look at the status counters. Counter Name MySQL 5.5 MySQL 5.6 MySQL 5.6 w/ join_buffer_size=6M & read…_read 4127318 6718351 6611675 6707882 5479445 5527245 Select_scan 1 1 1 1 1 1 Sort_scan 1 1 1 1 1 1…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… on IO bound workload, mentioned above. Counter Name MySQL 5.5 MySQL 5.6 MySQL 5.6 w/ read_rnd_bufer_size=4M…_rows_read 799239 914146 912318 914146 912318 Select_scan 1 1 1 1 1 Sort_scan 1 1 1 1 1 As you… MRR range scan is performed, but we can see its only incremented in MariaDB 5.5 and not in MySQL 5.6…
