maria-discuss team mailing list archive

[Roberto Spadim <roberto@xxxxxxxxxxxxx>]

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 <stephane@xxxxxxxxxxx>:

> 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 <roberto@xxxxxxxxxxxxx>
> 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 <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
>>
>
>
>


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