Hi,
On 2016-04-21 06:21, Nathan Scott wrote:
> ----- Original Message -----
>> On 2016-04-15 10:06, Marko Myllynen wrote:
>>> On 2016-04-15 09:15, Marko Myllynen wrote:
>>> [...]
>>> To follow-up our IRC discussion:
>>>
>>>> And finally this:
>>>>
>>>> [Fri Apr 15 09:08:48] pmdaoracle(125624) Error: pmdaFetch: Unavailable
>>>> metric PMID 32.12.4[1]
>>>> [Fri Apr 15 09:08:48] pmdaoracle(125624) Error: pmdaFetch: Unavailable
>>>> metric PMID 32.12.4[3]
>>>> [Fri Apr 15 09:08:48] pmdaoracle(125624) Error: pmdaFetch: Unavailable
>>>> metric PMID 32.12.4[7]
>
> cluster 12 is v$librarycache - but these messages will be lesser issues, I
> think, possibly not related to the fetch timeout.
>
>>>> [Fri Apr 15 09:08:48] pmdaoracle(125624) Error: pmdaFetch: Unavailable
>>>> metric PMID 32.0.73[0]
>>>> [Fri Apr 15 09:08:48] pmdaoracle(125624) Error: pmdaFetch: Unavailable
>>>> metric PMID 32.0.79[0]
>
> And misc. missing v$sysstat metrics - also probably benign at this stage.
>
>>> After "service pmcd restart" (I'm on RHEL 6.8 Beta so HUPing pmcd is not
>>> an option) I see some metrics being available, however there's steady
>>> flow of the above kind of errors printed in the log (with occasional
>>> errors from the line 430 - DBI->connect()).
>
> I was suggesting to try to pinpoint specific problem clusters (each cluster
> has unique SQL statements associated, and its likely one is problematic in
> your Oracle version/setup - e.g. the Intel folk found v$filestat to have
> occasional extreme (multiple minutes long) latencies, depending on various
> factors.
I now had a chance to retest. There was not much other DB load during
my tests.
The patch down below cures the DBI->connect() issues.
But even after that ./Install always fails:
pmcd(89137) Warning: pduread: timeout (after 5.000 sec) while attempting to
read 12 bytes out of 12 in HDR on fd=18
pmcd(89137) Info: CleanupAgent ...
Cleanup "oracle" agent (dom 32): protocol failure for fd=18, exit(1)
After pmcd restart we see numbers like these:
# time pminfo -f oracle > /dev/null
real 0m6.583s
user 0m0.026s
sys 0m0.010s
Memory usage shows up in top(1) output but isn't alarming:
91340 oracle 20 0 378m 158m 12m S 0.0 0.2 0:02.10 perl
CPU usage for the process is ~50% during pminfo -f oracle, for Oracle
itself I didn't see notable change.
Then the most relevant part: for most clusters response times are
somewhere between 0.03 and 0.3 sec but these two stand out:
- oracle.file takes ~1.3s with ~1k rows
- oracle.object_cache takes ~3.2s with ~225k rows
When I tried with sqlplus, it's none of the individual metrics which
would cause this but it seems the access to these tables is slow here.
---
src/pmdas/oracle/connect.pl | 6 +++++-
src/pmdas/oracle/pmdaoracle.1 | 4 ++++
src/pmdas/oracle/pmdaoracle.pl | 4 +++-
3 files changed, 12 insertions(+), 2 deletions(-)
diff --git a/src/pmdas/oracle/connect.pl b/src/pmdas/oracle/connect.pl
index a8057c6..9d21f5b 100755
--- a/src/pmdas/oracle/connect.pl
+++ b/src/pmdas/oracle/connect.pl
@@ -21,6 +21,8 @@ use DBI;
my $os_user = 'oracle';
my $username = 'SYSTEM';
my $password = 'manager';
+my $host = 'localhost';
+my $port = '1521';
my @sids = ( 'master' );
# Configuration files for overriding the above settings
@@ -38,6 +40,8 @@ if (defined($ARGV[0]) && ($ARGV[0] eq '-c' || $ARGV[0] eq
'--config')) {
print("os_user=$os_user\n");
print("username=$username\n");
print("password=$password\n");
+ print("host=$host\n");
+ print("port=$port\n");
# print("path: $ENV{LD_LIBRARY_PATH}\n");
my $sidstr = '';
foreach my $sid (@sids) {
@@ -51,7 +55,7 @@ if (defined($ARGV[0]) && ($ARGV[0] eq '-c' || $ARGV[0] eq
'--config')) {
my $status = 0;
foreach my $sid (@sids) {
print("Attempting Oracle login SID=$sid ... ");
- my $db = DBI->connect("dbi:Oracle:$sid", $username, $password);
+ my $db = DBI->connect("dbi:Oracle:host=$host;port=$port;sid=$sid",
$username, $password);
if (defined($db)) {
$db->disconnect();
print("OK\n");
diff --git a/src/pmdas/oracle/pmdaoracle.1 b/src/pmdas/oracle/pmdaoracle.1
index c77a119..e754f5a 100644
--- a/src/pmdas/oracle/pmdaoracle.1
+++ b/src/pmdas/oracle/pmdaoracle.1
@@ -37,6 +37,10 @@ $username \- username to connect to the database [default:
'SYSTEM']
.IP "\(bu" 4
$password \- password to connect to the database [default: 'manager']
.IP "\(bu" 4
+$host \- host the database is running on [default: 'localhost']
+.IP "\(bu" 4
+$port \- port the database is listening on [default: '1521']
+.IP "\(bu" 4
$os_user \-
operating system username (PMDA will run with the corresponding user id)
[default: oracle]
.IP "\(bu" 4
diff --git a/src/pmdas/oracle/pmdaoracle.pl b/src/pmdas/oracle/pmdaoracle.pl
index e4e0d5c..41345ca 100644
--- a/src/pmdas/oracle/pmdaoracle.pl
+++ b/src/pmdas/oracle/pmdaoracle.pl
@@ -23,6 +23,8 @@ use DBI;
my $os_user = 'oracle';
my $username = 'SYSTEM';
my $password = 'manager';
+my $host = 'localhost';
+my $port = '1521';
my @sids = ( 'master' );
# Configuration files for overriding the above settings
@@ -427,7 +429,7 @@ sub oracle_sid_connection_setup
if ($control_map{$sid} == 1) { return undef; }
if (!defined($dbh)) {
- $dbh = DBI->connect("dbi:Oracle:$sid", $username, $password);
+ $dbh = DBI->connect("dbi:Oracle:host=$host;port=$port;sid=$sid",
$username, $password);
if (defined($dbh)) {
foreach my $key (keys %tables_by_name) {
my ($query, $insts, $fetch);
Thanks,
--
Marko Myllynen
|