Hi guys, they noted that server is small and will replace with another one I have a doubt cause i prefer intell, but anyone with hardware experience could help? the question is, considering database workload what you prefer: amd opteron or intel xeon processors?
2015-07-05 5:37 GMT-03:00 Stephane VAROQUI <[email protected]>: > Roberto, > > It also looks like you are very aggressive with select for update. I would > advice to only do select for update in small chunk of primary key (1K to10K > rows). > You can run your big range select query without FOR UPDATE to pick the PK. > Loop for each chunk of PK do a select for update with the same range > condition and update the chunk. OAK toolkit have good python script for > generic chunk queries. > > /stephane > > > 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 >> > > > -- 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
