Hi Emmanuel, some quick remarks on your questions. I will do some more tests this week and keep you up to date:
Emmanuel Cecchet <[EMAIL PROTECTED]> wrote on 06.08.2007 10:55:31: > Hi Jan, > > I'm running Myosotis in a test environment using it for batch upload > > about 4GB of data into a sequoia mysql cluster with two controllers > > and two backends per controller. I've had massive performance and > > stability problems before just using the sequoia console with the sql > > client feature. Now, using Myosotis it runs like a charm. > > > > Just to give an impression what I'm doing: > > - Receiving preformatted csv-files form an I-series (AS400-DB2) > > containing table data (product/prices/catalogdata/factoryprices/...) > > via sftp > > - Scheduled check for new data in the receiving directory > > - Preparing data files for upload (splitting into manageable parts, > > adding header and footer files) > > - Deleting old data in table (Much faster than an update) by now > > calling mysql-client > > - Inserting new data calling mysql-client (datafile can be up 500 MB big) > > The process is covered by error-checking, backup & retry handling and > > mail alerts in case of failures and takes about 10 minutes for about > > 200MB. > > > > If someone needs more infos or some hints, just ask. > This is a very interesting feedback. As Myosotis is adding another > proxying layer, it is hard for me to understand why it performs better > than the SQL console directly plugged into Sequoia. There is then > certainly a problem with the sql client feature that should work like > mysql-client. The problem I've with the sequoia sql client is as follows: "delete from TABLENAME where true;" aborts on all backends at once with a query interrupted error probably issued from the MySQL-Server. No data is deleted. Same happens on "delete from TABLENAME where 1=1;". On "delete from TABLENAME;" a table not found error is coming up. Which is strange because the table is shown on "show tables;" jdbc:sequoia://192.168.12.80,192.168.12.81/SBDB (sa) > delete from TABLENAME where true; An error occured while executing SQL query (org.continuent.sequoia.common.exceptions.driver.DriverSQLException: Message of cause: write request 8444249 Backend SBDB - BackendWorkerThread for backend 'sbwebsh2-vm1-db1' with RAIDb level:1 failed (Query execution was interrupted Query being executed when exception was thrown: com.mysql.jdbc.ServerPreparedStatement[2] - delete from TABLENAME where true;) Backend SBDB - BackendWorkerThread for backend 'sbwebsh2-vm1-db2' with RAIDb level:1 failed (Query execution was interrupted Query being executed when exception was thrown: com.mysql.jdbc.ServerPreparedStatement[2] - delete from TABLENAME where true;) I traced this down a bit using other statements: jdbc:sequoia://192.168.12.80,192.168.12.81/SBDB (sa) > select count(distinct(ChangeDate)) from dbo_factoryprice where PartNo like "31016%"; +---+ | | +---+ | 3 | +---+ Query executed in 0 s 16 ms . jdbc:sequoia://192.168.12.80,192.168.12.81/SBDB (sa) > select count(distinct(ChangeDate)) from dbo_factoryprice where PartNo like "3101%"; An error occured while executing SQL query (org.continuent.sequoia.common.exceptions.driver.DriverSQLException: Message of cause: Request select count(distinct(ChangeD ate)) from ... failed on backend sbwebsh1-vm1-db1 (Statement cancelled due to timeout or client request Query being executed when exception was thrown: com.mysql.jdbc.ServerPreparedStatement[17] - select count(distinct(ChangeDate)) from dbo_factoryprice where PartNo like "3101%";)) Backends are set up from an initial database via dump backend, restore backend, transfer dump and log, sync log and restore backends on second controller. Timeout values on MySQL are set to high numbers. VDB-Configuration on two controllers: <?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE SEQUOIA PUBLIC "-//Continuent//DTD SEQUOIA 2.10.9//EN" " http://sequoia.continuent.org/dtds/sequoia-2.10.9.dtd";> <SEQUOIA> <VirtualDatabase name="SBDB"> <Distribution hederaPropertiesFile="/hedera_jgroups.properties"> <MessageTimeouts/> </Distribution> <Monitoring> <SQLMonitoring defaultMonitoring="off"> <SQLMonitoringRule queryPattern="^select" caseSensitive="false" applyToSkeleton ="false" monitoring="on"/> </SQLMonitoring> </Monitoring> <Backup> <Backuper backuperName="MySQLBackup" className="org.continuent.sequoia.controller.backup.backupers.MySQLBackuper" options="bindir=E:\Programme\MySQL5.0\Server 5.0.37\bin" /> </Backup> <AuthenticationManager> <Admin> <User username="sa" password="password"/> </Admin> <VirtualUsers> <VirtualLogin vLogin="sa" vPassword="password"/> </VirtualUsers> </AuthenticationManager> <DatabaseBackend name="sbwebsh1-vm1-db1" driver="com.mysql.jdbc.Driver" url="jdbc:mysql://192.168.12.80:3306/sbdb?zeroDateTimeBehavior=convertToNull&autoReconnectForPools=true&dumpQueriesOnException=true" connectionTestStatement="SELECT 1"> <DatabaseSchema dynamicPrecision="table" gatherSystemTables="false" schemaName="sbdb" /> <ConnectionManager vLogin="sa" rLogin="sa" rPassword="password"> <VariablePoolConnectionManager initPoolSize="10" minPoolSize="0" maxPoolSize="50" idleTimeout="86400000" waitTimeout="200000"/> </ConnectionManager> </DatabaseBackend> <DatabaseBackend name="sbwebsh1-vm1-db2" driver="com.mysql.jdbc.Driver" url="jdbc:mysql://192.168.12.81:3307/sbdb?zeroDateTimeBehavior=convertToNull&autoReconnectForPools=true&dumpQueriesOnException=true" connectionTestStatement="SELECT 1"> <DatabaseSchema dynamicPrecision="table" gatherSystemTables="false" schemaName="sbdb" /> <ConnectionManager vLogin="sa" rLogin="sa" rPassword="password"> <VariablePoolConnectionManager initPoolSize="10" minPoolSize="0" maxPoolSize="50" idleTimeout="86400000" waitTimeout="200000"/> </ConnectionManager> </DatabaseBackend> <RequestManager> <RequestScheduler> <RAIDb-1Scheduler level="passThrough"/> </RequestScheduler> <RequestCache> <MetadataCache/> <ParsingCache backgroundParsing="true"/> <ResultCache granularity="table"> <ResultCacheRule queryPattern="default" timestampResolution="1000"> <RelaxedCaching timeout="6000" keepIfNotDirty="true"/> </ResultCacheRule> </ResultCache> </RequestCache> <LoadBalancer> <RAIDb-1> <WaitForCompletion policy="all"/> <RAIDb-1-LeastPendingRequestsFirst/> </RAIDb-1> </LoadBalancer> <RecoveryLog driver="com.mysql.jdbc.Driver" url="jdbc:mysql://192.168.12.80:3306/recovery?autoReconnectForPools=true&dumpQueriesOnException=true" login="sa" password="password"> <RecoveryLogTable vloginColumnType="VARCHAR(50) NOT NULL" sqlColumnName="sqlcmd" sqlColumnType="TEXT NOT NULL" sqlParamColumnType="TEXT" /> <CheckpointTable checkpointNameColumnType="VARCHAR(255) NOT NULL"/> <BackendTable databaseNameColumnType="VARCHAR(255) NOT NULL" backendNameColumnType="VARCHAR(255) NOT NULL" checkpointNameColumnType="VARCHAR(255) NOT NULL"/> <DumpTable dumpNameColumnType="VARCHAR(255) NOT NULL" dumpPathColumnType="VARCHAR(255) NOT NULL" dumpFormatColumnType="VARCHAR(255) NOT NULL" checkpointNameColumnType="VARCHAR(255) NOT NULL" backendNameColumnType="VARCHAR(255) NOT NULL" tablesColumnType="VARCHAR(255) NOT NULL" /> </RecoveryLog> </RequestManager> </VirtualDatabase> </SEQUOIA> When I use Myotosis as proxy server and connect via the original MySQL-Client: E:\Programme\MySQL5.0\Server 5.0.37\bin>mysql -usa -ppassword -P9999 SBDB Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 20 Server version: 5.0.37-myosotis-0.5-BETA MySQL Community Edition (GPL) and then issue: delete from TABLENAME; all data is delete in the table without any problems an also very fast (I will give you some numbers later this week) mysql> delete from dbo_partnomapping; Query OK, 66372 rows affected (9.42 sec) mysql> select * from dbo_partnomapping; Empty set (0.06 sec) All other SQL-statements I mentioned above work without errors. > By the way, did you check that all databases where properly updated and > that mysql-client was well going through Myosotis and not accessing > directly MySQL? As the tables in the DB are deleted first and then refilled with data, I'm sure they are correctly updated. "show backend *" from sequoia console shows continued updates while the upload is running. > Could you provide us with rough performance numbers for sql > client/Sequoia vs mysql-client/Myosotis/Sequoia vs mysql-client/MySQL? I guess once the sequoia sql client executes the statements it will be faster than the mysql/myosotis-solution. > > Thanks again for your feedback, > Emmanuel Maybe this helps on sequoia/myosotis development, Jan-Peter
_______________________________________________ Sequoia mailing list [email protected] https://forge.continuent.org/mailman/listinfo/sequoia
