Equiv of "nice" for MySQL queries?

[Armitage]

On my site, I have cronjobs that hit the DB to generate certain reports. Some of these queries seem to hammer the system so hard that mysql becomes unresponsive, which makes the site drag badly.

Is there any way to decrease the priority of certain queries (or users) ... I don't care if it takes twice as long, as long at the db remains responsive. Something like nice for unix processes? All I've fouind is a "high prioority" option, which seems to be exactly the opposite of what I want!

 

[Beau]

Not sure of how to lower the priority, as I'm not a DB admin, but have you looked at your queries to make sure they're as efficient as they could be?

 

[Armitage]

I've been over that script pretty hard, but maybe I'll take another look at it tomorrow.

 

[mugs]

Is the issue that your queries are taking a long time, or that there are a lot of queries? If your queries are taking too long, setting up indexes could go a long way (at the expense of disk space).

 

[Armitage]

Originally posted by: mugs
Is the issue that your queries are taking a long time, or that there are a lot of queries? If your queries are taking too long, setting up indexes could go a long way (at the expense of disk space).

It's not all that many queries, but they're expensive, including a nasty one that needs DISTINCT, which IME really sucks the performance down. I may try taking that out, return all the relevant rows, and to the DISTINCT in my script ... if nothing else, I can 'nice' the script.

It's already heavily indexed, and I've used ANALYZE to make sure it's using them. Problem is that the data I have isn't very rational - I have to use a 3 column unique index to prevent duplicates, which pushes the size of my index file for this table up to about 1.8GB, and MySQL has a 1GB limit on the index cache. At some point, I think I'm going to have to split this table to get reasonable performance from it.

What I really need is a replication setup, so I could run these scripts against the slave database. But that's not in the budget.

 

[Argo]

There's no nice for any of the mainstream databases, as far as I know.

I suggest you go over your database design and create proper indexes. If you're doing a search on a certain field - create an index on that field.

 

[Armitage]

Originally posted by: Argo
There's no nice for any of the mainstream databases, as far as I know.

I suggest you go over your database design and create proper indexes. If you're doing a search on a certain field - create an index on that field.

See reply above.
(aka - bump for the weekday crowd )

 

[Armitage]

Originally posted by: Armitage

Originally posted by: mugs
Is the issue that your queries are taking a long time, or that there are a lot of queries? If your queries are taking too long, setting up indexes could go a long way (at the expense of disk space).

It's not all that many queries, but they're expensive, including a nasty one that needs DISTINCT, which IME really sucks the performance down. I may try taking that out, return all the relevant rows, and to the DISTINCT in my script ... if nothing else, I can 'nice' the script.

FWIW, I just tried this and it makes a huge difference ... DISTINCT sucks.

 

[Mong0]

Just an FYI you may want to try doing a group by instead of distinct, that is if MySQL supports group by clauses.