Description:
I'm using MySQL 5.7 on aurora server 2.09.1 of AWS system.
1. Problem overview
My Amazon Aurora MySQL is out of memory after a long time using the query with the ST_DISTANCE_SPHERE function in the sub query
2. Problem detail
I have a shop table in the system with longitude and latitude column, which show the position of the shop
In my query, I calculate the distance from a specific position (longitude, latitude) to the shop, and get the 100 nearest shop
SQL1:
SELECT
shop.*,
ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT(CONCAT('POINT(', :longitude, ' ', :latitude, ')')),
ST_GEOMFROMTEXT(CONCAT('POINT(',
shop.longitude,
' ',
shop.latitude,
')')))
FROM
shop
LIMIT 100;
==> It's ok here.
Then, I used the query bellow to count the shop
SQL2:
select count(*) from
(
SELECT
shop.*,
ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT(CONCAT('POINT(', :longitude, ' ', :latitude, ')')),
ST_GEOMFROMTEXT(CONCAT('POINT(',
shop.longitude,
' ',
shop.latitude,
')'))) as distance
FROM
shop
) as tblTemp
I have about 15000 shop in the system. I code an API which call SQL1, and then SQL2.
Make a benchmark test as following:
- 10 request per second
- send request continuously in 30 minutes
Test result is
- After starting the test about 10 minutes, RAM is decreased
- When I stop the test, RAM is not freed
- If I continue to test, RAM keep going down, then MySQL server dies and need to be restarted
Actually, my system is going to die after a day or a few days depending on the number of user's request
- If I remove the SQL2 from the API, RAM still goes down a bit but will be restored soon. My system will work stably. Therefore, the problem is in SQL2.
- If I remove the block which calculate distance from SQL2 as bellow, RAM also decreases a bit but will be restored soon. There is no problem with my system anymore
SQL2'
select count(*) from
(
SELECT
shop.*
FROM
shop
) as tblTemp
==> I think there is a memory leak problem with ST_DISTANCE_SPHERE function when used in sub query
How to repeat:
Use 2 SQLs in a long time (above 30 minutes)
Description: I'm using MySQL 5.7 on aurora server 2.09.1 of AWS system. 1. Problem overview My Amazon Aurora MySQL is out of memory after a long time using the query with the ST_DISTANCE_SPHERE function in the sub query 2. Problem detail I have a shop table in the system with longitude and latitude column, which show the position of the shop In my query, I calculate the distance from a specific position (longitude, latitude) to the shop, and get the 100 nearest shop SQL1: SELECT shop.*, ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT(CONCAT('POINT(', :longitude, ' ', :latitude, ')')), ST_GEOMFROMTEXT(CONCAT('POINT(', shop.longitude, ' ', shop.latitude, ')'))) FROM shop LIMIT 100; ==> It's ok here. Then, I used the query bellow to count the shop SQL2: select count(*) from ( SELECT shop.*, ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT(CONCAT('POINT(', :longitude, ' ', :latitude, ')')), ST_GEOMFROMTEXT(CONCAT('POINT(', shop.longitude, ' ', shop.latitude, ')'))) as distance FROM shop ) as tblTemp I have about 15000 shop in the system. I code an API which call SQL1, and then SQL2. Make a benchmark test as following: - 10 request per second - send request continuously in 30 minutes Test result is - After starting the test about 10 minutes, RAM is decreased - When I stop the test, RAM is not freed - If I continue to test, RAM keep going down, then MySQL server dies and need to be restarted Actually, my system is going to die after a day or a few days depending on the number of user's request - If I remove the SQL2 from the API, RAM still goes down a bit but will be restored soon. My system will work stably. Therefore, the problem is in SQL2. - If I remove the block which calculate distance from SQL2 as bellow, RAM also decreases a bit but will be restored soon. There is no problem with my system anymore SQL2' select count(*) from ( SELECT shop.* FROM shop ) as tblTemp ==> I think there is a memory leak problem with ST_DISTANCE_SPHERE function when used in sub query How to repeat: Use 2 SQLs in a long time (above 30 minutes)