Storage solution for large growing database

[5aberd]

we have an online E learning website for schools developed by our enterprise, my Main problem now is storage for the database files I already have an Adata XP G SX6000 LITE 1TB but the storage is going full very fast due to the large number of students what is the best solution to expand the storage for the database Server, I tried using a regular Seagate Hard disk but the Performance dropped really bad, and I cant put another NVME in the server due to board restriction ( one slot only). I'm searching for large storage with best performance to avoid performance drops.

 

[Drama Lama]

12 minutes ago, 5aberd said:

we have an online E learning website for schools developed by our enterprise, my Main problem now is storage for the database files I already have an Adata XP G SX6000 LITE 1TB but the storage is going full very fast due to the large number of students what is the best solution to expand the storage for the database Server, I tried using a regular Seagate Hard disk but the Performance dropped really bad, and I cant put another NVME in the server due to board restriction ( one slot only). I'm searching for large storage with best performance to avoid performance drops.

 

HDD and Intel optane 

( maybe. I don’t know if it is worth)

[WereCatf]

10 minutes ago, 5aberd said:

I already have an Adata XP G SX6000 LITE 1TB

You have a single, non-RAID drive for the databases at the moment? I hope you are at least taking frequent backups of it!

15 minutes ago, 5aberd said:

what is the best solution to expand the storage for the database Server

RAID, obviously. You can either use a local RAID or networked storage with e.g. FreeNAS.

[mariushm]

If the server has pci-e slots (even pci-e x1 slots, but ideally pci-e x4 or bigger) you can buy adapter cards which plug in pci-e slots and have one or two m.2 connectors on them allowing you to install extra ssd in pci-e slot.

Examples (both contain regular and low profile brackets so regardless of your server you could install them):

Amazon.com: M.2 to PCIe Adapter, NVME SSD to PCI-e 3.0 x4 Host Controller Expansion Card with Low Profile Bracket, Support M Key Solid State Drive Type 2280 2260 2242 2230 Converter to Desktop PCI Express: Computers & Accessories

 

Amazon.com: DEVMO Dual M.2 PCIE Adapter for SATA or PCIE NVMe SSD M.2 SSD NVME (m Key) or SATA (b Key) 22110 2280 2260 2242 2230 to PCI-e 3.0 x 4 Host Controller Expansion Card: Industrial & Scientific

The second m.2 connector on this adapter is SATA M.2 only - you need to connect a sata cable between the card and the motherboard for that SSD to work.

 

There's even riser cables in case you have a 1U server or for some reason you can't plug pci-e cards :

Amazon.com: Riser PCIe 4X Extension Cable M2 M.2 NVMe SSD to PCIe x4 Adapter Cards Riser Card Support PCI-E 3.0 x4 Full Speed ADT R24SF (80CM): Computers & Accessories

 

So with the above, you can just insert the riser cable into the pci-e slot and place the adapter board between slots or somewhere, on some plastic sheet or something (probably comes with some double sided adhesive tape and insulator on the back already)

 

Here's example of pci-e x1 adapter card, which would allow you to plug a nvme ssd into a pci-e x1 slot - note that maximum speed would be limited to theoretical speeed of pci-e x1 (which is 970 MB/s) but that's still almost twice as fast as SATA which is limited to 560 MB/s

 

Amazon.com: SIIG M.2 SSD M Key Nvme PCIe 3.0 X4 Card Adapter with Low and Full Profile Bracket - Supports M.2 PCIe 2230, 2242, 2260 and 2280 (SC-M20111-S1): Computers & Accessories

 

You can do the same with the adapter cards above just by cutting the extra contacts in the pci-e edge connector, but if you're not into do it yourself there's options like the above product.

 

 

Have you looked at various caching techniques, like using memcached or other caching solutions to hold stuff in memory instead of retrieving it from mechanical hard drives? It works well for static content and you could potentially add additional servers with mostly ram (for example an old 2-4 core server with 128-256 GB of ddr3 ram to use just to cache content in ram and serve it on request instead of hitting storage drives) 

 

 

 

 

[5aberd]

34 minutes ago, WereCatf said:

You have a single, non-RAID drive for the databases at the moment? I hope you are at least taking frequent backups of it!

RAID, obviously. You can either use a local RAID or networked storage with e.g. FreeNAS.

No of course I'm taking daily backup to Hard disk non SSD  so thats not a problem. my main problem is expanding the storage without dropping the performance.

[RayLeech]

Most anything you do with regular ssd/hd is going to run slower than the single, non-raid NVME drive you referenced.

 

Not sure I'd trust my database to a single nvme, but since you have a one slot limit, you could try an add-on board that supports raid 0/1 and add four more nvme's (2tb/4tb).

 

