0

I am looking for new DB server. I'd like to know your opinions and recommendations.

Current server spec:

  1. We use DB server for website with 100-200 req per sec(i plan growth up to 200-250)
  2. Today we have 1000 - 2000 rps for DB from php (growth to 3-4k)
  3. DB size - 63 Gb (growth up to 100Gb)
  4. Storage: 4 HDD x 2Gb, RAID 1 for DB data
  5. RAM: 32 Gb CPU: Intel Quad-Core Xeon E3-1270 (4 cores, 3400Mhz)
  6. Software MySQL 5.5 over Centos 6.4

What i want: I need server for next year, i wait for loading growth about 20-50%. I am going to add memory up to 64Gb, however i am not sure about storage and CPU. What do think about moving to SSD and more powerfull CPU?

This is server CPU loading graph enter image description here

I will appreciate your opinions and recomendations.

andreyvlru
  • 11
  • 4

1 Answers1

0

  1. Replace all plain queries with calls to the stored procedures. Parsing of SQL and preparing of bytecode consumes significant amount of CPU.

  2. Install as much RAM as you can. 128G allows to fit indexes into RAM - that boosts queries dramatically. Also, caching results is a plus.

  3. Despite of marketing materials SSD are not suited for massive inserts/updates. MTBF under the heavy load is about 9-12 months depending on capacity % used.

Just now I've cycle table 10G large with stored routine 400 lines long. I have about 5000 procedure calls per second (200k+ single queries/second). My host is Hetzner's EX40 in base configuration and vanilla MariaDB 5.3 on the FreeBSD 9.3. The bottleneck is the CPU, not the HDD (2x2TB RAID1).

Kondybas
  • 6,864
  • 2
  • 19
  • 24
  • -1 for item 3. I run a quite high insert 3tb database (around 400mb per day new data that gets purged after days) for a year now that is 100% SSD backed - and we have not had more than 10% usage on the (overprovisioned) SSD. It is all a matter of buying technology suitable for the job. And even if - the performance gain makes it worthwhile to regularly replace hardware. – TomTom Jan 08 '16 at 13:42
  • MTBF does not mean your SSD will die immediately after year of normal operation. – Kondybas Jan 08 '16 at 13:45
  • Yes. But I said I have used up 10% of the assumed minimum life cycle. Which is VERY trackable for SSD thanks to various counters. Given a 5 year investment cycle AND warranty... well, that is not worse than SAS drives. Actually a lot cheaper as I would need a LOT more drives for the same capacity. WHich means your argument 3 is wrong. – TomTom Jan 08 '16 at 13:57