nice, all emails help a lot guys thanks a lot i will check what to do, or maybe change the hardware (easiest option but a with a financial cost), before i will try to optimize (if possible) any news i will reply here , thanks guys :)
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
