CODEWITHSUNDEEP
mysql
pocketfullofcheese
pocketfullofcheese

Reputation: 8847

can mysqldump on a large database be causing my long queries to hang?

I have a large database (approx 50GB). It is on a server I have little control over, but I know they are using mysqldump to do backups nightly.

I have a query that takes hours to finish. I set it to run, but it never actually finishes.

I've noticed that after the backup time, all the tables have a lock request (SHOW OPEN TABLES WHERE in_use > 0; lists all tables).

The tables from my query have in_use = 2, all other tables have in_use = 1.

So... what is happening here? a) my query is running normally, blocking the dump from happening. I should just wait? b) the dump is causing the server to hang (maybe lack of memory/disk space?) c) something else?

EDIT: using MyISAM tables

There is a server admin who is not very competent, but if I ask him specific things he does them. What should I get him to check?

EDIT: adding query

SELECT citing.article_id as citing, citing.year, r.id_when_cited, cited_issue.country
FROM isi_lac_authored_articles as citing # 1M records
        JOIN isi_citation_references r ON (citing.article_id = r.article_id) # 400M records
        JOIN isi_articles cited ON (cited.id_when_cited = r.id_when_cited) # 25M records
        JOIN isi_issues cited_issue ON (cited.issue_id = cited_issue.issue_id) # 1M records

This is what EXPLAIN has to say: 

+----+-------------+-------------+------+--------------------------------------------------------------------------+---------------------------------------+---------+-------------------------------+---------+-------------+
| id | select_type | table       | type | possible_keys                                                            | key                                   | key_len | ref                           | rows    | Extra       |
+----+-------------+-------------+------+--------------------------------------------------------------------------+---------------------------------------+---------+-------------------------------+---------+-------------+
|  1 | SIMPLE      | cited_issue | ALL  | NULL                                                                     | NULL                                  | NULL    | NULL                          | 1156856 |             |
|  1 | SIMPLE      | cited       | ref  | isi_articles_id_when_cited,isi_articles_issue_id                         | isi_articles_issue_id                 | 49      | func                          |      19 | Using where |
|  1 | SIMPLE      | r           | ref  | isi_citation_references_article_id,isi_citation_references_id_when_cited | isi_citation_references_id_when_cited | 17      | mimir_dev.cited.id_when_cited |       4 | Using where |
|  1 | SIMPLE      | citing      | ref  | isi_lac_authored_articles_article_id                                     | isi_lac_authored_articles_article_id  | 16      | mimir_dev.r.article_id        |       1 |             |
+----+-------------+-------------+------+--------------------------------------------------------------------------+---------------------------------------+---------+-------------------------------+---------+-------------+

I actually don't understand why it needs to look at all the records in isi_issues table. Shouldn't it just be matching up by the isi_articles (cited) on issue_id? Both fields are indexed.

Upvotes: 0

Views: 1116

Answers (2)

Matt Beckman
Matt Beckman

Reputation: 5012

For a MySQL database of that size, you may want to consider setting up replication to a slave node, and then have your nightly database backups performed on the slave.

Upvotes: 3

user149341
user149341

Reputation:

Yes -- some options to mysqldump will have the effect of locking all MyISAM tables while the backup is in progress, so that the backup is a consistent "snapshot" of a point in time.

InnoDB supports transactions, which make this unnecessary. It's also generally faster than MyISAM. You should use it. :)

Upvotes: 1

Related Questions