MySQL queries timeout or lead to blank screen on web version
For the last 2 days or so I've been running into some problems with my personal project database. It seems queries in MySQL Workbench timeout and in the railway site they give errors (even when they shouldn't) or never return.
Attempted:
calling a procedure
selecting all from a table
showing events
with no success
Tried:
restarting the container
running the queries in the web version of railway
with no success
Is there simply a geographical time problem or could there be something more or even a bug? All these queries worked fine previously, especially select *.
39 Replies
Project ID:
f3f3aa54-1f32-4a2a-9e9a-61d26191712df3f3aa54-1f32-4a2a-9e9a-61d26191712d
but you are able to connect to the database just fine?
Seems so, though what used to be near instant now takes about a second or two
I don't have a ton of data either, mostly tables with ~100 rows
a second or two for the connection to succeed?
I believe so. Do you want me to dig up a specific metric?
no no that's fine
seems like your database may be in a degraded state
Thread has been flagged to Railway team by @Brody.
Hmm... is it normal with just this little data?
it's not normal under any circumstance, unless you changed some settings in your database?
Just enabled the task scheduler for one task - occuring 4 times a day, just the aforementioned procedure call
The procedure is dropping a table and pretty much creates a copy of one from another schema
Created some users but only 2 are in regular use - the other few are mostly debugging and never used
Anything else I changed was just workbench timeout settings
yeah so you didn't change any of the databases setting or anything like that right?
nothing major no, working with 99% defaults and the changes mentioned above
is the task scheduler a setting in mysql? that seems more like it would be a service running a scheduler?
I think it's a service
Really similar to cronjobs or Task Scheduler in OSs
has to be turned on though, default is off
okay then it's not a mysql setting, from what you've said, it doesn't look like you changed any mysql settings
please wait for the teams response
in the meantime you can always create a new database and restore a backup if you have one
Hi! Do you have exact timestamps of when this happened? It'd help us narrow down where the issue might be
Hi!
Would you like a specific source for the timestamps? I.e. the db or the server's timestamp?
Ah, well they're timestamps. So shouldn't be much variety there or differences at all.
Query at Sep 20 08:54:13 succeeded
will return when it fails
Right now it failed so it's Sep 20 10:48:40
railway site they give errors (even when they shouldn't) or never return.Could you elaborate on this? Are you specifying max connection timeout anywhere in your app/mysql workbench? Is this happening intermittently, and are there any patterns you've observed that would cause this?
I am specifying max conn timeout, but it's mostly above the default values
generally after several hours (maybe 2-3?) it slowly adds up
Eventually between 3-5 hours the slowdown becomes critical
Also connections are cleared both by the DB by default and by the backend of my site so there's not a hog on the connection pool either
Are you hitting any memory/cpu limits?
Not a lot of information to go on here as we haven't seen this happening for any other users 😕
Oh damn, didn't see your answer!
Not really... I think it might be a regional problem. I currently am calling from Romania, asked someone from Sweden and the UK to call and there's quite some difference between each of us in the time to get a response from the service... and making it worse is the fact that none of my testers could even get stable results
So... I am considering just remaking the service alltogether
Got any guides on how to do that?
Also thinking maybe cloning would not be a great idea in terms of getting different results
at the moment I set the timeout period to 30 minutes or so for queries and I'm rolling with it even if it's slow af sometimes - with restarts sprinkled in the middle so i'm limping but i'm walking through
If you have the Pro plan, check out https://docs.railway.app/deploy/deployments#regions
Sadly, I do not
Got any clue how to reduce the buffers from config with railway?
@Brody @Ray Hi guys
After a bit of a pain in the ass process I realized what was wrong: my service was old - at least older than most features recently added and thus I think it was spazzing out. I basically backed everything up, recreated the service and put everything back where it was supposed to be, now everything works properly and I can put the app to sleep, handle internals etc.
Please consider rounding up people with older services and informing them that yes, there is a huge difference to be made by recreating their services now (16$ estimation vs 7$ estimation for my database)
haha you finally did what i suggested
Yeah, I really thought I fucked up somewhere along the way and didn't want to go through the hassle of backing up 50 users manually
Gave up after a month of tryin
Still not sure how to handle backups with railway, or local cloning/configuration editing
there's multiple templates on the marketplace that can take database backups for you
But yeah turns out my service was ancient and I really didn't get what you meant the first time when you said creating a new one at that point haha
I assume the one you created is a database service with a volume, since you are on beta?
Right
Honestly out of the entire marketplace at a glance I think only 1 solution is non-cloud storage/not paid
now create a mariadb database as a replacement for mysql and use mariadb instead, it uses less memory
if only life was that simple 😦
Sadly, i'm on track learning mysql for a gig in the somewhat near future so I gotta stick to the beast
use one of the S3 backup based templates, then use cloudflare R2 as the S3 storage provider, you get the first 10gb of R2 storage free
I was under the impression that mariadb is a drop in replacement for mysql in most cases?
turns out there's some wild, wild thing going on with more advanced dynamic memory assignment scripts and what-not and this is a legacy enough project that some DBA thought up their own framework within mysql for handling stuff. Since this is a financial organisation most of their "best practices" are just panicked jerk-moves to crashes in the past so there's a big no-no on taking anything out of their very specific design
big words
Hence the focus on memory management and my general confusion if it the mistake was on my end cause I knew for a fact I was messing around with memory management. The problem was that the settings definitely should've crashed the mysql server rather than let it go up and wild.
well I'm glad you got it working now!
Thanks a bunch Brody and I'll go checkout the backup service.
no problem 🙂