Hi, Justin points are corrects but TokuDB perform better for OLAP if you set all your indexed to clustered. We have many satisfy users on TokuDB and it's a mature storage.
POC well Stéphane Varoqui, Senior Consultant Phone: +33 695-926-401, skype: svaroqui http://www.mariadb.com Le Jul 5, 2015 à 4:12 AM, Justin Swanhart a écrit : > Hi, > > TokuDB works best when the dataset is too large for memory and reads and > writes are small (ie, it doesn't excel at OLAP, but is good for OLTP). If > all data fits in memory, then it performs up to 3x worse than InnoDB. Both > have row level locking, though I don't know how TokuDB takes locks if you > have to scan a table. For MVCC repeatable-read I assume it has to behave > like InnoDB repeatable-read, but I don't know for sure. You could ask about > TokuDB on the Percona forums. > > If you are read head, why do you need select for update? At least do lock in > share mode, then when you write, you will upgrade to X lock, with small > chance of deadlock, but you will get better concurrency, significantly better. > > --Justin > > Regards, > > --Justin > > On Sat, Jul 4, 2015 at 5:36 PM, Roberto Spadim <[email protected]> wrote: > 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 <[email protected]>: > > 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 <[email protected]> > > 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 <[email protected]>: > >> > >> 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 <[email protected]>: > >> > >> > >> 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 <[email protected]> ha scritto: > >> > >> > >> > >> Oggetto: [Maria-discuss] doubt - replication at same mysqld process > >> > >> > >> A: "Maria Discuss" <[email protected]> > >> > >> > >> 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 : [email protected] > >> > >> > >> 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 : [email protected] > >> > >> 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 : [email protected] > >> 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 : [email protected] Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
