ObjectWeb Consortium
Search ObjectWeb Mail Archive: 

Advanced Search - Powered by Google


Mail Archive Home | c-jdbc List | August 2004 Index

<--  Date Index  --> <--  Thread Index  -->

Re: [c-jdbc] severe problem on production system


Hi Ozcan,

You should use the following connectionTestStatement:
select now()
for each postgresql backend.

You should also increase the pool size, I think 50 is a little bit too low. 100 or 200 as a maximum should be better.

The request cache should be like this:
     <RequestCache>
        <MetadataCache/>
<ParsingCache/> <ResultCache granularity="table" />
     </RequestCache>

You should also turn off monitoring completely ... If you have a huge number of selects requests, it might take some time to check the regexp on each of them.

I'd be interested in knowing the answer to Marc's question, whether the encoding method also appears in your standard application or only when you use C-JDBC.

Thanks for all your interest !

Hope we can help in making C-JDBC faster !

Nicolas,

Özcan Acar wrote:

Hi,

we use postgres 745, we have two backend db servers, each gave 3.5 GB Ram,
SCSCI 5, Linux, Dual 1 GHZ Pentium III

I attached postgres.conf


We user the following configuration:


echo 2021101312 > /proc/sys/kernel/shmmax
ulimit -n 64000
echo 64000 > /proc/sys/fs/file-max
echo 1 > /proc/sys/net/ipv4/icmp_echo_ignore_all
echo 1 > /proc/sys/net/ipv4/icmp_echo_ignore_broadcasts
echo 0 > /proc/sys/net/ipv4/tcp_timestamps
echo 30 > /proc/sys/net/ipv4/tcp_fin_timeout
echo 1800 > /proc/sys/net/ipv4/tcp_keepalive_time
echo 4096 > /proc/sys/net/core/netdev_max_backlog
echo 4096 > /proc/sys/net/ipv4/tcp_max_syn_backlog


I attached alo the virtueldatabases.conf from cjdbc.

I think, if we can do good configuration job with cjbc cache, c-jdbc can
handle this load. 70 % of the queries are selects.

We are very interested in using c-jdbc.
Thank you for your help :)

Özcan Acar




Hi Ozcan,

We need to have a look at your database configuration to see if the one you've specified looks good for the number of users you have. You may also want to switch to the blackdown jvm if you're under linux to improve performances, because after testing it looks as if the sun JVM is less responsive than the blackdown's one. Also, stability has not been a problem in either of them with C-JDBC.

The configuration of the cache, and the parsing of the queries is also important for applications that requires speed over reliability.

What do you mean by the controller crashed? Did the JVM crashed ? Was there a deadlock ? There was no more connections in the pool ?


Also, it would be interesting to know if your database configuration is read or write intensive ? or simply balanced between the two?

Thanks for using C-JDBC !!

Nicolas,

Özcan Acar wrote:

Hi Nicolas,




Hi Ozcan,

You can send an email to c-jdbc-pro@xxxxxxxxxxxxx if you are on a critical business.

Appart from that, can I ask you what os you're running on ?
How many clients ? (Approximately ... 100 ? 200 ?)
What is the JVM you're using ? The version ?
We use Suse LInux 9. Our community has over 200.000 Members, jvm 1.42_05,
apache 139, resin 2.12

At an average there are over 1500 Members in logged in state, in part up
to
2000 members...




Also, after how long the system went in that state ?
one or two



Did you send a particular query ? Did you send a query after a certain amount of time ?
The latest queries can you at the end of this message.




Was the system idle for a long time ?
no




Thanks in advance for helping us finding the problem,
We had to switch to our old solution yesterday and deactivated c-jdbc
driver. It blocks after 2 ours. We had also the case, that the controller
suddenly stoped or crashed yesterday and the clients (resin connection
pool)
had the error message "connection refused".

Overall the whole web application (community software, developed by us)
runs
slower because the sql query take to long to execute. We optimized the
sql
queries.

Maybe is c-jdbc not appropriate for such a huge load on the database?

Thanks for your help.

Özcan Acar
Dip. Inf (FH)
Germany





Nicolas,

Özcan Acar wrote:

