Your Dream DB Server Hardware Specs?

[AkkerKid]

You can see in my signature what I'm running now but I'm running up against it's limits with 250 agents and a ratio of 8.
I'd like opinions on an ideal database server for highest possible agent count with a dial ratio of maybe 10.
Let's assume I had money to spend as long as it was justifiable. I'm guessing between 25-50K USD.
It's ONLY for MySQL/MariaDB.

Do you prioritize core count or clock speed?
With or without hyperthreading?
Dual Xeons, Quad? 8x (Scalable)
Does DDR3 or DDR4 make a difference?
Does SATA SSD, NVMe or PCI-e SSD make a difference?
Has anyone tried GPU acceleration?

...Or should I build a second of what I've got already and dedicate it to the hopper and reporting?

[williamconley]

For a database, core count is essential. With hyperthreading or without, the number of processes that can run simultaneously is the primary concern. After that, of course, speed helps move those along so all the cores are never busy because they clear out their tasks quickly. In other systems (non-db) some of these tasks take a long time, but in the DB like Vicidial, each task is a nano-second or three and should clear out immediately.

And along with CPU speed of course, FSB and Memory speeds are equally important to get that data moved where it needs to be ... which brings us to:

Next up we have hard drive speed. Obviously faster HDs, RAID10 with LOTS of drives (6 or 8?) can improve throughput. I've experimented a bit with a fully virtual HD on some servers with lots of memory so there really is NO HD involved, it's all in memory. This is dangerous of course in case of a power loss, but redundant power supplies and UPSs would protect from that, and if it kicks up the throughput, a little risk is worthwhile. The prototype isn't finished yet (we're too busy). Multiple starts, but none got all the way to the end as nobody was paying for it. We always get too busy to play.

GPU should have zero impact as that's related to graphics. Overclocking the CPU is always fun, but also can burn out the CPU and requires equipment where this is possible (and excellent cooling, a nice cool server room, that sort of thing).

My dream system? 40 cores (physical) with hyperthreading at the fastest clock speed available. We have a couple of these, although I don't know if there are faster clock speeds available than what we have (and the accountant wouldn't let me buy one if there was, so I'm not looking) and 256G RAM so the entire DB could be loaded into a RAM drive. Add replication (for disaster recovery and reporting, both) and the system should be bulletproof.

Happy Hunting

