… a data warehouse and a data mart. (this post) Introduction to MDX and the kind of SQL which a ROLAP tool must generate… with and without aggregation over multiple MySQL storage engines at various data scales. What is a data warehouse? It turns out that this…, clustered tables and materialized views. These features make working with a star schema much easier than it may be on MySQL, but…
Post: Quickly finding unused indexes (and estimating their size)
December 5, 2012 By 2 Comments
… I can use some clever SQL to generate the precise ALTER TABLE statements to drop these indexes, …(not big in this case with test data): mysql> select * from droppable_indexes; +————–+—————+—————————–+—————+ | table_schema | table_name | index_name | …
Post: PHP Large result sets and summary tables.
July 6, 2007 By 8 Comments
… large data sets as part of the process we work on generation test database of significant size as testing your application on table… then populating tables. This all worked well for small tables… but not for larger ones. First problem was PHP script generating the table took…. There are some limits as you can’t use mysql_num_rows() and mysql_data_seek() if you use this method but this…
Post: Should we give a MySQL Query Cache a second chance ?
April 10, 2011 By 64 Comments
… table is updated) and check whenever version matches for all tables on which query depends on. If not you need to re-generate… being generated, wait for completion and when simply use it. More Granular Invalidation Can we get something less coarse than per table… be improved and data about Query Cache content and operation can be presented as some INFORMATION_SCHEMA tables. Self Refresh MySQL Query Cache…
Post: devops webinar - follow up Q&A
November 6, 2012 By 5 Comments
… automatically, do you use real production data or generating test data? A: When testing some features I use generated data (using sysbench for example), but when I need to validate changes for production (continuous integration), real production data is… & col_b use column col_a no changes for that table Table schema col_a & col_b col_a & col_b col…
Post: How to recover deleted rows from an InnoDB Tablespace
February 20, 2012 By 4 Comments
… the table_defs.h format in the following link: http://www.percona.com/docs/wiki/innodb-data-recovery-tool:mysql-data-recovery:generating_a_table_definition With the table definition on table_defs.h it’s the…
Post: Recovering Innodb table Corruption
July 4, 2008 By 22 Comments
…data operations and so you can’t insert or delete any data (though you can create or drop Innodb tables): mysql> optimize table test; +———–+———-+———-+———————————-+ | Table… generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql….
Post: Timezone and pt-table-checksum
September 25, 2012 By 1 Comment
… run pt-table-checksum against node1, we see that even though MySQL is storing these values in UTC internally, we have “data drift” on both nodes: pt-table-checksum –replicate=percona…-table-checksum reported the chunk as out of sync. You can verify this yourself looking at the actual SQL generated by pt-table…
Post: How adding another table to JOIN can improve performance ?
August 1, 2008 By 12 Comments
…generated table and use the join to retrieve the rows from original table: mysql> show create table dl \G *************************** 1. row *************************** Table: dl Create Table: CREATE TABLE… table. So we finally managed to get better performance by joining data to yet another table…
Post: Preprocessing Data
August 8, 2011 By 3 Comments
… to repopulate the tables with current data. You then change the main page of the website to query these new tables. You have… could be reporting, updating caches (preventing cache stampedes) and counter tables. Prefetching If there is a likely chance that the user… can pre-generate that result. Examples could be pagination. On Request If there is no good way to preprocess the data then…