Hi,
we use c-jdbc 1.0.1 and encountered a severe problem. c-jdbc stops
working
after awhile. We use postgres 745. ps aux shows on our linux server:

2298 ?        S      0:36 postgres: stats buffer process
2299 ?        S      0:30 postgres: stats collector process
2888 ?        S     42:01 postgres: acar bizimalem 127.0.0.1 idle
4672 ?        S      0:12 postgres: acar bizimalem 127.0.0.1 idle in
transaction
4673 ?        S      0:07 postgres: acar bizimalem 127.0.0.1 idle in
transaction
4674 ?        S      0:02 postgres: acar bizimalem 127.0.0.1 idle in
transaction
4676 ?        S      0:05 postgres: acar bizimalem 127.0.0.1 idle in
transaction
4677 ?        S      0:04 postgres: acar bizimalem 127.0.0.1 idle in
transaction
4681 ?        S      0:00 postgres: acar bizimalem 127.0.0.1 idle in
transaction
4682 ?        S      0:00 postgres: acar bizimalem 127.0.0.1 idle in
transaction
4683 ?        S      0:00 postgres: acar bizimalem 127.0.0.1 idle in
transaction
4684 ?        S      0:00 postgres: acar bizimalem 127.0.0.1 idle in
transaction
4685 ?        S      0:00 postgres: acar bizimalem 127.0.0.1 idle in
transaction
4686 ?        S      0:00 postgres: acar bizimalem 127.0.0.1 idle in
transaction
4687 ?        S      0:00 postgres: acar bizimalem 127.0.0.1 UPDATE
waiting
5404 pts/3    S      0:00 grep postgres



These are the last querys that accepted by c-jdbc


