2

We are using an Oracle 11G machine that is very powerful; has redundant storage etc. It's a beast from what I have been told.

We just got this DB for a tool that when I first came on as a coop had like 20 people using, now its upwards of 150 people. I am the only one working on it :(

We currently have a system in place that distributes PERL scripts across our entire data center essentially giving us a sort of "grid" computing power.

The Perl scripts run a sort of simulation and report back the results to the database. They do selects / inserts. The load is not very high for each script but it could be happening across 20-50 systems at the same time.

We then have multiple data centers and users all hitting the same database with this same approach.

Our main problem with this is that our database is getting overloaded with connections and having to drop some. We sometimes have upwards of 500 connections. These are old perl scripts and they do not handle this well. Essentially they fail and the results are lost. I would rather avoid having to rewrite a lot of these as they are poorly written, and are a headache to even look at.

The database itself is not overloaded, just the connection overhead is too high. We open a connection, make a quick query and then drop the connection. Very short connections but many of them. The database team has basically said we need to lower the number of connections or they are going to ignore us.

Because this is distributed across our farm we cant implement persistent connections. I do this with our webserver; but its on a fixed system. The other ones are perl scripts that get opened and closed by the distribution tool and thus arent always running.

What would be my best approach to resolving this issue? The scripts themselves can wait for a connection to be open. They do not need to act immediately. Some sort of queing system?

I've been suggested to set up a few instances of a tool called "SQL Relay". Maybe one in each data center. How reliable is this tool? How good is this approach? Would it work for what we need?

We could have one for each data center and relay requests through it to our main database, keeping a pipeline of open persistent connections? Does this make sense?

Is there any other suggestions you can make? Any ideas? Any help would be greatly appreciated.

Sadly I am just a coop student working for a very big company and somehow all of this has landed all on my shoulders (there is literally nobody to ask for help; its a hardware company, everybody is hardware engineers, and the database team is useless and in India) and I am quite lost as what the best approach would be?

I am extremely overworked and this problem is interfering with on going progress and basically needs to be resolved as quickly as possible; preferably without rewriting the whole system, purchasing hardware (not gonna happen), or shooting myself in the foot.

HELP LOL!

2
  • Does this belong on server fault?
    – It Grunt
    Jan 12, 2011 at 1:36
  • Possibly, didnt even know such a thing existed.
    – jreid42
    Jan 12, 2011 at 2:13

4 Answers 4

11

"We open a connection, make a quick query and then drop the connection. Very short connections but many of them."

I'd try with Shared Server connections. Oracle running on a unix box needs a unix process to do the 'work' requested by a session. Conventionally, under a dedicated connection, it forks a new unix process when a session connects and kills it when the session disconnects.

Under Shared Servers, the DBA defines a minimum and maximum number of connections, say 100 and 250. On start up, the database forks out the 100 processes and they sit there waiting for connections. If it gets 150 requests it will fire up the extra 50 processes needed. If it get 300 requests, 50 of them will hang around until one of the 250 (max) processes is available.

Importantly, the processes aren't tied to a specific session for the the life of the session, but only for a specific call (eg an individual insert or update). This does have some effect on memory usage. Anything retained between calls has to be in shared memory (SGA) rather than process memory (PGA). However under 11g, the database can move memory bettwen SGA and PGA so that isn't as big a deal as it used to be.

Read more here

2
  • 3
    +1. Shared Server would be the least intrusive thing to try (as opposed to updating your scripts). But if you cannot get the DBA to set this up for you or allow you to mess with the scripts, then I don't know what they expect you to do... Dropping in some magic middle ware sounds good on paper, but a situation where the DBA or application programmers cannot be bothered (or are unable) to work with you on critical infrastructure issues like this seems to be the real problem.
    – Thilo
    Jan 12, 2011 at 3:01
  • +1 perfect real world situation for shared servers.
    – DCookie
    Jan 12, 2011 at 13:48
0

Have you load tested the system if you increase the number of connections? If you have a separate environment to test this it would be a good thing.

In the short term, if you're able to control WHEN the scripts are executed you may be able to manage it so that you don't need to peg the connections at any given time. Instead, you might be able to drag it out over time. You say the scripts can wait for a connection, I'd say that seems to be the best place to start.

I suppose it's also possible to find performance improvements by finding the queries that take the longest to execute. It might be possible to find improvements by adding an index to a table that might not be indexed.

1
  • Cannot do this, have no direct access to the system. No administration. Also cannot control when the scripts are executed, users all over the place can start at anytime. Different time zones too.
    – jreid42
    Jan 12, 2011 at 2:11
0

You need some data to figure that out. Do you have the Oracle enterprise manager available? It will often tell you exactly what you need to do. Without that you'll need to include what error messages the scripts get and anything that shows up in the alert log at the same time. 500 connections is not a lot in the Oracle world but there are configuration parameters that may need to be increased.

1
  • I have no control over these things. I am told what I am told. It's like wearing a blind fold. I am just curious if thats what the problem is if a tool like SQL Relay would work and if anyone has used it before.
    – jreid42
    Jan 12, 2011 at 2:11
0

Connecting/disconnecting from Oracle is quite expensive and the resource usage is not tracked in normal Oracle statistics views. You can see it to some degree in v$system_time model under connection time, but I've seen cases where this is off by a factor of four. Just to through out an ballpark figure - having a couple of connections a second can easily burn a full core at 100%.

If you have the CPU to burn, it's generally OK, except you are introducing latency. The solution is to use session pooling, i.e. create a set of connections to the database and have a layer of code that manages who uses these connections.

Oracle multi-threaded server is a but of a hack solution from Oracle and delivers less than what one would expect from the name and marketing.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .