Re: [Sequoiadb-discuss] [Sequoia] Performance issues with Sequoia and postgresql

[Emmanuel C. <ma...@fr...>]

Hi Slawomir,

Use the new seq...@li... mailing list, the 
mailing list at continuent.org is being discontinued.

> We are currently trying to cluster our database.
> I'm testing solution based on Sequoia, but performance is very poor.
> About 8 database insert per second. We need reach at least 100/150 
> inserts per second.
You should be able to achieve 800 to 1000 inserts/sec without any fancy 
hardware. There is certainly a configuration problem somewhere in your 
stack.
> We are using PostgreSQL  8.1.11 on CentOS 5.2 on 32 bit Sun JDK and 
> Sequoia 2.10.10 on our test environment. Both test servers are inside 
> vmware server and have assigned 2 cores of Intel Xeon 3.06GHz CPU's 
> and 2 GB RAM each. Servers have very low load.
This version of Postgres is quite dated now but I think that is what 
ships with CentOS 5.2.
> Server are named node0 and node1. Each server consist of PostgreSQL 
> database and Sequoia controller.
> Database replication is RAIDb1.
> Most of configuration details are left default from Sequoia install. 
> As I can see default configuration uses JGroups communication.
> Each node has assigned in /etc/hosts static hostnames.
> When I run any insert query on JConsole attached to each node I see 
> little peak, about 5-10% of CPU but nothing more.
> Memory usage is mostly constant, when I run query heap size grows upto 
> 90MB but when query finish sequoia uses only ~20MB of Heap
I am not sure I understand the memory consumption part, is that for a 
single query or for all queries in your workload? Unless you are 
inserting a big BLOB, there is no reason that so much memory is used.
Could you give an example of an insert you are performing in the database?

The low CPU percentage is probably due to network configuration problems.
Here are some things to test:
1. Run JConsole in the same VM as Postgres and measure insert 
performance directly on Postgres
2. Run JConsole in a different VM and measure insert performance 
directly on Postgres in the other VM
3. Run JConsole on a single Sequoia controller (without group 
communication, remove the Distribution element)
4. Run JConsole on a single Sequoia controller with group communication 
enabled (re-introduce the Distribution element in your virtual database 
config file)

Usually UDP multicast does not perform very well with virtual machines. 
You should try a JGroups configuration based on TCP instead, you might 
see a significant performance improvement.

