These are the four parameters which will have the most affect on the performance of MySQL 5.1x and Innodb. If you are suffering from poor performance, try changing the following settings. Remember, each individual situation will vary, and in many cases, the actual design of your queries will have more to do with your overall performance than any system tuning tricks. The best tool I have found to capture, review, and analyse query performance for MySQL is Jet Profiler for MySQL. If you need help optimizing your queries, let us know.
innodb_buffer_pool_size =8G
Set this to ~80% of free server memory. If you have a dedicated MySQL server with 10G, set to 8G
innodb_flush_log_at_trx_commit =0
Setting this to 0 will have a huge performance improvement, however your data is at somewhat more if you have a hardware failure
sync_binlog =0
Setting this to 0 will have a huge performance improvement, however your data is at somewhat more if you have a hardware failure
innodb_flush_method=O_DIRECT
On many systems, this will provide a performance improvement. However, this can actually have a negative affect, so make sure you test appropriately.
Filed in MySQL, Optimization
|
Also tagged Binary, data, database, free, Innodb, INSERT, Logging, MyISAM, MySQL, mysql server, optimizations, performance improvement, profiler, query, query performance, server, UPDATE
|
Thursday, August 19, 2010
In MYSQL, as well as most database engines, restoring a backup, or importing from an existing dump file can take a long time depending on the number of indexes and primary keys you have on each table. You can speed this process up dramatically by modifying your original dump file by surrounding it with the following:
SET AUTOCOMMIT = 0;
SET FOREIGN_KEY_CHECKS=0;
.. your dump file ..
SET FOREIGN_KEY_CHECKS = 1;
COMMIT;
SET AUTOCOMMIT = 1;
This will force MySQL to not commit until all rows have been loaded, as well as skip all foreign keys checks. Only skip these checks if you are 100% sure that no constraint is violated. This will usually be the case when dumping from one table and inserting into another.
Filed in MySQL, Restore Backup
|
Also tagged backup, COMMIT, constraint, database, database engines, Dump, Foreign, long time, MySQL, Restoring, table
|
I am often asked if it makes sense to place an index on a Boolean field in order to improve query performance. In general, because a boolean value can only have three values (True, False, Null), this low cardinality would suggest that adding an index will not help performance, as the query optimizer will still usually perform a table-scan if you have an even distribution of values within your DB.
One situation in which an index on a boolean field (or other low cardinality field) might be useful is if there are relatively few of one of the values, for example 5 True values in a table of millions of records and you are searching for those few values on a regular basis.
However, you might index a boolean value on a combination of fields. Indexing on a single Boolean might be pointless, because there’s only 2 (or 3) values. However, indexing on 16 boolean values has the potential of 2^16 values. It might help to make a combined index but you should understand how the combined index can and cannot be used and be aware that the order of the columns matters.
In general, you should always profile your system to see if there are queries that are too slow and consider adding another index to handle those queries. Sometimes a single combined index can be used for multiple queries and others time you will need to make an index for each type of query. Remember that adding indexes slows down modifications to the data so it is possible to have too many indexes. There is always a trade-off when creating multiple indexes.
Filed in MySQL, Query Optimization
|
Also tagged Boolean, cardinality, data, index, Indexing, MySQL, query, table, trade-off, type
|
The number one thing that you can do to speed up Slave Replication is to set innodb_flush_log_at_trx_commit=0 in your my.cnf file. This will make the transactions less recoverable on your Slave in case of a crash, however with a Slave this is usually an acceptable risk. This setting prevents MySQL from forcing a fsync after every transaction, allowing transactions to be batched up and all fsynced in one operation. When using slower HD RAID’s, this is a huge performance benefit.
Setting sync_binlog=0 will also prove to be beneficial, but also at some level of additional risk.
We have been using Percona 5.1.43 for sometime now and have found it to be a great MySQL Build. It includes many high performance patches, and full support for XtraDB which is a tuned InnoDB implementation.
To install painlessly, create a file called Percona.repo in your /etc/yum.repos.d directory. Add the following to the file:
[percona]
name=CentOS-$releasever – Percona
baseurl=http://repo.percona.com/centos/$releasever/os/$basearch/
gpgcheck=0
Then install using Yum or Apt.
This release includes:
Performance improvements
- Improved buffer_pool scalability
- Fast recovery
- Improved IO path
- Improved rollback segment scalability
- Separate purge thread
- Limited size of data dictionary in memory
- Increased number of concurrent write transactions (undo slots) ( up to 4000 )
- Fast checksums ( in release process )
- Support of different pagesizes ( 4K, 8K, 16K) ( in release process )
Usability / operations
- Show content of buffer_pool
- Import / export of dedicated tables
- Import / export of buffer_pool
- Transactional replication
- Show internal InnoDB data dictionary
- Show InnoDB locking/io profiling in slow.log
Filed in MySQL
|
Also tagged data, high performance, Innodb, MySQL, Percona, recovery, repo, scalability, Transactional, transactional replication, XtraDB, yum
|