Ben:

What if it were the only "real" active vm?

There is some overhead in virtualization, but you would essentially have all the machine's resources available to your db server. That should be close enough to bare metal performance.

Neil

-- Neil Aggarwal, (281)846-8957, http://UnmeteredVPS.net/centos CentOS 5.4 VPS with unmetered bandwidth only $25/month! No overage charges, 7 day free trial, PayPal, Google Checkout

I was hitting the same problem last days so i went through some test, the configuration you suggest is quite nice and according to my result yun anticipated me on the problem doing my next step ;).

basicly i was wondering using MySQL Cluster and a Cluster MySQL for some different services. Setting the point that mysql cluster was taken in account for availability specs more than for its performance.

That said here some spec data:

hp/ibm dual quadcore with SAS RAID and 8GB of RAM. (for dom0) OS: Centos 5.3 Virtualization: xen

for real server no SAS but SATA RAID and 4GB of RAM

I got 4 of the former and 2 of the latter. All domus where on .img files.

Used sysbench for testing

i tested: mysql cluster on domUs, mysql on domu and mysql on real server

I got real pour performance with mysql cluster on domus no data for that, but no optimiziation was done.

Moreover consider that no optimizatino was done for none of the configurations.

No conclusion at all, more serious efforts can be done for getting the best from each donfiguration, so just get this datas as a non production test, which is what it is.

Here some numbers hope they are usefull:

***************************Mysql cluster on real server (SATA + 4GB) ******************** sysbench --num-threads=4 --max-requests=20000 --test=oltp --mysql-db=sbtest --mysql-user=test --mysql-password=*********** --mysql-host=************** --mysql-port=********* --mysql-table-engine=ndbcluster --oltp-test-mode=complex run

OLTP test statistics: queries performed: read: 280000 write: 100000 other: 40000 total: 420000 transactions: 20000 (440.95 per sec.) deadlocks: 0 (0.00 per sec.) read/write requests: 380000 (8378.12 per sec.) other operations: 40000 (881.91 per sec.)

Test execution summary: total time: 45.3563s total number of events: 20000 total time taken by event execution: 181.2726 per-request statistics: min: 5.72ms avg: 9.06ms max: 154.81ms approx. 95 percentile: 10.30ms

Threads fairness: events (avg/stddev): 5000.0000/2.74 execution time (avg/stddev): 45.3182/0.00

******************** Plain Mysql on xen domu ************************* OLTP test statistics: queries performed: read: 280000 write: 100000 other: 40000 total: 420000 transactions: 20000 (368.68 per sec.) deadlocks: 0 (0.00 per sec.) read/write requests: 380000 (7004.93 per sec.) other operations: 40000 (737.36 per sec.)

Test execution summary: total time: 54.2475s total number of events: 20000 total time taken by event execution: 216.8328 per-request statistics: min: 6.35ms avg: 10.84ms max: 263.48ms approx. 95 percentile: 11.14ms

Threads fairness: events (avg/stddev): 5000.0000/2.45 execution time (avg/stddev): 54.2082/0.00

******************* Finally mysql on real server (SATA + 4GB) *************** OLTP test statistics: queries performed: read: 280000 write: 100000 other: 40000 total: 420000 transactions: 20000 (467.17 per sec.) deadlocks: 0 (0.00 per sec.) read/write requests: 380000 (8876.18 per sec.) other operations: 40000 (934.33 per sec.)

Test execution summary: total time: 42.8112s total number of events: 20000 total time taken by event execution: 171.0977 per-request statistics: min: 5.67ms avg: 8.55ms max: 133.64ms approx. 95 percentile: 9.84ms

Threads fairness: events (avg/stddev): 5000.0000/2.55 execution time (avg/stddev): 42.7744/0.00

2010/1/15 compdoc compdoc@hotrodpc.com

-----Original Message----- Fhttp://www.liveleak.com/view?i=375_1263347833rom: centos-virt-bounces@centos.org [mailto:centos-virt-bounces@centos.org] On Behalf Of Ben M. Sent: Friday, January 15, 2010 8:56 AM To: Discussion about the virtualization on CentOS Subject: Re: [CentOS-virt] Xen Database vms

Neil:

What if it were the only "real" active vm? I know that might sound a bit of a waste, but I am really enjoying the backup and duplication abilities of running in a Xen hypervisor as well as its other features. It seems to be saving me a lot of time in production settings. And there is also a comfort level in uniformity on a LAN.

Would there still be a significant hit on resource performance by the hypervisor if running that database server alone in it, or alongside a few rarely used, lightweight or spurious vms? I am talking about the database activities running during the biz day and backups, batches and other maintenance in the off hours. Nothing urgent here, just trying to plan out the future, mull over the possibilities and where to head.

• Ben

-----Original Message-----

I think it could work well. Having a server in a vm makes it more portable.

Many of my servers and services are running in vms on two centos 5.4 servers: openfiler, efw firewall, trixbox 2.8, SME Server (in server mode for email and spamassassin), windows 2003 server, windows 2008 server, windows 7, and others that aren't running.

I would suggest:

If there are a lot of temp files or disk access to the OS, install the vm OS on a block device rather than to a file. The storage should be on a local block device as well.

