… you keep track of global achievement statistics in a single table with a row for each stat; there are just a… updates this table with a statement like this: UPDATE achievements SET count = count + 1 where achievement = ‘killed_troll’; How many players might… application. These are maintained in separate tables and there also exists a users_groups table to define the relationship between them. When…
Post: Percona Toolkit 2.2.2 released; bug fixes include pt-heartbeat & pt-archiver
May 6, 2013 By 2 Comments
… tcpdump parsing errors pt-query-digest 2.2 prints too many string values Some tools don’t have –help or –version… certain cases. A new –recursion-method was added to pt-table-checksum: cluster. This method attempts to auto-discover cluster nodes… the need to specify cluster node DSNs in a DSN table (–recursion-method=dsn). See https://launchpad.net/percona-toolkit/+milestone…
Post: Joining many tables in MySQL - optimizer_search_depth
April 20, 2012 By 8 Comments
… into interesting problem – query joining about 20 tables (thank you ORM by joining all tables connected with foreign keys just in case… compatibility, – the hypothesis that most users have joins with few tables, – it is not clear how far from optimal plans do… things are likely to get even better handling joins of many tables as optimizer heuristics are improved so much higher search depths…
Post: SHOW OPEN TABLES - what is in your table cache
December 14, 2008 By 3 Comments
… show how many threads are currently using this table, meaning have it locked or waiting on the table lock for this table to lock… ALL tables one by one you can run SHOW OPEN TABLES and flush only open tables, when run it again and see how many tables are open and in use and if FLUSH TABLES…
Post: table_cache negative scalability
November 16, 2009 By 23 Comments
…-only benchmark. Why ? Because for MyISAM tables table header has to be modified each time the table is opened. In this case the… (opened_tables) to 10/sec or less by using large table_cache you should do so. However if you have so many tables you still see 100+ misses/sec while your data (at least table headers) is well cached so the cost of table…
Post: Optimizing InnoDB for creating 30,000 tables (and nothing else)
December 22, 2011 By 5 Comments
… a lot more tables than a mere 30,000. There have historically been no tests for anything near this many tables in the… API. Writing a simple test program that creates 30,000 tables in a similar InnoDB configuration as default MySQL is pretty…_at_trx_commit I instead group the CREATE TABLE into transactions of creating 100 tables at a time, I get an execution…
Post: What to tune in MySQL Server after installation
September 29, 2006 By 118 Comments
… do not have Battery backed up cache. Many applications, especially those moved from MyISAM tables are OK with value 2 which means… full OS crash. table_cache – Opening tables can be expensive. For example MyISAM tables mark MYI header to mark table as currently in use… applications with couple hundreds tables (remember each connection needs its own entry) if you have many connections or many tables increase it larger. I…
Comment: Database problems in MySQL/PHP Applications
August 11, 2006 By 13 Comments
… thousands of users – I would rather do > many to many relationship between users and tables but it also > might work. Having a logical… subset of the data much easier. > Regarding if you use many tables you’re doing something wrong it > is frequently told by… are different with MySQL. Good design will help dictate how many tables are required in order to get the job done. To…
Post: Using MyISAM in production
June 17, 2006 By 36 Comments
… have many tables which are being repaired, each may allocate myisam_sort_buffer_size and MySQL could crash or go swapping. Second – table… cases table corruption will be mild, especially on low loaded servers. Many users do not even know you need to check MyISAM tables after crash and have been running for years survining many crashes…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 5 Comments
… how many user variables are allocated (and how much memory they use). Memory Tables MEMORY tables can take memory. There are implicit MEMORY tables… of MEMORY tables (the limit applies both to implicit and explicit ones) but as there is no control of how many tables application can create it does not really allows to restrict memory usage. For Permanent tables it is easy…
