…- 100M ids (size of datadir ~100GB), size of the …innodb_log_buffer_size=128M innodb_max_dirty_pages_pct=80 innodb_file_format=barracuda innodb_file_per_table innodb_read_io_threads = 8 innodb…innodb_buffer_pool_instances=16 # MySQL 5.5 and 5.6 loose-innodb_io_capacity_max = 15000 #Percona only innodb…
Post: Data compression in InnoDB for text and blob fields
May 30, 2012 By 2 Comments
… choose a KEY_BLOCK_SIZE that is the same value of the InnoDB page size of 16KB? As the fine MySQL manual states: This setting may still be useful for tables with many…. 
Keep in mind too that if you started with a 100GB table and assuming you know your approximate compression rate, you will…
Post: MySQL Server Memory Usage
May 17, 2006 By 21 Comments
…run with maximum theoretical memory usage of 100GB or more. Furthermore there is no easy …others, for example tmp_table_size is rather maximum amount of memory MySQL will allocate for this …memory consumption Innodb Table Cache. Innodb has its own table cache in which meta data about each table …
Post: Working with large data sets in MySQL
July 5, 2007 By 12 Comments
… learn when working with large data things in MySQL is patience and careful planning. Both of which … because for MyISAM they will lock tables for very long time and Innodb can get too many old …database size. Say it takes 30 minutes to alter 10GB table so it will take 5 hours to alter 100GB one…
Post: Just how useful are binary logs for incremental backups?
July 21, 2009 By 6 Comments
… topology. The database size is 100 GB (same tables on each). The …more than 30 minutes to restore 100GB (50MB/s), but to replay … solutions? If you are using all InnoDB tables, an XtraBackup incremental backup should … problem should be lessened by a MySQL Server feature – parallel execution on slaves…
Comment: MySQL Partitioning - can save you or kill you
June 28, 2012 By 45 Comments
…://mysql.rjweb.org/doc.php/memory For InnoDB, innodb_buffer_pool_size = 5500M might be optimal. (Plus key_buffer_size = 20M) If you switch to InnoDB…… SHOW TABLE STATUS says 50GB of data + 30GB of index (MyISAM) or 120GB + 100GB ? (The large PK will adversely impact InnoDB secondary keys…
Post: Predicting Performance improvements from memory increase
August 7, 2008 By 6 Comments
…example if your main queries do full table scan of 100GB table it does not matter if …MySQL scaling issues it is also possible to see performance to go down as you increase buffer pool size…table. Having such workload you would likely see no IO and a lot of CPU idle and not because of internal Innodb…
Post: Should I buy a Fast SSD or more memory?
April 8, 2010 By 10 Comments
… has traditionally been popular for MySQL, it’s interesting to see …the X axis is the size of innodb_buffer_pool_size: Let me point out three…you have 32GB of RAM and 100GB of data. Than it gets…size of your “active set” of data. For some people who just append data to some sort of logging table…
