… application updates this table with a statement like this: UPDATE achievements SET count = count + 1 where achievement = ‘killed_troll’; How many players might… one at a time (from Example 1, above), how about maintaining the counter in memcache or redis and only flushing to the database…
Post: How to recover table structure from InnoDB dictionary
April 22, 2013 By 4 Comments
…table name in form database_name/table_name e.g. sakila/actor. `ID` is a table identifier. We will need the table…table‘s index. Which one? A table can have many…
Post: SHOW OPEN TABLES - what is in your table cache
December 14, 2008 By 3 Comments
…TABLES on 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…
Comment: Database problems in MySQL/PHP Applications
August 11, 2006 By 13 Comments
…databases Honestly I do not see application using > one database per table that often. I however often see applications > using multiple databases to group tables…people with traditional database background. > Things are different with MySQL. Good design will help dictate how many tables are required…
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
… seen just about everything people throw at databases Many people assume their application is difficult or …. I need to know how many physical spindles are under the box, and how fast they are….tables that really matter. That’s why I save this part of the analysis for last. You cannot consider any one…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 5 Comments
… size 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… Buffer pool size, bytes 128849002496 Free buffers 1 Database pages 8252672 Old database pages 3046376 Modified db pages 23419 I’m using…
Post: How innodb_open_files affects performance
November 18, 2009 By 4 Comments
…open_files which defines how many files Innodb will keep open while working in innodb_file_per_table mode. …one can open/close file hundreds of thousands times per second. To verify this I tried doing “open table…if you have a lot of tables: 091118 18:43:36 InnoDB: Database was not shut down normally!…
Post: How to find MySQL queries worth optimizing ?
September 11, 2012 By 6 Comments
…be thinking in this case there is no database access happening at all – you would be wrong…and only to ones which examine one table only. What is about queries which query more than one table ? # Time: …to see if query is worth optimizing ? – see how many rows query sends after group by, distinct and…
Post: How expensive is USER_STATISTICS?
June 2, 2012 By 14 Comments
…how expensive it is. There are two dimensions to the question. One is how much slower the server becomes, and the other is how much more memory it uses. The second question is important on servers with many tables. Actually, “many… server on a database with 25,000 sysbench tables, performed a …
Post: Recovering Innodb table Corruption
July 4, 2008 By 22 Comments
…is 0 25556 InnoDB: (index “PRIMARY” of table “test”.”test”) InnoDB: Database page corruption on disk or a failed and…can see I can get rows from the table in the new one until we finally touch the row …OK. Again using binary search you can find out how many rows do you need to skip exactly to recover…