LOG:  statement: select * from community_login_failed where
username='ferhaat32' order by id asc
LOG:  statement: select friend, sex, username   from community_friend
where
friend='albustanli' and ignored=1 order by username asc
LOG:  statement: delete from community_login_failed where id=508449
LOG:  statement: select username, clubid from community_club_member
where
username='albustanli' and approved=1
LOG:  statement: select kontostand from community_konto where
username='albustanli'
LOG:  statement: select * from  nextval ('password_failed_unique_id')
as
result;
LOG:  statement: insert into community_login_failed (id,username, ip,
created) values(508539,'ferhaat32','80.133.157.24','2004-08-28
22:47:29.198')
LOG:  statement: select id,sex,sexwanted, familienstand, geburtsdatum,
stadt, plz, begruessung, username, pic_profil, pic_profil_activated,
pic_profi
l_thumb from community_single  where pic_profil_activated='1' order by
pic_lastupdate_activated desc  limit 20 offset 0
LOG:  statement: select plz from com_plz where plz='58455'
LOG:  statement: select ort from com_plz where plz='58455'
LOG:  statement: update community_account set
hobby='~.}}¯)~.}}¯)~.}}¯)~.}}¯)~.}}¯)~.}}¯)~.}}¯)~.}}¯)~.}}¯)
&#38;gt;&#38;gt;&#38;gt;&#38;gt;&#38;gt;&
#38;gt;&#38;gt; $efkat ve merhamette güne$ gibi ol....
&#38;gt;&#38;gt;&#38;gt;&#38;gt;&#38;gt;&#38;gt;&#38;gt;
(_~.}}(_~.}}(_~.}}(_~.}}(_~.}}(_~.}}(
_~.}}(_~.}}(_~.}
',height='~.}}¯)~.}}¯)',weight='(_~.}}(_~.}}',haircolor='~.}}¯)~.}}¯)',eyecolor='(_~.}}(_~.}}',bornin='~.}}¯)~.}}¯)',burc='(_~.}}(_~
..}}',favoritefilm='~.}}¯)~.}}¯)~.}}¯)~.}}¯)~.}}¯)~.}}¯)~.}}¯)~.}}¯)~.}}¯)
&#38;gt;&#38;gt;&#38;gt;&#38;gt;&#38;gt; Ba$kalarinin kusurunu örtmede
gece
gibi ol.... &#38;gt;&#38;gt;&#38;gt;&#38;gt;
(_~.}}(_~.}}(_~.}}(_~.}}(_~.}}(_~.}}(_~.}}(_~.}}(_~.}
',favoritemusic='~.}}¯)~.}}¯)~.}}¯)~.}}¯)~.}}¯)~.
}}¯)~.}}¯)~.}}¯)~.}}¯)
&#38;gt;&#38;gt;&#38;gt;&#38;gt;&#38;gt;&#38;gt;&#38;gt;&#38;gt;&#38;gt;
Hiddet ve asabiyette ölü gibi ol.... &#38;gt;&#38;gt;
&#38;gt;&#38;gt;&#38;gt;&#38;gt;&#38;gt;&#38;gt;&#38;gt;
(_~.}}(_~.}}(_~.}}(_~.}}(_~.}}(_~.}}(_~.}}(_~.}}(_~.}
',recentlyreadbooks='~.}}¯)~.}}¯)~.}}¯
)~.}}¯)~.}}¯)~.}}¯)~.}}¯)~.}}¯)~.}}¯)
&#38;gt;&#38;gt;&#38;gt;&#38;gt;&#38;gt; Tevazu ve alcakgönüllülükte
toprak
gibi ol.... &#38;gt;&#38;gt;&#38;gt
;&#38;gt;&#38;gt; (_~.}}(_~.}}(_~.}}(_~.}}(_~.}}(_~.}}(_~.}}(_~.}}(_~.}
',favoritesport='~.}}¯)~.}}¯)~.}}¯)~.}}¯)~.}}¯)~.}}¯)~.}}¯)~.}}¯)~.}}¯)
&#38;
gt;&#38;gt;&#38;gt;&#38;gt;&#38;gt;&#38;gt;&#38;gt;&#38;gt;&#38;gt;&#38;gt;&#38;gt;
Ho$görülükte deniz gibi ol.... &#38;gt;&#38;gt;&#38;gt;&#38;gt;&#
38;gt;&#38;gt;&#38;gt;&#38;gt;&#38;gt;&#38;gt;
(_~.}}(_~.}}(_~.}}(_~.}}(_~.}}(_~.}}(_~.}}(_~.}}(_~.}
',favoritevacationspot='~.}}¯)~.}}¯)~.}}¯)~.}}¯)
~.}}¯)~.}}¯)~.}}¯)~.}}¯)~.}}¯)
&#38;gt;&#38;gt;&#38;gt;&#38;gt;&#38;gt;&#38;gt;&#38;gt;&#38;gt;&#38;gt;&#38;gt;&#38;gt;&#38;gt;
Ya oldugun gibi görün
.....
&#38;gt;&#38;gt;&#38;gt;&#38;gt;&#38;gt;&#38;gt;&#38;gt;&#38;gt;&#38;gt;&#38;gt;&#38;gt;&#38;gt;
(_~.}}(_~.}}(_~.}}(_~.}}(_~.}}(_~.}}(_~.}}(_~.}
}(_~.}
',charakter='~.}}¯)~.}}¯)~.}}¯)~.}}¯)~.}}¯)~.}}¯)~.}}¯)~.}}¯)~.}}¯)
&#38;gt;&#38;gt;&#38;gt;&#38;gt;&#38;gt; Cömertlikte yardim etmede akar
su
gibi ol....   &#38;gt;&#38;gt;&#38;gt;&#38;gt;&#38;gt;
(_~.}}(_~.}}(_~.}}(_~.}}(_~.}}(_~.}}(_~.}}(_~.}}(_~.}
',computer='~.}}¯)~.}}¯)~.}}¯)~.}}¯)~.}
}¯)~.}}¯)~.}}¯)~.}}¯)~.}}¯)
&#38;gt;&#38;gt;&#38;gt;&#38;gt;&#38;gt;&#38;gt;&#38;gt;&#38;gt;&#38;gt;&#38;gt;&#38;gt;&#38;gt;
Ya göründügün gibi ol...
..
&#38;gt;&#38;gt;&#38;gt;&#38;gt;&#38;gt;&#38;gt;&#38;gt;&#38;gt;&#38;gt;&#38;gt;&#38;gt;&#38;gt;
(_~.}}(_~.}}(_~.}}(_~.}}(_~.}}(_~.}}(_~.}}(_~.}}(_
~.} ', birthday='1977-08-17', firstname='Arzu', name='Can',
email='mickymaus58@xxxxxx', sex='w', job='~Studentin}',
maritalstatus='',
country='DE', t
own='Witten', zip='58455', chattime='1', premiumsms=1093726031872 where
username='Zehra25'
ERROR:  value too long for type character varying(255)

