Poor Write Performance with Oracle

We recently deployed an Oracle virtual machine for development and testing purposes. Imports and database migration scripts were taking several hours on existing VM’s, so we hoped this new machine with more RAM (32 GB) and more CPU horsepower (quad core Intel Xeon’s) would allow for those operations to move along much more quickly.

We soon got reports from users that this server was in fact much slower then the existing less powerful Oracle VM’s. After doing some poking around (with vztop) we discovered that there were no issues with cpu or memory resources, however the server was performing terribly when it came to I/O.

I started by increasing the size of ‘sga_max_size’. It was a bit undersized and I thought that sizing it a bit more correctly might allow the system to rely more on memory and less on the underlying filesystem.

I then increased the number of ‘db_writer_processes’ from 1 to 3. Taken from help:

‘DB_WRITER_PROCESSES is useful for systems that modify data heavily. It specifies the initial number of database writer processes for an instance.’

After looking at the alert log I also found the following ‘Checkpoint not complete’. To try and combat this I increased the number of redo log groups from 3 to 5.

I also added the following lines to the /etc/sysctl.conf file.

kernel.shmall = 2097152
kernel.shmmax = 536870912
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000

It also turned out that the LSI RAID card that we were using has a long history of poor write performance.

Leave a Reply

Your email address will not be published. Required fields are marked *