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@yahoo.it>:

...

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@spadim.com.br> ha scritto:

Oggetto: [Maria-discuss] doubt - replication at same mysqld process A: "Maria Discuss" <maria-discuss@lists.launchpad.net> 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@lists.launchpad.net 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@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp

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@mariadb.com>:

...

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@yahoo.it>:

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@spadim.com.br> ha scritto:

Oggetto: [Maria-discuss] doubt - replication at same mysqld process

A: "Maria Discuss" <maria-discuss@lists.launchpad.net>

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@lists.launchpad.net

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@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp

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

Hi, Actually SBR thing won't work, you have to rewrite table and it definitely doesn't support that. You can extend the PHP rather easily if you know PHP though. Let me know if I can help further. A snapshot of SHOW ENGINE INNODB STATUS\G would help when you are having locking issues, as well as SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS\G and SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX\G You can also turn on the innodb lock monitor (though if you are using XtraDB that info should be in INNODB STATUS already) --Justin On Fri, Jul 3, 2015 at 4:17 PM, Justin Swanhart <greenlion@gmail.com> wrote:

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@mariadb.com> 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@mariadb.com>:

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@yahoo.it>:

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@spadim.com.br> ha scritto:

Oggetto: [Maria-discuss] doubt - replication at same mysqld process

A: "Maria Discuss" <maria-discuss@lists.launchpad.net>

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@lists.launchpad.net

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@lists.launchpad.net

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@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp

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@gmail.com>:

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@mariadb.com> 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@mariadb.com>:

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@yahoo.it>:

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@spadim.com.br> ha scritto:

Oggetto: [Maria-discuss] doubt - replication at same mysqld process

A: "Maria Discuss" <maria-discuss@lists.launchpad.net>

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@lists.launchpad.net

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@lists.launchpad.net

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@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp

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

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 <roberto@spadim.com.br> 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@gmail.com>:

...

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@mariadb.com> 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@mariadb.com>:

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@yahoo.it>:

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@spadim.com.br> ha scritto:

Oggetto: [Maria-discuss] doubt - replication at same mysqld process

A: "Maria Discuss" <maria-discuss@lists.launchpad.net>

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@lists.launchpad.net

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@lists.launchpad.net

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@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp

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

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@mariadb.com>:

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@spadim.com.br> 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)

...

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@mariadb.com> 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

...

...

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

...

...

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

2015-07-03 20:17 GMT-03:00 Justin Swanhart <greenlion@gmail.com>: table A that 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@mariadb.com>:

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@yahoo.it>:

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@spadim.com.br> ha scritto:

Oggetto: [Maria-discuss] doubt - replication at same mysqld process

A: "Maria Discuss" <maria-discuss@lists.launchpad.net>

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@lists.launchpad.net

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@lists.launchpad.net

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@lists.launchpad.net 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

Hi Frederico, So far i don't see why you would need multiple mysqld on the same server unless you have flash cards doing 128K iops The read scalability now a day of mariadb can get you very far on the same table , and read will not lock records . It looks like a simple better configuration and indexing can get you to the limit of a single server , than it will be time to talk about spider and flexview. Tell us more about your workload, how many read write queries and how many records read per sec (handler or innodb reads status ) and the hardware to you are running . We can give you an idea if such extra tools are needed . /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@yahoo.it>:

...

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@spadim.com.br> ha scritto:

Oggetto: [Maria-discuss] doubt - replication at same mysqld process A: "Maria Discuss" <maria-discuss@lists.launchpad.net> 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@lists.launchpad.net 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@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp