… pool mutexes. Another important configuration that I am using is that I am using “estimate” flushing method available only on Percona Server…’s take a look at the graphs have a better view of the whole picture. Note, in the above graph, we… indexes. Other important things to consider when you are concerned with write performance is using partitioning to reduce the size of the…
Post: Using VIEW to reduce number of tables used
September 28, 2007 By 12 Comments
… tempting to use VIEWs to reduce number of tables dramatically – merging say 1000 of users to the same table as VIEWs are significantly… CHECK OPTION is used to ensure we do not insert data in the view which will be invisible. The VIEW approach works for… the base table – generally there should be only few places when insert happens so it should not be big problem. Technically…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 12 Comments
… in Memcache. When the cache “expires”, the contents must be fully recalculated.. An incrementally refreshable view (that is, one which uses the incremental… view, even when the number of changes is very small. The Flexviews SQL_API Unlike views which use the complete refresh method, incrementally refreshable views…
Post: A workaround for the performance problems of TEMPTABLE views
May 19, 2010 By 7 Comments
… that MySQL sets the ALGORITHM=UNDEFINED, and will chose to use TEMPTABLE when the view is actually used: mysql> show warnings; +———+——+——————————————————————————-+ | Level | Code | Message… be used here for now (assumed undefined algorithm) | +———+——+——————————————————————————-+ 1 row in set (0.00 sec) When you try to restrict the second view using…
Post: Using Flexviews - part one, introduction to materialized views
March 23, 2011 By 2 Comments
… the results. Views are similar to regular SQL statements. A view is a SQL statement which acts like a table. When you execute… another, unless you stop all database write activity. Materialized views can be used to enhance performance by acting as a cache. Further… benefits which include: Refreshing large views is orders of magnitude faster than complete refresh. When updating the view, only a subset of the…
Post: EXPLAIN EXTENDED can tell you all kinds of interesting things
June 15, 2010 By 7 Comments
… query that I explained used the comma syntax, but when MySQL rewrote the query it switched it to use the JOIN keyword. This… the query rewrites that MySQL makes when accessing views which use the MERGE algorithm. For example: mysql> create view v1 as select * from j1… will see that the SELECT statement used in the view has been modified to include the WHERE clause that I used when accessing the view.
Post: MySQL opening .frm even when table is in table definition cache
November 21, 2011 By 8 Comments
… over four minutes to execute and get a more complete view of how the Storage Engine API is called. But back… code paths in the server: DROP TABLE RENAME TABLE DROP VIEW open table filling INFORMATION_SCHEMA tables (I think it is… didn’t look closely) An example of how this is used is that in the DROP TABLE code path, MySQL…
Post: Using Flexviews - part two, change data capture
March 25, 2011 By 11 Comments
… a materialized view(MV)? It explained that an MV can pre-compute joins and may aggregate and summarize data. Using the aggregated… performance by identifying the data which has changed. Maintain materialized views with Flexviews (the primary purpose of FlexCDC). Feed search engines… has a lot of problems. Triggers add a significant overhead. When the structure of a table is changed, the triggers must…
Post: Using MyISAM in production
June 17, 2006 By 35 Comments
… MyISAM features so I thought I would share my own view on using MyISAM in production. For me it is not only… but it was solved by using this significant number of tables. I still have the same view on Storage Engines – Innodb is… seen over 10 pieces in average per row – this is when rows contain some data which constantly growths in size. It…
Post: MySQL - to use or not to use
May 28, 2007 By 10 Comments
… ? Oh yes I know, it comes from the point of view market old timers try to show the value of their… is when or how to use MySQL not just if it should be used or not. There are many applications when MySQL works great, when there are some of applications when MySQL can be…
