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

[Slawomir K. <sa...@sa...>]

Hi Emmanuel,
Yesterday I've lost my test environment on VMware so I've decided to move it
to independent machines. I was suspecting that network connectivity on my
virtual machines could cause behaviour described in last message.But now I'm
sure that low level(on operating system) network configuration of my
computers has nothing to do with my problems.
Currently my test environment is:
2 laptops, both 4 GB of RAM and >2GHz dual core CPU's.
Distribution changed to Ubuntu(32bit) and PostgreSQL 8.3, Java: 32bit
1.6.0_13. Sequoia version didn't change.
Configuration is the same but I've changed values You proposed.
Computers are connected through 100Mbits switch, I've measured connection.
It's about 10MB/s on TCP and UDP.

>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?
Table I'm testing on has 3 columns, integer, integer and varchar(128).
My test consist of 4500 inserts filling this table. Without Sequoia my
postgres handles this insert about 0.3 second.. When sequoia is used 6-10
minutes.
Those are only test queries, target queries will consist of few integers and
one bytea(about 1kB).

If we talk about memory consumption part I mean values shown in JConsole
connected to Sequoia controller node.

>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
What You mean directly on Postgres?

>3. Run JConsole on a single Sequoia controller (without group
>communication, remove the Distribution element)
After removing Distribution element on one node, and shutdown second one
performance increases.
Performing query I've described before takes about 8 seconds. Almost 600
queries per second.

>4. Run JConsole on a single Sequoia controller with group communication
>enabled (re-introduce the Distribution element in your virtual database
>config file)
When only one node is running and distribution element is present
performance is the same as when both nodes are running and enabled. 6-10
minutes 4500 simple insert queries.


-----------------CUT---------------------------- ;)
Hi Slawomir,

Use the new sequoiadb-discuss@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: manu@fr...
Skype: emmanuel_cecchet