…, while in our original tests we used a much larger one, 16 tables with 5M rows each (about 23GB). So it makes… –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-ranges… –mysql-user=root –mysql-db=sbtest8t1M –mysql-table-engine=INNODB –mysql-socket=/tmp/mysql.sock –oltp-point-selects=9 –oltp-simple-ranges=0 –oltp-sum-ranges…
Post: Benchmarking Percona Server TokuDB vs InnoDB
May 7, 2013 By 32 Comments
…r100, sbtest$I_r1000, with roll-up sum for 10, 100, 1000 records in …easily fill TokuDB tables with 1bln of rows on this SSD, and projected InnoDB… quite concerning, and it might be a problem for some users. Now, I…_threads = 1 myisam_recover socket=/var/lib/mysql/mysql.sock user=root skip-grant-tables…
Post: Researching your MySQL table sizes
March 17, 2008 By 28 Comments
… query to find largest tables last month and it got a good response. Today I needed little modifications to that query…, rows, total data in index size for given MySQL Instance SELECT count(*) tables, concat(round(sum(table_rows)/1000000,2),’M') rows, concat(round(sum(data…_schema | rows | data | idx | total_size | idxfrac | +——–+——————–+——-+——-+——-+————+———+ | 48 | cacti | 0.01M | 0.00G | 0.00G | 0.00G | 0.72 | | 17 | mysql | 0…
Post: The case for getting rid of duplicate “sets”
May 17, 2011 By 2 Comments
…) mysql> select count(*) from ex1; +———-+ | count(*) | +———-+ | 73027220 | +———-+ 1 row in set (0.00 sec) mysql> select sum(val) from ex1; +———–+ | sum(val) | +———–+ | 871537665 | +———–+ 1 row in… | +—–+——————–+ 4 rows in set (39.82 sec) We can also check to see if any two numbers sum to zero: mysql> select a.val, b.val from ex2 a,ex2 b where a.val + b.val = 0; +—–+—–+ | val | val | +—–+—–+ | 0 | 0 | +—–+—–+ 1 row…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 5 Comments
…are cases when MySQL will allocate a lot of memory…mysql> select sum(data_length+index_length) from information_schema.tables where engine=’memory’; +——————————-+ | sum(data_length+index_length) | +——————————-+ | 126984 | +——————————-+ 1 row…
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 (0.18 sec) The query consumed 0.18 seconds. Where did the time go? mysql> SHOW PROFILE… the SUM(DURATION) from the query’s real response time. If there were, I could add in a UNION to inject another row…
Post: Using any general purpose computer as a special purpose SIMD computer
May 16, 2011 By 5 Comments
… with this. — AGGREGATION SQL: SELECT `word`,`md5(word)`,SUM(`count(*)`) AS `count(*)` FROM `aggregation_tmp_27656998` GROUP … such a degree. mysql> select min(id),max(id) from words; +———+———+ | min(id) | max(id) | +———+———+ | 1 | 3088896 | +———+———+ 1 row …
Post: Checking the subset sum set problem with set processing
May 16, 2011 By Leave a Comment
Hi, Here is an easy way to run the subset sum check from SQL, which you can then distribute with Shard… | 35417088 | +—–+———-+ 8 rows in set (20.47 sec) Now insert a value which will cause our check to pass: mysql> insert into data (val) values (16); Query OK, 1 row affected…
Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark
March 11, 2013 By 7 Comments
… having innodb_old_blocks_time=1000 appears to cause a major performance regression. The new setting changes which …quantity < 25\G *************************** 1. row *************************** revenue: 446268068091 1 row in set (22.54 sec) mysql> explain select straight_join sum(lo_extendedprice*…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
… directly from SQL. Instead, Flexviews includes a MySQL stored procedure API, called the SQL_…mysql> select sum(total_lines) from complete_example2; +——————+ | sum(total_lines) | +——————+ | 155187034 | <-- too high +------------------+ 1 row in set (0.68 sec) mysql> select sum…