LOG:  connection received: host=192.168.1.58 port=41391
LOG:  connection authorized: user=acar database=bizimalem
LOG:  statement: set datestyle to 'ISO'; select version(), case when
pg_encoding_to_char(1) = 'SQL_ASCII' then 'UNKNOWN' else
getdatabaseencoding() e
nd;
LOG:  statement: set client_encoding = 'UNICODE'
LOG:  statement: select * from community_account where
username_lowercase='deli-kiz87'
LOG:  statement: select count(*) from com_guestbook where
guestbookowner='dudu-dudu-20' and ablage='0'
LOG:  statement: select * from community_account where
username_lowercase='_sevgi'
LOG:  recycled transaction log file "000000010000008F"
LOG:  recycled transaction log file "0000000100000090"
LOG:  recycled transaction log file "0000000100000091"
LOG:  recycled transaction log file "0000000100000092"
LOG:  connection received: host=192.168.1.198 port=25639
LOG:  incomplete startup packet


Here you can see a thread dump from c-jdbc controller

http://www.bizimalem.com/cjdbc-thread-dump.txt

The file contains the output of the controller, at the end of the file
the
thread  dump, taken with kill -QUIT pid

Can you please help me to isolate the problem. We are on the production
system and this problem affects our business.

Thanks

Özcan Acar






------------------------------------------------------------------------





------------------------------------------------------------------------

# -----------------------------
# PostgreSQL configuration file
# -----------------------------
#
# This file consists of lines of the form:
#
#   name = value
#
# (The '=' is optional.) White space may be used. Comments are introduced
# with '#' anywhere on a line. The complete list of option names and
# allowed values can be found in the PostgreSQL documentation. The
# commented-out settings shown in this file represent the default values.
#
# Any option can also be given as a command line switch to the
# postmaster, e.g. 'postmaster -c log_connections=on'. Some options
# can be changed at run-time with the 'SET' SQL command.
#
# This file is read on postmaster startup and when the postmaster
# receives a SIGHUP. If you edit the file on a running system, you have # to SIGHUP the postmaster for the changes to take effect, or use # "pg_ctl reload".


#---------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#---------------------------------------------------------------------------

# - Connection Settings -

#tcpip_socket = false
max_connections = 256
        # note: increasing max_connections costs about 500 bytes of shared
        # memory per connection slot, in addition to costs from shared_buffers
       # and max_locks_per_transaction.
#superuser_reserved_connections = 2
#port = 5432
#unix_socket_directory = ''
#unix_socket_group = ''
#unix_socket_permissions = 0777 # octal
#virtual_host = ''              # what interface to listen on; defaults to any
#rendezvous_name = ''           # defaults to the computer name

# - Security & Authentication -

#authentication_timeout = 60    # 1-600, in seconds
#ssl = false
#password_encryption = true
#krb_server_keyfile = ''
#db_user_namespace = false


#---------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#---------------------------------------------------------------------------

# - Memory -

shared_buffers = 64000          # min 16, at least max_connections*2, 8KB each
sort_mem = 16384                # min 64, size in KB
vacuum_mem = 16384              # min 1024, size in KB

# - Free Space Map -

#max_fsm_pages = 20000          # min max_fsm_relations*16, 6 bytes each
#max_fsm_relations = 1000       # min 100, ~50 bytes each

# - Kernel Resource Usage -

#max_files_per_process = 1000   # min 25
#preload_libraries = ''


#---------------------------------------------------------------------------
# WRITE AHEAD LOG
#---------------------------------------------------------------------------

# - Settings -

fsync = false                   # turns forced synchronization on or off
#wal_sync_method = fsync        # the default varies across platforms:
                                # fsync, fdatasync, open_sync, or 
open_datasync
#wal_buffers = 8                # min 4, 8KB each

