…in the wsrep_provider_options variable. Accepted values are in the range [0, 255] (inclusive). Quorum is computed using weighted sum… script would hang in case the MySQL server on …In case CREATE TABLE AS SELECT statement was running in parallel with the DDL statement on the selected table, in…
Post: More on MySQL transaction descriptors optimization
April 26, 2013 By Leave a Comment
…the perfect case for read-only transaction optimization in MySQL 5.6, because all SELECT queries in the AUTOCOMMIT mode are, by definition, …99 –mysql-user=root –mysql-db=sbtest8t1M –mysql-table-engine=INNODB –mysql-socket=/tmp/mysql.sock –oltp-point-selects=1 –oltp-simple-ranges=0 –oltp-sum-…
Post: Benchmarking Percona Server TokuDB vs InnoDB
May 7, 2013 By 32 Comments
…$I_r1000, with roll-up sum for 10, 100, 1000 records in the main table. and transactions …` is low selectivity is not good for fast inserts, but it is suitable for range selects by `id`….myisam_repair_threads = 1 myisam_recover socket=/var/lib/mysql/mysql.sock user=root skip-grant-tables TokuDB-…
Post: Optimizing repeated subexpressions in MySQL
January 23, 2009 By 11 Comments
…MySQL…sum := (select sum(rental_id) from sakila.rental), 1), pow(@sum, 2), pow(@sum, 3), pow(@sum, 4), pow(@sum, 5), pow(@sum, 6), pow(@sum, 7), pow(@sum…sum, 11), pow(@sum, 12), pow(@sum, 13), pow(@sum, 14), pow(@sum, 15), pow(@sum, 16), pow(@sum, 17), pow(@sum, 18); This query runs 500 iterations in…
Post: A micro-benchmark of stored routines in MySQL
August 16, 2009 By 7 Comments
… not in MySQL 5.1 and earlier. I loaded the World sample database and cooked up this query: select sql_no_cache sum(ci…: mysql> select sql_no_cache sum(ci.Population) from City as ci where speaks_english(CountryCode) > 0; +——————–+ | sum(ci.Population) | +——————–+ | 237134840 | +——————–+ 1 row in set…
Post: How to convert MySQL's SHOW PROFILES into a real profile
February 20, 2012 By 12 Comments
… to demonstrate: mysql> SET profiling=1; mysql> pager cat > /dev/null mysql> SELECT * FROM nicer_but_slower_film_list; 997 rows in set (…this partially: SET @query_id := 1; SELECT STATE, SUM(DURATION) AS Total_R, ROUND( 100 * SUM(DURATION) / (SELECT SUM(DURATION) FROM INFORMATION_SCHEMA.PROFILING …
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 5 Comments
… tables: mysql> select sum(data_length+index_length) from information_schema.tables where engine=’memory’; +——————————-+ | sum(data_length+index_length) | +——————————-+ | 126984 | +——————————-+ 1 row in set…: mysql> select sum(data_length+index_length) from information_schema.global_temporary_tables where engine=’memory’; +——————————-+ | sum(data_length+index_length) | +——————————-+ | 126984 | +——————————-+ 1 row in…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… to turn this off. The query used is: select c_custkey, c_name, sum(l_extendedprice * (1 – l_discount)) as revenue, c… in MariaDB 5.5 and not in MySQL 5.6, is that because of a bug in MySQL 5.6 code? As MRR was used in both MySQL 5.6…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 4 Comments
… available in both MySQL 5.6 and MariaDB 5.5. You can read more about BKA in MySQL 5.6 here and BKA in MariaDB 5.5 here. However, MariaDB 5.5 has one additional optimization that is used in… variables in case of MariaDB 5.5. You can read more about these variables here. The query used is: select l_orderkey, sum…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
I have been working with Peter in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a look at and benchmarking optimizer enhancements one by one. So in… new optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5 Now let’s take…
