… 5 seconds even though in the read less than 1000 rows and doing it completely in memory. The plan optimizer picked… number of tables. I was wondering if 0 is automatic selection why do we have value of 62 being default in MySQL… learn how automatic selection works – it picks value of min(number of tables, 7) essentially limiting search depth to no more…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… that MySQL 5.6 and MariaDB 5.5 are both reading more rows than MySQL 5.5, as can be seen by the numbers reported for the status counter Innodb_rows_read. While MySQL 5.6 is also reporting increased numbers for the counter…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
… Traditionally, MySQL always uses Nested Loop Join to join two or more tables. What this means is that, select rows from first… you have a small buffer pool and a very large number of rows from table2 that do not fit into the buffer…. However, there is one number in MariaDB 5.5 that is quite large as compared to MySQL 5.6 and that is…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a… optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5 Now let’s take…
Post: Modeling MySQL Capacity by Measuring Resource Consumptions
March 8, 2011 By 1 Comment
… restrictions, such as row level locks. There is also a load aspect – “random arrivals” tell us the number of work system has… ? You can take a look at procfs for MySQL process: root@ubuntu:/var/log/mysql# cat /proc/19018/stat 19018 (mysqld) S… IO (which you can see separately through number of IOPS) but it also can be row level locks, etc. The ratio between…
Post: Why MySQL could be slow with large tables ?
June 9, 2006 By 139 Comments
… probably heard complains about MySQL being unable to handle more than 1.000.000 (or select any other number) rows by some of the users. On other hand it is well known with customers like Google, Yahoo, LiveJournal,Technocarati MySQL… by index: mysql> select count(pad) from large; +————+ | count(pad) | +————+ | 31457280 | +————+ 1 row in set (4 min 58.63 sec) mysql> select count…
Post: Scaling problems still exist in MySQL 5.5 and Percona Server 5.5
May 20, 2011 By 11 Comments
… times better performance or 150%, which is a great headline numbers. If we look at more details however we can see…_p cur_open_with_no_init_func,row_sel_get_clust_rec_for_mysql,row_search_for_mysql,ha_innobase::index_read,handler::read…_p cur_open_with_no_init_func,row_sel_get_clust_rec_for_mysql,row_search_for_mysql,ha_innobase::general_fetch,handler::read…
Post: Dynamic row format for MEMORY tables
September 6, 2011 By 3 Comments
… in the MEMORY storage engine (also known as HEAP) in MySQL has been one of the long-standing limitations caused by… for both row formats. (Speaking very strictly, the VM Peak value is not comparable due to slightly different number of rows inserted). In the second test we check if the dynamic row format delivers the memory…
Post: Should we give a MySQL Query Cache a second chance ?
April 10, 2011 By 63 Comments
… rather large set of applications. Many applications in MySQL have very large number of queries doing updates through primary key and use… unsure about though as MySQL Query Cache is most useful for complex expensive queries, for which tracking exact rows used from query syntax may be hard. Query Cache Storage Engines MySQL Query Cache now stores queries…
Post: When EXPLAIN estimates can go wrong!
October 13, 2011 By 7 Comments
… was totally off. The actual number of rows was 18 times more than the number of rows reported by MySQL in the output of EXPLAIN. Now this can be a real pain as MySQL uses “the number of rows… “number of rows estimation”, because page sampling is used to generate index cardinality estimates which are then in turn used by MySQL to…
