…no # Tmp table 100% yes, 0% no # Tmp table on 100% yes, 0% no # String: # Databases…could be to instead of selecting every column from all the tables involved in the query, probably selecting only the needed columns which could…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
Post: Why MySQL could be slow with large tables ?
June 9, 2006 By 141 Comments
…database. Some people would also remember if indexes are helpful or not depends on index selectivity… by indexes also could be located sequentially…MySQL single query runs as single thread (with exeption of MySQL Cluster) and MySQL issues IO requests one by one for query…so there is no random IO needed…
Post: Modeling MySQL Capacity by Measuring Resource Consumptions
March 8, 2011 By 1 Comment
…Databases # Hosts localhost # InnoDB trxID 3BBF3B55 (1/0%), 3BBF3B5A (1/0%)… 1753695 more # Last errno 0 # Users user # Query…query which analyzes no more than 1 row may take up to 8 io requests (could…
Post: Upgrading MySQL
January 5, 2010 By 24 Comments
…so you can run MySQL 5.0 database with MySQL 5.1 with no problems in most cases…MySQL 4.1 or changing JOIN evaluation in 5.0 (which could both cause different result set or make query not runnable in MySQL…. The tool will run SELECT queries on both new and old MySQL installations and check result…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 5 Comments
…not much of the problem in MySQL 3.23 when there would only handful of places where memory could… on server. There is no comparable variables of how many… as you can query temporary tables too: mysql> select sum(data_length…Free buffers 1 Database pages 8252672 Old database pages 3046376 Modified …
Post: Checking for a live database connection considered harmful
May 5, 2010 By 38 Comments
…MySQL protocol level, which will increment Com_admin_commands in SHOW GLOBAL STATUS, or a trivial query such as ‘SELECT…could have nearly halved the load on this database server. That’s not unusual! When the application’s queries are long, the extra query…
Post: How to convert MySQL's SHOW PROFILES into a real profile
February 20, 2012 By 12 Comments
… Sakila sample database to demonstrate: mysql> SET profiling=1; mysql> pager cat > /dev/null mysql> SELECT * …not accounted by the profiling). That is, the SUM(DURATION) isn’t the same as the total query duration. Alas, there is no query…from the query‘s real response time. If there were, I could add …
Post: Shard-Query adds parallelism to queries
November 15, 2010 By 18 Comments
… are not open source. In the future, Shard-Query can be extended to other database servers such… data set, so this means that if MySQL could fully utilize all cores for my workload, …query reads one year, and the last query reads 21 years of collected flight data. For example (the final query): select…
Post: Distributed Set Processing with Shard-Query
May 14, 2011 By 27 Comments
…database what you want but not how to get it. Most distributed engines work with rows. They break the queries…queries over time. I realised that I could apply these same mathematical concepts to distributed queries…query processing. There is almost no… MySQL … — AGGREGATION SQL: SELECT `origin_airport…
Post: The Optimization That (Often) Isn't: Index Merge Intersection
December 14, 2012 By 6 Comments
…MySQL could only use one index per table in a given query …MySQL could sometimes make use of the multiple indexes. For instance, “SELECT …intersection=off”; This is the database equivalent of kicking the … on a query-by-query basis, so there’s no need… indexes in every query they write (not that I have …
