CODEWITHSUNDEEP
phpmysqlperformanceapache
Pavel Niedoba
Pavel Niedoba

Reputation: 1557

PHP app with mysql and long running backup

I have PHP application using mysql as its database. The application is not heavy, normally it loads immediately. It has admin page, where I can run some queries which might return very long lists. There is also cron process backing whole database few times a day.

I noticed, that user pages which normally loads very fast can time-out or become slow when backup or long running (about 30sec) admin query is taking place.

Server is single processor virtual machine. I think that even if there's another thread using database, it should still respond 2 or 3 times slower, not like 100 times slower.

So how should I address this problem ? Can I set priority to mysql query or can it be more PHP-Apache problem ? I don't have too much information about it, because it happens rarely and mostly just reported by users.

I will be happy for any hint where to start looking because I don't want to test/debug production site nor install test environment. Thank anyone

Upvotes: 0

Views: 51

Answers (1)

Rick James
Rick James

Reputation: 142298

How is the backup being taken? mysqldump?

Are you using MyISAM or InnoDB? Sounds like MyISAM?

Regardless of the answers to my questions, here is what you are faced with: The dump is hitting the disk vary hard, and it is probably blowing out the cache in RAM. (The details depend heavily on the answers.) This inherently slows things down.

Furthermore, if you are using MyISAM, then the table is locked; this is far worse than if InnoDB were being used. InnoDB uses row locks and is less invasive, but not free.

What is the setting of key_buffer_size and/or innodb_buffer_pool_size versus how much RAM is available? Those can have a impact whether the "cache is blown".

Upvotes: 1

Related Questions