Hi,
As you probably understand, our team, as always, is taking these days MySQL to its limits. It is our pleasure to share the insights with you.
First lets make a small assumptions to the process: We were dealing with InnoDB configuration (which MySQL engine should I choose?)
MySQL Performance Benchmarks:
I would like to refer you to several case studies and benchmarks of MySQL:
1. In our tests while boosting an OLAP mechanism, we reached a number of 12 Group by queries/second on a 1 Million records.
2. 17 Transactions/second were reached on a basic machine (PIII, 256MB RAM on a 100K records table). However, the benchmark performer never revealed his exact benchmark.
3. A MySQL performance benchmark paper from 2005, reached 500 reads on a 10 Million records table on a 8 CPU machine (8 MySQL instances). Meaning that MySQL reached a 60 reads/seconds on a single instance MySQL in a well optimized benchmark.
4. Sun performed on a Sun Solaris based 4 AMD Opteron Dual Core Model 875 (8 MySQL instances) 16GB RAM reached 1800 (RW) and 2900 (RO) transactions/seconds or 220 (RW) and 350 (RO) transactions per second on a 1 Million records table.
Bottom Line:
As a rule of thumb we recommand to not use tables with more than few dozens million of records, and not to expect to more than few dozends reads per second per MySQL instance
Other useful issues for building your scalable software system:
1. MySQL supports triggers. However, we recommend to avoid this feature due to performance issues. Please, if you fill any need to use triggers, please implement it in the BLL level, rather then using triggers.
2. MySQL supports Identity using @@Identity. However, please notice that this one is not well working with triggers (noitce our hazard before).
3. MySQL supports XML, and both SQL Server "FOR XML" and "OPEN XML" can be implemented using various methods (critics). However, we do NOT recommand to use these methods either in MySQL and SQL Server. Usually the database is the bottleneck of any system; Therefore, you would like to avoid any unnecesary operation in the database.
4. INSERT DELAYED: very useful in cases, when you would like to make an insert to a table (e.g log tables and queue like tables) and wants to avoid the wait till the INSERT is processes. INSERT DELAYED are performed on the server open windows and not immidiatly.
5. Multiple inserts: multi valued INSERT performs best in MySQL
6. Transactions are supported in MySQL, use them when needed.
7. Try Catch are supported as well.
8. MySQL row sizes:
- Regular fields take up to 8KB.
- You can use
- 1000 columns is the limit per table
Best,
Moshe. RockeTier. The Performance Experts.
As you probably understand, our team, as always, is taking these days MySQL to its limits. It is our pleasure to share the insights with you.
First lets make a small assumptions to the process: We were dealing with InnoDB configuration (which MySQL engine should I choose?)
MySQL Performance Benchmarks:
I would like to refer you to several case studies and benchmarks of MySQL:
1. In our tests while boosting an OLAP mechanism, we reached a number of 12 Group by queries/second on a 1 Million records.
2. 17 Transactions/second were reached on a basic machine (PIII, 256MB RAM on a 100K records table). However, the benchmark performer never revealed his exact benchmark.
3. A MySQL performance benchmark paper from 2005, reached 500 reads on a 10 Million records table on a 8 CPU machine (8 MySQL instances). Meaning that MySQL reached a 60 reads/seconds on a single instance MySQL in a well optimized benchmark.
4. Sun performed on a Sun Solaris based 4 AMD Opteron Dual Core Model 875 (8 MySQL instances) 16GB RAM reached 1800 (RW) and 2900 (RO) transactions/seconds or 220 (RW) and 350 (RO) transactions per second on a 1 Million records table.
Bottom Line:
As a rule of thumb we recommand to not use tables with more than few dozens million of records, and not to expect to more than few dozends reads per second per MySQL instance
Other useful issues for building your scalable software system:
1. MySQL supports triggers. However, we recommend to avoid this feature due to performance issues. Please, if you fill any need to use triggers, please implement it in the BLL level, rather then using triggers.
2. MySQL supports Identity using @@Identity. However, please notice that this one is not well working with triggers (noitce our hazard before).
3. MySQL supports XML, and both SQL Server "FOR XML" and "OPEN XML" can be implemented using various methods (critics). However, we do NOT recommand to use these methods either in MySQL and SQL Server. Usually the database is the bottleneck of any system; Therefore, you would like to avoid any unnecesary operation in the database.
4. INSERT DELAYED: very useful in cases, when you would like to make an insert to a table (e.g log tables and queue like tables) and wants to avoid the wait till the INSERT is processes. INSERT DELAYED are performed on the server open windows and not immidiatly.
5. Multiple inserts: multi valued INSERT performs best in MySQL
6. Transactions are supported in MySQL, use them when needed.
7. Try Catch are supported as well.
8. MySQL row sizes:
- Regular fields take up to 8KB.
- You can use
VARBINARY, VARCHAR, BLOB and TEXT columns to get more.- 1000 columns is the limit per table
Best,
Moshe. RockeTier. The Performance Experts.