> Here's my controller.xml
>
> <SEQUOIA-CONTROLLER>
>         <Controller ipAddress="node1" port="54321">
>         <JmxSettings>
>                 <RmiJmxAdaptor port="1090"/>
>         </JmxSettings>
>         <VirtualDatabase configFile="postgres-raidb1-distribution-1.xml"
>                 virtualDatabaseName="counter" autoEnableBackends="true"/>
>         </Controller>
> </SEQUOIA-CONTROLLER>
>
> And here is my VD configuration
> <SEQUOIA>
>         <VirtualDatabase name="counter">
>                 <Distribution >
>                         <MessageTimeouts />
>                 </Distribution>
>                 <Backup>
>                         <Backuper backuperName="pgdump"
>                                 
> className="org.continuent.sequoia.controller.backup.backupers.PostgreSQLBinaryBackuper" 
> />
>                 </Backup>
>
>                 <AuthenticationManager>
>                         <Admin>
>                                 <User username="admin" password="xxx" />
>                         </Admin>
>                         <VirtualUsers>
>                                 <VirtualLogin vLogin="user" 
> vPassword="yyyy" />
>                         </VirtualUsers>
>                 </AuthenticationManager>
>
>                 <DatabaseBackend name="node1" 
> driver="org.postgresql.Driver"
>                         url="jdbc:postgresql://node1/counter"
>                         connectionTestStatement="select now()">
>                         <DatabaseSchema dynamicPrecision="all" 
> gatherSystemTables="true">
>                                 <DefaultStoredProcedureSemantic
>                                    hasSelect="true" hasInsert="true"
>                                    hasUpdate="true" hasDelete="true"
>                                    hasDDL="false"   hasTransaction="false"
>                                    isCausallyDependent="true" 
> isCommutative="false" />
>                                 <StoredProcedureSemantic     
> procedureName="sequoia_rowcounts"
>                                         parameterCount="1" 
> writeTables="true"
>                                         hasSelect="true" hasInsert="true"
>                                         hasUpdate="false" 
> hasDelete="false"
>                                         hasDDL="false"  
> hasTransaction="false"
>                                         isCausallyDependent="true" 
> isCommutative="false" />            
>                         </DatabaseSchema>
>
>                         <ConnectionManager vLogin="user" rLogin="postgres"
>                                 rPassword="">
>                                 <VariablePoolConnectionManager 
> initPoolSize="10"
>                                         minPoolSize="5" 
> maxPoolSize="50" idleTimeout="30" waitTimeout="10" />
>                         </ConnectionManager>
>                 </DatabaseBackend>
>                 <RequestManager>
>                         <RequestScheduler>
>                                 <RAIDb-1Scheduler level="passThrough" />
>                         </RequestScheduler>
>
>                         <RequestCache>
>                                 <MetadataCache />
>                                 <ParsingCache />
I would recommend to remove the ResultCache element below:
>                                 <ResultCache granularity="database" />
>                         </RequestCache>
>
>                         <LoadBalancer>
>                                 <RAIDb-1>
As you have a single database attached to your controller, use 
WaitForCompletion policy="all" instead, this will be faster.
>                                         <WaitForCompletion 
> policy="first" />
>                                         
> <RAIDb-1-LeastPendingRequestsFirst />
>                                 </RAIDb-1>
>                         </LoadBalancer>
>                        <RecoveryLog driver="org.postgresql.Driver"
>                                 url="jdbc:postgresql://node1/recovery" 
> login="sequoia" password="">
>                          <RecoveryLogTable />
>                          <CheckpointTable />
>                          <BackendTable />
>                          <DumpTable />
>                        </RecoveryLog>
>                 </RequestManager>
>         </VirtualDatabase>
> </SEQUOIA>
> sequoia_sequencer.xml [used in jgroups.properties]:
>
> -->
> <config>
>     <UDP mcast_port="45566"
>          mcast_addr="228.8.8.9"
>          tos="16"
>          ucast_recv_buf_size="20000000"
>          ucast_send_buf_size="640000"
>          mcast_recv_buf_size="25000000"
>          mcast_send_buf_size="640000"
>          loopback="false"
>          discard_incompatible_packets="true"
>          max_bundle_size="64000"
>          max_bundle_timeout="30"
>          use_incoming_packet_handler="true"
>          use_outgoing_packet_handler="false"
>          ip_ttl="2"
>          down_thread="false" up_thread="false"
>          enable_bundling="true"/>
>     <PING timeout="2000"
>           down_thread="false" up_thread="false" num_initial_members="3"/>
>     <MERGE2 max_interval="10000"
>             down_thread="false" up_thread="false" min_interval="5000"/>
>     <FD_SOCK down_thread="false" up_thread="false"/>
>     <!--VERIFY_SUSPECT timeout="1500" down_thread="false"/-->
>     <pbcast.NAKACK max_xmit_size="60000"
>                    use_mcast_xmit="false" gc_lag="0"
>                    retransmit_timeout="100,200,300,600,1200,2400,4800"
>                    down_thread="false" up_thread="false"
>                   discard_delivered_msgs="true"/>
>     <UNICAST timeout="300,600,1200,2400,3600"
>              down_thread="false" up_thread="false"/>
>     <pbcast.STABLE stability_delay="1000" desired_avg_gossip="50000"
>                    down_thread="false" up_thread="false"
>                    max_bytes="400000"/>
>     <VIEW_SYNC avg_send_interval="60000" down_thread="false" 
> up_thread="false" />
>     <pbcast.GMS print_local_addr="true" join_timeout="3000"
>                 down_thread="false" up_thread="false"
>                 join_retry_timeout="2000" shun="true" 
> handle_concurrent_startup="true" />
>     <SEQUENCER down_thread="false" up_thread="false" />
>     <FC max_credits="2000000" down_thread="false" up_thread="false"
>            min_threshold="0.10"/>
>     <!-- FRAG2 frag_size="60000" down_thread="false" up_thread="true"/ -->
>     <!-- pbcast.STATE_TRANSFER down_thread="false" up_thread="false"/-->
> </config>
>
>
> What can be wrong. What we can do to increase performance?
Keep us posted with the performance numbers with the different 
configurations proposed above.
> I've been browsing list and I found something about group 
> communication protocol change and use appia but I can't find anything 
> about proper configuration, When I change distribution element of 
> configuration to use hedera_appia.properties I'm getting No Route to 
> host at controller output.
If you use TCP, you will have to hardcode the IP addresses in the 
configuration files or use a gossip server. That might be the issue.

Keep us posted with your progress,
Emmanuel

-- 
Emmanuel Cecchet
FTO @ Frog Thinker 
Open Source Development & Consulting
--
Web: http://www.frogthinker.org
email: ma...@fr...
Skype: emmanuel_cecchet