0

I have benchmarked lxd and the difference has not been more than 2-3% of the bare metal performance.

I am using fusion sx300 1.6tb to store a 450gb database with 20b rows in a single table.

We have 512GB ram

I want to know if io of the sx300 through lxd virtio device would be impacted more than 2-3% of baremetal.

We are on proxmox 5.3 with latest lxd drivers.

  • Is this a Data Warehouse application? Or sensor data? What queries hit that 20b-row table? What is the retention period for the data? Do you have "summary tables". Where am I heading with these questions? Hardware can provide a "few percent" speedup. But rethinking the data may give you 10-fold speedup. Let's focus on the latter. – Rick James Jan 12 at 3:28
  • We operate a free file backup application. This table has about on average 40k average entries for files per user. The table is not wide. It is a relationship table between a file hash table and user id and other meta data on the columns. It is a 3nf normalised column. We are not essentially looking for a speedup but we are worried what happens when that table index size grows out of the ram size of 512gb. We have a 80:20 write to read ratio. We are at the moment doing about 5k reads per second (peak time) with cpu util on 2xe5-2697v2 of 30% and io usage of 25% – Vish Jan 12 at 4:01
  • What are your suggestions on how we grow into this. – Vish Jan 12 at 4:04
0

If you are pushing the limits of the hardware, you should consider "sharding". For your application, it seems quite practical to put some users on one server, some on another. And keep adding servers as your business grows.

  • Only two things (that I can think of) correlate to index size versus RAM size: table scans (reading entire tables) and UUIDs.
  • UUIDs and hashes (MD5, etc) can be a performance killers. Please provide details on how they are being used. This is a case where performance degrades when the index becomes bigger than RAM.
  • 80% writes -- as in someone backs up a bunch of files; the table records the meta info. Is there a AUTO_INCREMENT involved?
  • Please provide SHOW CREATE TABLE for the main table(s), so I won't have to come up with questions one at a time.
  • And let's see the typical queries (read and write).
  • You really have 500K users on one machine?
  • If 5K reads/sec is peak, what is writes/sec? Generally writes are tougher to scale.

I envision that one of your tables is indexed by sha2, and you use it for deduping the files as they come in. Quite possibly this is the most critical performance issue, but it would mostly be for reads, not writes. There are a few things that can be done to mitigate the issues; they mostly have to do with shrinking the table size. Again, SHOW CREATE TABLE is needed.

  • Sorry, the read:write ratio is 80:20, not the other way around. We have a slave of the exact configuration. We have not seen downtime so far and hopefully we should mitigate risks before we do. We only use three tables on this server that are linked together. Other thinks such as authentication, logging, etc is handled from another server. We use md5 hashes to make sure to prevent uploading duplicate files, that saves the user bandwidth and saves us a lot of space sometimes. Imagine movie files that can be upto 10gb each but 100 different users have the same file locally, so we md5 hash it – Vish Jan 12 at 21:48
  • @Vish - OK, 80:20 makes sense. The rest of my comments still stand. And my requests still stand; it will be hard to give specific advice without knowing the specific schema. – Rick James Jan 13 at 0:11

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.