June 19, 2013

Post: XtraDB/InnoDB CPU bound benchmarks on 24cores server

December 29, 2008 By 6 Comments

…_table_size=64M max_prepared_stmt_count=1000000 query_cache_size=0 skip_grant_tables skip_locking sort_buffer_size=64K table_cache=2048 thread_cache… proud of. We definitely we will look how to fix it as next step, read detailed investigation what is the reason of performance drop…

Filed Under: Benchmarks Tagged With: ,

Post: Shard-Query EC2 images available

May 11, 2011 By 2 Comments

…is the complete InnoDB schema from one shard. The schema is duplicated 20 times (but not the ontime_fact data): DROP TABLE…TABLES symbolic-links table-definition-cache=16384 table-open-cache=128 thread-cache-size=32 thread-stack=256K tmp-table…

Filed Under: MySQL

Post: Knowing what pt-online-schema-change will do

November 1, 2012 By 1 Comment

…CACHE */ `guest_…is not the optimal index. The technical details behind the creation of the SQL statements for copying the table are beyond the…definitions?  If a column is defined as “NOT NULL” but the tool does “col IS NULL”, something is…what the tool will do.  Read and think carefully about the…

Filed Under: Insight for DBAs, Percona Software Tagged With:

Post: Finding your MySQL High-Availability solution – Replication

November 13, 2009 By 6 Comments

…what is the most popular HA solution for MySQL, replication. High Availability solution for MySQL: Replication This HA solution is the…caches warm up. Finally, it is well known that with MySQL, altering a table means recreating the whole table and it is…

Filed Under: Insight for DBAs, MySQL Tagged With: ,

Post: Flexviews - part 3 - improving query performance using materialized views

April 4, 2011 By 20 Comments

…The definition of the MV is stored internally, and represents the actual SQL that defines the view. A simple API call is made to associate the SQL definition with the…the orders, order_lines and customers tables directly (the tables on which dashboard_customer_sales is built), then the…

Filed Under: Insight for DBAs, MySQL

Post: InnoDB Full-text Search in MySQL 5.6 (part 1)

February 26, 2013 By 1 Comment

… of the way, let’s get to it. Here is our basic table definition for the DIR dataset. The table for the…20 16:09 FTS_00000000000000ad_BEING_DELETED_CACHE.ibd -rw-rw—-. 1 mysql mysql 98304…the table, run an OPTIMIZE or two, delete some data, and see what happens: mysql> set global innodb_ft_aux_table…

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: , , , , ,

Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact

March 12, 2012 By 12 Comments

I have been working with Peter in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5… Now let’s take a look briefly at what this enhancement actually is, and what is it aimed at. Index Condition Pushdown Traditional B… as range scans, where index parts after the part on which range condition is applied cannot be used for filtering records. For…

Filed Under: Benchmarks, Insight for DBAs, MySQL Tagged With: , , , , , , ,

Post: SHOW INNODB STATUS walk through

July 17, 2006 By 40 Comments

…Definition of foreign key which failed as well as the closest match which was located in parent table. There is… is reported which is basically what transaction is doing it…is displayed. For writes Innodb has to ensure data makes it to the disk – just passing it to OS cache is not enough. This is…

Filed Under: Insight for DBAs Tagged With:

Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5

March 21, 2012 By 10 Comments

…the secondary index definition can result in a lot of Random I/O. The purpose of MRR is…is > 0 then it means Handler_mrr_rowid_refills + 1 MRR range scans had to be performed. As in the table…the optimizer chooses the wrong query execution plan. Let’s take a look at what are the…

Filed Under: Benchmarks, Insight for DBAs, MySQL Tagged With: , , , , , , , , , , , , , ,

Post: What's required to tune MySQL?

July 27, 2011 By 4 Comments

…the variety and severity of problems we write about here. Examples include what happens when you have query cache…the word “tuning,” because database tuning is an activity that can be done endlessly, with little definition…

Filed Under: Insight for DBAs, MySQL
« Older Posts
Newer Posts »