https://highpoint-tech.com/USA_new/series-ssd7101a-1-overview.htm

https://www.amazon.com/Sabrent-Rocket-Internal-Performance-SB-ROCKET-4TB/dp/B07ZQSDQDB

[RayLeech]

Quote

No of course I'm taking daily backup

If your database is growing pretty quickly, daily might not be the right increment. You may want to stage a large but slower ssd in the box and replicate transactions, or block changes (something). Daily sounds like a pretty big window to lose if something goes wrong.

[shoutingsteve]

11 hours ago, WereCatf said:

You have a single, non-RAID drive for the databases at the moment? I hope you are at least taking frequent backups of it!

RAID, obviously. You can either use a local RAID or networked storage with e.g. FreeNAS.

I second the FreeNas idea.  you can build a freenas server out of old parts and when you do need to upgrade the storage again, it is only a matter of resilvering the array.  Just make sure that you get HDDs that are all teh same size (1 TB is fine if you have 4 or 5 connected, but don't forget that you will need a boot disk separate from the storage.  I used a 16 gig flashdrive so it didn't waste one of the SATA slots on the mobo.

[5aberd]

6 hours ago, shoutingsteve said:

I second the FreeNas idea.  you can build a freenas server out of old parts and when you do need to upgrade the storage again, it is only a matter of resilvering the array.  Just make sure that you get HDDs that are all teh same size (1 TB is fine if you have 4 or 5 connected, but don't forget that you will need a boot disk separate from the storage.  I used a 16 gig flashdrive so it didn't waste one of the SATA slots on the mobo.

yeah but that is just gonna move me back to the HDD and the performance drop especially if I remove the database from the Web server and rely on network; the platform working 24/7 so i cant afford any drop, it happened once never again. ill have to lookup the freenas subject even deeper if I want to implement it

[mariushm]

buy 2nd server, set it up with multiple nvme drives in raid or whatever, set it up with a 10g network card so that you'd be able to connect a nas to it directly through the 10g card, then mirror the main server

At some point at lowest traffic level (let's say 2-3 am) , pause the website, dump the database, import the database in the other server  (or maybe just move the hard drive over) , move the network cable to your new ready to run machine

If all goes right, your website will be paused for less than 10 minutes or so.

Now you can get the old machine and convert it to NAS or whatever (or keep it as hot backup)

 

[featherwolf]

On 6/8/2020 at 6:25 AM, 5aberd said:

we have an online E learning website for schools developed by our enterprise, my Main problem now is storage for the database files I already have an Adata XP but the storage is going full very fast due to the large number of students what is the best solution to expand the storage for the database Server, I tried using a regular Seagate Hard disk but the Performance dropped really bad, and I cant put another NVME in the server due to board restriction ( one slot only). I'm searching for large storage with best performance to avoid performance drops.

 

Regarding the Seagate drive, was this connected to the server via SATA or USB or what? Wanna make sure the bottle neck is actually the drive and not the connection. That said, assuming you're okay with the higher cost of flash memory, slap a few terabytes of 2.5" SSDs in RAID in your rig. You will suffer from write endurance on SSDs but if you have a good backup methodology (remember the 3, 2, 1 rule), and you pick higher quality SSDs then I believe that would be a very good option.

[5aberd]

On 6/9/2020 at 8:27 PM, featherwolf said:

Regarding the Seagate drive, was this connected to the server via SATA or USB or what? Wanna make sure the bottle neck is actually the drive and not the connection. That said, assuming you're okay with the higher cost of flash memory, slap a few terabytes of 2.5" SSDs in RAID in your rig. You will suffer from write endurance on SSDs but if you have a good backup methodology (remember the 3, 2, 1 rule), and you pick higher quality SSDs then I believe that would be a very good option.

the seagate was connected via SATA and it was brand new so idoubt it had any issue and i think the bottleneck was from using HDD since i tried multiple ones and when i replaced them with an SSD it worked like a charm no bottleneck no lag nothing it was perfect.

my only problem is the way of setting the SSD array ?

[5aberd]

anyone got an idea if using 512 GB NVME SSD (as a cache) with a 7200 RPM harddisk would work in this scenario?

[shoutingsteve]

On 6/9/2020 at 1:23 AM, 5aberd said:

yeah but that is just gonna move me back to the HDD and the performance drop especially if I remove the database from the Web server and rely on network; the platform working 24/7 so i cant afford any drop, it happened once never again. ill have to lookup the freenas subject even deeper if I want to implement it

I am all for finding the best solution for you and not just preaching the greatness of FreeNAS, but I have a raidz1-0 in Freenas and it gets me full 120 MB/sec of ACTUAL data transfer.  The 10 TB system only cost me about 500 bucks with all new parts.