… consists of four sets of queries. Each set is known as a “flight”. I have labeled each query as Q{FLIGHT_NUMBER}.{QUERY_NUMBER… better when a larger number of rows must be joined together (Q4.1, Q4.2) but worse when small amounts of data are examined… [mysqld] datadir=/mnt/mysql56/data basedir=/usr/local/mysql socket=/var/lib/mysql/mysql.sock user=justin innodb_buffer_pool_size=64G innodb…
Post: Implementing SchemaSpy in your MySQL environment
June 4, 2013 By 2 Comments
… Percona Server 5.6.10 sandbox: description=MySQL driver=com.mysql.jdbc.Driver connectionSpec=jdbc:mysql://127.0.0.1:5610/schemaspy driverPath… is the only component of the tool. There are additional tabs: Tables – Names, number of children, parents, count of columns, row counts, and comments — a…
Post: Choosing a MySQL HA Solution - Post-Webinar Q&A
June 7, 2013 By Leave a Comment
…safe bet that MySQL is up and running. That said, there are plenty of other MySQL and OS-…, if you see a large number of long-running queries in the output of SHOW PROCESSLIST that might be…of “bad file descriptor” errors. I was able to get the server up with –skip-grant-tables and insert a few rows…
Post: MySQL Query Patterns, Optimized - Webinar questions followup
June 3, 2013 By 2 Comments
… a presentation on “MySQL Query Patterns, Optimized” for Percona MySQL Webinars. If you missed it…gap of id’s between 47000 and 50000 with no movies, but your random number … solution for randomly choosing rows involves some compromise, either of performance, or of accuracy of randomness. The ORDER…
Post: Understanding the maximum number of columns in a MySQL table
April 8, 2013 By 5 Comments
… be two sets of polls: “What is the maximum number of columns in MySQL?” and “What is the minimum maximum number of columns in MySQL?”. Before you… into play around the maximum number of columns is this: /* Hack to avoid bugs with small static rows in MySQL */ reclength=max(file->min… working out the maximum number of columns in a MySQL table. So what’s the maximum number of columns in a MySQL table? Well.. I tried…
Post: Is Synchronous Replication right for your app?
May 14, 2013 By 18 Comments
… they don’t touch the same row. Row locks are held for modifications until … MySQL replication from this instance, since MySQL replication is asynchronous. What about semi-sync MySQL replication… However, each autocommit is retried only the number of times specified by this variable (default is …
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 results in…much larger one, 16 tables with 5M rows each (about 23GB). So it makes sense… –percentile=99 –mysql-user=root –mysql-db=sbtest8t1M –mysql-table-engine=INNODB –mysql-socket=/tmp/mysql.sock –oltp-point-…
Post: Virident vCache vs. FlashCache: Part 2
May 17, 2013 By 3 Comments
… intervals. All tests were conducted with approximately 78GiB of data (32 tables, 10M rows each) and a 4GiB buffer pool. The cache… test run. With that out of the way, let’s look at some numbers. vCache vs. vCache – MySQL parameter testing The first test was designed to look solely at vCache performance under some different sets of MySQL…
Post: How to find MySQL queries worth optimizing ?
September 11, 2012 By 6 Comments
… had to examine 10Mil rows to produce result. What would be good scenario ? – query examining same amount of rows as they end up… only actual rows which are found and returned up to the top level MySQL part for processing are counted the Rows_examined remains… how many rows query sends after group by, distinct and aggregate functions are removed (A) – look at number of rows examined divided by number of tables…
Post: MySQL 5.6.10 Optimizer Limitations: Index Condition Pushdown
March 14, 2013 By 2 Comments
… ref: const rows: 10259274 Extra: Using index condition 1 row in set (0.00 sec) With ICP, the actual number of rows read at SQL layer is actually very different from the “rows” value seen above. This is because the second part of… features of MySQL 5.6, and actually is a great improvement over MySQL 5.5. For example, in this case, the actual number of “Handler…
