Polr hangs when there are hundreds of thousands of links in DB, and consumes all server RAM

[urbaez22]

Polr is having a weird behavior in our production environment, it's throwing timeout when shorten action is called from either API or web interface. The server in which is running got out of memory and there are tens of thousands of queries to DB (according to NewRelic), with each request lasting several minutes (after throwing timeout).

Expected Behavior

Just shorten the link regardless of the amount of links already shortened in DB.

Current Behavior

The server in which is running got out of memory and there are tens of thousands of queries to DB (according to NewRelic), with each request lasting several minutes (after throwing timeout).

Possible Solution

After logging been enabled, we could find some queries to DB lasting too much (more than one second), so we created an index in table links for is_custom: CREATE INDEX links_is_custom_index ON links(is_custom); And changed a PHP instruction to execute a raw-query instead of a Eloquent-generated one (as before). New raw query: SELECT * FROM links WHERE id = (SELECT max(id) FROM links WHERE is_custom = ?); And query started to last less than 10ms per call. Nevertheless, the problem arose after some days, and we found there's a mechanism in code used to ensure not to pick an already created ending to short-link (a while loop). There we found MySQL was returning results when looking for a just-generated ending because it was case-insensitive in WHERE clause. So we altered the table to store the short_url as binary, and that resolved the problem.

Steps to Reproduce (for bugs)

1. You have to have hundreds of thousands of links in DB, so you can see the MySQL issue when ensuring the just-generated short ending has not been used yet.

Context

The issue affected operation in production because Polr is sharing the same server with a Tomcat which hosts several critical Java web applications.

Your Environment

• Version or latest commit hash (git rev-parse HEAD): f9622d733b554154a39e9a716c5c134da35acba7
• Environment name and version (e.g. Chrome 39, PHP 7.0, etc): -> php7.0-fpm -> nginx 1.10.3 -> Ubuntu Server 16.04 -> MariaDB: Server version: 10.1.44-MariaDB-0ubuntu0.18.04.1
• Instance link (optional):

[cydrobolt]

Thanks for reporting this, I'm not sure why the case-insensitivity default in MySQL is causing your server to hang, but you are right that it definitely should default to case-sensitive search. We should perform a BINARY comparison so that the WHERE clause is case sensitive. Would you be willing to elaborate on why the case insensitive issue was causing the loop to hang?