Running mySQL database from memory

N

nry

Limp Gawd
Joined
Jul 10, 2008
Messages
409
I have a PHP based app service which allows a load of tablets on a LAN to communicate with each other. Everything is stored in mySQL with around 100 queries a second :eek:

The database never exceeds 50MB in size. The app only runs for 4 hours.

It's a pile a crap, there is no budget for a rewrite, currently the server struggles with the load, going to a higher powered server isn't an option as it needs to be remotely portable.
Currently using an i7 with 16GB ram and 128GB SSD.

Anyway my question, is there a way to easily run the mySQL database from memory and only have writes touch the disk?

This will also use mySQL replication across two servers for basic fail over.

While I understand running it in memory like this is probably very bad, but currently I see no other option for a quick fix. Looked into memcached, not an option due to the time it would take to implement and test.

Also looking into using opscode caching for PHP to speed things up a little
 
I don't get it. You have a really small DB that's getting hit by 100+ queries a second and you refuse to use an in memory db to act as a caching layer.

It really wouldn't take long to setup redis or memcached and get everything working.

You could also try figure out which of your queries take the longest to execute and then rewrite them. 100 queries a second isn't much at all, I imagine you have some really poorly written queries that are taking much longer than necessary to execute.
 
If I could show you the code base I would. Got to be very brief in how much I say here as it is due to my contract. Im lost for words at the code, what the developer was thinking is beyond me.

I have a number of hours I am allowed to spend setting up the servers on this project and that's it.

There is ZERO budget for any code modifications, if I had my way I would rewrite the whole thing! While I understand implementing a nice caching layer wouldnt take long on a well written app, the level of code duplication on this is through the roof, I have found one query + block of supporting code duplicated 13 times!!

Also it's not so much the complexity of the queries, they are all very simple selects on one table, theres just hundreds of them all over the place. While any sane developer would probably do a join onto the other tables, this just does another query on the next table. :eek:

So if you imagine all these tablets hitting the server. Each one makes multiple requests every 2-3 seconds. And each request will carry out upwards of 10 queires, you start to see how it struggles.

Hence me wanting to do something at the database layer so I don't have to touch/break the code. As somehow it does actually work :confused:
If it's not possible I will just setup as before and hope the server remains up. :rolleyes:
 
I understand. A few years ago I used to be that guy haha. I think you can still setup a caching layer.

It really isn't going to be that difficult, even with the worst code base imaginable. I don't know anything about in memory mysql tables but I googled it.

This came up:
http://dev.mysql.com/doc/refman/5.0/en/memory-storage-engine.html

Implementing that seems much more complicated and tedious than just wrapping a few hot queries in a single if statement and then load the data from redis instead of mysql (ie. this is your cache layer). It's a very simple band-aid fix. After targeting the heaviest used queries you can apply exactly what you've been doing to the others.

You will just need to be weary of each potential cache read you implement because you will also need to update or expire the cache on writes too.
 
Duplicating code in a SQL platform is acceptable.
Performing unnecessary table scans is not.

Consider adding indices if you're read heavy. Consider reducing them if you're write heavy.
Verify that the clustered indices make sense.

If you're not allowed to change code you may still improve performance with some server tuning.
 
MySQL is notoroiusly bad at caching. You can try tuning it, but you're probably better off with a different architecture or a different DBMS.
 
yeah, you may be able to tune it some. look at the queries and make sure the common values are indexed. That is a fairly fast fix if they are not.
 
Thought this would be the case, sounds like I am fighting a losing battle on this.
My only option is probably going to just cross my fingers and hope it works!

I had a mad idea of running the db from a ram disk too!
http://tomislavsantek.iz.hr/2011/03/moving-mysql-databases-to-ramdisk-in-ubuntu-linux/

Obviously really bad, but if I have mySQL replication setup with the 2nd server storing on persistent storage, worst case I will only lose a few seconds worth of data which I can pretend never happened haha
 
Sorry; I misread the first post. Is it true that you haven't done any query tuning?
 
Correct, as it's not an option.
Touching the code is out of the question unfortunately, how I love my job :\
 
If touching the code is not an option, then fixing the problem is not an option and it's that simple. While you might make progress tuning the physical schema, you need to push back on that constraint, as it's unreasonable and counterproductive.
 
nry said:
Correct, as it's not an option.
Touching the code is out of the question unfortunately, how I love my job :\
Click to expand...

So you're expected to fix performance issues using only configuration changes and what you can modify outside of the code?

