…. Other then that, I have also disabled query cache to avoid contention caused by query cache on write heavy workload. OK, so that was… with write performance is using partitioning to reduce the size of the B+tree, having multiple buffer pool instances to reduce contention problems caused by buffer pool mutexes, using “estimate” checkpoint method to reduce chances of log flush storms and disabling the query…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
… such as statement which is being executed, query id, query status etc. Next section you will see …commit=2 log writes are done to OS cache, and being sequential writes these logs writes are pretty fast. …buffer pool for new data – when previous page content was not read to the buffer pool. Finally…
Post: Heikki Tuuri answers to Innodb questions, Part II
November 4, 2007 By 13 Comments
… writes to the same page, then fewer disk writes are needed. But if your write … opened about multi-core scalability (concurrent queries, autoincrement, concurrent inserts, instrumentation, etc.). Rather…mutexes and contention? How does this fit into the development process? HT: Contention and thread …
Post: Percona XtraDB Cluster: Failure Scenarios with only 2 nodes
July 25, 2012 By 6 Comments
… master and slave IPS and will accept writes. But what could happen with Galera replication on… ~]# clustercheck HTTP/1.1 503 Service Unavailable Content-Type: Content-Type: text/plain Node is *down*. We …mysql> insert into percona values (0,’percona1′,’vadim’); Query OK, 1 row affected (7.77 sec) …
Post: Percona XtraDB Cluster reference architecture with HaProxy
June 20, 2012 By 63 Comments
… rollbacks can happen if we have conflicting writes. In the second configuration, we will …identified by ‘clustercheckpassword!’; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 …. # clustercheck HTTP/1.1 200 OK Content-Type: Content-Type: text/plain Node is running. We…
Post: Using Flexviews - part one, introduction to materialized views
March 23, 2011 By 3 Comments
…, in that it represents the result set of a query, but the contents are stored (materialized!) as a real table. So… transactionally consistent with one another, unless you stop all database write activity. Materialized views can be used to enhance performance by… lower because incremental refresh is faster than recomputing the contents from scratch. The contents of the result set are updated periodically, not…
Post: Percona Server with XtraDB Case Study, Behind the Scenes
October 21, 2010 By 13 Comments
… is still outperforming their previous version. I thought I’d write a few notes that didn’t seem appropriate to include… mutex contention. Each spike of slow queries I found was caused by different things. Sometimes GDB stack traces showed InnoDB mutex contention, sometimes oprofile showed the query cache…
Post: DROP TABLE and stalls: Lazy Drop Table in Percona Server and the new fixes in MySQL
June 22, 2012 By 33 Comments
… latch. The buffer pool mutex is a major point of contention as it is taken at a lot of different places… AHI latch can cause contention for writes, since its a shared lock, so while read queries can still access AHI, write queries would have to… for 15 minutes with 8 threads which would execute read/write queries against a 20M rows table: sysbench –test=oltp –oltp-table…
Post: SQL Injection Questions Followup
July 26, 2012 By 2 Comments
…alone. Brian P. commented: Q: If you’re writing Dynamic SQL in your Stored Procedures you …in your code where SQL statements interpolate external content. Unfortunately, this can be laborious and time-consuming…be protected again if you use query results in a subsequent SQL query, then you should be …
Post: How to Monitor MySQL with Percona's Nagios Plugins
February 22, 2012 By 11 Comments
…LVM snapshots that don’t have enough copy-on-write space can fail before the backup completes. The… of severe internal problems within InnoDB, such as contention that crosses a tipping point and cripples the … increase in a variable such as Queries and alert on a queries-per-second threshold. (I don’t…
