…. As of MySQL 5.5 FLUSH TABLES WITH READ LOCK does not work as optimally as you could think it works. Even… | 2359297 | | 10290 | root | localhost | NULL | Query | 317 | Waiting for table flush | flush tables with read lock | 0 | 0 | 1 | | 10291 | root | localhost… to the database at all (use mysql -A in this case, to prevent it from getting field list from all tables) Reads…
Post: MariaDB 5.3 is released as GA!
March 5, 2012 By 17 Comments
…first-class member of the MySQL family of databases. Finally, High Performance MySQL, Third Edition mentions … joins, faster subqueries, and elimination of useless tables in joins Thread pooling and a segmented … support, as well as a lot of optimizer improvements. Even the standard MySQL 5.5 …
Comment: Thinking about running OPTIMIZE on your Innodb Table ? Stop!
March 26, 2012 By 13 Comments
Cool! This is a very good tip. I was actually looking for a better way to optimize my database since it is taking almost 6 hours to optimize a single table. Your trick is way faster thanks!
Post: Database access Optimization in Web Applications.
September 12, 2006 By 10 Comments
… you can use. Just remember avoiding accessing database is the best way you can optimize database access. This applies to anything else – if… retrieved from the database matches information you display Way to often much more information is retrieved from the database than used to… to be redesigned rather than simply adjusted – for example summary table which holds number of links per domain would do in…
Post: Heikki Tuuri answers to Innodb questions, Part II
November 4, 2007 By 13 Comments
… all major database brands. Q37: We have a fairly large Innodb table (150GB) that is showing poor performance for full table scans. using… run to show any table / index fragmentation, without having to run the OPTIMIZE? HT: Since OPTIMIZE rebuilds the whole table, it may last way too long for your 150 GB table! If you…
Post: Database problems in MySQL/PHP Applications
August 11, 2006 By 12 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…becomes managable, your ALTER TABLE or OPTIMIZE TABLE now locks small table for few seconds rather than giant 100GB table for few hours so can…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
… dump with a dump created with –innodb-optimize-keys (the test database contained only the table I used in my previous examples): $ mysqldump -uroot test > dump_unoptimized.sql $ mysqldump -uroot test –innodb-optimize…
Post: Recovering Innodb table Corruption
July 4, 2008 By 19 Comments
…”PRIMARY” of table “test”.”test”) InnoDB: Database page …tables): mysql> optimize table test; +———–+———-+———-+———————————-+ | Table | Op | Msg_type | Msg_text | +———–+———-+———-+———————————-+ | test.test | optimize…
Post: Guidance for MySQL Optimizer Developers
September 20, 2009 By 4 Comments
… large portion of my life working on MySQL Performance Optimization and so MySQL Optimizer is quite important to me. For probably last… some people have their database 100% in RAM (even for Disk tables such MyISAM or Innodb) others keep database on SSD which has… Performance problems can be due to optimizer picking the wrong plan, such as doing full table scan when Index access is better…
Post: Innodb Performance Optimization Basics
November 1, 2007 By 70 Comments
… RAM which will be dedicated for MySQL with large Innodb database using typical Web workload what settings you would adjust and… applications, though the optimal settings of course depend on the workload. Hardware If you have large Innodb database size Memory is paramount… options if you’re using innodb_file_per_table and a lot of tables though benefit of these is minor. Also make…