Unless the cause of your bad performance is configuration related, you won't be able to fix much. You really should fight with whatever management is making these decisions and tell them you can't get anywhere unless you can make code changes. It doesn't need to be a total re-write, but if you can't even tune your queries or even think about using adding a caching layer, there's no reason to bother budgeting to make any configuration changes either.
 
I thought there may be a small chance of finding a dirty hacky way to resolve this.

There is absolutley no way that even if I had the budget I would attempt implementing a caching layer into this. The code is impossible to follow, the variable names make no sense and follows no consistent way of working. With code duplication to the extreme and unused code almost every other line!
A rewrite in my opinion is the only option.

Looks like I'm going to just have to support this on site and hope that it won't fail while in use by a few 100 people!
As some of you know management sometimes live in a bubble and have no idea what they have been given is utter ***
 
Edit: not resolve, improve server load*

As its always going to be crap
 
Seems like you're not hearing us. We agree that management is being ignorant. We're telling you it's your job to explain to them the problem and how it is best addressed. Inform and enlighten them. It's stupid to say that it's a management problem, then do nothing to improve management and proceed with the terrible approach you're suggesting. At that point, you'd just be compounding their mistake.
 
I know exactly what your saying, and that what I am proposing is the worst way of approaching this situation. But I'm left with 2 options
1. Attempt some dirty hack server side
2. Leave it as is and hope the server won't fail

I have sat down with them and said you can't use this in a production environment, because of x, y, z.
I'm only addressing 1 of many issues with the application here.

They are perfectly aware of what they have since I came on the scene. Will they do anything about it? No
There is no budget to rectify it.

The response I got was either you do it or someone else will. And not wanting to lose other jobs which are unrelated to this project, or potential future work I agreed.
 
Sounds like either you're incapable of making a persuasive argument, or that it's a job worth losing.
 
All the other projects are fine, this one is just ridiculous
 
That "someone else will" line is almost always bs. I've dealt with clients who always said things like "well, I know someone who can get this done for $50" [meanwhile it's a $1,500 project easily].

It's not unreasonable to tell them they should have someone else handle it. Chances are they will get in touch with you in a few weeks and agree to pay the actual amount it costs to do what they want because that magical person they always happen to know doesn't exist.
 
ShoeLace said:
That "someone else will" line is almost always bs. I've dealt with clients who always said things like "well, I know someone who can get this done for $50" [meanwhile it's a $1,500 project easily].

It's not unreasonable to tell them they should have someone else handle it. Chances are they will get in touch with you in a few weeks and agree to pay the actual amount it costs to do what they want because that magical person they always happen to know doesn't exist.
Click to expand...

I did consider that, but they already pay me well over market rate, the project is only 4 days. And this client keeps me very busy with work, usually 75% of my work over the year.

So I decided that I would be an idiot to risk losing the client over 4 days of nail biting and pulling my hair out. When all other projects go very smoothly and rarely over my time allowance.

I already have an contract signed which states that basically states if it all goes tits up it's not my fault. So im covered on that front.

Hope that clears up a few points that have been made about why I have agreed to this. As if you were in my shoes, would you want to potentially lose such a client over 4 days of ****?
 
Did he actually say "you're not allowed to touch the code base" or did he just say there's not much of a budget to work with? It sounds like you won't fix it because he won't pay for it.

I really do understand how bad it is to work with really poorly written code. It saps your entire will to work, and you would rather impose bodily harm on yourself instead of work with it, and it has to be doubly worse since the code isn't even yours to begin with.

If this guy is giving you most your work and overpays most of the time I think you should just step up and spend the dozen or so hours it takes to implement the cache layer and be done with it. Now it's a fixed problem, he's happy and you don't have to worry about it anymore.

If he strictly imposes the "can't touch the code" rule then you need to make a better argument as Mike mentioned. Just use some analogy like...

"If you drove into a building and trashed your car would you goto a mechanic and demand them to fix the car by just looking at it and performing basic diagnosis on it?"

It sounds borderline retarded but that's what he's expecting from you.
 
If I could fix it in a day I'd probably take the hit and implement a caching layer. I can touch the code, but they would be concerned about testing the whole thing.
1 test takes of the complete app process takes around 2 hours. Zero automated tests so all has to be done manually which is incredibly laborious

