How much can you get out of your MySQL

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 VARBINARY, VARCHAR, BLOB and TEXT columns to get more.
- 1000 columns is the limit per table

Best,
Moshe. RockeTier. The Performance Experts.

Java, MySQL and Large Datasets Retrieval

Hi,

As told before, it was a MySQL week,

We had a major work this week solving a performance issue in a reporting component to one of our clients. Since its current component worked directly against the raw database, it was facing degragated performance as database and business get larger.

Therefore, we designed an OLAP solution that extracts information from the raw tables, group and summarizes the data and then created a compact table, which data can be easily read from.

However, the database is MySQL, and we used Java to implement this mechanism. Unfortunately, it seems that Java and MySQL don't really each other or at least like large tables: When you try to extract records our of large MySQL table you receive out of memory error in the execute and executeQuery methods.

How to overcome this?
1. As suggested by databases&life, set the fetchSize to Integer.MIN_VALUE. Yes, I know it a bug, not a feature, but yet it solves this issue:

The reason for this bug is the code in StatementImpl.java of the MySQL JDBC driver code:
protected boolean createStreamingResultSet() {
return ((resultSetType == ResultSet.TYPE_FORWARD_ONLY)
&& (resultSetConcurrency == ResultSet.CONCUR_READ_ONLY)
&& (fetchSize == Integer.MIN_VALUE));
}

And the solution is:

public void processBigTable() {
PreparedStatement stat = c.prepareStatement(
"SELECT * FROM big_table",
ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_READ_ONLY
);
stat.setFetchSize(Integer.MIN_VALUE);
ResultSet results = stat.executeQuery();

while (results.next()) {
...
}
}


2. The other option is doing this fetch applicative, meaning that each time setMaxRows will set to N and reocrds will be extracted only if their id is larger than the extracted before

public void processBigTable() {
long nRowsNumber = 1;
long nId = 0;
while (nRowsNumber > 0) {
nRowsNumber = 0;
PreparedStatement stat = c.prepareStatement(
"SELECT * FROM big_table WHERE big_table_id > " + nId ,
ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_READ_ONLY
);
stat.setMaxRows(10000);
ResultSet results = stat.executeQuery();

while (results.next()) {
++nRowsNumber;
nId = results.getLong(1);
...
}
}

Hope you find it useful as we found it, and thanks again to databases&life,

Best
Moshe. RockeTier. The Performance Experts.

Does MySQL 5.0 work with multi-core processors? yes???

Hi,

I think this week can truly be named: "The MySQL week". So many issues regarding this product. You know, sometimes it is the cost of getting things for free...

Well, first of all, MySQL is a great product. Many start ups started with this product and many giants are using it for their billions events per day systems.

However, MySQL has several limitations. One of them is that it not really supports multi core processors. Yes, I know, MySQL definitly tell that they do support multi threading. However, our analysis found out that this is not the case. As you can see in the attached graph, the MySQL machine really works hard. However, since it's a quad core machine, it is reaching only 25% CPU utilization

To be more accorate, too many people around the globe like Jeremy Kusnetz, peter with who Sun reach performance on 256 cores and starnixhacks got to the same conclusion: MySQL do use multi threading to it periphrial components, but when it get to do real work, it not really supports multi core processors.

This can be really defined as a major issue, since modern CPUs are based on slower clocks and more cores... So what can we do?

Well the answer is Sharding...
In few words, sharing is the internet companies way to install many weak type databases, each dealing with a vertical or horizental part of the database. This way you can install on a dual qual core CPUs machine, 8 MySQLs instances, each dealing with a single partition of your application (for example the first stores customer with name starts with 'A', while the second stores these that start with 'B', 'C' and 'D' and so on). A broader review of this method will be provided in the next weeks.

Anyhow, I recommend MySQL Optimization guide, as a must to any MySQL would be tuning professional

Moshe.
RockeTier, The Performance Experts