Hi everyone,

I have a MySQL database which has a number of tables, some of the rows within contain 86,000,000 entries. Overall its about 100GB in size.

Some queries for the large rows take around 5 minutes, maxing out the CPU on the box. It's on an AMD proliant running RHEL5.2 Xen.

My question is, if I were to take one of the tables which has a huge amount of data and turn it into a new database - will I be able to 'run' that database on a different core to achieve a form of load balancing? A bit like setting afinity.

To help illustrate this point:

Database DATA -> TABLES MyData1 + MyData2 (LARGE) + MyData3 (VERY LARGE) - CPU Core 0

into:

Database DATA_SPLIT1 -> TABLES MyData1 + MyData2 (LARGE) - CPU Core 0
Database DATA_SPLIT2 -> TABLES MyData3 (VERY LARGE) - CPU Core 1

Is this possible or should I look at other methods? I've got plenty of cores to throw at this and it doesn't hammer the disk much at all (approximately 20% utilisation).

Thanks MySQL DBA's

Why don't you check out the MySQL Load Balancer?? Check here: http://dev.mysql.com/doc/refman/5.1/...-balancer.html

I'll take a look at it, thanks

5 minutes isn't necessarily that long, depending on the actual data. Do these tables have indexes? What type of SQL are you using, can you give any type of examples? I've learned a lot of the time dealing with SQL and queries, it's usually the SQL being used that is the number one culprit for long running queries. The second culprit is the indexes or lack of indexing.

I'd say for a 5 minute query, you don't need to throw another server at it or the like.

Can you post the describe output of the table, or explain what it looks like, or perhaps the sql output from a mysqldump showing the indexes, etc. What is the query that takes 5 minutes? It is very likely that indexing would help tremendously, but as was said earlier, seeing the query would help diagnose the issue also.