… gave a presentation on “MySQL Query Patterns, Optimized” for Percona MySQL Webinars. If you missed it, you… range 1..MAX(id)? Yes, there’s a risk if you have irregular distribution of matching rows that… and 50000 with no movies, but your random number generator picks values in that range with equal…
Post: Understanding the maximum number of columns in a MySQL table
April 8, 2013 By 5 Comments
…: “What is the maximum number of columns in MySQL?” and “What is the minimum maximum number of columns in MySQL?”. Before you read on… play around the maximum number of columns is this: /* Hack to avoid bugs with small static rows in MySQL */ reclength=max(file->min_record… out the maximum number of columns in a MySQL table. So what’s the maximum number of columns in a MySQL table? Well.. I…
Post: More on MySQL transaction descriptors optimization
April 26, 2013 By Leave a Comment
…Dimitri Kravchuk, we have received a large number of questions on why the benchmark … Dimitri was using 8 tables with 1M rows each (about 2.1GB) for his tests…max-time=120 –max-requests=0 –percentile=99 –mysql-user=root –mysql-db=sbtest8t1M –mysql-table-engine=INNODB –mysql-socket=/tmp/mysql…
Post: Virident vCache vs. FlashCache: Part 2
May 17, 2013 By 3 Comments
… 78GiB of data (32 tables, 10M rows each) and a 4GiB buffer pool….let’s look at some numbers. vCache vs. vCache – MySQL parameter testing The first test …shutdown=1 –max-time=7200 –max-requests=0 –percentile=95 \ –mysql-user=root –mysql-socket=/tmp/mysql.sock –mysql-table-engine=…
Post: MySQL 5.5 and MySQL 5.6 default variable values differences
February 21, 2013 By 8 Comments
…MAX_THREAD_INSTANCES | 1000 | 224 | | SOCKET | /tmp/mysql_sandbox5530.sock | /tmp/mysql… 56 rows…number of different variables which would make more sense in my opinion. MySQL…
Post: Modeling MySQL Capacity by Measuring Resource Consumptions
March 8, 2011 By 1 Comment
…look at procfs for MySQL process: root@ubuntu:/var/log/mysql# cat /proc/… separately through number of IOPS) but it also can be row level locks…QPS, 0x concurrency ________________ # Attribute total min max avg 95% stddev median # ============ ======= ======= ======= ======= ======= ======= ======= # …
Post: Recovery deleted ibdata1
August 10, 2012 By 9 Comments
… evaluation benchmark No DB drivers specified, using mysql Running the test with following options: Number of threads: 16 Doing OLTP test. Running… are flushed: — LOG — Log sequence number 363096003 Log flushed up to 363096003 Last checkpoint at 363096003 Max checkpoint age 7782360 To speed… dirty pages percentage to zero: mysql> set global innodb_max_dirty_pages_pct=0; Query OK, 0 rows affected (0.01 sec) It…
Post: Is there room for more MySQL IO Optimization?
January 3, 2013 By 11 Comments
… *************************** 1. row *************************** Name: post_channel Engine: InnoDB Version: 10 Row_format: Compact Rows: 278987416 Avg_row_length: 75 Data_length: 20975714304 Max_…of untapped potential in MySQL IO as I think we should be getting very close numbers for buffered and …
Post: Is MySQL 5.6 slower than MySQL 5.5?
February 18, 2013 By 23 Comments
… especially for simple queries. I ran small (1M rows) benchmark so data was well fitting in …=on –max-requests=0 –max-time=300 run For single thread MySQL 5.5 is 11% faster than MySQL 5….contention even though MySQL 5.6 is positioned as improving scalability over MySQL 5.5. Also number of expected…
Post: InnoDB compression woes
May 20, 2011 By 14 Comments
…tables: 38278 sec. Compressed tables are create as: ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8, with …=on –num-threads=16 –max-requests=0 –rand-type=uniform –max-time=1800 –mysql-user=root –report-interval=… utilized 85% and this is quite decent number. With compressed tables CPU utilization is 7%. …
