…mysql> select * from information_schema.global_temporary_tables \G *************************** 1. row *************************** SESSION_ID: 7234 TABLE_SCHEMA: test TABLE_NAME: my ENGINE: InnoDB…
Post: Reasons for run-away main Innodb Tablespace
June 10, 2010 By 1 Comment
So you’re running MySQL With innodb_file_per_table option but your ibdata1 file which holds main (or system) … what caused your system tablespace size to explode – they will shrink to the normal size and all what you have will…’re running Percona Server the following can be helpful: mysql> select * from innodb_rseg; +———+———-+———-+———+————+———–+ | rseg_id | space_id | zip_size | page_no…
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
… InnoDB tables — don’t use GUIDs. Which brings me to my next point: Use InnoDB Assuming that you will use the stock MySQL… use InnoDB/XtraDB tables… Optimize For I/O It is pretty much inevitable: if you do this kind of data processing in MySQL… other than the primary key, we can shrink the primary key’s width: create table ads_by_day_by_blueness ( day date…
Post: 10+ Ways to Crash or Overload MySQL
November 13, 2007 By 20 Comments
… mysql_stmt_send_long_data call – Server buffers such data until you have executed prepared statement. Innodb Table Cache Leak – Innodb never shrinks its internal table cache (data dictionary) so by creating and accessing large amount of Innodb tables you can…
Post: MySQL Blob Compression performance benefits
January 11, 2008 By 17 Comments
…significant size in the table it often makes sense to keep it compressed. Unfortunately MySQL does not …true data size and performance gains, especially for Innodb tables. With Innodb tables BLOB gets its own page (allocated …how many rows you will be able to shrink down so they fit back to the …
Post: MySQL-Memcached or NOSQL Tokyo Tyrant - part 1
October 15, 2009 By 16 Comments
… I can then compare using MySQL to using MySQL + Memcached, and then to other…address ). Seems simple enough right? My two tables each contain 5M rows of data…. to always grow and very rarely shrink. Which leads to a challenge faced… and 4GB. For these tests I left Innodb with a 256M buffer pool, or …
Comment: MySQL File System Fragmentation Benchmarks
October 10, 2008 By 28 Comments
… is a “bug” in MySQL InnoDB which don’t shrink the file ibdata1 after a DROP Database without dropping the Table – If you have the defalut setting and not innodb_file_per_table… for a production enviroment…. http://crazytoon.com/2007/04/03/mysql-ibdata-files-do-not-shrink-on-database-deletion-innodb/ best regards gregor
Comment: Choosing innodb_buffer_pool_size
January 20, 2011 By 38 Comments
… mysql 5.5.8 @ freebsd 7.1. the main db is innodb, myisam is used for the mysql-system-tables(small-sized). the production-innodb… dump is about 19GB big. i know that innodb – ibdata – files will never shrink, so i have to drop and import the… learned(thanx to your blog!) i definitely want to increase innodb_puffer_pool_size(which is set to just 386MB). my…
