insert update speed
Posted by:
Josh B
Date: July 15, 2006 04:59PM
We just launched a site with a mysql cluster as the database backend for reasons of high availability and to increase speed to some extent since our other option would of been a database server with lots of hard drive space and no redundancy. So, our current setup is
server 1 / 4 gb ram, dual xeon HT proc
server 2 / 4 gb ram, dual xeon HT proc
server 3 / 4 gb ram, dual xeon HT proc
server 4 / 4 gb ram, dual xeon HT proc
server 5 / 1 gb ram, dual xeon HT proc
Servers 1-4 run both a data node and a sql node, they are all connected on a cisco 10/100 mbit switch and we have a hardware load balancer that controls connections to the 4 sql nodes. Also, the switch carries a few other servers on it also.
Now, connections, selects(except for joins and counts) all run fine, just a little slower, but as to be expected for clustering. The problem is that we have a program that gets some data, and runs a stored procedure to do an update or insert, now this stored procedure does some logic, checks if the row exists, what type of row is it, etc. Now, on a single mysql box, this process takes about 3 hours to complete from start to finish. Now, with the cluster, it takes about an hour for 200 records, in the end we have 11,000. We ran it overnight, and it slowly speed up some and finished by morning, but we are concerned since we are expecting 2 million records in this table, and each one will have to be updated, moved, or a new one inserted every night. What are some things to check to see where a bottleneck is occuring?