…gave a presentation on “MySQL Query Patterns, Optimized” for Percona MySQL Webinars. If you missed it, …random selection only work when the IDs for movies are distributed uniformly over the range 1..MAX(id)?… movies, but your random number generator picks values in that range with equal frequency as …
Post: Benchmarking Percona Server TokuDB vs InnoDB
May 7, 2013 By 32 Comments
… INTO sbtest (hid, mid, id, k) VALUES ($HID, $MID, $ID, $K); INSERT INTO sbtest_…is low selectivity is not good for fast inserts, but it is suitable for range selects by …mysql-user=root –oltp-tables-count=32 –mysql_table_engine=tokudb –oltp_auto_inc=on –max-time=18000 –report-interval=10 –max…
Post: MySQL 5.5 and MySQL 5.6 default variable values differences
February 21, 2013 By 8 Comments
…the values from MySQL 5.5.30 and MySQL 5.6.10 to the different tables and ran the query: mysql [localhost] {msandbox} (test) > select…SIZE | 20 | 300 | | PERFORMANCE_SCHEMA_MAX_THREAD_INSTANCES | 1000 | 224 | | SOCKET | /tmp/mysql_sandbox5530.sock | /tmp/mysql_sandbox5610.sock | | INNODB_FILE_PER_…
Post: Using any general purpose computer as a special purpose SIMD computer
May 16, 2011 By 5 Comments
… DUPLICATE KEY UPDATE `word`=VALUES(`word`), `md5(word)`=VALUES(`md5(word)`), `count(*)`=`count(*)` + VALUES(`count(*)`) All six … support such a degree. mysql> select min(id),max(id) from words; +———+———+ | min(id) | max(id) | +———+———+ | 1 | 3088896 | +———+———+ …
Post: Efficient Boolean value storage for Innodb Tables
April 23, 2008 By 6 Comments
…: Fixed Rows: 2097152 Avg_row_length: 11 Data_length: 23068672 Max_data_length: 3096224743817215 Index_length: 1024 Data_free: 0 Auto… NULL as one of flag values means you can’t use normal “=” comparison operator with them: mysql> select count(*) from cbool where c1… not work) or you can use Null-Aware comparison operator: mysql> select count(*) from cbool where c1NULL; +———-+ | count(*) | +———-+ | 1048576 | +———-+ 1 row in…
Post: Percona Replication Manager, a solution for MySQL high availability with replication using Pacemaker
November 29, 2011 By 102 Comments
… target-role=”Started” ms ms_MySQL p_mysql \ meta master-max=”1″ master-node-max=”1″ clone-max=”3″ clone-node-max=”1″ notify=”true” globally… above user max_slave_lag: The maximum allowed slave lag in seconds, if a slave lags by more than that value, it… to test MySQL. Default is to run select count(*) on mysql.user table, so the user given should at least have select on that…
Post: Recovering Innodb table Corruption
July 4, 2008 By 22 Comments
… would need to find max PK which could be recovered and try some higher values mysql> select max(id) from test2; +———+ | max(id) | +———+ | 220 | +———+ 1 row in set (0.00 sec) mysql> insert ignore into test2 select * from test…
Post: Review of MySQL 5.6 Defaults Changes
October 8, 2012 By 10 Comments
… the value I’ve been recommending for a while. innodb_stats_on_metadata = off Yet another change I actively recommended max_connect… for value 0 which would mean limit disabled. Very few users actually need DOS prevention this variable strives to provide as MySQL is typically protected by Firewall to begin with. open_files_limit = 5000 strangely enough this is where the smart selection…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
…COUNT(*). mysql> select count(*) cnt from order_lines\G *************************** 1. row *************************** cnt: 155187034 1 row in set (32.03 sec) mysql> select sum…MIN/MAX/COUNT_DISTINCT, a secondary view will be built in the flexviews schema, transparently, to manage the distinct values…
Post: Modeling MySQL Capacity by Measuring Resource Consumptions
March 8, 2011 By 1 Comment
…Select. It is good to check it over certain intervals – some workloads will have this as a very stable value… for MySQL process: root@ubuntu:/var/log/mysql# cat…concurrency ________________ # Attribute total min max avg 95% stddev median # ============ ======= ======= ======= ======= ======= ======= …
