2

I am fairly new to mySQL, I have had an ongoing problem with my DB.

It works fine for a while (a month or so), then all of a sudden the web socket hangs and doesn't return the information. I have ran mysql check/repair and it does nothing. The DB comes back as OK when I check for errors. The only thing that fixes it is to truncate the table(there is only one) which holds the values and then it works fine for another month or so. I think I narrowed it down to a memory issue but I can't find any documentation about what the "free" on the first line represents(see the pictures), so I can fix it.

Anyone know what this means, or any other issues it could be?

Additional info:

  1. Innodb engine
  2. Never says out of memory
  3. Does not "crash"
  4. The data is just a user ID, latitude, and longitude

Before Truncation of DB (webhook stuck): enter image description here

After Truncation of DB (Webhook works fine): enter image description here

Chris Albert
  • 2,462
  • 8
  • 27
  • 31
Hike more
  • 59
  • 3
  • I think this is a problem with the table rather than the MySQL server - I'm assuming the code behind your web socket is using this specific table? How many rows does it have when you truncate it? (And can you change the code so that it keeps the number of rows down to reasonable proportions as it goes along?) – MandyShaw Oct 14 '18 at 17:25
  • 1
    Interesting, I'm not sure how many rows, although; I can check the next time it happens. In the meantime I will think about how I can change the data structure to keep the rows down. Thank you for the input. – Hike more Oct 14 '18 at 21:50
  • My guess is it has something to do with the memory MySQL is allowed to use. In SQL Server we can cap the engine at a set amount of RAM. Check out this post for some info on this with MySQL. [link](https://stackoverflow.com/questions/1178736/mysql-maximum-memory-usage) – Chris Albert Oct 15 '18 at 13:52
  • Thank you for the input, I have increased the RAM. It will take about a month to get any feedback on the results, I will update when I find out more. – Hike more Oct 21 '18 at 13:54
  • So it looks like it was a memory issue, it took over two months for the issue to reoccur after increasing the memory. I would like to figure out a way to keep the issue from locking up the db if the event does occur again. – Hike more Jan 05 '19 at 15:10

0 Answers0