…mechanism, unlike anything you find in standard async MySQL replication. It is my belief that the lack…transactions are copied to all nodes and global ordering is established, but apply and commit is …that helps single node writing in PXC to perform better. gcs.fc_factor If fc_limit controls…
Post: The write cache: Swap insanity tome III
April 19, 2013 By 5 Comments
Swapping has always been something bad for MySQL performance but it is even more important for HA systems. It …, you need to interleave the allocation of memory for the MySQL process using the numactl utility, drop the file cache and… up is strange, unexpected states. What was I missing? In order to figure out, I started of data gathering metrics like…
Post: MySQL Performance - eliminating ORDER BY function
October 17, 2007 By 6 Comments
… rules you would learn about MySQL Performance Optimization is to avoid using functions when comparing constants or order by. Ie use indexed_col… set (0.00 sec) mysql> explain select * from tst where i=5 and date(d)=date(now()) order by d \G *************************** 1… this would be possible: mysql> explain select * from tst where i=5 and date(d)=date(now()) order by unix_timestamp(date…
Post: MySQL performance on EC2/EBS versus RDS
August 4, 2011 By 11 Comments
… overview of these two approaches to hosting MySQL in the Amazon cloud. In general, MySQL performance overall on EC2 and EBS isn’t… contention. The performance improvements I got with Percona Server were similar to the performance improvements I saw versus stock MySQL 5.1. This… huge performance difference between EC2+EBS+MySQL versus RDS, in most cases, unless you use Percona Server; but it’s still not orders…
Post: MySQL performance: Impact of memory allocators (Part 2)
March 8, 2013 By 12 Comments
… different memory allocators and what difference we will see in MySQL performance in this scenario? Let me share a conclusion first: If…_RO test consists of 5 select queries – select_ranges, select_order_ranges, select_distinct_ranges, select_sum_ranges, point_select. Processing… not by MySQL. Notes regarding jemalloc version I’ve used in my tests: I’ve noted notable impact on MySQL performance after version…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
…_id,order_id) in addition to index on (vendor_id) alone. The cases when you really should worry about performance impact of… “`id` = 5″ and “`id` IN (5)” regarding indexes and performance? A: Recent MySQL versions are smart enough to convert id IN (5…. This works because by scanning data in index order MySQL gets data in already sorted order and looks at “one group at the…
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 100 Comments
… ORDER BY implementation, especially together with LIMIT is often the cause of MySQL Performance problems. Here is what you need to know about ORDER BY … LIMIT optimization to avoid these problems ORDER BY with…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
… about 40 minutes to build Deleted 100 order lines. Refreshed both views. Compared the refresh performance and query results. The view type… functions. Now I delete some line items from orders: mysql> delete -> from order_lines -> where order_id -> between 1 and 100 -> limit 500; Query…
Post: Implications of Metadata Locking Changes in MySQL 5.5
February 1, 2013 By 9 Comments
… prior to MySQL 5.5.3 Metadata Locking behavior prior to MySQL 5.5.3 Prior to MySQL 5….from test_2 order by id; session8 > select * from test_2 order by id; session9 > select * from test_2 order by id… You can see that no table open operation was performed and hence no wait. Does the fact that …
Post: Eventual Consistency in MySQL
November 18, 2011 By 8 Comments
… that the enforcement of foreign keys incurs a significant performance overhead.1,2 MySQL allows us to set FOREIGN_KEY_CHECKS=0… = ‘fk_lineitems’, TABLE_SCHEMA = ‘ecommerce’, TABLE_NAME = ‘LineItems’, COLUMN_NAME = ‘order_id’, ORDINAL_POSITION = 1, POSITION_IN_UNIQUE_CONSTRAINT = 1, REFERENCED…