Oh: And if you wanted to "turbo" an existing Vicidial, some modifications to the code would be useful.
* Turn off all logging that isn't absolutely required. This includes log tables and log files, both.
* Modify any required logging routines that edit a table to ONLY append, never edit. If a value isn't known when the log entry is created, a new log table should be built (look up "normalized") to hold this other value that will arrive late. Requires modifying some reports, too, but dramatically improves throughput on DB writes.
* All "Read" entries that are not mission-critical should be read from the replication server. Any functions that have "read" entries could be blocked if replication falls behind, or automatically shunted to the live server, depending on which is more important (ie: screw the reports, we're dialing, that can wait 'til after we're done dialing).

I also have a client who has had us build (prototype online for a few months, it's working) a Master server capable of pushing leads to multiple Vicidial Clusters. He's not yet had us claw back the resulting reports and/or unused leads, but the system is designed to keep the hoppers full with "Just in time delivery" (by estimation, not technically calculated) so that a each list can be "doled out" among the various clusters until exhausted instead of putting 50k leads on each server and then hoping they all finish at the same time.

[AkkerKid]

I've heard plenty of conflicting opinions on hyperthreading. Some say since two different SQL queries (one hyper-threaded, one not) would use the same cache on the same core, they're more likely to flush or overwrite the cache of another process, requiring that other process to start from scratch.

I see a machine with 40 cores and no hyperthreading on 4 chips as a realistic possibility for my next big rig.
Intel's E5-4627V4 has 10 cores and a max turbo of 3.2GHz. However, for only $20k more (LOL) I could have four E5-4669V4. That's 22 cores with HT at 3Ghz turbo...

I'm not adept enough with Vici to edit scripts to disable logging. I don't think i'm disk-bound at all any way.
I am VERY interested in setting up the slave server but the install script has let me down as seen in my other thread here: viewtopic.php?f=4&t=38848

[williamconley]

40 cores @ 2.3 vs 10 cores @3.2, I'm leaning toward 40 for the DB and 3.2Ghz for anything else. 22 Cores @ 3Ghz would be a tougher choice, though. lol

But nobody will let us test: once they get past 100 agent no "risk" is allowed near any client server without their express permission, and that is (as a rule) not forthcoming, lol. I don't have anyone on Beta servers with more than 10 agents.

We don't use the "slave server" script to replicate. We just use mysql replication manually installed. Seems to work, it's not a "vicidial-related" function.

[Kumba]

Unless you are mixing architectures, like an AMD vs Intel CPU or a current generation CPU versus one 5 generations old, the IPC (Instructions per Clock) are pretty much going to be the same for the CPUs by a given manufacturer. So in that instance a Quad-Core CPU running at 3.5Ghz is going to be faster then a Hex-Core CPU running at 2.2Ghz. If you make a composite CPU performance rating that consists of Cores time Ghz you can see what I mean. In the example I gave, a Quad Core at 3.5Ghz has a composite rating of 14 (4-cores times 3.5Ghz, or 4x3.5). The same composite rating for that Hex-Core running at 2.2Ghz is only 13.2 (6x2.2). Hyperthreading can be helpful on lighter queries but you should really only expect at the most 10-15% performance gain on average. Good news is almost all Intel CPU's will have hyper-threading once you get past the very bottom CPU in each family.

The ViciDial code is definitely I/O bound versus being CPU bound at the database server. This means that it's all about making I/O as fast as possible. Therefore you are better off chasing RAM and fast drive subsystems versus throwing a lot of CPU cores at it. There is certainly nothing wrong with having a lot of CPU cores, but that's probably the least important performance aspect of a ViciDial database server. The most CPU intensive thing a ViciDial database typically does, custom code not withstanding, is a text search across a column. Everything else is simple count(), sum(), and a few date things here and there. The only time we typically run into a pure CPU bottleneck is when you are around 500+ agents on some of the memory tables. The issue here is that the memory table is a single-thread engine and runs into contention issues once you hit a high enough queries per second. In this scenario pure CPU speed (not cores) is more critical to alleviating the issue.

One of the most overlooked (thanks to SSDs) part of a good DB design is the hard-drive setup. I would recommend having a simple RAID-1 using inexpensive disks for the OS to boot on, and then a set of dedicated SSD's in a RAID-1 for the database to live on under /srv/mysql. The OS drives we use are SeaGate 1TB enterprise SATA drives. These boot the OS, store logs, store the binary logs for MySQL under /var/lib/mysql, and are more then adequate for the task. Lately our SSD of choice for MySQL to live on is the Samsung 860 Pro due to the more balanced read/write IOPS versus an EVO. The Intel 545s is also a good choice for a SATA-based SSD.

The fastest SSD set-up you can get is an NVMe setup. We've done installs with both the Intel 750 and 905p and they are incredibly fast. What we do is configure the NVMe drives into a Linux Software RAID-1 using MD. This has resulted in some amazingly fast databases. In our limited testing a single NVMe RAID-1 was around 11x faster then the same DB with 4 SATA-based SSD's in a RAID-10. That's pretty damn impressive. We haven't done much testing with the Samsung NVMe drives as they seem to be focusing those on the M.2 form factor and we use either U.2 or add-in cards. The one caveat to NVMe drives is that finding a hardware RAID controller for them is difficult. Even if the hardware RAID controllers were readily available I would still recommend using the Linux Software RAID-1 as the robustness and scalability of it seems to always outpace a hardware controller.

[williamconley]

That was probably the coolest post I've seen in several months.