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.
Large MySQL database on your VPS?
pubcrawler
Banned
Anyone running a large MySQL database on a VPS?
Looking for databases in the millions of rows total. It can be logging or any other type of data. Ideally a database with continued growth and a good number of daily inserts.
Looking for provider recommendations where such a database operates as it should and IO is fine as is page load/generation time.
Comments
Just few hundred thousand records for me, running from 4GB RAM from Pyramid Servers. Gave 2GB of RAM to MySQL and its doing great!
We MAY be able to help. If you're willing to try it out, we can discuss resources and price, then we can give you some sort of 2 week trial to see if it'll mutually work for both of us
If it doesn't work out, we'll package your VM and you can take it elsewhere.
Your main concern would be RAM for caching and IO. What location are you looking for?
Haven't tried a large MySQL. We run Postgres and OrientDB mostly.
We could probably do something for you with SSD storage, but it wouldn't be within the LEB price bracket
I heard prometeus VZ SSD7 is great for huge databases. Lots of RAM and SSD space. Or their SSD based Offloaded MySQL.
What is the price range we're talking about? What worries me is that on VPS no matter how "exclusive" it is you can always bother other containers with I/O and load.
Millions of rows is easy.
I have a DB with a couple million-row tables sitting on a 128MB LEB and it works just fine. Entire DB size is about 250MB. I think I'm actually using @dmmcintyre3 's mysql settings, which are designed to reduce memory.
Of course, I'm the only one using it...
A database with 1 billion rows in a table and 1 user is a whole lot easier to manage than a database with 100,000 rows in a table and 2,000 users.
How the application is written really matters a lot. If the application is only querying by unique indexed column, then no problem even if its very large.
Not sure if they have stock. Maybe ticket them for inquiries. But their offloaded MySQL is very cheap, but limited to 2GB I think.
http://www.lowendbox.com/blog/prometeus-e5-34-month-1024mb-ssd-openvz-e18-year-256mb-ssd-openvz/
If I were tinkering with this on a VPS, I'd try BuyVM and their offloaded MySQL.
Remember debugging someones forum issue with insane MySQL activity and that was running all on BuyVM. Was real impressed with BuyVM's offloaded MySQL and what they allow people to push throughput wise.
Plans on that are 10GB, so unsure how long that will last you and if they would do a custom larger plan.
So far we have three providers willing to give this a try (IPXCore, Cloud Shards, GetKVM) and Prometeus and BuyVM mentioned.
Honestly, 90% of the time it's not going to be a hardware issue. If it is, you've probably got something so big you need dedicated servers etc.
It comes down to tuning, optimizations, etc... happy to play with it at a cost. Run my own database clusters on VPS with no issues.
Well depends on
1. Definition of exact specs of a VPS can vary between web hosts. I work with alot of clients mainly vBulletin based that run millions of row forum databases on VPS servers.
But you can have VPS node servers with different defined number of neighbours and resource sharing allocation. For instance there's ALOT of difference between a VPS node which has only 4-8 VPS instances versus a VPS node which has 30-100 VPS instances. Of course price will differ too
2. Concurrency load. Shear size of database is only part of the equation. You could have a 10 million row database but if you only ever get 1-5 concurrent users even reading or writing to that database at any one time, then you are not going to require more and higher end server resources to service such database requirements. As such a very low end memory/disk spec'd VPS would suffice.
But if you have 1,000s or 10,000s of concurrent users accessing even a medium sized database, then server resource requirements are going to way higher - to the point of pushing end user requirements outside the region of VPSes into dedicated hosting.
@eva2000, agree entirely.
@concerto49, interesting about your database cluster on VPS.
heh should of read more carefully, as @raindog308 already stated the same as well
RamNode could also be an option, they are on SSD. If UK, VirtIO.
I use their offloaded mySQL for smaller databases but their offloaded MySQL accounts only include 2GB storage which eliminates their offloaded accounts as an option for large databases.
I have a 3.2 GB database that has a table with almost 5 million rows running on a 4GB Xen VPS at CloudVPS . The VPS also hosts 35 websites and 54 other MySQL databases. The average total RAM usage for the VPS is about 2.5GB with an average server load of less than 0.40 (VPS has 3 x 2.3 Ghx cores)
IOPS is key, be sure to get a VPS with someone running some nice SSDs.
Offloaded MySQL was born as a support to LEB vps, were you have less ram to use for the database.... :-)
Key is to have intelligent coding, if its mostly read then memcached will help you a lot. No wonder all big sites uses it. Good amount of RAM and frequently accessed data in RAM and you are done.
Other than that, make proper use of keys and indexes, one of my stupid developer made an stupid query in an application I developed for a client 2 months back and when client hit 50K records, it was not working at one place(time out issues), I then helped him to make proper use of keys and added indexes it is now instantaneous.
Simple it is, just develop something useful (keep basics in mind) and when you grow, optimize as per need. Just remember, with good coding, even a LEB box can do wonders..
offloaded MySQL fascinates me but it also 'scares' me the fact that I don't have access to the load and ram used by my db/queries , do I? How do I know what I need next if they kick me out for heavy load?
Also how far does the convenience go compared to having for example another VPS acting as your own offloaded MySQL?
Does buyvm even provide master-master redundancy for the offloaded MySQL? That's essential in any HA MySQL
Pubcrawler
6 cores
6gb ram
50gb
1 ipv4
$20.00 p/m special deal! Just for you!
No but at no point did we ever list it as an HA setup
At least in 1090 @ coresite there is major heat issues. They run the space at 75-85 F the whole time. Our servers were blinking their overheat lamps on more than a few occasions. The worst part is... We did all those CPU upgrades on our nodes and we should have come out of that with like 4A+ of power savings since a good handful of the CPU's were E series (80W vs 60W). Upgrades finish and right after that, we're back to rimming our power.
Vegas gives us a lot of freedom in the power/growth department so actually going HA could be done if we choose to do so.
http://wiki.buyvm.net/doku.php/sharedsql
As you can see, we don't claim a 100% uptime on it, but past the rare percona crash, it's a very stable service.
Francisco
@Francisco is innodb mandatory to use and for backups? InnoDB is much slower than MyISAM when you need its features.
Also I wish you could elaborate on the "don't be a jerk"part. What kind of load can you put on the server? Would it be a higher load compared to having a second VPS from you plans and run MySQL on your own?
Maybe on regular SQL but percona's innodb is very fast. Reason we recommend it is simply because innodb allows per row locking so backups can run freely. Worst case your backups won't be a 'true' snapshot of time on myisam since we use --skip-lock to stop peoples sites from being down.
Generally you'd have to be doing like.... table scans on a 300,000 row table that has exactly 0 indexes. We have a few users that hit 100 - 200 q/s w/o issue and cause us no issue doing so. Why? Because they index any field that they scan.
It's rare that we have to bust peoples chops and when we do it's almost always because of table scans or terrible things like that.
Francisco
Are the indexes all cached? Or are there RAM limits per user?
Is percona's innodb as fast as regular MyISAM?
As much as possible is cached. We have like 4 - 8GB of key buffer. alone. I may be going to Vegas again in the next 6 months to build a few more nodes, if I do that I'll likely replace the box the SQL runs on and put it on an L5520 with 48GB+ RAM, instead of the E3 with 32GB RAM max.
I'm not 100% sure. A lot of software is moving to innodb and i've heard 0 speed complaints.
Honestly, the only times our SQL has speed complaints is if someone is doing something really stupid. It's part of why I want to get the L5520's in there, just so we have more cores.
Francisco
There's a somewhat cryptic message in http://dev.mysql.com/doc/refman/5.5/en/query-cache-configuration.html ...
...but in typical Mysql fashion, it does not say what is "too large" or what "a very large cache" is. I'm guessing you haven't run into such issues?
@Damian I believe the key buffer is different from the query cache. I think you are comparing apples to oranges.