… joins with few tables, – it is not clear how far from optimal plans do we get by using a greedy search. From the same discussion we can learn how automatic selection works – it picks value of min(number of tables… somewhat conflicting though as if we assume MySQL users do not join lots of tables (less than 7) when using 0 as…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a look at and benchmarking optimizer… blog post is aimed at a new optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5 Now let’s take a look briefly at what this…
Comment: Joining many tables in MySQL - optimizer_search_depth
April 23, 2012 By 6 Comments
… ago, when I was just hired at MySQL. The assumptions about what are typical MySQL may have been true then in 2004… experiments to determine what is a reasonable limit for the search depth (and the total number of tables) that can be optimized in… to plan pruning in MySQL 5.6. Ideally, the server should analyze the query and should determine the search depth based on…
Comment: InnoDB's gap locks
April 13, 2012 By 15 Comments
… mysql> show create table t\G *************************** 1. row *************************** Table: t Create Table: CREATE TABLE `t` (…if you’re doing a simple SELECT. session1> select * from t; +——+——–+ | i | … Now go back to session1 and try to search the row with the value “…
Post: Scaling problems still exist in MySQL 5.5 and Percona Server 5.5
May 20, 2011 By 11 Comments
…even for read only workloads. Workloads which got a lot of attention such as Sysbench and … The query looks something like this: SELECT * FROM table WHERE deleted = 0 AND group_id IN (62715996, …search_to_nth_level,btr_p cur_open_with_no_init_func,row_sel_get_clust_rec_for_mysql,row_search_for_mysql…
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
…. And besides, most applications have a lot of the same components. Tagging, friends, queues, click tracking, search, paginated displays — we’ve seen… itself OK, so here’s a very basic audit, whose scope I’ll keep within just a MySQL server. The first step is… this.) There are a bunch of other things I’ll look at, including checking for default users in the mysql.* tables, running mk…
Post: BoardReader - Forum Search Engine
November 29, 2006 By 12 Comments
… it to Search MySQL Forums Notice simple link structure – you can replace mysql.com in it with any other domain to search forums from that domain. For example you can use this link to search our MySQL Performance Forums Second… is too much data to group and quite a hassle to build summary tables as we want to count uniques, but it…
Post: Using Flexviews - part two, change data capture
March 25, 2011 By 11 Comments
… purpose of FlexCDC). Feed search engines like Sphinx or…a dedicated MySQL slave which has log_slave_updates=1 and binlog_format=row to process SBR changes from a MySQL…a moment. Lets create a table, insert some rows, add a change log, delete rows and then insert some more of them: mysql> create table…
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
… easy and familiar for me to use the example of search engine marketing. I’ll do that throughout this article. Requirements…/XtraDB tables… Optimize For I/O It is pretty much inevitable: if you do this kind of data processing in MySQL, you… tables in a complex query with many joins. I have never seen this approach scale well. Use The Best Technologies You Can MySQL…
Post: Percona's Sessions at the O'Reilly MySQL Conference and Expo
April 7, 2011 By Leave a Comment
…MySQL The Aspersa System Administrator’s Toolkit Percona Server and XtraBackup: painless operations Forecasting MySQL Performance and Scalability Summary tables, aggregate tables…MySQL 5.5 Performance Schema Performance Best Practices for MySQL NoSQL with MySQL Using Sphinx Beyond Full Text Search…
