… recently who needed to reduce their database size on disk quickly without a lot … an exercise left to the reader. 
Estimating the size of these indexes But, what if … can can see index sizing information in the all_indexes view: mysql> select * from all_indexes\G … *************************** 33…
Post: Quickly finding unused indexes (and estimating their size)
December 5, 2012 By 2 Comments
Post: Be productive with the MySQL command line
December 21, 2012 By 14 Comments
… easily solved by using the pager command: mysql> pager more PAGER set to ‘more’ mysql> select title from sakila.film; +—————————–+ | title | +—————————–+ | ACADEMY… command is when you want to estimate a good size for you InnoDB redo logs: the estimation is based on the variation of… startup with -A Database changed mysql> select count(*) from sakila; ERROR 1146 (42S02): Table ‘sakila.sakila’ doesn’t exist mysql> select count(*) from…
Post: Estimating Undo Space needed for LVM Snapshot
June 9, 2008 By 5 Comments
… back to our question of space requirements for MySQL Backups using LVM. As you can see … depending on database or workload this may need to be adjusted. If you have 10GB database size (including logs …you can see how correct was your original estimates. Assuming you have already some space allocated …
Post: Modeling MySQL Capacity by Measuring Resource Consumptions
March 8, 2011 By 1 Comment
… compare it to estimated (or benchmarked) system performance to provide our estimates. If we’re running Innodb with MySQL we can … 12.83M 324.99 0 32.65k 0 # Query size 294.90M 14 783 158.14 621.67 149… 0 0 0 0 0 0 0 0 # String: # Databases # Hosts localhost # InnoDB trxID 3BBF3B55 (1/0%), 3BBF3B5A (1…
Post: How to estimate time it takes Innodb to Recover ?
May 9, 2007 By 7 Comments
…. Database Size This comes back to the data locality but with same data access distribution the larger database you’… and undo phase – rolling back uncommitted transactions. As MySQL 5.0 these seems to be done in … number of hard drives. So how do you estimate how long it takes Innodb to Recover ? Now…
Post: Choosing innodb_buffer_pool_size
November 3, 2007 By 39 Comments
… than your database size you would not loose much anyway. You also may choose to set buffer pool as if your database size is… are other things you need OS cache for – MyISAM tables (mysql database, temporary etc) will need it, .frm file, binary logs, or… to 1GB good estimates for this number. Eliminate Double Buffering – This is again very important for buffer pool size choice. You do…
Post: Using LVM for MySQL Backup and Replication Setup
August 21, 2006 By 53 Comments
…hot backup, but bad thing it is hard to estimate when it is hot and when it is not …to perform backup of MySQL Database (or create slave) using LVM2 on Linux. 1) Connect to MySQL and run FLUSH TABLES…I’ve specified 16GB in this case. If your undo size is not large enough snapshot will get invalidated …
Post: MySQL Sizing questions
June 23, 2006 By 4 Comments
… this example to give estimate which is correct at least to the order of magnitude. Seriously in MySQL Consulting practice it would… same for database server handling custom application – no it is not. So am I saying we do not need any sizing at all and should just pray we purchased enough hardware ? Of course note. We need estimates, however we…
Post: Finding your MySQL High-Availability solution – The questions
October 16, 2009 By 36 Comments
… These technologies are by far, not a one size fits all and many deployments use combination of… a database server. In term of write capacity here are you choices. Write capacity MySQL replication …gotten many questions about the time estimates mentioned here. The above estimates shouldn’t be used to …
Post: Find unused indexes
June 30, 2012 By Leave a Comment
… some load on the database I’m going to use the benchmark software tpcc-mysql. After some runs…’re comparing an estimation with real data. EXPLAIN, as I said before, is an estimation from the optimizer…testing environment with an similar data estructure and size. Conclusion We’ve learnt how to find …
