[MDEV-17420] MariaDB slave 10.2 leaks temporary tables Created: 2018-10-10 Updated: 2021-03-12 |
|
| Status: | Open |
| Project: | MariaDB Server |
| Component/s: | Replication, Server |
| Affects Version/s: | 10.2.17, 10.2.18 |
| Fix Version/s: | 10.2 |
| Type: | Bug | Priority: | Major |
| Reporter: | Gabriel Gomiz | Assignee: | Andrei Elkin |
| Resolution: | Unresolved | Votes: | 1 |
| Labels: | leak, myisam, replication, slave, stale, temporary | ||
| Environment: |
Centos 7.4 |
||
| Issue Links: |
|
||||||||
| Description |
|
We have 4 slaves using MariaDB 10.2 doing replication from a 10.2 master. Binary log format is MIXED and temporary tables are using MyISAM format. The number of temporary tables on the slave is growing continuously up to the point of exhausting the file descriptor limit of the operating system assigned to the process. After that the only solution is stopping the slave server and starting again. This was happening with 10.2.17 on the slaves. We have upgraded to 10.2.18 and the behavior is the same. The Slave_open_temp_tables variable counter goes up and is consistent with the number of #sql*.MYD files on the tmpdir.
The server does not delete the tmp files from different slave sql threads execution. For example:
After stopping the server, also does not remove the files. We have to remove the files manually. We have tried changing the variable default_tmp_storage_engine from MyISAM to InnoDB and the counter Slave_open_temp_tables also keeps growing but, of course, we don't see any temp files on tmpdir, but we believe that eventually something will broke inside InnoDB. We have been analyzing the binary log and it appears that all explicit create temporary tables are being dropped, either manually by the application, or automatically by the master after session close. Could these stale temporary tables be internal temporary tables that the server is using to resolve some difficult queries? |
| Comments |
| Comment by Gabriel Gomiz [ 2018-10-24 ] |
|
Anyone know how can I find the names of these stale temporary tables? It would be very useful for debugging... |
| Comment by Gabriel Gomiz [ 2018-10-25 ] |
|
We finally found a workaround to bug MDEV-17490 and managed to get a MariaDB 10.3.10 slave to replicate from our 10.2.18 master. Then, we can confirm this bug is still there on 10.3.10. The slave keeps leaking temporary tables. If yo need any more detail , please let me know |
| Comment by Andrei Elkin [ 2018-10-29 ] |
|
Confirming that _After stopping the server, also does not remove the files. _ The temporary files |
| Comment by Gabriel Gomiz [ 2018-10-30 ] |
|
Andrei, I can confirm that the *.MY? files in our tmpdir are NOT removed at the following server start. As a matter of fact, files are named #sqlXXXX_YY_ZZZ.MY? where XXXX is the process PID number. What files would the following server remove at start? The new instance doesn't know the previous server PID..., and if deletes all temp files, maybe could be deleting temporary files of other MariaDB running instances. Maybe this is another problem on the master, because it started after we have upgraded the master from MySQL 5.6 to MariaDB 10.2. I can confirm that previously (with MySQL 5.6 master) this problem doesn't existed. And could be somewhat related to the 17490 bug, because of misplaced "DROP TEMPORARY TABLES" statements in the binary log? Finally, I agree that those files are destined to removal but should be removed as soon as posible as long as they are not needed any more for subsequent queries because the actual behavior ends up exhausting the operating system file descriptor limit for the MariaDB process and after that the only solution is thu shutdown server process. Please let me know if I can help you debug this further Kind regards |
| Comment by Daniel Black [ 2021-03-12 ] |
|
I suspect |