Currently have approximately 2000 simultaneouse connections. We average approximately 425 reads and writes per second. We have a read to write ration of 3:1. All of our tables are myisam. Can we expect better or worse performance when we go from mysql 4.1.22 to 5.0?
views:
50answers:
4
+2
A:
There's no way for anyone here to tell you without the schema, queries and test data.
Why not setup a dev environment on 5.0 and testing it out?
Ben S
2010-06-15 18:33:38
right :-)But if the author would be using InnoDB (what he doesn't) he will definetly get a big performance boost.(+1)
Tobias P.
2010-06-15 20:00:19
A:
The main concern should be that the 5.0 Information Schemas, are a HUGE vulnerability and can be used to very easily gain access to the SQL server from remote locations simply by printing off the schema using injection will let an unwanted viewer, view all of the tables and capitalize off the knowledge to get passwords using the same schema for its columns.
Gnostus
2010-06-15 18:34:37
Our database is behind a firewall and used internally within our company. We're not too concerned about vulnerabilities with respect to our employees. We're very concerned about performance.
2010-06-15 19:17:37
try on any php hosted with the SQL and try, if we have an id param.id?=1 this is what it should be!id?=1' UNION SELECT 1,2,3,4(... however many tables you have) From Information.Schema_tables--this will push all your SQL tables onto a web browser no matter what 'permissions' you assign, unless you directly handle these URLS to redirect to a Unauth page, you can easily find your self compromised.
Gnostus
2010-06-15 21:13:12
A:
The MySQL source tree includes a set of benchmark tests written as Perl scripts. See The MySQL Benchmark Suite for some information. You can download the source distribution for MySQL 5.0.91 at the archives.
Source distribution of MySQL 4.1 doesn't seem to be easily available anymore. You might have to check it old sources from LaunchPad unless you can find a copy of an old source distribution elsewhere on the internet.
However, the comparison that these benchmarks show is only of general interest. It may be irrelevant to how your application performs. For instance, your usage of the database may not take advantage of some performance improvements in MySQL 5.0, but it may run into some regressions in MySQL 5.0 that were necessary.
The only way to get an answer that is relevant to your application is to try the new software with a test instance of your application, using a sample of data that is a realistic model of the type and volume of data your application typically deals with. As @BenS says, no one on a site like StackOverflow can give an answer specific to your application.
You say in a comment that you're very concerned about performance, but if you don't have an instance of your application and database that you can run tests on, you aren't doing the work necessary to satisfy this concern.
Bill Karwin
2010-06-15 19:58:34
A:
I would strongly suggest moving straight to 5.1.45 with Innodb Support. Percona provides an excellent version with XtraDB that provides a number of performance related improvements. Moving off of your MyISAM tables and onto Innodb will provide a huge performance increase in almost all cases. If you are going to burn the QA/Testing time to move, do a full move now, not a half-way step.
Gary
2010-06-15 20:47:22