If there's a lot of lan traffic to/from the other vms, install a 3rd ethernet card in the server that is only used for db traffic.

I also use a virtual network that the vms can use to reach each other. This is basically a private internal lan running across the host machine's buses, rather than through your network switch.

I get native performance with my set up...

---

CentOS-virt mailing list CentOS-virt@centos.org http://lists.centos.org/mailman/listinfo/centos-virt

I think it could work well. Having a server in a vm makes it more portable.

Many of my servers and services are running in vms on two centos 5.4 servers: openfiler, efw firewall, trixbox 2.8, SME Server (in server mode for email and spamassassin), windows 2003 server, windows 2008 server, windows 7, and others that aren't running.

I would suggest:

If there are a lot of temp files or disk access to the OS, install the vm OS on a block device rather than to a file. The storage should be on a local block device as well.

If there's a lot of lan traffic to/from the other vms, install a 3rd ethernet card in the server that is only used for db traffic.

I also use a virtual network that the vms can use to reach each other. This is basically a private internal lan running across the host machine's buses, rather than through your network switch.

I get native performance with my set up...

Thank you. Excellent thoughts all, and just the type of feedback I needed to think about. I would be going out a dedicated NIC on the data traffic, and I would do block devices in LVM for the disk IO, Windows 2008 32bit seems to be very happy with that setup in a mixed Xen environment. Under no circumstances would I run these as file images. There is no comparison to dedicated hardware for this application using file based vms. Has to be block devices.

I think for my purposes putting the database servers in Xen is absolutely worth a shot. If it works, I have a lot of benefits from it. If it doesn't, I lost some hours but gained some knowledge, and maybe a backup server.

A vpn (OpenVPN) server in Xen is part of my plan as well. I'm trying to sketch out all the lines of IO and see what I have to do to keep things snappy.

Ben M. wrote:

Neil Aggarwal wrote:

...

Ben:

...

Are "centralized" database (SQL) servers best left out of virtualization

...

...

and intensive querying, Accounting Systems and so forth.

It really depends on the hardware you allocate to the VM and how intensive the usage is.

Personally, if I have an intensively queried database server, I want it directly on hardware.

Ditto.

At my $work_place we tried to move our main database server into XEN VM, and while it kinda worked, we had to revert it to a traditional "hardware+os+db" setup, mostly because backups and maintenance operations started to cause performance problems with regular OLTP-like queries -- a situation that had not happen when we were running non-virtualized setup.

The reasons for virtulaization were that it simplifies administration, backups and disaster recovery of database servers.

(setups: "virtual": dell server, local scsi raid, Cent OS 5.x Dom0, CentOS 5.x DomU, disk as LVM volume of Dom0, + PostgreSQL 8.3, database VM is the only VM that dom0 runs) "normal": dell server, local scsi raid, Cent OS 5.x + PostgreSQL 8.3)

...

Neil

Neil:

What if it were the only "real" active vm? I know that might sound a bit of a waste, but I am really enjoying the backup and duplication abilities of running in a Xen hypervisor as well as its other features. It seems to be saving me a lot of time in production settings. And there is also a comfort level in uniformity on a LAN.

Would there still be a significant hit on resource performance by the hypervisor if running that database server alone in it, or alongside a few rarely used, lightweight or spurious vms? I am talking about the database activities running during the biz day and backups, batches and other maintenance in the off hours. Nothing urgent here, just trying to plan out the future, mull over the possibilities and where to head.

There are no significant hits in performance, except in one area: IO. If your database VMs will have visualized storage -- then there will be IO overhead, it will be most noticeable in "mixed IO load" scenarios, like "lot of random IO requests + 1 or 2 sequential IO streams", i.e. a common situation when OLTP database generates random IO requests, and a backup process generates a steam of sequential read requests (or re-index process, or cluster operation, etc)

Taken alone neither random nor sequential IO presents a performance problem, but the mixture of thereof does. The reason is that XEN effectively serializes all VM's IO in a single kernel thread of the Dom0. The situation, I think, can be avoided if a VM has given direct access to a storage controller as a PCI device, but I have not tested such setup.

Details: For each block device inside VM xen hypervisor creates a kernel thread (named blkback.<device id>.xvd ). All IO requests that VM generates to this device are passed via buffers inside single kernel thread which creates IO starvation and latency problems. (I think only pointers are passed, not the data, but still it does not change the situation) This old article in linux journal does a good job explaining the nature of those problems in plain terms: http://www.linuxjournal.com/article/6931

I've run a number of tests to confirm this theory: 2 setups "normal" and "virtual" (see above) perform roughly the same (normal being ~10% faster) on purely transactional or heavily-sequential workloads (transactional: pgbench tests, sequentional: pg_dump or re-index process) , but taken together (pgbench test + pg_bump in parallel) normal setup turns out to be ~50% faster. During the tests in dom0 you can observe "blkback" kernel thread sitting in IO-wait mode and in DomU you see "disk 100% busy" in atop.

Regards, -Konstantin

-- Konstantin Antselovich mailto: konstantin@antrselovich.com

• Ben

---

CentOS-virt mailing list CentOS-virt@centos.org http://lists.centos.org/mailman/listinfo/centos-virt