very slow queries ~30 seconds

[hickstooor]

Hey Guys,

I've upgraded from yourls v1.5 to v1.9.2 , and also from PHP 7.4 to PHP 8.2

My queries to convert short urls to long urls in specific are slow now. I have 5.5 million rows, when I check my database processes it shows "Waiting for table level lock". I've been reading similar issues to find a fix, I saw something about people fixing it regarding DNS issues and resolve.conf, but unsure of how they fixed or disabled DNS checks. I'm really stuck here, and unsure how to fix :/ could really use some help or guidence on optimizations, maybe some common config errors people make that slow down queries? I will post my debug tomorrow to help you help me :) Thanks friends.

[hickstooor]

debug:
Connected to database yourls on localhost
SQL perform: SELECT option_name, option_value FROM yourls_options WHERE 1=1 (0.00019 s)
Check for new version: no
Successfully encrypted passwords in config.php
SQL perform: SELECT option_value FROM yourls_options WHERE option_name = 'defer_hashing_error' LIMIT 1 (0.00024 s)
SQL perform: SELECT COUNT(keyword) as count, SUM(clicks) as sum FROM yourls_url WHERE 1=1 (15.31301 s)
SQL perform: SELECT * FROM yourls_url WHERE 1=1 ORDER BY timestamp desc LIMIT 0, 15; (0.00929 s)

Also I added define('YOURLS_NOSTATS', true); to my config, but still see these queries being ran:
UPDATE yourls_url SET clicks = clicks + 1 WHERE keyword = '467de82fbb'

Another query I see running a lot and seems to lock the table until it's done is:
SELECT * FROM yourls_url WHERE url = 'http://mysite/dir/preview.php?Doc=someid'

I'm using no plugins by the way, any help or nudge in the right direction will really be appreciated @ozh @dgw , thanks.

[hickstooor]

oh just noticed something as well this query I suspect to be the issue:
SELECT * FROM yourls_url WHERE url = 'http://mysite/dir/preview.php?Doc=someid'

I was watching the mysql processes and this one was "executing" while around 20 other queries where "Waiting for table level lock", and as soon as it finished all the other queries instantly finished. Any idea why it's running this query? Ideally I only want to insert queries and select queries where keyword = "xyz" to be running.

I found where that query is from its the yourls_long_url_exists() function, which I'm guessing I can skip by setting define( 'YOURLS_UNIQUE_URLS', false );

What are my tradeoffs here? Obviously I will have multiple short urls per a long url , but I don't think this will be a problem as my long url contains a hash that should be different every time. However I have 5.5 million short urls already does this increase my chance of a short url collision ? where an existing short url is overwritten to a different long url ?

[hickstooor]

So I've decided to set define( 'YOURLS_UNIQUE_URLS', false ); because my urls by design should already be unique. And I'm running very fast now, processes dont even show up on mysql process list.

I'm still curious of the implications of disabling unique urls @ozh @dgw if you could please just tell me what I should keep an eye on in regards to duplicate problems plzzz :) anyways great software thanks.

[dgw]

I don't know why you felt the need to @-mention specific people in your comments. We all (the core team) already get notifications whenever a new discussion or comment is posted.

The YOURLS_UNIQUE_URLS setting is documented along with the rest of the main configuration options. Its only function is to prevent (true) or allow (false) multiple short URLS being created with the same destination.

The long-running query you spotted is most certainly coming from here, inside yourls_long_url_exists(). Because YOURLS does not have an index on the url column, according to the table setup code, that particular operation being slow makes sense.

Simply adding an index to it isn't necessarily the best option, however. url is a TEXT column, which would require specifying a prefix length if an index were to be added. Two long URLs that only differ at some point past the prefix length could be erroneously considered non-unique. The prefix index wouldn't help your particular case if the prefix length was too short, though (shorter than the real-world value of http://mysite/dir/preview.php?Doc=). MySQL should select candidate rows using the prefix index, then perform full matching against those—and obviously if too many possible matches are selected by prefix, full matching will still be slow.

FULLTEXT indexing is a possible alternative, but would require rewriting the query. Storing a hash of the long URL for efficient indexing and lookup is another alternative, but would require even more code changes.

I'll summarize some of this performance stuff in a new issue, since it seems worth keeping on our radar.

[flaviovs]

@dgw, what would be the impact if one does the below directly on the database?

1. Change the url column to VARCHAR(X), where X is something that would allow any URL one plans to have in their instance + safety net, and
2. Add an index on url

Would that break the code?

Asking because this is a fairly simple change anyone can do without touching code. Of course, selecting X is key, but nothing that a MAX(LENGTH(url)) query couldn't help one to do, assuming all URL use cases are in the DB already.

We, for example, have an instance running for years with almost 500K URLs in the DB. URLs structure is predictable and won't change. However, the maximum length of an URL we have today is 251 which is pretty manageable for a MariaDB/MySQL index, even if we double that length. If we could be confident that such change would have no impact in the code, we would have done that already.

[dgw]

I wouldn't change the column type, but I can't immediately think of any pitfalls from manually adding an index with a prefix length of your choosing that suits the data you have and expect to add in your instance.

Full disclosure: I can't promise that such an index won't conflict with future migrations. You'd need to be prepared to handle those yourself. (I'm also not much of a database guru, so take DBA advice from me at your own risk 😉)

This performance concern is tracked in #3793, and if you find any problems resulting from the added index I hope you'll share what you tried and the outcome(s) there.

[flaviovs]

Thanks for the reply. I'm aware that future migrations could be a problem. Anyways, if we decided to do this I will definitely share the results.