maria-discuss team mailing list archive

[Stephane VAROQUI <stephane@xxxxxxxxxxx>]

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