I have installed MySQL 5.1x instance on a Linux machine (768MB RAM). I restored a backup of about 1000 rows and through my .NET application (deployed on a different Windows webserver) I performed certain read operations, which, when considering that the table had no indexes, were fast.
I then cleared the server from these rows and used a feature in my web application on the other server to insert rows (Basically, I read text off a text file, entering the data into fields of a custom class, and then calling a stored procedure with these parameters for each record). When I had the website and database both on my local machine, I would insert 2500 rows instantly (less than a second). Now, however, in the "production" environment, these same 2500 rows are taking 10 minutes to be inserted. I am using the default my.cnf that comes with the installation of MySQL.
Is there any way I can optimise writes without compromising the speed of read operations? Or am I missing something obvious?
Thanks in advance!
Have you looked at the MySQL manual page for insert speed? The first guess that comes to mind for me is that you aren't wrapping the set of 2500 procedures in a Begin Transaction/Commit statement, so MySQL is autocommitting each one of the 2500 actions.