You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@tomcat.apache.org by Mohan Wickramasinghe <mo...@roomsnet.com> on 2006/03/25 08:59:07 UTC
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: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: Recommended Specs for Oracle 10g db server
Posted by Martin Gainty <mg...@hotmail.com>.
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" <ar...@gmail.com>
To: "Tomcat Users List" <us...@tomcat.apache.org>; <mo...@roomsnet.com>
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 <mo...@roomsnet.com> 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: users-unsubscribe@tomcat.apache.org
> For additional commands, e-mail: users-help@tomcat.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: Recommended Specs for Oracle 10g db server
Posted by Barry Roberts <bl...@www.robertsr.us>.
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: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
RE: Recommended Specs for Oracle 10g db server
Posted by Nick Havard <ni...@nickhavard.com>.
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:nick@nickhavardxyz.com
-----Original Message-----
From: users-return-142978-nick=nickhavard.com@tomcat.apache.org
[mailto:users-return-142978-nick=nickhavard.com@tomcat.apache.org] On Behalf
Of Alex Turner
Sent: 25 March 2006 16:19
To: Tomcat Users List; mohan@roomsnet.com
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 <mo...@roomsnet.com> 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: users-unsubscribe@tomcat.apache.org
> For additional commands, e-mail: users-help@tomcat.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: Recommended Specs for Oracle 10g db server
Posted by Alex Turner <ar...@gmail.com>.
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 <mo...@roomsnet.com> 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: users-unsubscribe@tomcat.apache.org
> For additional commands, e-mail: users-help@tomcat.apache.org
>
>
reject
Posted by Mikolaj <jo...@o2.pl>.
--
Ten list zosta³ przeskanowany przez program ArcaMail
Silnik (2006-01-25/2006-03-25)
This message has been scanned by ArcaMail
Engine (2006-01-25/2006-03-25)
http://www.mks.com.pl
RE: Recommended Specs for Oracle 10g db server
Posted by Wade Chandler <hw...@yahoo.com>.
--- Mohan2005 <mo...@roomsnet.com> 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
Objects....they 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.
Connection con = ...
con.setAutoCommit(false);//...the usual
Statement s = ...
buffer.update();//errors can still be thrown so you
could rollback on errors....
con.commit();
Anyways, that's a little vague, but you should be able
to get the point. I've done it and it works, and was
the only way we were able to support our number of
users with this educational software we had created.
Before that database updates were taking way too long
and it greatly reduced the number of users we could
support and we were getting all kinds of complaints.
Now it's running fine and supporting more and more
users with no real ceiling reached as of yet, and that
was the only change we implemented once we knew the
database was our bottlekneck. We had to make some
changes in our overall code obviously, but it was
pretty easy as we had already implemented a set of
Objects which were responsible for database access
(the norm) instead of having anything scattered into
other objects. We called this layer the DAO (Data
Access Objects) layer, and they are called other
things by different people.
You'll know best whether this would help you or not,
but don't let the need to change a little code deter
you as getting around database bottleknecks without
being a little inventive can cost you hundreds of
thousands if not millions by trying to get around them
with more clustering and hardware schemes until you
know what the real issue is. My guess: the person who
told you his issue was locks is giving you the best
advice.
Hope some of it helps,
Wade
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
RE: Recommended Specs for Oracle 10g db server
Posted by Mohan2005 <mo...@roomsnet.com>.
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: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
RE: Recommended Specs for Oracle 10g db server
Posted by Nick Havard <ni...@nickhavard.com>.
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:nick@nickhavardxyz.com
P.S. Remove xyz to reply direct.
-----Original Message-----
From: users-return-142971-nick=nickhavard.com@tomcat.apache.org
[mailto:users-return-142971-nick=nickhavard.com@tomcat.apache.org] 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: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org