# - Checkpoints -

checkpoint_segments = 32        # in logfile segments, min 1, 16MB each
#checkpoint_timeout = 300       # range 30-3600, in seconds
#checkpoint_warning = 30        # 0 is off, in seconds
#commit_delay = 0               # range 0-100000, in microseconds
#commit_siblings = 5            # range 1-1000


#---------------------------------------------------------------------------
# QUERY TUNING
#---------------------------------------------------------------------------

# - Planner Method Enabling -

#enable_hashagg = true
#enable_hashjoin = true
#enable_indexscan = true
#enable_mergejoin = true
#enable_nestloop = true
#enable_seqscan = true
#enable_sort = true
#enable_tidscan = true

# - Planner Cost Constants -

#effective_cache_size = 1000    # typically 8KB each
#random_page_cost = 4           # units are one sequential page fetch cost
#cpu_tuple_cost = 0.01          # (same)
#cpu_index_tuple_cost = 0.001   # (same)
#cpu_operator_cost = 0.0025     # (same)

# - Genetic Query Optimizer -

#geqo = true
#geqo_threshold = 11
#geqo_effort = 1
#geqo_generations = 0
#geqo_pool_size = 0             # default based on tables in statement,
                                # range 128-1024
#geqo_selection_bias = 2.0      # range 1.5-2.0

# - Other Planner Options -

#default_statistics_target = 10 # range 1-1000
#from_collapse_limit = 8
#join_collapse_limit = 8        # 1 disables collapsing of explicit JOINs


#---------------------------------------------------------------------------
# ERROR REPORTING AND LOGGING
#---------------------------------------------------------------------------

# - Syslog -

syslog = 0                      # range 0-2; 0=stdout; 1=both; 2=syslog
#syslog_facility = 'LOCAL0'
#syslog_ident = 'postgres'

# - When to Log -

#client_min_messages = notice   # Values, in order of decreasing detail:
                                #   debug5, debug4, debug3, debug2, debug1,
                                #   log, info, notice, warning, error

#log_min_messages = notice      # Values, in order of decreasing detail:
                                #   debug5, debug4, debug3, debug2, debug1,
                                #   info, notice, warning, error, log, fatal,
                                #   panic

#log_error_verbosity = default   # terse, default, or verbose messages

#log_min_error_statement = panic # Values in order of increasing severity:
                                 #   debug5, debug4, debug3, debug2, debug1,
                                 #   info, notice, warning, error, panic(off)
#log_min_duration_statement = -1 # Log all statements whose
                                 # execution time exceeds the value, in
                                 # milliseconds.  Zero prints all queries.
                                 # Minus-one disables.

#silent_mode = false             # DO NOT USE without Syslog!

# - What to Log -

#debug_print_parse = false
#debug_print_rewritten = false
#debug_print_plan = false
#debug_pretty_print = false
log_connections = true
#log_duration = false
#log_pid = false
log_statement = true
#log_timestamp = false
#log_hostname = false
#log_source_port = false


#---------------------------------------------------------------------------
# RUNTIME STATISTICS
#---------------------------------------------------------------------------

# - Statistics Monitoring -

#log_parser_stats = false
#log_planner_stats = false
#log_executor_stats = false
#log_statement_stats = false

# - Query/Index Statistics Collector -

stats_start_collector = true
stats_command_string = true
#stats_block_level = false
stats_row_level = true
#stats_reset_on_server_start = true


#---------------------------------------------------------------------------
# CLIENT CONNECTION DEFAULTS
#---------------------------------------------------------------------------

# - Statement Behavior -

#search_path = '$user,public'   # schema names
#check_function_bodies = true
#default_transaction_isolation = 'read committed'
#default_transaction_read_only = false
#statement_timeout = 0          # 0 is disabled, in milliseconds

# - Locale and Formatting -

#datestyle = 'iso, mdy'
#timezone = unknown             # actually, defaults to TZ environment setting
#australian_timezones = false
#extra_float_digits = 0         # min -15, max 2
#client_encoding = sql_ascii    # actually, defaults to database encoding

