Problem with MySQL MyISAM — duplicate entries and crash on large tables

0 like 0 dislike
12 views
All of a sudden the working draft began to happen strange things:
  • off the two large tables — one for GB and about 70 million records, the other 500 MB and 700 000 records. Approximately 100-1000 inserts per second in the first and 2-5 in the second. From the second data actively selects yatsya
  • periodically, for unknown reasons, the base starts to give the error too many connections. Optimized scripts, one script — one instance of the connection (class database — "single")
  • today neither from that nor from this data began to be duplicated, one request passed from two to 13 times. And not one query, but several that go together.
Scripts checked out, all right, for a long time nothing changed, the project with an average attendance. No surge for today, no.
The server is selected, the settings are standard, the OS is CentOS. Version Of MySQL — 5.0.77

What could be the reason? I with such never faced, I can not understand what is happening.
by | 12 views

5 Answers

0 like 0 dislike
Too many connections — part I will explain. On myisam you can't do it as you described. Active block selects the entire table and the inserts are waiting for the lock. So, if your select stopped for a second, then your data in the queue has already gotten 1000 inserts. Here you have too many connections.
Switch to InnoDB, it is slow, but there is blocking at the row level and failure, she recovered well.
Better to make one extended insert 1000 rows than 1000 inserts one row. Think about it.
by
0 like 0 dislike
FaceBook your not a serious project?
by
0 like 0 dislike
Notice for those who fiercely shit bricks higher, at 70 million records in the largest table is not so much. Not much to say, "for Example, if in one table a lot of records (70 million), it is possible to use the partition".
\r
7 billion and tens of terabytes total size of the database — that is a lot.
by
0 like 0 dislike
with a large number of inserts of your choice innodb
by
0 like 0 dislike
It is necessary to look locks in mysql and iostat at all, and that there may be disk related.
And generally somehow it is strange to see MyISAM for tables that are frequently written.
by

Related questions

0 like 0 dislike
1 answer
asked Apr 9, 2019 by ART4
0 like 0 dislike
1 answer
0 like 0 dislike
1 answer
0 like 0 dislike
1 answer
asked Apr 13, 2019 by Mr-Governor
110,608 questions
257,186 answers
0 comments
28,696 users