MySQL query causing process stack up

HI - I have a table in a MySQL db that we were trying to shrink by moving all records older than 1 year from today's date to an identical archive table. Then we've written some pages so these old records are still accessible, but the unneeded 1.1 million records we end up needlessly scanning through on all the current activities pages can hopefully be eliminated from most hits on the db.

So far, so good. But we built and ran our first test on a backup copy and...nothing. We are still waiting. Hours later. If I go into Heidi SQL and try to do anything to this db, the application hangs.

So I went to command line and did a SHOW FULL PROCESSLIST and sure enough, that first query is the first in a line of 30 processes queued up. I can't tell if the first query is still running, or if this will eventually make it through all these processes. I've seen that you can KILL processes, but this appears to be a one at a time thing. If I kill this first process hanging everything up, will the other queued up processes then proceed to run? Any suggestions on an elegant way to stop all of these processes at once?

Could I just stop the MySQL service?

Bill HendersonWeb MarketingAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Matt KmetyEnterprise Application AdministratorCommented:

Restarting the MySQL service will stop the entire process. The queued up transactions should be cleared out after the restart.

We ran into a similar situation with one of our MySQL databases. Are you trying to move them from table to table in the same DB or is the archive table on another server?

At first, we created a bash script that would dump the data in the tables, delete the data in production, and insert them into our archive database that had an identical table structure.

This caused issues. The dump wasn't the issue, but the deletes were. It would hang our database and would take hours to go through the transactions.

Our solution was to dump what we needed using the mysqldump utility. We used a date range to break the dumps into smaller files.

We would then delete small amounts out of production so the DB wouldn't hang. Again, this is what bottlenecked our DB.

We then used the mysql utility to restore the mysql dump files into the archived table.

Now that we have our tables where we want them, the long-term goal is to script all of this to make it an automated process.

Hope this helps.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bill HendersonWeb MarketingAuthor Commented:
Sorry for the delay. Thanks for the help!  We moved the records manually since there were so many. Our process seems to work great on a couple of years of records - but 8 years - no way!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.