RE: Recommended Specs for Oracle 10g db server
Thank you all kindly for these valuable comments and suggestions. I have notices somethings we have obvious problems and need to be addressed. We are running this 10g on a hardware RAID-5 array (SCSI). The 10g is at the backend of 12 tomcat servers load balanced thru a apache web server. Each tomcat has a max. connections of 40 simulataneously. As you have said we have identified few sql processes overloading the CPU's. However, we cannot move back to non HT technology as this is not provided at a HW level to us. So were either planning to go for a quad (HT) with lots more RAM so to increase SGA. Thanks again for your help. -- View this message in context: http://www.nabble.com/Recommended-Specs-for-Oracle-10g-db-server-t1340651.html#a3595161 Sent from the Tomcat - User forum at Nabble.com. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: Recommended Specs for Oracle 10g db server
--- Mohan2005 [EMAIL PROTECTED] wrote:
Thank you all kindly for these valuable comments and
suggestions. I have
notices somethings we have obvious problems and need
to be addressed.
We are running this 10g on a hardware RAID-5 array
(SCSI).
The 10g is at the backend of 12 tomcat servers load
balanced thru a apache
web server.
Each tomcat has a max. connections of 40
simulataneously.
As you have said we have identified few sql
processes overloading the CPU's.
However, we cannot move back to non HT technology as
this is not provided at
a HW level to us.
So were either planning to go for a quad (HT) with
lots more RAM so to
increase SGA.
Thanks again for your help.
--
View this message in context:
http://www.nabble.com/Recommended-Specs-for-Oracle-10g-db-server-t1340651.html#a3595161
Sent from the Tomcat - User forum at Nabble.com.
I noticed one comment sent to you was their issue with
bottle necks dealt with locks. We had a similar issue
with an MS SQL server at my last company. I started
studying the issue a bit and the problem was we were
doing multiple inserts, updates, deletes in a
transaction using multiple JDBC (executeUpdate)calls.
What I did to speed this up was to create a SQL Buffer
which would stack multiple insert,update, and delete
statements in a single string which would get sent to
the backend (database) server at once instead of this
happening individually. The only thing you lose is
the update count returned by JDBC, but what you gain
is a HUGE performance gain by allow you to shorten the
over all transaction and significantly reduce the lock
time.
We had things which were running 30 minutes or so
before we made the buffer changes. After adding this
buffering mechanism we added to our DAO (Data Access
Objectsthey were Plain Old Java Objects) the runs
of an individual users updates took between 30-60
seconds (1/2 minute thru 1 minute). This was their
overall process time to perform all of the updates the
software was making to their data.
So, our difference was better than we hoped for. 30
minutes vs 30-60 seconds is a performance gain to me
;-). We had something like 1000-100,000 insert and
updates at a time. What I did was come up with a
piece that would run multiple large SQL statements.
Reason: different databases allow different buffer
sizes for the SQL which can be run at one time. MS
SQL Server I limited the overall calling to around
1000 statements at a time. You'll have to figure out
what happens with Oracle and how many statements can
be included at one time. Basically:
buffer.addUpdate(INSERT..yada yada yada);
buffer.addUpdate(INSERT..yada yada yada);
buffer.addUpdate(UPDATE..yada yada yada);
buffer.addUpdate(UPDATE..yada yada yada);
buffer.addUpdate(UPDATE..yada yada yada);
buffer.addUpdate(DELETE..yada yada yada);
...etc
buffer.update();//runs the buffered SQL statements.
...every time I called addUpdate limit number of
times I added the current List to a Vector to hold
statements which would be called when update was
issued. So...basically...update did:
//you've already gotten a Statement object s
for(int i = 0; i vector.size();i++){
List l = (List)vector.get(i);
StringBuffer sql = new StringBuffer();
for(int x = 0; x l.size();x++){
String s = (String)l.get(i);
sql.append(s);
sql.append('\n');
}
//remeber since you have multipe statements here
//the last call is the only call the return
//value mentions...update count of the last
//statement in the buffer
s.executeUpdate(sql.toString());
}
Anyways, I've used this scheme with MySQL, MS SQL,
Firebird, Sybase, and HSQLDB, and it worked equally
well with all. You don't have to use it with
everything, but for very large DB transactions it
seems to be the best way to get around locks and hangs
and other performance issues. So, maybe you could get
away with locating your main long running transactions
and replacing any code there with this type of scheme
to see how it helps. Granted this may not even be
your issue as it depends on what your software is
doing, but if you are performing many (hundreds or
thousands or hundreds of thousands) updates in a
single transaction and using multipe executeUpdate
calls from JDBC to do this updates or using EJB or
Hibernate or something else like it then I'm guessing
this is going to be a large part of your problem if
not the problem. MS SQL server hates long running
transactions (not sure about Oracle, but guessing it's
the same..just the way locks work), and it's PIDs
would show processes locking up and dropping off the
map, never coming back, and leaving locks locked
indefinitely or for hours at a time sometimes.
Basically once you are done implementing this
buffering the only real JDBC code you'll end up with
is getting the Connection, Statement, and calling
update on your buffer which calls these buffered
statements.
RE: Recommended Specs for Oracle 10g db server
Hi
Have you tuned the database? Do you have DBA's that monitor the database
etc. What is your storage sub-system? Most database performance issues come
back to the application.
Run an AWR report against it from SQL*Plus and then get it analysed at
http://www.oraperf.com (it's a free service you just need to register). Run
the script ${ORACLE_HOME}/rdbms/admin/awrrpt.sql during your peak loads.
Sizing a database machine is much more complex than you imply. I have a lot
of databases that run on machines of that size and they are capable of a lot
of transactions per second.
Regards.
Nick Havard
mailto:[EMAIL PROTECTED]
P.S. Remove xyz to reply direct.
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf
Of Mohan Wickramasinghe
Sent: 25 March 2006 07:59
To: users@tomcat.apache.org
Subject: Recommended Specs for Oracle 10g db server
We have 12 jboss nodes using a single oracle 10g db server with following
specs...
4GB RAM
Dual CPU HT 3.2GHz Intel
RHEL 4 smp
We see load average going to 10 on a very regular basis and 30-90 during
peak hours and see the database as the bottleneck to our application
performance.
We also had issue with c3p0 connections from nodes having issues and
upgraded it.
Can someone recommend machine (HW) specs for a oracle 10g database under
these conditions please.
regards
mohan
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Re: Recommended Specs for Oracle 10g db server
Turn off hyperthreading for starters. Oracle requires a minimum of 5 seperate logical disks to function at optimal in a production environment. You will need a good raid controller, and a good disk array to get any kind of decent insert speed. You will need to do some serious oracle tuning, there are some good books available out there for this. But at the very least you need to set the SGA to about 2.5 Gig, and tune the other memory usage things. Profile your database and find out which queries are running slow and why. Look at statspack (at least that's what we use in Oracle 9i) which can generate some good reports. Alex On 3/25/06, Mohan Wickramasinghe [EMAIL PROTECTED] wrote: We have 12 jboss nodes using a single oracle 10g db server with following specs... 4GB RAM Dual CPU HT 3.2GHz Intel RHEL 4 smp We see load average going to 10 on a very regular basis and 30-90 during peak hours and see the database as the bottleneck to our application performance. We also had issue with c3p0 connections from nodes having issues and upgraded it. Can someone recommend machine (HW) specs for a oracle 10g database under these conditions please. regards mohan - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: Recommended Specs for Oracle 10g db server
On Sat, Mar 25, 2006 at 11:18:58AM -0500, Alex Turner wrote: Turn off hyperthreading for starters. Do that very carefully. We're using Xeons for a 10g cluster and thought that turning off hyperthreading might help with stability. In fact it just killed our performance. It's back on now. But our performance bottleneck was locks, not stricly CPU load, so YMMV. FWIW, Barry Roberts - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: Recommended Specs for Oracle 10g db server
Afternoon All- Dont forget the direct correlation between hyperthreading (vis-a-vis cpu-count) affecting CPU cost (verified in plan table) for parallel tables Tom kyte addressed this issue here http://asktom.oracle.com/pls/ask/f?p=4950:8:F4950_P8_DISPLAYID:36798353051561 Good Stuff, Martin-- - Original Message - From: Alex Turner [EMAIL PROTECTED] To: Tomcat Users List users@tomcat.apache.org; [EMAIL PROTECTED] Sent: Saturday, March 25, 2006 11:18 AM Subject: Re: Recommended Specs for Oracle 10g db server Turn off hyperthreading for starters. Oracle requires a minimum of 5 seperate logical disks to function at optimal in a production environment. You will need a good raid controller, and a good disk array to get any kind of decent insert speed. You will need to do some serious oracle tuning, there are some good books available out there for this. But at the very least you need to set the SGA to about 2.5 Gig, and tune the other memory usage things. Profile your database and find out which queries are running slow and why. Look at statspack (at least that's what we use in Oracle 9i) which can generate some good reports. Alex On 3/25/06, Mohan Wickramasinghe [EMAIL PROTECTED] wrote: We have 12 jboss nodes using a single oracle 10g db server with following specs... 4GB RAM Dual CPU HT 3.2GHz Intel RHEL 4 smp We see load average going to 10 on a very regular basis and 30-90 during peak hours and see the database as the bottleneck to our application performance. We also had issue with c3p0 connections from nodes having issues and upgraded it. Can someone recommend machine (HW) specs for a oracle 10g database under these conditions please. regards mohan - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: Recommended Specs for Oracle 10g db server
Hyperthreading is a plus in 10g (my experience). One of my clients has seen a 70-80% improvement in their batch processing by using it with appropriate parallelism etc. Setting the SGA to 2.5GB on a 4GB machine is well over the top. By the time you add in other memory components you'll be killing the OS. In 10g statspack was replaced with AWR. Regards. Nick Havard mailto:[EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Alex Turner Sent: 25 March 2006 16:19 To: Tomcat Users List; [EMAIL PROTECTED] Subject: Re: Recommended Specs for Oracle 10g db server Turn off hyperthreading for starters. Oracle requires a minimum of 5 seperate logical disks to function at optimal in a production environment. You will need a good raid controller, and a good disk array to get any kind of decent insert speed. You will need to do some serious oracle tuning, there are some good books available out there for this. But at the very least you need to set the SGA to about 2.5 Gig, and tune the other memory usage things. Profile your database and find out which queries are running slow and why. Look at statspack (at least that's what we use in Oracle 9i) which can generate some good reports. Alex On 3/25/06, Mohan Wickramasinghe [EMAIL PROTECTED] wrote: We have 12 jboss nodes using a single oracle 10g db server with following specs... 4GB RAM Dual CPU HT 3.2GHz Intel RHEL 4 smp We see load average going to 10 on a very regular basis and 30-90 during peak hours and see the database as the bottleneck to our application performance. We also had issue with c3p0 connections from nodes having issues and upgraded it. Can someone recommend machine (HW) specs for a oracle 10g database under these conditions please. regards mohan - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
