New on LowEndTalk? Please Register and read our Community Rules.
All new Registrations are manually reviewed and approved, so a short delay after registration may occur before your account becomes active.
All new Registrations are manually reviewed and approved, so a short delay after registration may occur before your account becomes active.
Interests in SQL Offloading?
concerto49
Member
How much interest is there in SQL Offloading?
Is MySQL the only thing that people want? What about e.g. Postgresql? That's quite popular too.
Would you prefer a Master/Master or Master/Slave cluster?
How useful is it? Would it affect your choice in a VPS from a provider?
What performance would be acceptable? Is it only read or read/write?
(Wanting to try play with some non-VPS node hardware for a change)
Comments
Maybe @Francisco or @Prometeus can share some experiences if popular. But I guess Postgres will not be that popular
I've had like 3 people ask for postgres.
We lose money on the venture in the sense that the amp's could be making us far more doing other projects. It sells slow and requires a lot of tuning, babysitting, etc.
The worst thing you're going to experience is when innodb takes a dump. It isn't a matter of if, it's a matter of when. A simple kernel panic can cause your innodb's to go to hell and you have to go through a very long process of recovery since there is no way to repair them.
People expect an SQL that they can throw their heavy workloads at. We have more than a few users with 30GB+ databases on there that expect to be able to chew through with reasonable query times. We have one user that at one point was running such complex queries that a single query run took about 140s of pure CPU slamming and this was 'quite fast' in his books.
The service gets busy and i'm going to have to upgrade to a dual quad w/ 48GB+ RAM next month when I'm in Vegas.
Francisco
I wonder if this has to do with LEB/LET market. Postgresql is heavily used in a lot of organizations and environments that I know of more than MySQL.
Just not in cPanel and other panel environments etc. Also less in LAMP environments of course.
Doesn't sound fun.
I was only wanting to get a few LSI Nytro to play with. Sigh.
I'd be interested in Postgres, but it's one of those things where I'd have to have a VPS in the same datacenter to make it worthwhile
Also Offloaded Redis would be cool, although unreasonable
>
IO isn't usually the big problem with SQL boxes. We SSD cache our box and rarely ever have issues there. The issues come up when you have a user that hasn't taken indexes 101 in their database course. "Hey why is SQL sitting at 750% CPU?".
Francisco
I don't think I'd want to pay for the Internap bandwidth you'd potentially chew up with it being in another data center either
It's expensive.
That's usually the case. Maybe we'll offer it as a managed service instead. Thanks Fran.
I received a few postgres requestes over time, not enough to justify some action.
Mysql service was started because I received a large number of request from people with small vps to save some ram moving mysql elsewhere (as some of them werealready doing with buyvm)
I dont think the service will do some profit, nor I intended to profit from it :-)
What the hell? Is this what people expect they should be allowed to do for $1/month?
Expect and do. We had at least one user that was doing 20 ~ 40Mbit/sec to datashack off our shared SQL. After we warned him 3 times to cleanup his code we simply told him we couldn't service his SQL needs remotely anymore. I'm not sure if he ended up cancelling or just moving his project inhouse.
I have a bad habit of doing my best to offer the moon for $1.50/m but it sometimes nips me
Francisco
@Francisco why not cap query to 5seconds max? Or something like that
We have many users that use ~100 q/s (and need it) and cause us almost no loads.
Francisco
@Francisco, since you openly have your hardware listed on your wiki, except the SQL box - mind adding that to the wiki.
Just curious what your build is like.
You didn't look hard enough
http://wiki.buyvm.net/doku.php/sharedsql
And I thought it'd be something very beefy. There goes my Nytro deployment needs...
I mean 5sec per query
It's getting upgraded to 16 cores and 48GB RAM
A well tuned RAID + SQL configuration goes a long way.
Francisco
According to your data CPU is more important than IO in this scenario?
Everything is important.
The CPU's help deal with the abuse that will happen and buys you some time between shifts or for you to get alerts.
We've had a few times where SQL went thread mental and caused ~200 loads.
Francisco
Totally agrees. Hence my question when offering it wasn't profit, but more if people want it. I see that it adds value to VPS and gets more people to buy products. A lot of things bring indirect profit and not direct profit at times. Have to look at the bigger picture
@concerto49 am a fan of abstracting http, mail, and data base processes as much as possible ..
Is SSD caching vital to MySQL's performance, as its size seems tiny compared to the harddisks? And could you reveal how you incorporate SSD with your IO systems? Is it flashcache?
Can't share either of those
We've put a lot of time into our platform. Our shared SQL idea dates back to when Frantech first opened.
Francisco
As far as my database knowledge goes, besides sorting and a few other things, it's should be all disk io and RAM.
I was evaluating the LSI Nytro for this.
Having said that, if you do stuff like import/export that require compression/decompression, encryption/decryption and perhaps fancy stored procedures then it might require CPU.
Err, well, not exactly. The sorting/searching are mostly all CPU based. SELECT is by far the biggest type of queries encountered.
We're talking about a disk based merge sort or variants of it here. Each block is loaded into memory, sorted and then buffered to a temp file. These blocks are then loaded and merged as they go. This would somewhat consume CPU.
A simple SELECT on the other hand wouldn't. Anything that just scans records doesn't do that much work. JOIN possible, but hence has Fran says is why you need correct indexing.
JOIN is the thing that costs the most and puts the most load on a database. Hence, if you're writing OO code, go with an OO database that doesn't need joins. That's what I use now.
Scans rip through CPU if there's no indexes.
@nickm can fill you in.
/ducks
Francisco
How many records are we talking about? Haven't really seen it being a problem unless they are huge. Is there a join involved or a simple SELECT?
I suspect it has to do with the cache tuning. If it's purging the cache on SELECT every time due to a bad algorithm, then it's bound to chew up CPU.
Every time we've had scan issues it's for people with 100k+ rows in their DB.
@nickm had a case where he was eating 250%+ CPU on a 250k row scan. He threw a few indexes in and he was back down into a few % CPU and a little extra RAM usage.
Francisco
100k+ rows make sense. I assume the tables would be huge and I wouldn't just charge $1/month for it though. (I'm referring to me that is).
Would be setting up monitors and as said might offer this as a managed solution. Wouldn't be too hard to have some tools monitoring such issues and offering a fix to the customer.
Thanks again Fran though. It's good to see what's happening in real world scenarios.