Henrik mentioned the problem of patches for MariaDB that ignore the Windows platform. I am guilty of this as MySQL on Windows is irrelevant to me. I am not alone. What contributions do we get from the MySQL on Windows community in patches, performance tests or bug reports? I have never seen a benchmark result for MySQL on Windows.
There is nothing wrong with this. The relationship between users and vendors for MySQL on Windows is different. This is an opportunity for vendors (Sun/MySQL, Monty Program, Innobase/Oracle, Percona, Pythian, Open Query) to add value. I suspect that Sun/MySQL and Innobase/Oracle already care a lot about MySQL on Windows. But maybe they should offer a discount for MySQL on Linux users to offset our contributions (insert smiley face).
As an example of the value that can be added, I filed bug 46957 because InnoDB does not appear to support concurrent IO requests per file on Windows. That is, there can be either 1 pending read or 1 pending write to a file. That might hurt performance. I wrote about this in March and while there were assertions that this couldn't be the case I am not aware of any progress. The basis for my claim is from reading the source. Maybe someone who builds and/or runs MySQL on Windows can confirm or deny this.
If you want to learn more about systems programming on Windows, this is the book to buy. I have an earlier edition of it. One day I will read it. I tried to learn more about WriteFile, ReadFile and why win32/win64 don't support the equivalent of pread/pwrite by searching online for 'WriteFile API' and 'WriteFile API site:mysql.com'. The results were disappointing. Am I expected to buy a copy of the API docs?
-
We now have 2 great storage engines for 5.1 -- InnoDB 1.0.4 and XtraDB. We need more performance results to understand InnoDB 1.0.4, but it looks excellent from the code I have reviewed. This describes some of the changes based on a brief review. All of this make my work easier as I can reduce the size of the patch I need to maintain extreme performance with MySQL.
Kudos to InnoDB for delivering these features in 5.1 and to Percona and Google for contributing patches.
- support for more background IO threads - InnoDB and XtraDB support a configurable number of background IO threads for prefetch reads and dirty page writes. The my.cnf parameters are innodb_read_io_threads and innodb_write_io_threads. Prefetch read requests are generated during queries and when insert buffer entries must be merged. For InnoDB, IO requests are hashed by extent number (64 16kb pages per extent) to the per thread request queues although when a request queue is full, then a request will use any queue. Each queue can hold 256 pending requests. I assume the code in XtraDB is the same. The Google patch uses one queue for all read or write threads which should provide better throughput when there are hot extents, but also requires many more changes to the current source.
- support for group commit - not only does this fix an old regression, I think that it also fixes bug 46459 which degrades performance when autocommit insert statements are used on tables with an auto increment column.
- adaptive flushing - one of the things that makes InnoDB is the use of adaptive algorithms to keep the server balanced. Many of these are not documented because they work and we don't need to know about them. They may have added a new one with support for adaptive flushing. I hope to see performance results from Percona for this, but I think this is another thing in InnoDB we can soon forget as it will work without problems.
- readahead - prior to 1.0.4, InnoDB could generate read prefetch requests when it detected sequential or random access to most pages in an extent. For 1.0.4, the use of readahead for random access to pages within an extent appears to have been removed. The use of readahead for sequential access to pages within an extent has been changed to use a new my.cnf parameter, innodb_read_ahead_threshold, that sets the number of pages that must be accessed sequentially within an extent before all of the pages in the physically adjacent extent will be prefetched. I am still not fond of this feature because:
- I am not aware of any performance counters that report on the success of readahead (#fetched versus #fetched_and_used). But you can disable readahead now and measure the impact on your application.
- Prefetch requests for the pages in the next extent are generated late. For example, if innodb_read_ahead_threshold=56, then requests are generated when the 56th (out of 64) page in the current extent is used.
- If request merging is done for all of the pages in the next extent, then a 1MB read will be used and none of the pages can be accessed until the read completes.
6View comments
-
Why must SELECT COUNT(*) FROM FOO run fast? It is much more valuable to make that query fast when it has a WHERE clause. When there isn't a where clause, MyISAM executes SELECT COUNT(*) FROM FOO fast. When there is a WHERE clause, MySQL has limited support for combining index scans but nothing like bitmap indexes.
If you must, the following will make SELECT COUNT(*) FROM FOO fast for InnoDB:
- Install INSERT and DELETE triggers to maintain the row count for the InnoDB table in a metadata table. This will kill concurrency on the table, just like MyISAM.
- Modify the parser to accept SELECT ESTIMATED_COUNT(*) and evaluate this internally to use the row count estimate provided by the storage engine. Is the estimate good enough? If an exact answer is needed and the table is not locked after the exact value is computed, then the exact value will quickly become incorrect.
14View comments
View comments