# These settings are initialized by initdb -- they may be changed
lc_messages = 'de_DE@euro'              # locale for system error message 
strings
lc_monetary = 'de_DE@euro'              # locale for monetary formatting
lc_numeric = 'de_DE@euro'               # locale for number formatting
lc_time = 'de_DE@euro'                  # locale for time formatting

# - Other Defaults -

#explain_pretty_print = true
#dynamic_library_path = '$libdir'
#max_expr_depth = 10000         # min 10


#---------------------------------------------------------------------------
# LOCK MANAGEMENT
#---------------------------------------------------------------------------

deadlock_timeout = 1000 # in milliseconds
#max_locks_per_transaction = 64 # min 10, ~260*max_connections bytes each


#---------------------------------------------------------------------------
# VERSION/PLATFORM COMPATIBILITY
#---------------------------------------------------------------------------

# - Previous Postgres Versions -

#add_missing_from = true
#regex_flavor = advanced        # advanced, extended, or basic
#sql_inheritance = true

# - Other Platforms & Clients -

#transform_null_equals = false
------------------------------------------------------------------------

<?xml version="1.0" encoding="UTF8"?>
<!DOCTYPE C-JDBC PUBLIC "-//ObjectWeb//DTD C-JDBC 1.0rcf//EN" 
"http://c-jdbc.objectweb.org/dtds/c-jdbc-1.0rcf.dtd";>

<C-JDBC>

 <VirtualDatabase name="baDB">

   <Monitoring>
     <SQLMonitoring defaultMonitoring="off">
        <SQLMonitoringRule queryPattern="^select" caseSensitive="false" applyToSkeleton 
="false" monitoring="on"/>
     </SQLMonitoring>
   </Monitoring>

   <AuthenticationManager>
     <Admin>
        <User username="admin" password="admin"/>
</Admin> <VirtualUsers>
       <VirtualLogin vLogin="user" vPassword=""/>
     </VirtualUsers>
   </AuthenticationManager>

   <DatabaseBackend name="localhost" driver="org.postgresql.Driver" 
url="jdbc:postgresql://localhost/bizimalem" connectionTestStatement="select count(*) from 
community_logs">
     <ConnectionManager vLogin="user" rLogin="k" rPassword="">
       <VariablePoolConnectionManager initPoolSize="10" minPoolSize="5" maxPoolSize="50" 
idleTimeout="30" waitTimeout="10"/>
     </ConnectionManager>
   </DatabaseBackend>

<DatabaseBackend name="notebook" driver="org.postgresql.Driver" 
url="jdbc:postgresql://192.168.0.225/bizimalem" connectionTestStatement="select count(*) from 
community_logs">
     <ConnectionManager vLogin="user" rLogin="k" rPassword="">
       <VariablePoolConnectionManager initPoolSize="10" minPoolSize="5" maxPoolSize="50" 
idleTimeout="30" waitTimeout="10"/>
     </ConnectionManager>
   </DatabaseBackend>

<RequestManager>
     <RequestScheduler>
        <RAIDb-1Scheduler level="pessimisticTransaction"/>
     </RequestScheduler>

     <RequestCache>
        <MetadataCache/>
        <ResultCache granularity="table" />
     </RequestCache>

     <LoadBalancer>
        <RAIDb-1>
           <WaitForCompletion policy="first"/>
           <RAIDb-1-LeastPendingRequestsFirst/>
        </RAIDb-1>
     </LoadBalancer>

     <RecoveryLog>
        <JDBCRecoveryLog driver="org.postgresql.Driver" url="jdbc:postgresql://localhost/bizimalem" 
login="k" password="">
           <RecoveryLogTable tableName="RECOVERY" idColumnType="BIGINT NOT NULL" 
sqlColumnType="VARCHAR NOT NULL" extraStatementDefinition=",PRIMARY KEY (id)"/>
           <CheckpointTable tableName="CHECKPOINT"/>
<BackendTable/> </JDBCRecoveryLog>
     </RecoveryLog>
   </RequestManager>

 </VirtualDatabase>

</C-JDBC>




<--  Date Index  --> <--  Thread Index  -->

Reply via email to:

Powered by MHonArc.

Copyright © 1999-2005, ObjectWeb Consortium | contact | webmaster.