For the past couple of years, the Microsoft Win2K/Win2K3 website that I support had been plagued with 500 errors and subsequent IIS resets. Our website is a combination of custom ASP and ASP.net code running on 32-bit Windows, connecting via ODBC driver to an Oracle 9.2.0.7 backend on Solaris. The side effect to our customers of IIS blowing up is that for a short period of time (less than an hour, usually), customers would see a number of these 500 server errors from the server that panicked. Usually, this was not much of a problem, as it happened on one or two servers once or twice per day and the number of errors was low. The workaround was to reset IIS.
Recently, we added more servers into our web farm and the problem happened more often, with a greater number of 500 errors being spit out when one of these newer, more powerful machines' IIS process loses its mind. So, we contacted Microsoft and used DebugDiag to setup a crash/hang dump to find out what memory threads were in play when IIS died.
Sending this information to Microsoft, Microsoft engineers pointed to an interaction between IIS and Oracle. Specifically, there were pointers to the 9.2.0.7 Oracle ODBC driver that we use to connect our web application to our database. In the past, we have had very little luck with Oracle being able to solve any driver related issues for us. But we know that the first thing Oracle Support will ask for is "do you have the latest driver set installed." Knowing that Oracle was going to require this of us, we started the process of upgrading the driver from 9i to 10G (10.2.0.4) in our development environment. More specifically, we needed the following:
- the Oracle 10G client (1GB)
- the 6810189 patch set for 32-bit Windows (1GB)
- the 7218676 patch set for 32-bit Windows (67MB)
The install is quite large. Our very basic install took about 500MB of disk space. Once we got the new 10G drivers and patch set installed in development, we put it through a gamut of tests. It seemed to work fine, but development is no substitute for production traffic on a site that gets millions of hits every day. So, we started rolling out the new drivers to the most problematic web servers.
The first week was very tense, as we let the drivers cook on one server. Previously, this server blew up at least once or twice a day. With the new drivers in place, two days went by without IIS experiencing a problem. Three days went by. Then an entire week went by without a problem! We were psyched! We started rolling out the driver to the rest of the eighteen web servers, two servers every two days.
After a week went by, we could tell that the number of 500 errors and IIS resets were diminishing. After a second week went by, we were about half way through the lot, with every decreasing numbers of 500 errors. Best of all, the boxes that were patched weeks back had not reset themselves. This was unheard of! After using Oracle drivers for eight years, this was the only time in the history of our website that we were not seeing 500 errors! Fabulous!
We finished upgrading from 9.2.0.7 to 10.2.0.4 a couple weeks later and are EXTREMELY happy to report that we no longer are experience IIS resets and blasts of 500 errors.
Thank you Oracle, for finally fixing this issue!
Showing posts with label oracle 10g. Show all posts
Showing posts with label oracle 10g. Show all posts
Tuesday, September 09, 2008
Thursday, January 24, 2008
using regular expressions in Oracle
I needed to select rows of data from a column where some of the records begin with a date in the form:
YYYYMMDD
The other records in the column had random text strings. For example:
N MESSAGE
- -------
1 20080124: Hello. I have written to you today to compliment you
2 I have a problem with one of the products you sell.
I thought it would be perfect to use regular expressions to pluck out only the records I needed. Luckily, Oracle now supports for regular expressions as of version 10G:
Writing Better SQL Using Regular Expressions
I needed to first create the regular expression to match the date. In simple terms, I want to match the eight-digit string.
First, here is a regular expression that matches any digit, 0-9:
[0-9]
Secondly, we can expand this match to include the entire eight digit date:
[0-9]\{8\}
The escaped braces repeat the initial match for any number eight ("8") times. I will then put a carat (^) in front of the regular expression to match the beginning of the pattern buffer. The pattern buffer in this case is the MESSAGE column in our table:
^[0-9]\{8\}
OK! So we have our regular expression. Let's put it in a SELECT statement. We will use Oracle 10Gs new regexp_like condition to perform a regular expression match:
SELECT n,message
FROM message_table
WHERE regexp_like (message, '^[0-9]\{8\}');
N MESSAGE
- -------
1 20080124: Hello. I have written to you today to compliment you
Sweet! This works.
There is a slight problem. I did notice a substantial performance penalty for using regexp_like. For a very small table (<20,000 rows), it took about a minute to return the results of the search. Eeek. A normal WHERE clause came back in less than a second.
If I have any further information regarding performance tuning using regular expressions in Oracle, I will let you know.
Here are some more complex examples
TAG
YYYYMMDD
The other records in the column had random text strings. For example:
N MESSAGE
- -------
1 20080124: Hello. I have written to you today to compliment you
2 I have a problem with one of the products you sell.
I thought it would be perfect to use regular expressions to pluck out only the records I needed. Luckily, Oracle now supports for regular expressions as of version 10G:
Writing Better SQL Using Regular Expressions
I needed to first create the regular expression to match the date. In simple terms, I want to match the eight-digit string.
First, here is a regular expression that matches any digit, 0-9:
[0-9]
Secondly, we can expand this match to include the entire eight digit date:
[0-9]\{8\}
The escaped braces repeat the initial match for any number eight ("8") times. I will then put a carat (^) in front of the regular expression to match the beginning of the pattern buffer. The pattern buffer in this case is the MESSAGE column in our table:
^[0-9]\{8\}
OK! So we have our regular expression. Let's put it in a SELECT statement. We will use Oracle 10Gs new regexp_like condition to perform a regular expression match:
SELECT n,message
FROM message_table
WHERE regexp_like (message, '^[0-9]\{8\}');
N MESSAGE
- -------
1 20080124: Hello. I have written to you today to compliment you
Sweet! This works.
There is a slight problem. I did notice a substantial performance penalty for using regexp_like. For a very small table (<20,000 rows), it took about a minute to return the results of the search. Eeek. A normal WHERE clause came back in less than a second.
If I have any further information regarding performance tuning using regular expressions in Oracle, I will let you know.
Here are some more complex examples
TAG
Wednesday, June 27, 2007
testing the Sun X4600 M2 and ESX Server 3.0, part II
To reiterate, we visited Sun a few weeks back with visions of virtual machine testing faeries running through our heads:
http://www.techanswerguy.com/2007/06/testing-sun-x4600m2-and-esx-server-30.html
The plan was to put the Sun X4600 M2 virtualization platform through its paces by toting three virtual machines to a Sun testing center and use ESX Server 3.0 installed on the box to manage a simulation of a highly available, high throughput eCommerce website:
- a Win2K3 web server running our web application (5GB)
- a RHEL3 server running Oracle 10G (34GB)
- a Win2K server to apply load to the web application (5GB)
The plan sounded easy enough, but the execution was somewhat more difficult. Let me tell you a story..
The vms were all stored on an external USB2.0 disk. We had left the two app and testing vms at Sun from the last visit, so they were already installed and ready to go. Since our data model is proprietary, we needed to bring a development version of our database with us each time we visited Sun's facility and delete it when we left. As I built the database on an Intel processor architecture, we needed to convert the database to the Opteron platform of the X4600 using VMware Converter each time we visited Sun. VMware Converter only runs on XP, so it was necessary to copy the db over to an XP workstation and then point VMware Converter to the ESX Server running on the X4600. As you can imagine, this was not an optimal situation, as we spent hours setting this up. By the way, VMware installs its own variant of Linux on the X4600. The Sun box does not run Solaris.
We had two hurdles to overcome:
1) we needed to copy our 34GB database to an XP machine with VMware Converter installed on it..this was a huge time sink
2) we needed to convert the database virtual machine from the Intel platform it was created on to the AMD Opteron platform of the Sun X4600M2
On this second visit to Sun, it took a couple hours to copy the RHEL3 Oracle database vm to the only available XP machine with enough space for it..our hosts' notebook computer! I guess that's what you get for trying to find an XP machine in a Sun testing lab! After the hour and a half long USB transfer, we were then able to start the conversion of the db vm. The conversion took about an hour. Both of these steps ended up eating through the entire morning.
The afternoon allowed me to familiarize myself with the Virtual Center Infrastructure client and the power it has. It bears a more in-depth review, but Virtual Center allows you to setup virtual machines with your desired configuration, start and stop them, and monitor running vms. One quick note if the VMware guys are reading this blog post: a nice addition to the Virtual Center client would be the ability to right-click on the performance charts to switch among the different performance monitor counters (CPU/Memory/Network/etc) immediately, instead of having to laboriously go into a separate menu each time you want to see the running stats of a particular virtual machine. I will try to review the product more in-depth at a future date.
The rest of the afternoon was mostly disappointing, but we did make a bit of headway. Since our last visit, we dumped Microsoft Web Stress Application tool in favor of the QEngine load testing tool. QEngine is much more robust than MS WSAT, is fairly easy to use as load testing tools go and has real time insight into the load on the source and destination servers. For now, we had installed QEngine on the Win2K virtual machine. Unfortunately, due to our limited knowledge of the tool, we were only able to generate a maximum volume of 20 simultaneous users. The tool froze when we tried to apply more load. This was an ignominious end to a generally disappointing day. But QEngine is easy to use and has some advanced functionality, so it bears further evaluation. In the next two weeks, the team is going to resolve the issue and I will try to give a full review of that product in the coming weeks.
One positive note that came out of the session was a chat session on how to resolve our copying/conversion dilemma by using a somewhat clever workaround. The idea is to bring an XP notebook with our database vm installed on it, so we would no longer have to spend the time to wait for the database to transfer over the network. And remember that XP is needed because VMware Converter runs on that OS alone. We could then hook up the notebook to Sun's network in the test lab, start VMware Converter in XP on the notebook and simply point the VMware Converter to the ESX Server installation on the X4600. The interesting thing is that I will use my MacBook Pro with 120GB of space to do this! I've made headway in the last few days on this issue and here's how I'm doing it:
Since we needed an XP machine, I thought I'd take one that was already configured..my XP workstation back in the office! But that's not a notebook, it's a dual-processor Xeon box chock full of 140GBs worth of programming tools, abandoned code and bloatware. As I didn't want to start with this fat XP Pro system, I slimmed down my XP Pro workstation's install profile to 8GB by deleting everything I didn't need off of it. Believe me, that was a helluva chore by itself! :)
Now it was time for VMware to the rescue. I used VMware Converter to convert the now trim physical XP system to a virtual machine. The destination of this conversion was another local drive within the workstation. After a slight hiccup, the conversion was successful! Now it was time to put the database in the vm. I started up the vm in VMware Server and copied the 34GB database to the vm via Windows Share. NICE! The file size of the XP vm was still 10GB though..I didn't quite understand that yet.
I stopped the new XP vm for transfer to the MacBook. Aha! Now I see that the vmdk file had ballooned to 45GB. I guess VMware waits until the vm is stopped to take inventory of how much space it uses. I wanted to transfer the XP Pro system with our database on it across to the MacBook, so I zipped up the virtual machine directory with WinZip. WinZip does an excellent job of compressing a virtual machine, so the final size of the zip archive was about 12GB zipped. Nice. I then copied it over the network to my MacBook Pro. I was able to copy the file in about seven minutes (roughly 5000Kbps) because I wired up a crossover cable between my dual Xeon workstation and the MacBook. Sweet!
The important moment was at hand! I had installed the latest 6/21/07 version of VMware Fusion and was excited to see if the 45GB vm would start up running under Mac OSX! I was a little nervous that the 45GB file wouldn't work on Fusion. Its starting slowly..BIOS..XP display..lights are dancing..mouse is moveable..logon prompt..YES! I'm in!! So I am very happy to report that the monster 45GB XP Pro vm with database started up and runs smoothly in VMware Fusion!
So we are good to go for round three at Sun. In the last few weeks, I have had some ups and downs with VMware Converter, but in general, I am still very happy with the rest of the toolset. So we are one step closer to validating our web site using VMware's virtualization technology. And we're using the full suite of VMware's tools to do it:
VMware Server
VMware Converter
VMware ESX Server 3.0
Until next time..
http://www.techanswerguy.com/2007/06/testing-sun-x4600m2-and-esx-server-30.html
The plan was to put the Sun X4600 M2 virtualization platform through its paces by toting three virtual machines to a Sun testing center and use ESX Server 3.0 installed on the box to manage a simulation of a highly available, high throughput eCommerce website:
- a Win2K3 web server running our web application (5GB)
- a RHEL3 server running Oracle 10G (34GB)
- a Win2K server to apply load to the web application (5GB)
The plan sounded easy enough, but the execution was somewhat more difficult. Let me tell you a story..
The vms were all stored on an external USB2.0 disk. We had left the two app and testing vms at Sun from the last visit, so they were already installed and ready to go. Since our data model is proprietary, we needed to bring a development version of our database with us each time we visited Sun's facility and delete it when we left. As I built the database on an Intel processor architecture, we needed to convert the database to the Opteron platform of the X4600 using VMware Converter each time we visited Sun. VMware Converter only runs on XP, so it was necessary to copy the db over to an XP workstation and then point VMware Converter to the ESX Server running on the X4600. As you can imagine, this was not an optimal situation, as we spent hours setting this up. By the way, VMware installs its own variant of Linux on the X4600. The Sun box does not run Solaris.
We had two hurdles to overcome:
1) we needed to copy our 34GB database to an XP machine with VMware Converter installed on it..this was a huge time sink
2) we needed to convert the database virtual machine from the Intel platform it was created on to the AMD Opteron platform of the Sun X4600M2
On this second visit to Sun, it took a couple hours to copy the RHEL3 Oracle database vm to the only available XP machine with enough space for it..our hosts' notebook computer! I guess that's what you get for trying to find an XP machine in a Sun testing lab! After the hour and a half long USB transfer, we were then able to start the conversion of the db vm. The conversion took about an hour. Both of these steps ended up eating through the entire morning.
The afternoon allowed me to familiarize myself with the Virtual Center Infrastructure client and the power it has. It bears a more in-depth review, but Virtual Center allows you to setup virtual machines with your desired configuration, start and stop them, and monitor running vms. One quick note if the VMware guys are reading this blog post: a nice addition to the Virtual Center client would be the ability to right-click on the performance charts to switch among the different performance monitor counters (CPU/Memory/Network/etc) immediately, instead of having to laboriously go into a separate menu each time you want to see the running stats of a particular virtual machine. I will try to review the product more in-depth at a future date.
The rest of the afternoon was mostly disappointing, but we did make a bit of headway. Since our last visit, we dumped Microsoft Web Stress Application tool in favor of the QEngine load testing tool. QEngine is much more robust than MS WSAT, is fairly easy to use as load testing tools go and has real time insight into the load on the source and destination servers. For now, we had installed QEngine on the Win2K virtual machine. Unfortunately, due to our limited knowledge of the tool, we were only able to generate a maximum volume of 20 simultaneous users. The tool froze when we tried to apply more load. This was an ignominious end to a generally disappointing day. But QEngine is easy to use and has some advanced functionality, so it bears further evaluation. In the next two weeks, the team is going to resolve the issue and I will try to give a full review of that product in the coming weeks.
One positive note that came out of the session was a chat session on how to resolve our copying/conversion dilemma by using a somewhat clever workaround. The idea is to bring an XP notebook with our database vm installed on it, so we would no longer have to spend the time to wait for the database to transfer over the network. And remember that XP is needed because VMware Converter runs on that OS alone. We could then hook up the notebook to Sun's network in the test lab, start VMware Converter in XP on the notebook and simply point the VMware Converter to the ESX Server installation on the X4600. The interesting thing is that I will use my MacBook Pro with 120GB of space to do this! I've made headway in the last few days on this issue and here's how I'm doing it:
Since we needed an XP machine, I thought I'd take one that was already configured..my XP workstation back in the office! But that's not a notebook, it's a dual-processor Xeon box chock full of 140GBs worth of programming tools, abandoned code and bloatware. As I didn't want to start with this fat XP Pro system, I slimmed down my XP Pro workstation's install profile to 8GB by deleting everything I didn't need off of it. Believe me, that was a helluva chore by itself! :)
Now it was time for VMware to the rescue. I used VMware Converter to convert the now trim physical XP system to a virtual machine. The destination of this conversion was another local drive within the workstation. After a slight hiccup, the conversion was successful! Now it was time to put the database in the vm. I started up the vm in VMware Server and copied the 34GB database to the vm via Windows Share. NICE! The file size of the XP vm was still 10GB though..I didn't quite understand that yet.
I stopped the new XP vm for transfer to the MacBook. Aha! Now I see that the vmdk file had ballooned to 45GB. I guess VMware waits until the vm is stopped to take inventory of how much space it uses. I wanted to transfer the XP Pro system with our database on it across to the MacBook, so I zipped up the virtual machine directory with WinZip. WinZip does an excellent job of compressing a virtual machine, so the final size of the zip archive was about 12GB zipped. Nice. I then copied it over the network to my MacBook Pro. I was able to copy the file in about seven minutes (roughly 5000Kbps) because I wired up a crossover cable between my dual Xeon workstation and the MacBook. Sweet!
The important moment was at hand! I had installed the latest 6/21/07 version of VMware Fusion and was excited to see if the 45GB vm would start up running under Mac OSX! I was a little nervous that the 45GB file wouldn't work on Fusion. Its starting slowly..BIOS..XP display..lights are dancing..mouse is moveable..logon prompt..YES! I'm in!! So I am very happy to report that the monster 45GB XP Pro vm with database started up and runs smoothly in VMware Fusion!
So we are good to go for round three at Sun. In the last few weeks, I have had some ups and downs with VMware Converter, but in general, I am still very happy with the rest of the toolset. So we are one step closer to validating our web site using VMware's virtualization technology. And we're using the full suite of VMware's tools to do it:
VMware Server
VMware Converter
VMware ESX Server 3.0
Until next time..
Wednesday, June 06, 2007
testing the Sun X4600M2 and ESX Server 3.0, part I
Outside of a few obstacles, we had a useful and interesting session testing a Sun X4600M2. The plan was to use three virtual machines on ESX Server 3.0 to simulate our eCommerce infrastructure:
- one Win2K3, IIS 6.0 web server running our website application
- one RHEL 3.0 AS running Oracle 10G
- one Win2K server running MS Web Stress Application Tool (MS WSAT) to generate HTTP traffic load against the Win2K3 web server
The X4600M2 we tested was an eight, dual-core 2.4Ghz Opteron RevE cpus connected to a Sun 5310 fiber storage array. The 4600 ran VMware ESX 3.0 server on top of a customized version of Linux built for VMware. I provided the vendor with the three preconfigured virtual machines. The vms were zipped on dual layer DVDs and took a while to copy and unzip, roughly an hour each. Also, the virtual machines were built on an Intel box and as such, needed to be converted specifically to the AMD Opteron architecture of the ESX server (the 4600). This was news to us and took about twenty minutes to convert the 8GB Windows vms and about an hour to convert the 33GB database file.
We started all three vms, did some Windows configuration and verified connectivity between the servers. TNSnames and an ODBC driver needed setup on the web server. The first large hurdle we encountered was that unlike our test system, the RHEL3 vm was not able to find its IP address via DHCP. After trying a few things, we assigned the address statically and the server then became available on the network. Once all three boxes were talking, we then verified that the website could pull data from the database. We did this; however, we saw that the database sequences were not created when we added an item to our cart. I got on the phone with our programmers and after about 45 minutes, resolved the problem using a public synonym. After this problem was solved, we spent a half an hour using the WSAT's recorder function to navigate the website and create the test cases. We were then able to start testing.
As our vendor did not have an Enterprise license for the ESX Server installation, we were limited to assigning up to four cpus per vm. So we assigned each vm the maximum available:
- Oracle vm: four cpus
- IIS vm: four cpus
- MS WSAT vm: four cpus
Since one CPU on the 4600 is dedicated to VMware overhead, this left three CPUs unused in the 4600.
We used MS WSAT to apply load to the Web server instance, slowly increasing load from one session to ten to one hundred virtual users in order to verify that:
1) the stress tool was working correctly,
2) the website was responding appropriately, and
3) we could see data via the VMware Virtual Infrastructure Client management app
We verified that these conditions were met.
It was interesting to view the VMware instrumentation. The VMware Infrastructure management app is a lot like Performance Monitor in Windows. You can view CPU/disk/memory and network stats. We toggled between the three vms and checked out performance stats for each. The most stressed vm was the IIS webserver, as it was serving data to the testing client (the Win2K server running MS WSAT), as well as pulling content from the database.
One interesting metric we saw in the management interface was called Megahertz Used, which is basically the percent of the total megahertz available to a vm. For example, if a vm has one 2.4Ghz cpu dedicated to it and that cpu is 10% busy, you're using 240Mhz of the available CPU power. On our Win2K3 web server vm, we had four cpus available at 2.4Ghz each. This gave us a total of about 10,000 megahertz available to the vm. When we increased the load to the Win2K3 web server, we saw that the webserver was using about 80-90% total CPU available or about 8,800Mhz of CPU. This load was more or less equally divided by the four CPUs assigned to the VM:
cpu0: 2300 mhz used
cpu1: 2200 mhz used
cpu2: 2200 mhz used
cpu3: 2100 mhz used
Utilizing the megahertz available to a vm, VMware is able to balances load to cpus within a vm as well as balance load between vms. ESX server 3.0 can dynamically provision new vms by analyzing this statistic.
Another interesting thing we did was to clone our testing server, the Win2K server with MS WSAT installed on it. As the clone is essentially a file copy, the process is i/o intensive and took about 10 minutes for the 8GB vm. With a configuration tweak and a quick start of the server, the cloned testing server was up and applying load against the website in 15 minutes total from start of clone to finish. Nice!
While testing, we found that the MS stress tool applies load, but has a nagging inability to capture enough information about a users' session so that an order can be completed through the test website. Also, the stress tool seems to quiesce after about 7-10 minutes. This may have been due to some caching on the database and web server layers, but is more likely due to a limitation with MS WSAT. So we are looking to replace this testing tool with one that doesn't have these limitations and can do interesting things like parameterize order and sku numbers in the requested URL. Compuware QALoad is a top candidate and one we're already licensed to have. We are currently researching tools for round 2 and hopefully, we'll have a substitute in the next couple of weeks.
In order to get a more full day of testing on the 4600, we will schedule a second visit to our vendor, with the caveat that I will bring a fully configured database, unzipped on an external USB drive in order to expedite the setup. Also, I hope to persuade the vendor to get an Enterprise license for ESX Server, so that we can assign more than four CPUs to an individual vm. Finally, at the end of the day, I will try to provide some screen shots or scripts of the evaluation session for the blog.
More to come..keep you posted!
- one Win2K3, IIS 6.0 web server running our website application
- one RHEL 3.0 AS running Oracle 10G
- one Win2K server running MS Web Stress Application Tool (MS WSAT) to generate HTTP traffic load against the Win2K3 web server
The X4600M2 we tested was an eight, dual-core 2.4Ghz Opteron RevE cpus connected to a Sun 5310 fiber storage array. The 4600 ran VMware ESX 3.0 server on top of a customized version of Linux built for VMware. I provided the vendor with the three preconfigured virtual machines. The vms were zipped on dual layer DVDs and took a while to copy and unzip, roughly an hour each. Also, the virtual machines were built on an Intel box and as such, needed to be converted specifically to the AMD Opteron architecture of the ESX server (the 4600). This was news to us and took about twenty minutes to convert the 8GB Windows vms and about an hour to convert the 33GB database file.
We started all three vms, did some Windows configuration and verified connectivity between the servers. TNSnames and an ODBC driver needed setup on the web server. The first large hurdle we encountered was that unlike our test system, the RHEL3 vm was not able to find its IP address via DHCP. After trying a few things, we assigned the address statically and the server then became available on the network. Once all three boxes were talking, we then verified that the website could pull data from the database. We did this; however, we saw that the database sequences were not created when we added an item to our cart. I got on the phone with our programmers and after about 45 minutes, resolved the problem using a public synonym. After this problem was solved, we spent a half an hour using the WSAT's recorder function to navigate the website and create the test cases. We were then able to start testing.
As our vendor did not have an Enterprise license for the ESX Server installation, we were limited to assigning up to four cpus per vm. So we assigned each vm the maximum available:
- Oracle vm: four cpus
- IIS vm: four cpus
- MS WSAT vm: four cpus
Since one CPU on the 4600 is dedicated to VMware overhead, this left three CPUs unused in the 4600.
We used MS WSAT to apply load to the Web server instance, slowly increasing load from one session to ten to one hundred virtual users in order to verify that:
1) the stress tool was working correctly,
2) the website was responding appropriately, and
3) we could see data via the VMware Virtual Infrastructure Client management app
We verified that these conditions were met.
It was interesting to view the VMware instrumentation. The VMware Infrastructure management app is a lot like Performance Monitor in Windows. You can view CPU/disk/memory and network stats. We toggled between the three vms and checked out performance stats for each. The most stressed vm was the IIS webserver, as it was serving data to the testing client (the Win2K server running MS WSAT), as well as pulling content from the database.
One interesting metric we saw in the management interface was called Megahertz Used, which is basically the percent of the total megahertz available to a vm. For example, if a vm has one 2.4Ghz cpu dedicated to it and that cpu is 10% busy, you're using 240Mhz of the available CPU power. On our Win2K3 web server vm, we had four cpus available at 2.4Ghz each. This gave us a total of about 10,000 megahertz available to the vm. When we increased the load to the Win2K3 web server, we saw that the webserver was using about 80-90% total CPU available or about 8,800Mhz of CPU. This load was more or less equally divided by the four CPUs assigned to the VM:
cpu0: 2300 mhz used
cpu1: 2200 mhz used
cpu2: 2200 mhz used
cpu3: 2100 mhz used
Utilizing the megahertz available to a vm, VMware is able to balances load to cpus within a vm as well as balance load between vms. ESX server 3.0 can dynamically provision new vms by analyzing this statistic.
Another interesting thing we did was to clone our testing server, the Win2K server with MS WSAT installed on it. As the clone is essentially a file copy, the process is i/o intensive and took about 10 minutes for the 8GB vm. With a configuration tweak and a quick start of the server, the cloned testing server was up and applying load against the website in 15 minutes total from start of clone to finish. Nice!
While testing, we found that the MS stress tool applies load, but has a nagging inability to capture enough information about a users' session so that an order can be completed through the test website. Also, the stress tool seems to quiesce after about 7-10 minutes. This may have been due to some caching on the database and web server layers, but is more likely due to a limitation with MS WSAT. So we are looking to replace this testing tool with one that doesn't have these limitations and can do interesting things like parameterize order and sku numbers in the requested URL. Compuware QALoad is a top candidate and one we're already licensed to have. We are currently researching tools for round 2 and hopefully, we'll have a substitute in the next couple of weeks.
In order to get a more full day of testing on the 4600, we will schedule a second visit to our vendor, with the caveat that I will bring a fully configured database, unzipped on an external USB drive in order to expedite the setup. Also, I hope to persuade the vendor to get an Enterprise license for ESX Server, so that we can assign more than four CPUs to an individual vm. Finally, at the end of the day, I will try to provide some screen shots or scripts of the evaluation session for the blog.
More to come..keep you posted!
Thursday, April 26, 2007
Oracle 10G on RHEL3 quick install instructions
One of the more interesting things I've done lately is install Oracle 10G on RHEL3 Advanced Server. Actually, a virtual instance of RHEL3 running under VMware Server. Granted, it's an older version of RHEL, but since this was a development/test platform, our company had some install disks laying around and I decided to take the path of least resistance. The install guide for 10G on RHEL3 is here:
Oracle® Database Installation Guide, 10g Release 2 (10.2) for Linux x86
http://download-east.oracle.com/docs/cd/B19306_01/install.102/b15660/install_overview.htm
OK. So nothing is ever "quick" about an Oracle installation. Maybe I should have said "install synopsis" or something similar. But the point is that the instructions below are not a substitute for the full documentation above, but serve to remind me of what I did to get Oracle up and running. So here is the list of high-level steps and quick diagnostics for folks doing the same:
1) Prepping the operating system
- install RHEL3
- include compatibility libraries and GCC
- create oracle user
- create oinstall and dba groups
2) The oracle installation
- during the install, SYS/SYSTEM/DMSNMP/SYSMAN will get the same password by default
- to configure Enterprise Manager, execute dbca at the command line
3) OS tweaks
- as root, set ORACLE_HOME and ORACLE_SID environment variables
- as oracle, set ORACLE_BASE, ORACLE_HOME, ORACLE_SID environment variables
- as oracle, add ORACLE_HOME/bin to PATH
- set NLS_LANG environment variable
4) configure Oracle net svcs
- Modify the listener.ora file. Here is a sample:
LISTENER =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)
(HOST = oracletest.test.com)
(PORT = 1521) (QUEUESIZE =32)
)
)
- Modify the tnsnames.ora file. Here is a sample:
TEST.WORLD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracletest)(PORT = 1521))
)
(CONNECT_DATA =
(SID = test)
(SERVER = DEDICATED)
)
)
5) Start the db
In SQL*Plus, as follows:
[oracle@oracletest oracle]$ sqlplus "/ AS SYSDBA"
Start the database:
SQL> STARTUP
Command to shutdown the database:
SQL> SHUTDOWN IMMEDIATE
6) start the listener
[oracle@oracletest oracle]$ lsnrctl start
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 26-APR-2007 15:43:39
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Starting /oracle/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 10.2.0.1.0 - Production
System parameter file is /oracle/network/admin/listener.ora
Log messages written to /oracle/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracletest.test.com)(PORT=1521)))
Connecting to (ADDRESS=(PROTOCOL=TCP)(HOST=oracletest.test.com)(PORT=1521)(QUEUESIZE=32))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 26-APR-2007 15:43:39
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/network/admin/listener.ora
Listener Log File /oracle/network/log/listener.log
Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracletest.test.com)(PORT=1521)))
The listener supports no services
The command completed successfully
You can also use "status" or "stop" as arguments to the lsnrctl command:
lsnrctl [stop,start,status]
7) Starting the web based 10G Enterprise Manager database control
If you want to use the web based 10G Enterprise Manager database control, start it up:
[oracle@oracletest oracle]$ emctl start dbconsole
TZ set to US/Eastern
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
http://oracletest.test.com:1158/em/console/aboutApplication
Starting Oracle Enterprise Manager 10g Database Control .............. started.
------------------------------------------------------------------
Logs are generated in directory /oracle/oracletest.test.com_test/sysman/log
Login to the web based console: http://host:port/em
You may get a timezone mismatch. If so, check this out:
http://cacasodo.blogspot.com/2007/04/timezone-mismatch-agenttzregion-does.html
Verifying the database from a client
1) Verify the database listener is up and running
C:\WINDOWS>tnsping test
TNS Ping Utility for 32-bit Windows: Version 9.2.0.7.0 - Production on 23-APR-2007 11:54:19
Copyright (c) 1997 Oracle Corporation. All rights reserved.
Used parameter files:
C:\oracle9i\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = oracletest)(PORT = 1521))) (CONNECT_DATA = (SID = test) (SERVER = DEDICATED)))
OK (30 msec)
2) connection errors
a. You may get this error if you do not have a proper tnsnames entry for your database:
C:\WINDOWS>sqlplus web/web@test
SQL*Plus: Release 9.2.0.7.0 - Production on Mon Apr 23 11:53:43 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
ERROR:ORA-12505: TNS:listener could not resolve SID given in connect descriptor
Enter user-name: ^C
b. You may get this error if you are using the wrong user or password to connect to the database:
C:\WINDOWS>sqlplus web/web@test
SQL*Plus: Release 9.2.0.7.0 - Production on Mon Apr 23 11:54:32 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
ERROR:ORA-01017: invalid username/password; logon denied
Enter user-name: ^C
3) Successful connection.
If all is well, select SYSDATE from the DUAL table just to verify you can get data
C:\WINDOWS>sqlplus web/web@test
SQL*Plus: Release 9.2.0.7.0 - Production on Mon Apr 23 11:55:12 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining options
SQL> select sysdate from dual;
SYSDATE
---------------
26-APR-07
SQL> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining options
Other helpful commands
Finding your SID
First, check your environment settings..
[oracle@test]$ set grep SID
ORACLE_SID=test
Then login to oracle:
sqlplus sys as sysdba
SELECT instance_name
FROM v$instance;
Make sure your listener is up!
[oracle@test oracle]$ lsnrctl status
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 26-OCT-2010 23:25:17
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=TCP)(HOST=test.test.com)(PORT=1521)(QUEUESIZE=32))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 26-OCT-2010 23:15:19
Uptime 0 days 0 hr. 9 min. 58 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/network/admin/listener.ora
Listener Log File /oracle/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test.test.com)(PORT=1521)))
Services Summary...
Service "test" has 1 instance(s).
Instance "test", status READY, has 1 handler(s) for this service...
Service "testXDB" has 1 instance(s).
Instance "test", status READY, has 1 handler(s) for this service...
Service "test_XPT" has 1 instance(s).
Instance "test", status READY, has 1 handler(s) for this service...
The command completed successfully
Good luck!
TAG
References
http://forums.oracle.com/forums/thread.jspa?threadID=260359&tstart=60 http://forums.oracle.com/forums/thread.jspa?threadID=917370
Changing Oracle Enterprise Manager port numbers
Oracle Insert statements
Get list of constraints
Oracle® Database Installation Guide, 10g Release 2 (10.2) for Linux x86
http://download-east.oracle.com/docs/cd/B19306_01/install.102/b15660/install_overview.htm
OK. So nothing is ever "quick" about an Oracle installation. Maybe I should have said "install synopsis" or something similar. But the point is that the instructions below are not a substitute for the full documentation above, but serve to remind me of what I did to get Oracle up and running. So here is the list of high-level steps and quick diagnostics for folks doing the same:
1) Prepping the operating system
- install RHEL3
- include compatibility libraries and GCC
- create oracle user
- create oinstall and dba groups
2) The oracle installation
- during the install, SYS/SYSTEM/DMSNMP/SYSMAN will get the same password by default
- to configure Enterprise Manager, execute dbca at the command line
3) OS tweaks
- as root, set ORACLE_HOME and ORACLE_SID environment variables
- as oracle, set ORACLE_BASE, ORACLE_HOME, ORACLE_SID environment variables
- as oracle, add ORACLE_HOME/bin to PATH
- set NLS_LANG environment variable
4) configure Oracle net svcs
- Modify the listener.ora file. Here is a sample:
LISTENER =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)
(HOST = oracletest.test.com)
(PORT = 1521) (QUEUESIZE =32)
)
)
- Modify the tnsnames.ora file. Here is a sample:
TEST.WORLD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracletest)(PORT = 1521))
)
(CONNECT_DATA =
(SID = test)
(SERVER = DEDICATED)
)
)
5) Start the db
In SQL*Plus, as follows:
[oracle@oracletest oracle]$ sqlplus "/ AS SYSDBA"
Start the database:
SQL> STARTUP
Command to shutdown the database:
SQL> SHUTDOWN IMMEDIATE
6) start the listener
[oracle@oracletest oracle]$ lsnrctl start
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 26-APR-2007 15:43:39
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Starting /oracle/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 10.2.0.1.0 - Production
System parameter file is /oracle/network/admin/listener.ora
Log messages written to /oracle/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracletest.test.com)(PORT=1521)))
Connecting to (ADDRESS=(PROTOCOL=TCP)(HOST=oracletest.test.com)(PORT=1521)(QUEUESIZE=32))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 26-APR-2007 15:43:39
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/network/admin/listener.ora
Listener Log File /oracle/network/log/listener.log
Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracletest.test.com)(PORT=1521)))
The listener supports no services
The command completed successfully
You can also use "status" or "stop" as arguments to the lsnrctl command:
lsnrctl [stop,start,status]
7) Starting the web based 10G Enterprise Manager database control
If you want to use the web based 10G Enterprise Manager database control, start it up:
[oracle@oracletest oracle]$ emctl start dbconsole
TZ set to US/Eastern
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
http://oracletest.test.com:1158/em/console/aboutApplication
Starting Oracle Enterprise Manager 10g Database Control .............. started.
------------------------------------------------------------------
Logs are generated in directory /oracle/oracletest.test.com_test/sysman/log
Login to the web based console: http://host:port/em
You may get a timezone mismatch. If so, check this out:
http://cacasodo.blogspot.com/2007/04/timezone-mismatch-agenttzregion-does.html
Verifying the database from a client
1) Verify the database listener is up and running
C:\WINDOWS>tnsping test
TNS Ping Utility for 32-bit Windows: Version 9.2.0.7.0 - Production on 23-APR-2007 11:54:19
Copyright (c) 1997 Oracle Corporation. All rights reserved.
Used parameter files:
C:\oracle9i\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = oracletest)(PORT = 1521))) (CONNECT_DATA = (SID = test) (SERVER = DEDICATED)))
OK (30 msec)
2) connection errors
a. You may get this error if you do not have a proper tnsnames entry for your database:
C:\WINDOWS>sqlplus web/web@test
SQL*Plus: Release 9.2.0.7.0 - Production on Mon Apr 23 11:53:43 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
ERROR:ORA-12505: TNS:listener could not resolve SID given in connect descriptor
Enter user-name: ^C
b. You may get this error if you are using the wrong user or password to connect to the database:
C:\WINDOWS>sqlplus web/web@test
SQL*Plus: Release 9.2.0.7.0 - Production on Mon Apr 23 11:54:32 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
ERROR:ORA-01017: invalid username/password; logon denied
Enter user-name: ^C
3) Successful connection.
If all is well, select SYSDATE from the DUAL table just to verify you can get data
C:\WINDOWS>sqlplus web/web@test
SQL*Plus: Release 9.2.0.7.0 - Production on Mon Apr 23 11:55:12 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining options
SQL> select sysdate from dual;
SYSDATE
---------------
26-APR-07
SQL> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining options
Other helpful commands
Finding your SID
First, check your environment settings..
[oracle@test]$ set grep SID
ORACLE_SID=test
Then login to oracle:
sqlplus sys as sysdba
SELECT instance_name
FROM v$instance;
Make sure your listener is up!
[oracle@test oracle]$ lsnrctl status
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 26-OCT-2010 23:25:17
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=TCP)(HOST=test.test.com)(PORT=1521)(QUEUESIZE=32))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 26-OCT-2010 23:15:19
Uptime 0 days 0 hr. 9 min. 58 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/network/admin/listener.ora
Listener Log File /oracle/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test.test.com)(PORT=1521)))
Services Summary...
Service "test" has 1 instance(s).
Instance "test", status READY, has 1 handler(s) for this service...
Service "testXDB" has 1 instance(s).
Instance "test", status READY, has 1 handler(s) for this service...
Service "test_XPT" has 1 instance(s).
Instance "test", status READY, has 1 handler(s) for this service...
The command completed successfully
Good luck!
TAG
References
http://forums.oracle.com/forums/thread.jspa?threadID=260359&tstart=60 http://forums.oracle.com/forums/thread.jspa?threadID=917370
Changing Oracle Enterprise Manager port numbers
Oracle Insert statements
Get list of constraints
Subscribe to:
Posts (Atom)