All new Registrations are manually reviewed and approved, so a short delay after registration may occur before your account becomes active.
Choosing a VPS for hosting large database forum
Hello LET,
I'm currently hosting a forum on an ordinary openvz vps with default LAMP stack and willing to go lowend 
The deal is mysql database is large, about 4gb and myisam indexes are 1.3gb.Sphinx is ised for fulltext searching.Luckily its a niche site and do not have much visitors/page views.About 100k/month visitors and 1 million pv/month.There is also xcache running.
My current vps has 8 core xeon 3440 2.52ghz 1536mb ram 100gb sata hdd.I/O is terrible(Benchmark below, forum was disabled while testing) however load average and php execution time is low and I'm satisfied with the browsing speed and uptime of the vps.
http://serverbear.com/benchmark/2012/10/24/e3wq8HvuTzgCy1W8
Do you think is it a good idea to host this forum on a LEB with this mysql and visitor specs ? any suggestions for choosing right LEB ?
Below is the mysqltuner.pl output optimized for 1536mb ram.
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.77-log
[OK] Operating on 32-bit architecture with less than 2GB RAM
-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 4G (Tables: 231)
[--] Data in InnoDB tables: 1M (Tables: 110)
[--] Data in MEMORY tables: 23M (Tables: 1)
[!!] Total fragmented tables: 18
-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned
-------- Performance Metrics -------------------------------------------------
[--] Up for: 9d 11h 33m 31s (19M q [23.784 qps], 2M conn, TX: 42B, RX: 4B)
[--] Reads / Writes: 78% / 22%
[--] Total buffers: 870.0M global + 4.9M per thread (120 max threads)
[!!] Maximum possible memory usage: 1.4G (95% of installed RAM)
[OK] Slow queries: 0% (1K/19M)
[OK] Highest usage of available connections: 27% (33/120)
[OK] Key buffer size / total MyISAM indexes: 700.0M/1.3G
[OK] Key buffer hit rate: 99.8% (361M cached / 828K reads)
[OK] Query cache efficiency: 26.8% (2M cached / 10M selects)
[!!] Query cache prunes per day: 17668
[OK] Sorts requiring temporary tables: 0% (877 temp sorts / 1M sorts)
[!!] Temporary tables created on disk: 46% (26K on disk / 57K total)
[OK] Thread cache hit rate: 99% (33 created / 2M connections)
[OK] Table cache hit rate: 52% (724 open / 1K opened)
[OK] Open file limit used: 34% (737/2K)
[OK] Table locks acquired immediately: 99% (12M immediate / 12M locks)
[OK] InnoDB data size / buffer pool: 1.8M/8.0M
And vzfree output.
./vzfree
Total Used Free
Kernel: 2048.00M 10.90M 2037.10M
Allocate: 1536.00M 1241.41M 294.59M (1536M Guaranteed)
Commit: 1536.00M 1115.40M 420.60M (89.0% of Allocated)
Swap: 20.53M (1.9% of Committed)>
Comments
I think you need a SSD storage for that database. Or offloaded MySQL, tho, with SSD you wont have many problems.
4 GB is not that huge, there can be many SSD offers to cater for your needs.
We have special biz plans with 16 GB per node where no IRC, no gameservers and Tor are allowed. Also you can have backups on the free 20 GB space provided in Germany in case there is an earthquake and italy sinks in the sea
Price is not much bigger than regular SSD offers, but the space is smaller. For a single forum with big database and almost not much else, I recommend lean SSD VPS.
RamNode could also have something for you, as well as other SSD providers, if you give me your location will be able to offer some advice.
Thanks for the mention M.
@imperio - our SSD options would be great for you, although our 1 or 2GB RAM plans are not technically low end pricing.
You don't need SSD's for a database and website that small. Regular sata will do,
we offer KVM that pull 300MB/s+ I/O aswell as OVZ in that pull around 400.
We can do 1GB on KVM for $5. (SAS)
or 2GB OVZ in germany for $6.95
or 1GB SAS in UK for $6.95
That being said if you need the IOPs we can do SAS aswell :-)
If possible, upgrade your ram. Keeping SQL queries in cache will help immensely. This is for a single forum we host:
As you can see, 4 queries in cache are serving ~235 commands per second.
Additionally, the ioping on your benchmark page has the signature of a hardware RAID system that's overloaded.
I think a VPS will handle your load, but it needs to be not overloaded. Some of our shared hosting servers are handling 75-90 million requests per month and not breaking a sweat, because they're not overloaded and are sized/built/maintained for the job. That's what matters.
Technically, SSD is not needed, however, it is better since he doesnt need much space, it is one of the ideal usage models for SSD.
4 GB is not impossible, add 1-2 more for OS and the rest of the tools and you can run all in RAM with only reading/writing from disk at boot and syncing from time to time 
Since small space SSD VPS is at about the same price as the larger SAS/SAS2/SATA storages, I say go with SSD to be sure of a good experience, why risk having problems or not such a good satisfaction for something you dont need (space).
@Damian He doesnt have that many visitors, so I think a 1 GB will do. Anything above 2 is certainly overkill.
However, loading all DB in RAM would certainly make it take off
SSD definately speeds things up. For my huge MyISAM table/db, fusioned.net is the fastest to date, closely followed by Ramnode and prometeus.
Indeed. At 1 million pageviews per month, that's one every 0.3858024691358024691358024691358 seconds. Or one every ~3 seconds. Anything should be able to handle that.
@imperio: Do you anticipate having large spikes in visitors?
@imperio I've been fiddling a lot with MySQL performance recently, and have made a few discoveries - Please bare in mind, that my use case is blogging, so high read, low write. Your mileage may vary.
Moving from MyISAM to Percona's XtraDB storage engine helped speeding things up a lot, although it also increased RAM usage significantly.
SSDs are also awesome for anything I/O intensive, make that a high priority.
In addition to all the standard MySQL/PHP tweaks you could do, you should perhaps consider a reverse proxy/caching setup, whereby it'll send out cached pages for unauthenticated users and dynamically generate them for logged in - Awesome for visitor spikes.
I'm currently using Redis for this, but you could equally use Varnish or even Nginx.
In terms of 'which provider'; I've used Ramnode recently and cannot fault @Nick_A's service - Excellent performance on both the hardware and network
@imperio you definitely need to move to a host that can provide higher I/O.
I see you're currently hosted in Netherlands, so if you're happy with the network and latency from your location, we can offer you NL based KVM SSD plans
(btw you can get a -20% coupon code from our profile page at @serverbear
In my first month with him, but very impressed. Would recommend trying.
Thank you for the comments.My current hdd usage is about 40gb.SSD vps providers generally offer low space than my needs..
I do not anticipate large spikes in visitors but due to searching, sphinx daemon is killed for OOM reason sometimes.Privvmpages have 31 failcnts for 9 days. I do not want to lower key_buffer_size (myisam index size is 1.3gb) so i need at least 2gb dedicated RAM i guess.
Despite terrible I/O score i do not feel slowness.Mysql qps is 23 but visitor count is low.Xcache is doing very well here.
I would try Ramnode SSD-cached.
I run the script available from centminmod.com on a 1GB Xen VPS for 2500 registered users running vBulletin
It runs fine and without any problems
I was about to mention the same
@imperio - we have SSD-cached options which give you similar speed to SSD but with a lot more space.
We offer SSD Cached OpenVZ hosting. We also optionally offer full management. If you're interested in that option, we'll be happy to help you tune your MySQL instance for you.
i recommended Nodedeploy KVM VPS
Maybe you can move your forum to BuyVM, and utilize its MySQL offloading service?
Since youre server its on Europe, I would suggest you Prometeus he has a good service a lot of members are quite happy with the performance on the nodes.
If you contact him he might give you a special deal.
And like @ElliotJ mention, change to Percona, leave apache and go with Nginx, PHP-FPM and a php cache solution
+1 for RamNode.
Bit late to this thread possibly, but I was in a similar situation with a forum & Memcache saved the day. I also always use the Percona build of MYSQL, found it to be able to handle a lot more thrown at it.
Do not switch if it's working out for you, first and foremost.
If you 'must,' (There is no must in this case, actually) Ramnode/Cshards/Hostigation would be my recommendation.
I do almost 2 million page views per month on a 512 OVZ from BuyVM with no sweat. That's with up to 400 active visitors. If you have proper caching set up, you should be able to do 1 million pageviews easily on a 512 or even a 256.
Edit: A little bit about my setup:
Debian 6
nginx, php-fpm, and mysql from the dotdeb repo
Forum software: SimpleMachines Forum
php-apc enabled
Just make sure you have indexes.
/dig
Francisco
@Francisco I hate you. :P
But yes, don't try selecting from a table with 250k rows with no indexes, otherwise Pony will stomp you.