I have just done a quick search through the project, theres 892 select queries, 307 INSERT and 822 update queries.
That isn't going to be easy to implement a caching layer, bearing in mind the code is unreadable in alot of places so I'd probably have to tear it apart a little to work out what that part is actually for.

Even if I did manage to implement the caching layer, the amount of testing scenarios which would need to be carried out is almost 3 days of work alone.

Basically I am fighting a losing battle with this one, I know that.
And I know what there asking is retarded but as far as the non technical people at the top are concerned it works.
Telling them its very badly written means nothing to them or it MAY fail, they see it working and say it's fine.
It really is like talking to a wall.

The whole architecture of the code is flawed, it simply will never work properly. There is nothing that can be done in any reasonable amount of time that will solve the issue.

Hence me asking for a dirty hack to speed things up a little to attempt to reduce the load a little on the server.
Yes it's completely backwards and the wrong way of doing things.
 
Ok, that's pretty big. I expected there to be much less code involved. They can look at it running and see it's working but if it cripples your server and either crashes or hangs all the time then it's definitely not working.

I still think if you explained the situation to them they would budget out the necessary $ to get it rewritten. It sounds like it's an important piece of their business.

Translate this:
The whole architecture of the code is flawed, it simply will never work properly. There is nothing that can be done in any reasonable amount of time that will solve the issue.
Click to expand...

Into something they can understand better. Tell them they have no choice. Either the service is going to fail to work or they can budget out the $ to properly fix/rewrite it. Then use your knowledge to push them in the direction of rewriting it because fixing the currently implementation is probably going to be more expensive for them.
 
I think they know perfectly well what needs to happen, and just ignoring the fact they hired an incompetent developer in the first place. But not interesting in fixing it as technically it does work, and it hasn't failed YET but it's on edge, running a server flat out like this is asking for something to give sooner or later.
The issue is its only a medium sized business I work for. But this project is for one of their clients who are completely unaware of such issues. Not my place to talk to them about it either.

I may have a go at running the db from a RAM disk and cache all php code one evening and see what happens. Even if it reduces load by 10% it could be worth it.
 
nry said:
I think they know perfectly well what needs to happen, and just ignoring the fact they hired an incompetent developer in the first place. But not interesting in fixing it as technically it does work, and it hasn't failed YET but it's on edge, running a server flat out like this is asking for something to give sooner or later.
Click to expand...

I would tell them the only reasonable thing that can be done with it is a re-write. Let them say no. They'll either find someone else who will tell them the same thing, or they'll just let it become a problem, at which point they should understand that it is garbage.
 
Storing the database on a RAM disk is absolutely the wrong thing to do. At power loss or restart, you'll lose all the data.

If you're telling them about the problems the same way you're telling us about them, it's obvious why they don't seem to be receptive. You want to make a reasoned argument, not a rant about the problems. Since you're responsible for fixing the problems, it absolutely is your role to communicate with them about the state of the system.

An exercise that might help you is examining the issue from their perspective. They think things are going fine; you know they're not. But you don't tell them. When the system goes south, they'll want to know why nobody was managing it or remedying the problems. If you knew, or someone knows you knew, then they're going to be very disappointed that you weren't working with them towards a solution.
 
mikeblas said:
Storing the database on a RAM disk is absolutely the wrong thing to do. At power loss or restart, you'll lose all the data.

If you're telling them about the problems the same way you're telling us about them, it's obvious why they don't seem to be receptive. You want to make a reasoned argument, not a rant about the problems. Since you're responsible for fixing the problems, it absolutely is your role to communicate with them about the state of the system.

An exercise that might help you is examining the issue from their perspective. They think things are going fine; you know they're not. But you don't tell them. When the system goes south, they'll want to know why nobody was managing it or remedying the problems. If you knew, or someone knows you knew, then they're going to be very disappointed that you weren't working with them towards a solution.
Click to expand...


Agreed.
Also, you can't just tell them it needs a rewrite.
Every arrogant and lazy developer claims a rewrite is required. I'm not suggesting that's you, but if they've been in the software industry long they know this.

And "the big rewrite" is usually a trap and a mistake.

Your task, as a professional, is to locate specific pain points and propose surgical solutions. If they can't do major platform work perhaps they will do small pieces as they can afford. If you're not willing to do this research and reporting then perhaps this specific task isn't for you.
 
nry said:
I have just done a quick search through the project, theres 892 select queries, 307 INSERT and 822 update queries.
Click to expand...
The good news is that you don't have to tune them all -- only the slow ones. Or only the most frequently executed. If they're all slow, then it's even easier, since the problem is most likely with the database schema and a few fixes to that would improve them all.
 
