… questions along the lines of “how many transactions per second MySQL can do” or “how many servers I need to handle… this example to give estimate which is correct at least to the order of magnitude. Seriously in MySQL Consulting practice it would…. We need estimates, however we need to spend time doing serious capacity planning, probably look at real data try tunning sample queries etc…
Post: Estimating Undo Space needed for LVM Snapshot
June 9, 2008 By 5 Comments
… our question of space requirements for MySQL Backups using LVM. As you can…writing completely different data all the time you can reduce this number. …see how correct was your original estimates. Assuming you have already some … I mean binary logs, slow query log, general query log etc, if they get …
Post: Getting MySQL to use full key length
January 31, 2007 By 5 Comments
… is one bug, or “missing feature” in MySQL Optimizer which may give you hard time causing performance problems which may be hard… can see in this case estimate becomes closer to the truth and this query runs about 50 times faster and easy on buffer… for second query is accessing subset of rows from the first plan so estimation for number of rows for the first query should…
Post: Find unused indexes
June 30, 2012 By Leave a Comment
… about how to find duplicate indexes. This time we’ll learn how to find unused …usage reads the slow query log and execute every query with EXPLAIN to ask MySQL which indexes would it…estimation with real data. EXPLAIN, as I said before, is an estimation from the optimizer and sometimes the real query…
Post: Cache Performance Comparison
August 9, 2006 By 74 Comments
… cache hit ratios which also needs to be factored in estimating improvement for real application. So instead of following his route… data read from database multiple times. Examining MySQL full query logs from many applications seeing several exactly same queries executed during page load is…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a… optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5 Now let’s take…
Post: When the subselect runs faster
March 18, 2010 By 9 Comments
… and of course, there is an index on the col1. MySQL estimated the cardinality of that index as 87, though what was… fit in cache well) When you want to run this query mysql first will try to find each row where col1 is… result time: (20 rows in set (0.01 sec)) So by rewriting query using subqueries we actually improved it performance 100 times. So…
Comment: Star Schema Bechmark: InfoBright, InfiniDB and LucidDB
January 12, 2010 By 48 Comments
…queries like this, grouping by Complexity and Aggregation/Join Cardinality, where the values are rough estimates…in that query appears to be influencing the timing. …query 1.2 in 7.91 seconds instead of 1465 seconds). http://infinidb.org/infinidb-blog/mysql-parallel-query-processing-of-ssb-queries…
Post: How does MySQL Replication really work?
January 9, 2013 By 9 Comments
… on estimating replication capacity, I don’t think we have one that covers the very basics of how MySQL replication … want to look at diskstats or even do a query review for SQL thread to get a good picture. … will only ever read from one disk at a time. That being said, one solution to fix IO-bound …
Post: Using LVM for MySQL Backup and Replication Setup
August 21, 2006 By 53 Comments
…copy files. Fast Recovery Recovery time is as fast as putting data back and standard MySQL crash recovery, and it…hot backup, but bad thing it is hard to estimate when it is hot and when it is not …quite a while to complete on systems with long queries. Problems with data on multiple volumes If you …
