maria-discuss team mailing list archive

[Roberto Spadim <roberto@xxxxxxxxxxxxx>]

i think the problem is select for update (i tryed with myisam/aria but
they was using lock tables to 'solve' problems, but it didn't worked,
obvious too much small updates/deleted and big reads is a problem with
myisam/aria), i will check again what to do

i didn't tested tokudb yet, any experience is wellcome here, does it
'works' like innodb with many small writes and big reads? (i will
check it with some days, i will try to log all queries and execute the
same workload at another server - my laptop and check what happens)

2015-07-03 20:17 GMT-03:00 Justin Swanhart <greenlion@xxxxxxxxx>:
> Hi,
>
> You can't create a slave of the master itself, because you can't replicate
> from/to same server_id.
>
> Before you consider other solutions, you should look into why InnoDB is
> locking.  InnoDB locks are held during INSERT/UPDATE/DELETE operations and
> during SELECT .. FOR UPDATE or SELECT .. LOCK IN SHARE MODE.  If you are
> getting locking issues it is because multiple writers are writing to the
> same rows, or you are using SELECT with a locking option.
>
> If you are doing updates or deletes, make sure the table is well indexed for
> the operation.  InnoDB will lock all rows it traverses, so if an
> update/delete needs a FTS, whole table is locked.  You can somewhat mitigate
> this problem with READ COMMITTED, as it will release the locks that are not
> needed after the scan finishes, but you will still lock many (or all) rows
> during the scan.
>
> FlexCDC has an SBR replication mechanism in trunk/ (it is in
> consumer/replication I think).  I haven't tested it in awhile but SBR
> doesn't really change.  I can't remember if it supports filtering only a
> specific table though.  I wrote it four or five years ago so my memory is
> fuzzy.  I can trivially add that though if it missing.
>
> Or just use a Flexviews materialized view:
>
> call flexviews.create_mvlog('schema', 'A');
> call flexviews.create('schema','B', 'INCREMENTAL');
> set @mvid := LAST_INSERT_ID();
> call flexviews.add_expr(@mvid,'COLUMN','*','all_columns');
> call flexviews.add_table(@mvid, 'schema', 'A', 'A',NULL);
> call flexviews.enable(@mvid);
>
>
> Or use mysqlbinlog + php/python/perl to extract statements for table A, and
> rewrite the statements to table B, and apply them.  Use SBR for this,
> because otherwise the complexity is same as FlexCDC and you should just use
> materialized view.
>
> --Justin
>
>
>
> On Fri, Jul 3, 2015 at 3:23 PM, Stephane VAROQUI <stephane@xxxxxxxxxxx>
> wrote:
>>
>> Flexview can probably help with  tables  denormalization that's mainly use
>> to avoid joins on disks
>>
>> About writes
>>
>>
>> To keep write performance you need to have table size of all inserted
>> tables in memory ? so usage flexview for that help only if you have table A
>> insert heavy purged but at the same time table B would need to have less
>> indexes  and will still get same memory issues  and instead of doing single
>> write you would need 2 writes A and B ?
>> Partitioning have been created for this purpose so if you can find a
>> partition key that will always insert into the same partition than you can
>> maintain both tables in memory .
>>
>> About reads .
>>
>> 1 - if you can put all data in memory  you will be able to get 800K reads
>> per sec . if your count , group by , etc parse more than 1M like 32 Million
>> it will still do 50s  to get you a result .
>>
>> Need a lot of slaves (cores) to satisfy 100 qps /s. that possible we have
>> clients or users having Kilo servers to satisfy such requirements
>>  ( maxscale can help keeping those slaves in sync when you have so many)
>>
>> 2- If you wan't to reduce the amount of servers needed for big data
>> computing or you can't offer to put all data in memory and also can't wait
>> those 32 sec you can use a column based storage, those tools are dedicated
>> for processing multi millions data reads /s  at the price of asynchronous
>> ETL loading
>> You can try out InfiniDB.  documentation and binaries can be found from
>> the MariaDB Portal
>>
>> 3 - Now if you think that you don't really need to read so many records
>> like around a Million but that you can't afford to get memory , it's
>> interesting to use TokuDB as it will parse less record per second in memory
>> vs innodb butwill use a lot lot less disks io compare to innodb or myisam.
>> With heavy compression and fractal tree it is a drastic io reduction.
>>
>> 4- Other solution to keep memory low is using a good flash storage like
>> fusion IO , it can produce 128K reads IO per sec using1/5 in memory  that
>> would give you still the million parse in less than few seconds.
>>
>> 5 - If you have access to many nodes to compute the same request , you can
>> think spider that can split every partition into a separate server . You
>> will end up having data back in memory and spider can help you consolidate
>> all results performed on each server , with 32 nodes you can probably get
>> job done on all nodes adding the time to consolidate. This would be possible
>> on basic query plan like group and sum of a single table . but it become
>> more interesting if you need a million record count that end up into a
>> single partition  where data stay in the memory on the destination server .
>>
>> Hope it helps
>>
>> Stephane.
>>
>>
>> Stéphane Varoqui, Senior Consultant
>>
>> Phone: +33 695-926-401, skype: svaroqui
>> http://www.mariadb.com
>>
>> Le Jul 3, 2015 à 11:40 PM, Roberto Spadim a écrit :
>>
>> the main table have 29M rows (39GB, servers are "small" and old
>> (2008), 16GB ram, 8core dual-cpu xeon, raid 10 hd with 4 sata disks
>> 250gb each, 4 1Gbps network card )
>> 20+ process updating, deleting, inserting (oltp, 1000+ qps, i think it
>> will not grow with time)
>> 5+ process running olap with big selects/group/order/min/max/sum/count
>> etc (some queries take more than 5 minutes to end, it's not a problem
>> to user, but a problem when use oltp+olap apps at same table)
>>
>> today using replication to another server or mysqld process at same
>> machine i don't have problem, olap and oltp runs nice with different
>> mysqld process
>> but when i try oltp + olap at same table at same mysqld, locks begin a
>> problem, i was thinking about creating a replication at same server
>> but to different table (replicate table a to table b)
>>
>>
>>
>> 2015-07-03 18:28 GMT-03:00 Stephane VAROQUI <stephane@xxxxxxxxxxx>:
>>
>> Hi,
>>
>>
>> How many writes do you have ? How much do you plan ?
>>
>> When you read how many records and what is your business case  ?
>>
>>
>> /stephane
>>
>>
>> Stéphane Varoqui, Senior Consultant
>>
>>
>> Phone: +33 695-926-401, skype: svaroqui
>>
>> http://www.mariadb.com
>>
>>
>> Le Jul 3, 2015 à 11:23 PM, Roberto Spadim a écrit :
>>
>>
>> i didn't tested but spider have HA/federate, maybe i could use it?
>>
>>
>> 2015-07-03 18:00 GMT-03:00 Federico Razzoli <federico_raz@xxxxxxxx>:
>>
>>
>> I'm not sure. Flexviews should be a good solution.
>>
>>
>>
>> Or you can used triggers to "replicate" the table instantly.
>>
>>
>>
>> I don't understand your idea with SPIDER, how will it help you?
>>
>>
>>
>> Regards
>>
>>
>> Federico
>>
>>
>>
>>
>>
>> --------------------------------------------
>>
>>
>> Ven 3/7/15, Roberto Spadim <roberto@xxxxxxxxxxxxx> ha scritto:
>>
>>
>>
>> Oggetto: [Maria-discuss] doubt - replication at same mysqld process
>>
>>
>> A: "Maria Discuss" <maria-discuss@xxxxxxxxxxxxxxxxxxx>
>>
>>
>> Data: Venerdì 3 luglio 2015, 22:23
>>
>>
>>
>> hi guys, i have a doubt about
>>
>>
>> replciation on same machine, i never did
>>
>>
>> this before
>>
>>
>>
>>
>> i have a table running many writes and few reads, and
>>
>>
>> another process
>>
>>
>> start reading a lot, my today solution is replication on two
>>
>>
>> servers
>>
>>
>> (on same machine or other machine), the point is... could i
>>
>>
>> replicate
>>
>>
>> in same server (with only one server running / only one
>>
>>
>> mysqld
>>
>>
>> process) ?
>>
>>
>>
>> something like change table A, and a background process
>>
>>
>> replicate to
>>
>>
>> table B? "many writes" will write at table A, reads will
>>
>>
>> read table B
>>
>>
>> (read can be out of sync)
>>
>>
>>
>> innodb is locking a lot of rows, and myisam/aria is locking
>>
>>
>> table a
>>
>>
>> lot, both engines i have problem with lock, i consider
>>
>>
>> replication to
>>
>>
>> another mysqld process as the only solution, but i'm
>>
>>
>> considering
>>
>>
>> running only one mysqld process (if possible)
>>
>>
>> i was thinking about something like HA in spider, but i
>>
>>
>> didn't tested,
>>
>>
>> maybe with flexviewcdc i could have a materialized view "B"
>>
>>
>> of table
>>
>>
>> A?
>>
>>
>>
>> any idea/help is wellcome
>>
>>
>>
>> --
>>
>>
>> Roberto Spadim
>>
>>
>>
>> _______________________________________________
>>
>>
>> Mailing list: https://launchpad.net/~maria-discuss
>>
>>
>> Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
>>
>>
>> Unsubscribe : https://launchpad.net/~maria-discuss
>>
>>
>> More help   : https://help.launchpad.net/ListHelp
>>
>>
>>
>>
>>
>>
>> --
>>
>> Roberto Spadim
>>
>> SPAEmpresarial - Software ERP
>>
>> Eng. Automação e Controle
>>
>>
>> _______________________________________________
>>
>> Mailing list: https://launchpad.net/~maria-discuss
>>
>> Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
>>
>> Unsubscribe : https://launchpad.net/~maria-discuss
>>
>> More help   : https://help.launchpad.net/ListHelp
>>
>>
>>
>>
>>
>>
>> --
>> Roberto Spadim
>> SPAEmpresarial - Software ERP
>> Eng. Automação e Controle
>>
>>
>>
>> _______________________________________________
>> Mailing list: https://launchpad.net/~maria-discuss
>> Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
>> Unsubscribe : https://launchpad.net/~maria-discuss
>> More help   : https://help.launchpad.net/ListHelp
>>
>



-- 
Roberto Spadim
SPAEmpresarial - Software ERP
Eng. Automação e Controle