mikeblas said:
The good news is that you don't have to tune them all -- only the slow ones. Or only the most frequently executed. If they're all slow, then it's even easier, since the problem is most likely with the database schema and a few fixes to that would improve them all.
Click to expand...

Or maybe, since he did mention code duplication....

nry said:
With code duplication to the extreme and unused code almost every other line!
Click to expand...

...300 of those 800 select statements are the same select statement, and tuning one will have tuned 300 of them.
 
Cant touch the code, cant update the hardware. How exactly do they expect you to solve the issue? Idiots.
 
mikeblas said:
Storing the database on a RAM disk is absolutely the wrong thing to do. At power loss or restart, you'll lose all the data.
Click to expand...

As I would have mySQL replication to a second server this wouldn't be a major issue. Yes I may lose a few seconds of data if there is a slight lag between the servers, but it isn't mission critical data so could get away with this. Would aim for persistent storage on the 2nd server. Yes it's a terrible way of doing it and I would never suggest this usually, but I am limited here.

Zangmonkey said:
Agreed.
Also, you can't just tell them it needs a rewrite.
Every arrogant and lazy developer claims a rewrite is required. I'm not suggesting that's you, but if they've been in the software industry long they know this.

And "the big rewrite" is usually a trap and a mistake.

Your task, as a professional, is to locate specific pain points and propose surgical solutions. If they can't do major platform work perhaps they will do small pieces as they can afford. If you're not willing to do this research and reporting then perhaps this specific task isn't for you.
Click to expand...

Thing is I have been working with this company for over 7 years now, and had projects in the past where I have gone to management and told them they simply can't work this way, use this code, expect to hit the deadline etc
95% of the time they listen, come back to me a few hours/days later with what they can do. This is just a no go, I am assuming they must have had a huge headache with the development of it in the first place and don't want the issues all over again.

Dogs said:
Or maybe, since he did mention code duplication....

...300 of those 800 select statements are the same select statement, and tuning one will have tuned 300 of them.
Click to expand...

But ideally to tune these etc you need to know what they do, the tables are not properly normalized or have any sensible column names. It's some of the worst coding practices I have ever seen.

Monkey God said:
Cant touch the code, cant update the hardware. How exactly do they expect you to solve the issue? Idiots.
Click to expand...

Yes ridiculous, and I can't be the first to get asked to achieve the impossible and I doubt I will be the last


Anyway this has gone a little off topic here. With this project I am literally talking to a brick wall with management, there simply not interested. They have seen it working and that's good enough for them.
Im pretty sure they would be liable for legal action if shit hits the fan too, and they were more than happy to sign an agreement with me to allow me to cover my back.

I have explained to them the problems, and im sorry if I haven't communicated this properly across a internet forum in my own time, I am probably ranting about it a little more here than I would show in person mainly due to the fact I have accepted to work with this bag of crap. If you lot have never had clients/projects like this I envy you, as near enough every company I work for has stupid impossible deadlines and stupidly low budgets at some point.
And I know plenty of other contractors and staff developers which have the same problem, usually worse infact so I do consider myself quite lucky in most cases.
 
nry said:
But ideally to tune these etc you need to know what they do, the tables are not properly normalized or have any sensible column names. It's some of the worst coding practices I have ever seen.
Click to expand...

You have the queries and you have the table schemas. You should be able to figure out to a reasonable extent what is going on. Certainly within a reasonable amount of time you should be able to record some statistics on how frequently the different queries are being called and how long the different queries take. Queries that are slow and/or frequent are queries you should look to speed up. Given how much you have mentioned how terrible the rest of the code is, I don't see it being unlikely that their queries are stupid too. If their queries are stupid, you should be able to get some improvement out of them even without having completely reverse engineered the entire application.

I honestly can't see any justification for completely avoiding query tuning altogether. The amount of time it would take to do some query optimization can't be too terribly great in comparison to moving the tablespaces over to some sketchy RAM-drive setup. You'd need to spend a good amount of time testing that sort of a setup just to make sure it's reliable and will actually work. Plus, if you introduce some data integrity issues by persisting the data on volatile storage, you'll be spending even more time cleaning up your mess. I'm not sure I, were I in your management's position, would be comfortable with what I'm hearing from you.

nry said:
Yes I may lose a few seconds of data if there is a slight lag between the servers, but it isn't mission critical data so could get away with this.
Click to expand...

