Re: [PVE-User] SQL server 2014 poor performances
> > > Have you tested the performance of your ssd zpool from the command line > on > > the host? > > > Do you mean vzperf ? > I think he means doing zfs performance tests on the host itself rather than inside the VM. This is in order to rule out the possibility that the slow performance is caused by the virtualization layer(s). You can use tools like "zpool iostat" , "iostat" , "fio", "dd" etc, to benchmark the performance on the host. If the results are good on the host, then you can move further, optimizing the virtualization layer. My understanding is that zfs needs proper tuning when it's used for databases, but I might be wrong. Searching in zfs mailing list archives might give you some clues on this. Gianni ___ pve-user mailing list pve-user@pve.proxmox.com https://pve.proxmox.com/cgi-bin/mailman/listinfo/pve-user
Re: [PVE-User] SQL server 2014 poor performances
Sorry, PVEPERF not VZPERF - Il 28-ott-19, alle 17:15, Fabrizio Cuseo f.cu...@panservice.it ha scritto: > - Il 28-ott-19, alle 16:56, Mark Adams m...@openvs.co.uk ha scritto: > >> There is a WD Blue SSD - but it is a desktop drive, you probably shouldn't >> use it in a server. > > Hello mark. > I know that is a desktop drive, but we are only testing the different > performances between this and a desktop pc with a similar (or cheaper) desktop > ssd. > > >> Are you using the virtio-scsi blockdev and the newest virtio drivers? also, > > I am using the virtio-scsi, and virtio drivers (not more than 1 year old > version) > >> have you tried with writeback enabled? > > Not yet. > > >> Have you tested the performance of your ssd zpool from the command line on >> the host? > > > Do you mean vzperf ? > > PS: i don't know if the bottleneck is I/O or some problem like the SQL > "content > switch" setting. > > PPS: SQL server is 2017, not 2014. > > > >> On Mon, 28 Oct 2019 at 15:46, Michael Rasmussen via pve-user < >> pve-user@pve.proxmox.com> wrote: >> >>> >>> >>> >>> -- Forwarded message -- >>> From: Michael Rasmussen >>> To: pve-user@pve.proxmox.com >>> Cc: >>> Bcc: >>> Date: Mon, 28 Oct 2019 16:46:23 +0100 >>> Subject: Re: [PVE-User] SQL server 2014 poor performances >>> On Mon, 28 Oct 2019 15:47:18 +0100 (CET) >>> Fabrizio Cuseo wrote: >>> >>> > Hello. >>> > I have a customer with proxmox 5.X, 4 x SSD (WD blue) in raid-10 ZFS >>> > configuration, Poweredge R710 dual xeon and 144Gbyte RAM. Same >>> > problem with 4 x SAS 15k rpm drives. >>> > >>> Are you sure it is SSD? I don't recollect that WD has produced WD blue >>> as SSD. >>> >>> -- >>> Hilsen/Regards >>> Michael Rasmussen >>> >>> Get my public GnuPG keys: >>> michael rasmussen cc >>> https://pgp.key-server.io/pks/lookup?search=0xD3C9A00E >>> mir datanom net >>> https://pgp.key-server.io/pks/lookup?search=0xE501F51C >>> mir miras org >>> https://pgp.key-server.io/pks/lookup?search=0xE3E80917 >>> -- >>> /usr/games/fortune -es says: >>> Follow each decision as closely as possible with its associated action. >>> - The Elements of Programming Style (Kernighan & Plaugher) >>> >>> >>> >>> -- Forwarded message -- >>> From: Michael Rasmussen via pve-user >>> To: pve-user@pve.proxmox.com >>> Cc: Michael Rasmussen >>> Bcc: >>> Date: Mon, 28 Oct 2019 16:46:23 +0100 >>> Subject: Re: [PVE-User] SQL server 2014 poor performances >>> ___ >>> pve-user mailing list >>> pve-user@pve.proxmox.com >>> https://pve.proxmox.com/cgi-bin/mailman/listinfo/pve-user >>> >> ___ >> pve-user mailing list >> pve-user@pve.proxmox.com >> https://pve.proxmox.com/cgi-bin/mailman/listinfo/pve-user > > -- > --- > Fabrizio Cuseo - mailto:f.cu...@panservice.it > Direzione Generale - Panservice InterNetWorking > Servizi Professionali per Internet ed il Networking > Panservice e' associata AIIP - RIPE Local Registry > Phone: +39 0773 410020 - Fax: +39 0773 470219 > http://www.panservice.it mailto:i...@panservice.it > Numero verde nazionale: 800 901492 > ___ > pve-user mailing list > pve-user@pve.proxmox.com > https://pve.proxmox.com/cgi-bin/mailman/listinfo/pve-user -- --- Fabrizio Cuseo - mailto:f.cu...@panservice.it Direzione Generale - Panservice InterNetWorking Servizi Professionali per Internet ed il Networking Panservice e' associata AIIP - RIPE Local Registry Phone: +39 0773 410020 - Fax: +39 0773 470219 http://www.panservice.it mailto:i...@panservice.it Numero verde nazionale: 800 901492 ___ pve-user mailing list pve-user@pve.proxmox.com https://pve.proxmox.com/cgi-bin/mailman/listinfo/pve-user
Re: [PVE-User] SQL server 2014 poor performances
- Il 28-ott-19, alle 16:56, Mark Adams m...@openvs.co.uk ha scritto: > There is a WD Blue SSD - but it is a desktop drive, you probably shouldn't > use it in a server. Hello mark. I know that is a desktop drive, but we are only testing the different performances between this and a desktop pc with a similar (or cheaper) desktop ssd. > Are you using the virtio-scsi blockdev and the newest virtio drivers? also, I am using the virtio-scsi, and virtio drivers (not more than 1 year old version) > have you tried with writeback enabled? Not yet. > Have you tested the performance of your ssd zpool from the command line on > the host? Do you mean vzperf ? PS: i don't know if the bottleneck is I/O or some problem like the SQL "content switch" setting. PPS: SQL server is 2017, not 2014. > On Mon, 28 Oct 2019 at 15:46, Michael Rasmussen via pve-user < > pve-user@pve.proxmox.com> wrote: > >> >> >> >> -- Forwarded message -- >> From: Michael Rasmussen >> To: pve-user@pve.proxmox.com >> Cc: >> Bcc: >> Date: Mon, 28 Oct 2019 16:46:23 +0100 >> Subject: Re: [PVE-User] SQL server 2014 poor performances >> On Mon, 28 Oct 2019 15:47:18 +0100 (CET) >> Fabrizio Cuseo wrote: >> >> > Hello. >> > I have a customer with proxmox 5.X, 4 x SSD (WD blue) in raid-10 ZFS >> > configuration, Poweredge R710 dual xeon and 144Gbyte RAM. Same >> > problem with 4 x SAS 15k rpm drives. >> > >> Are you sure it is SSD? I don't recollect that WD has produced WD blue >> as SSD. >> >> -- >> Hilsen/Regards >> Michael Rasmussen >> >> Get my public GnuPG keys: >> michael rasmussen cc >> https://pgp.key-server.io/pks/lookup?search=0xD3C9A00E >> mir datanom net >> https://pgp.key-server.io/pks/lookup?search=0xE501F51C >> mir miras org >> https://pgp.key-server.io/pks/lookup?search=0xE3E80917 >> -- >> /usr/games/fortune -es says: >> Follow each decision as closely as possible with its associated action. >> - The Elements of Programming Style (Kernighan & Plaugher) >> >> >> >> -- Forwarded message -- >> From: Michael Rasmussen via pve-user >> To: pve-user@pve.proxmox.com >> Cc: Michael Rasmussen >> Bcc: >> Date: Mon, 28 Oct 2019 16:46:23 +0100 >> Subject: Re: [PVE-User] SQL server 2014 poor performances >> ___ >> pve-user mailing list >> pve-user@pve.proxmox.com >> https://pve.proxmox.com/cgi-bin/mailman/listinfo/pve-user >> > ___ > pve-user mailing list > pve-user@pve.proxmox.com > https://pve.proxmox.com/cgi-bin/mailman/listinfo/pve-user -- --- Fabrizio Cuseo - mailto:f.cu...@panservice.it Direzione Generale - Panservice InterNetWorking Servizi Professionali per Internet ed il Networking Panservice e' associata AIIP - RIPE Local Registry Phone: +39 0773 410020 - Fax: +39 0773 470219 http://www.panservice.it mailto:i...@panservice.it Numero verde nazionale: 800 901492 ___ pve-user mailing list pve-user@pve.proxmox.com https://pve.proxmox.com/cgi-bin/mailman/listinfo/pve-user
Re: [PVE-User] SQL server 2014 poor performances
There is a WD Blue SSD - but it is a desktop drive, you probably shouldn't use it in a server. Are you using the virtio-scsi blockdev and the newest virtio drivers? also, have you tried with writeback enabled? Have you tested the performance of your ssd zpool from the command line on the host? On Mon, 28 Oct 2019 at 15:46, Michael Rasmussen via pve-user < pve-user@pve.proxmox.com> wrote: > > > > -- Forwarded message -- > From: Michael Rasmussen > To: pve-user@pve.proxmox.com > Cc: > Bcc: > Date: Mon, 28 Oct 2019 16:46:23 +0100 > Subject: Re: [PVE-User] SQL server 2014 poor performances > On Mon, 28 Oct 2019 15:47:18 +0100 (CET) > Fabrizio Cuseo wrote: > > > Hello. > > I have a customer with proxmox 5.X, 4 x SSD (WD blue) in raid-10 ZFS > > configuration, Poweredge R710 dual xeon and 144Gbyte RAM. Same > > problem with 4 x SAS 15k rpm drives. > > > Are you sure it is SSD? I don't recollect that WD has produced WD blue > as SSD. > > -- > Hilsen/Regards > Michael Rasmussen > > Get my public GnuPG keys: > michael rasmussen cc > https://pgp.key-server.io/pks/lookup?search=0xD3C9A00E > mir datanom net > https://pgp.key-server.io/pks/lookup?search=0xE501F51C > mir miras org > https://pgp.key-server.io/pks/lookup?search=0xE3E80917 > -- > /usr/games/fortune -es says: > Follow each decision as closely as possible with its associated action. > - The Elements of Programming Style (Kernighan & Plaugher) > > > > -- Forwarded message -- > From: Michael Rasmussen via pve-user > To: pve-user@pve.proxmox.com > Cc: Michael Rasmussen > Bcc: > Date: Mon, 28 Oct 2019 16:46:23 +0100 > Subject: Re: [PVE-User] SQL server 2014 poor performances > ___ > pve-user mailing list > pve-user@pve.proxmox.com > https://pve.proxmox.com/cgi-bin/mailman/listinfo/pve-user > ___ pve-user mailing list pve-user@pve.proxmox.com https://pve.proxmox.com/cgi-bin/mailman/listinfo/pve-user
Re: [PVE-User] SQL server 2014 poor performances
https://shop.westerndigital.com/products/internal-drives/wd-blue-3d-nand-sata-ssd#WDS250G2B0A On 28/10/2019 15:46, Michael Rasmussen via pve-user wrote: On Mon, 28 Oct 2019 15:47:18 +0100 (CET) Fabrizio Cuseo wrote: Hello. I have a customer with proxmox 5.X, 4 x SSD (WD blue) in raid-10 ZFS configuration, Poweredge R710 dual xeon and 144Gbyte RAM. Same problem with 4 x SAS 15k rpm drives. Are you sure it is SSD? I don't recollect that WD has produced WD blue as SSD. -- Hilsen/Regards Michael Rasmussen ___ pve-user mailing list pve-user@pve.proxmox.com https://pve.proxmox.com/cgi-bin/mailman/listinfo/pve-user -- Kind regards, Rob Strzelecki, ___ pve-user mailing list pve-user@pve.proxmox.com https://pve.proxmox.com/cgi-bin/mailman/listinfo/pve-user
Re: [PVE-User] SQL server 2014 poor performances
--- Begin Message --- On Mon, 28 Oct 2019 15:47:18 +0100 (CET) Fabrizio Cuseo wrote: > Hello. > I have a customer with proxmox 5.X, 4 x SSD (WD blue) in raid-10 ZFS > configuration, Poweredge R710 dual xeon and 144Gbyte RAM. Same > problem with 4 x SAS 15k rpm drives. > Are you sure it is SSD? I don't recollect that WD has produced WD blue as SSD. -- Hilsen/Regards Michael Rasmussen Get my public GnuPG keys: michael rasmussen cc https://pgp.key-server.io/pks/lookup?search=0xD3C9A00E mir datanom net https://pgp.key-server.io/pks/lookup?search=0xE501F51C mir miras org https://pgp.key-server.io/pks/lookup?search=0xE3E80917 -- /usr/games/fortune -es says: Follow each decision as closely as possible with its associated action. - The Elements of Programming Style (Kernighan & Plaugher) pgp_RPtpF6JlM.pgp Description: OpenPGP digital signature --- End Message --- ___ pve-user mailing list pve-user@pve.proxmox.com https://pve.proxmox.com/cgi-bin/mailman/listinfo/pve-user
Re: [PVE-User] SQL server 2014 poor performances
Thank you for your answer: CPU(s) 24 x Intel(R) Xeon(R) CPU X5650 @ 2.67GHz (2 Sockets) The VM has 2 socket x 4 core, both KVM and host type, with NUMA enabled. - Il 28-ott-19, alle 15:59, José Manuel Giner j...@ginernet.com ha scritto: > What is the exact CPU model? > > > > On 28/10/2019 15:47, Fabrizio Cuseo wrote: >> Poweredge R710 dual xeon > > -- > José Manuel Giner > https://ginernet.com > > ___ > pve-user mailing list > pve-user@pve.proxmox.com > https://pve.proxmox.com/cgi-bin/mailman/listinfo/pve-user -- --- ___ pve-user mailing list pve-user@pve.proxmox.com https://pve.proxmox.com/cgi-bin/mailman/listinfo/pve-user
Re: [PVE-User] SQL server 2014 poor performances
What is the exact CPU model? On 28/10/2019 15:47, Fabrizio Cuseo wrote: Poweredge R710 dual xeon -- José Manuel Giner https://ginernet.com ___ pve-user mailing list pve-user@pve.proxmox.com https://pve.proxmox.com/cgi-bin/mailman/listinfo/pve-user
[PVE-User] SQL server 2014 poor performances
Hello. I have a customer with proxmox 5.X, 4 x SSD (WD blue) in raid-10 ZFS configuration, Poweredge R710 dual xeon and 144Gbyte RAM. Same problem with 4 x SAS 15k rpm drives. A VM with Windows Server 2016, and SQL Server 2014. The SQL performances are very poor if compared with a standard desktop pc with i5 and a single consumer grade SSD. There is some tweak like "Trace Flag T8038" to apply to SQL 2014 and/or 2017 ? Thanks in advance, Fabrizio Cuseo ___ pve-user mailing list pve-user@pve.proxmox.com https://pve.proxmox.com/cgi-bin/mailman/listinfo/pve-user
