June 20, 2013

Post: Why you should ignore MySQL's key cache hit ratio

February 28, 2010 By 28 Comments

…_requests. [Note: their use of 'rate' doesn't match mine; I would call this the 'miss ratio']. … in MySQL to guide your decisions. It is also not the be-all and end-all of MySQL performance, … wondering, what about InnoDB tuning? What is the best way to choose an innodb_buffer_pool_size setting…

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

Post: Interesting MySQL and PostgreSQL Benchmarks

November 30, 2006 By 20 Comments

… number of threads about matches number of Cores/Threads and stays at this level at higher concurrency. MySQL with Innodb shows its… CPU comparison in this test. Woodcrest has best performance in this test (and in many other MySQL tests), Opteron comes second and older… do not have full disclosure. They however do match my own experience with MySQL so I tend to trust PostgreSQL data points…

Filed Under: Benchmarks Tagged With: , ,

Post: Investigating MySQL Replication Latency in Percona XtraDB Cluster

March 3, 2013 By 13 Comments

… with a little bit stale data but looking for the best performance possible. So far so good. We can make cluster to… is impacted: mysql> update sbtest2 set k=k+1; Query OK, 1000000 rows affected (1 min 14.12 sec) Rows matched: 1000000… different tables on the different nodes. Summary: Percona XtraDB Cluster performs very well when it comes to small transactions offering very…

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

Post: On Character Sets and Disappearing Tables

June 21, 2012 By 1 Comment

…in this blog post. When is a matching datatype not really a matching datatype? If you guessed that it …at all, and that we want to achieve the best performance possible, so we’re going to convert everything …think there’s something of an inconsistency present when MySQL won’t allow us to change an FK-…

Filed Under: Insight for DBAs, MySQL

Post: Computing 95 percentile in MySQL

November 28, 2008 By 5 Comments

…get the data if MySQL only provides you the avg: mysql> select count(*),avg(wtime) from performance_log_081128 where …data what we need is number of rows which matches for given query type. Dividing the count by … much worse problems. You do not want your best client to suffer for example, even if he …

Filed Under: Insight for Developers Tagged With:

Post: Fighting MySQL Replication Lag

September 22, 2008 By 7 Comments

…”%cheap rolex%”; This query will perform full table scan in MySQL 5.0 (even if there…matched on the first place you should also use query chopping and run update in chunks if application allows it. In MySQL…up being extremely complicated query. It is best to avoid INSERT … SELECT going through …

Filed Under: Insight for DBAs Tagged With: ,

Post: Using GROUP BY WITH ROLLUP for Reporting Performance Optimization

September 17, 2007 By 14 Comments

… reporting you may be interested in best selling product or top sales people. …matches our where clause. Most Simple Way Number One is to simply run two queries: mysql…MySQL. Why am I looking on reporting performance optimization ? It is for ClickAider project which is growing rapidly and use of MySQL…

Filed Under: Benchmarks, Insight for Developers Tagged With:

Post: Using UNION to implement loose index scan in MySQL

August 10, 2006 By 15 Comments

…about MySQL Indexing, however very important for successfull MySQL Performance Optimization is understanding when exactly MySQL …MySQL will only be able to use multiple keyparts if first keyparts matched with “=”. Here is example: mysql… to do so. This strategy is best applied if no others work well. …

Filed Under: Insight for Developers Tagged With: ,

Post: The story of one MySQL Upgrade

October 8, 2010 By 8 Comments

… So replication was running properly but does data match ? (This also would cover data improperly loaded …from Percona Server 5.1 back to MySQL 5.0 and performed the same checks again – happily …setup being trivial. This was the best choice as with New MySQL version upgrade involves new Operating …

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

Post: Sphinx search performance optimization: multi-threaded search

January 16, 2013 By 10 Comments

… Sphinx query log and look at the number of results matched per query – this should give you a clue. Execution Let… following basic configuration (many irrelevant details omitted): source src1 { type = mysql sql_query = SELECT id, text FROM table } index idx1 { type… necessarily the best way to split data, you may want these to be ranges by using a helper table in MySQL to…

Filed Under: MySQL Tagged With: , , ,
« Older Posts
Newer Posts »