You're not familiar enough with the program or how it uses it's data to optimize the queries, but you are familiar enough to decide that the data isn't mission critical? Why isn't the data mission critical? If the data isn't important, why is it being stored in the first place? How does the application handle missing data/data that doesn't make sense? Are you sure that the developer who wrote the spaghetti code was competent enough to handle these situations? I just don't see data loss as an acceptable outcome in most situations. Most of the time, data is stored either because a record of something needs to be kept, or because that data is being used to make something else work. If data is being lost and that data is needed for the application to work, that application isn't going to work properly, and management is going to think that you took something that was working and made it not work anymore.
 
Last edited:
As mentioned previously I have many queries which should be joins etc
But the biggest thing here is the code really is unreadable, one/two character variable names are very common in this project.

Yes I could probably knock a few better queries in, then I would need to rejig the code to support the data pulled out in a different fashion.
Then test it, for each scenario, your talking days if not weeks of work for what should be a few hours on a properly written project.

My other solution is to take along my new Xeon server on a trolly haha could be interesting loading/unloading that from the van
Knowing this app though, would still struggle!
 
nry said:
Then test it, for each scenario, your talking days if not weeks of work for what should be a few hours on a properly written project.
Click to expand...

If you're not going to test it after you reconfigure it to run the database from memory, you're not doing a good job. Therefore, testing isn't really a variable in this decision because you should be testing it either way.
 
Dogs said:
You're not familiar enough with the program or how it uses it's data to optimize the queries, but you are familiar enough to decide that the data isn't mission critical? Why isn't the data mission critical? If the data isn't important, why is it being stored in the first place? How does the application handle missing data/data that doesn't make sense? Are you sure that the developer who wrote the spaghetti code was competent enough to handle these situations? I just don't see data loss as an acceptable outcome in most situations. Most of the time, data is stored either because a record of something needs to be kept, or because that data is being used to make something else work. If data is being lost and that data is needed for the application to work, that application isn't going to work properly, and management is going to think that you took something that was working and made it not work anymore.
Click to expand...

The app simply picks up where it left off, somehow this works quite well.
Yes some data may be lost that was written to the database, on a related issue one of the tests I carry out is what if the wireless fails?
The app continues to send data but never gets picked up. Then after 30~ it says connection lost. So in theory all that has to happen is the wireless fail and I have data loss. Yet when it comes back up in theory it should return back to pre-data loss ready to collect the data again.
As I said, its shit

Dogs said:
If you're not going to test it after you reconfigure it to run the database from memory, you're not doing a good job. Therefore, testing isn't really a variable in this decision because you should be testing it either way.
Click to expand...

I'd test it but not for every scenario, I'm not too sure that's even possible, nor does anyone know all the possible options to test. This is far from a small app, I believe there are parts which are not even used.
The test plans I have been given are around 1-1.5 days worth. That's to be done with or without any server setup changes.

And if I am honest a little bored of this discussion as it's off topic now, yes I am going about it all wrong, it says that in my first post, but have NO CHOICE UNLESS I WORK FOR FREE! I have mouths to feed and can't be putting the amount of time required to get this done correctly for no return. I'd be lucky to get a thanks!
I thought there may be some way of caching mySQL that I had never heard of, clearly not.
 
dude, have you looked at the indexes and compared to the queries? you can add indexes easily and this can be a simple fix that only takes a few minutes but can have a great impact.
 
I didn't see it stated one way or another, but - Have you looked at the MySQL server tunables? The default config can be quite conservative. You could let mysqltuner.pl take a look at it and see what you think of what it thinks.

edit: I know with all the talk of query optimization, replication, and ram drives that it should be a given that it's been looked at, but sometimes we forget the easier things.
 
Last edited:
Monkey God said:
Cant touch the code, cant update the hardware. How exactly do they expect you to solve the issue? Idiots.
Click to expand...

And he can't talk to anyone, and he won't drop the project because he loves the client. It's really just amazing. He should go on Dr Phil, or something.


Frobozz said:
edit: I know with all the talk of query optimization, replication, and ram drives that it should be a given that it's been looked at, but sometimes we forget the easier things.
Click to expand...

Tuning was suggested, but as far as I can tell nry's reaction was to cross his fingers and leave it to fate.
nry said:
My only option is probably going to just cross my fingers and hope it works!
Click to expand...
 
You must log in or register to reply here.
Back
Top