Re: [Maria-discuss] Best way to scale writes
Clint Byrum wrote: Excerpts from Jon Foster's message of 2016-10-14 09:33:59 -0700: I have a DB scenario that is very write intensive. Essentially its a large scale hit counter of sorts. Currently we're running on a single 12core server with 6 SSDs in a RAID6 array. But we're looking for a way to scale out write volume by adding more servers [...] Anyhow some pointers would be appreciated. If you're overwhelming one server (you haven't even begun to scale up btw) you will have to shard. However, before you do that.. consider how cheap 24 core servers with 10x FusionIO or similar SSDs would be versus the cost in complexity of sharding. Maybe take a look at Vitess, which helps shard things but keeps all the good stuff you like about MariaDB/MySQL: http://vitess.io/ ___ 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 Thanks, Clint, we are moving towards the 24core option too. Just looking to the future. :-) In fact we're moving the DB onto another box just to determine if the "semaphore freezes" we're experiencing are hardware. The newer box is a dual processor box! I'll look at Vitess. THX - Jon -- Sent from my Debian Linux workstation -- http://www.debian.org/intro/about Jon Foster JF Possibilities, Inc. j...@jfpossibilities.com 541-410-2760 Making computers work for you! ___ 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
Re: [Maria-discuss] Best way to scale writes
I asked about schema and queries to determine which sharding framework makes the most sense to suggest. Sent from my iPhone > On Oct 14, 2016, at 1:05 PM, Clint Byrumwrote: > > Excerpts from Jon Foster's message of 2016-10-14 09:33:59 -0700: >> I have a DB scenario that is very write intensive. Essentially its a >> large scale hit counter of sorts. Currently we're running on a single >> 12core server with 6 SSDs in a RAID6 array. But we're looking for a way >> to scale out write volume by adding more servers, hopefully as >> conveniently as I might add Apache servers to a website. We see two >> servers laboring so we add a third to the mix and so on. >> >> I'm still looking at the various technologies available to me and was >> wondering if someone out there had some suggestions on this front. >> Although Galera Cluster says it scales for write loads too it also says >> that all servers write the same data and make sure its committed by the >> time the transaction is completed. So it seems like write performance >> would never scale because all servers are doing the same write. >> >> Maybe I'm missing something. >> >> Anyhow some pointers would be appreciated. >> > > If you're overwhelming one server (you haven't even begun to scale up > btw) you will have to shard. However, before you do that.. consider how > cheap 24 core servers with 10x FusionIO or similar SSDs would be versus > the cost in complexity of sharding. > > Maybe take a look at Vitess, which helps shard things but keeps all the > good stuff you like about MariaDB/MySQL: > > http://vitess.io/ > > ___ > 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 ___ 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
Re: [Maria-discuss] Best way to scale writes
Excerpts from Jon Foster's message of 2016-10-14 09:33:59 -0700: > I have a DB scenario that is very write intensive. Essentially its a > large scale hit counter of sorts. Currently we're running on a single > 12core server with 6 SSDs in a RAID6 array. But we're looking for a way > to scale out write volume by adding more servers, hopefully as > conveniently as I might add Apache servers to a website. We see two > servers laboring so we add a third to the mix and so on. > > I'm still looking at the various technologies available to me and was > wondering if someone out there had some suggestions on this front. > Although Galera Cluster says it scales for write loads too it also says > that all servers write the same data and make sure its committed by the > time the transaction is completed. So it seems like write performance > would never scale because all servers are doing the same write. > > Maybe I'm missing something. > > Anyhow some pointers would be appreciated. > If you're overwhelming one server (you haven't even begun to scale up btw) you will have to shard. However, before you do that.. consider how cheap 24 core servers with 10x FusionIO or similar SSDs would be versus the cost in complexity of sharding. Maybe take a look at Vitess, which helps shard things but keeps all the good stuff you like about MariaDB/MySQL: http://vitess.io/ ___ 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
Re: [Maria-discuss] Best way to scale writes
Can you describe your schema and typical queries please? Sent from my iPhone > On Oct 14, 2016, at 12:33 PM, Jon Fosterwrote: > > I have a DB scenario that is very write intensive. Essentially its a > large scale hit counter of sorts. Currently we're running on a single > 12core server with 6 SSDs in a RAID6 array. But we're looking for a way > to scale out write volume by adding more servers, hopefully as > conveniently as I might add Apache servers to a website. We see two > servers laboring so we add a third to the mix and so on. > > I'm still looking at the various technologies available to me and was > wondering if someone out there had some suggestions on this front. > Although Galera Cluster says it scales for write loads too it also says > that all servers write the same data and make sure its committed by the > time the transaction is completed. So it seems like write performance > would never scale because all servers are doing the same write. > > Maybe I'm missing something. > > Anyhow some pointers would be appreciated. > > TIA - Jon > > -- > Sent from my Debian Linux workstation -- http://www.debian.org/intro/about > > Jon Foster > JF Possibilities, Inc. > j...@jfpossibilities.com > 541-410-2760 > Making computers work for you! > > > ___ > 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 ___ 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
[Maria-discuss] Best way to scale writes
I have a DB scenario that is very write intensive. Essentially its a large scale hit counter of sorts. Currently we're running on a single 12core server with 6 SSDs in a RAID6 array. But we're looking for a way to scale out write volume by adding more servers, hopefully as conveniently as I might add Apache servers to a website. We see two servers laboring so we add a third to the mix and so on. I'm still looking at the various technologies available to me and was wondering if someone out there had some suggestions on this front. Although Galera Cluster says it scales for write loads too it also says that all servers write the same data and make sure its committed by the time the transaction is completed. So it seems like write performance would never scale because all servers are doing the same write. Maybe I'm missing something. Anyhow some pointers would be appreciated. TIA - Jon -- Sent from my Debian Linux workstation -- http://www.debian.org/intro/about Jon Foster JF Possibilities, Inc. j...@jfpossibilities.com 541-410-2760 Making computers work for you! ___ 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
Re: [Maria-discuss] Reg replication and commit
Karthick: You should post this in maxscale group as well. Dipti On Fri, Oct 14, 2016 at 6:34 PM, Kristian Nielsenwrote: > Karthick Subramanian writes: > > > Below when I try at Slave DB: > > > > MariaDB [dr_repl]> select * from test_dr_repl; > > Empty set (0.00 sec) > > > > MariaDB [dr_repl]> commit; > > Query OK, 0 rows affected (0.00 sec) > > > > MariaDB [dr_repl]> select * from test_dr_repl; > > ++--+ > > | id | val | > > ++--+ > > | 1 |1 | > > | 2 |2 | > > | 3 |3 | > > ++--+ > > 3 rows in set (0.00 sec) > > I wasn't really able to fully understand your explanation of your problem. > > However, the above suggests you have an open transaction with isolation > level REPEATABLE READ. This is the only situation I can think of where a > COMMIT will affect the visibility of other rows. When you open a > transaction > with REPEATABLE READ (with BEGIN, or with autocommit off), no new changes > will be visible until COMMIT or ROLLBACK. This is a basic feature of InnoDB > transactions, independent of replication. > > - Kristian. > > ___ > 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 > ___ 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
Re: [Maria-discuss] MariaDB Server 10.3 notes
i read opnions about why choose mysql, and the common point is 'mysql runs simple queries really fast' i think that's the main feature of mysql 2016-10-14 11:25 GMT-03:00 Reindl Harald: > > > Am 14.10.2016 um 15:21 schrieb Richard Bensley: > >> Me and many others had hope that TokuDB would be the death of InnoDB in >> MariaDB, and the catalyst to a truly amazing open source database. >> > > please fix that "many others" above by saying "some others" - if we would > like a "truly amazing open source database" with is not a mysql dropin > replacement we would just use postgresql - the "dead of innodb and myisam > in mariadb" would be a clear reason to migrate away at all > > > > ___ > 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
Re: [Maria-discuss] MariaDB Server 10.3 notes
Am 14.10.2016 um 15:21 schrieb Richard Bensley: Me and many others had hope that TokuDB would be the death of InnoDB in MariaDB, and the catalyst to a truly amazing open source database. please fix that "many others" above by saying "some others" - if we would like a "truly amazing open source database" with is not a mysql dropin replacement we would just use postgresql - the "dead of innodb and myisam in mariadb" would be a clear reason to migrate away at all ___ 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
Re: [Maria-discuss] MariaDB Server 10.3 notes
> > On Friday, 14 October 2016, Roberto Spadimwrote: > >> I don't think (most) guys must migrate from other RDBM, normally they >> select mysql/mariadb/webscale/etcetc before starting the project, a >> migration "in the middle" of a project is costly. >> I agree, there's many open issue at jira/mysql bug report/percona/etc/etc >> and others issue tracker systems that being completed make users migrate to >> mysql >> >> > For years Roberto, that's all I did. From oracle, MySQL, MSSQL, access and > sybase to mariadb for ecommerce and finance. > nice, but are these companies big? mid? small? normally what i see (i'm in brazil) is only big/mid companies doing this > Connect, gtid, toku, and multi source rep are killer features. > this i must agree with you, reduce the costs aggressively, i used many times -- 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
Re: [Maria-discuss] Reg replication and commit
Karthick Subramanianwrites: > Below when I try at Slave DB: > > MariaDB [dr_repl]> select * from test_dr_repl; > Empty set (0.00 sec) > > MariaDB [dr_repl]> commit; > Query OK, 0 rows affected (0.00 sec) > > MariaDB [dr_repl]> select * from test_dr_repl; > ++--+ > | id | val | > ++--+ > | 1 |1 | > | 2 |2 | > | 3 |3 | > ++--+ > 3 rows in set (0.00 sec) I wasn't really able to fully understand your explanation of your problem. However, the above suggests you have an open transaction with isolation level REPEATABLE READ. This is the only situation I can think of where a COMMIT will affect the visibility of other rows. When you open a transaction with REPEATABLE READ (with BEGIN, or with autocommit off), no new changes will be visible until COMMIT or ROLLBACK. This is a basic feature of InnoDB transactions, independent of replication. - Kristian. ___ 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
Re: [Maria-discuss] MariaDB Server 10.3 notes
I don't think (most) guys must migrate from other RDBM, normally they select mysql/mariadb/webscale/etcetc before starting the project, a migration "in the middle" of a project is costly. I agree, there's many open issue at jira/mysql bug report/percona/etc/etc and others issue tracker systems that being completed make users migrate to mysql 2016-10-14 10:21 GMT-03:00 Richard Bensley: > Is anyone surprised by the lack of TokuDB support? Percona is primarily an > Oracle MySQL fork . The InnoDB changes in MySQL 8 represent something > entirely different. Good, but different. > > I don't think I am being too speculative when I say, I am sure Oracle are > in no hurry to make MySQL a serious contender to Oracle just yet! But > MariaDB could. > > Me and many others had hope that TokuDB would be the death of InnoDB in > MariaDB, and the catalyst to a truly amazing open source database. > > RocksDB can still come into play later, but it's not going to help users > migrate from other RDBM systems, fix existing "missing" features from > MariaDB, nor is it anything I don't think, existing TokuDB users care > about. > > Richard > > ___ > 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
Re: [Maria-discuss] MariaDB Server 10.3 notes
Is anyone surprised by the lack of TokuDB support? Percona is primarily an Oracle MySQL fork . The InnoDB changes in MySQL 8 represent something entirely different. Good, but different. I don't think I am being too speculative when I say, I am sure Oracle are in no hurry to make MySQL a serious contender to Oracle just yet! But MariaDB could. Me and many others had hope that TokuDB would be the death of InnoDB in MariaDB, and the catalyst to a truly amazing open source database. RocksDB can still come into play later, but it's not going to help users migrate from other RDBM systems, fix existing "missing" features from MariaDB, nor is it anything I don't think, existing TokuDB users care about. Richard ___ 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
Re: [Maria-discuss] Reg replication and commit
I didn't expect that autocommit = OFF will affect the replication feature. I thought replication will be handled irrespective what commit type is enabled in the DB Server. So whenever I promote any new master from a slave, I have to taken care this auto commit accordingly. Is my understanding correct? Without mentioning in my.cnf file, is there a way we can handle this behavior during start of the server, like mysqld --auto-commit=ON/OFF. Thank you. On Fri, Oct 14, 2016 at 5:11 PM, Karthick Subramanian < ksubraman...@paycommerce.com> wrote: > Hi All, > > I have the below topology: > > Master -> Slave (using GTID replication) > > Master -> Maxscale (Binlog Router) > > Maxscale -> DR Site (via binlog router) > > > my.cnf is same across all sites: > > lower-case-table-names=1 > autocommit=0 > > #replication variables > log-bin=binlog > binlog-format=ROW > replicate_annotate_row_events=OFF > binlog_annotate_row_events=OFF > > > I have created a table and I could see on slave DB as well as at DR Site > DB. > When I INSERT records, this is not shown up in Slave DB as well DR Site > DB. I have checked replication status and both are sync with master server > binlog pos. > > But the rows reflected in slave and DR site DB only when we COMMIT on both > Slave as well DR site DB. > > Below when I try at Slave DB: > > MariaDB [dr_repl]> select * from test_dr_repl; > Empty set (0.00 sec) > > MariaDB [dr_repl]> commit; > Query OK, 0 rows affected (0.00 sec) > > MariaDB [dr_repl]> select * from test_dr_repl; > ++--+ > | id | val | > ++--+ > | 1 |1 | > | 2 |2 | > | 3 |3 | > ++--+ > 3 rows in set (0.00 sec) > > I am wondering whether I did any weird mistake. Could anyone please help > me to understand and a solution for this. > > Regards, > Karthick > > ___ 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
[Maria-discuss] Reg replication and commit
Hi All, I have the below topology: Master -> Slave (using GTID replication) Master -> Maxscale (Binlog Router) Maxscale -> DR Site (via binlog router) my.cnf is same across all sites: lower-case-table-names=1 autocommit=0 #replication variables log-bin=binlog binlog-format=ROW replicate_annotate_row_events=OFF binlog_annotate_row_events=OFF I have created a table and I could see on slave DB as well as at DR Site DB. When I INSERT records, this is not shown up in Slave DB as well DR Site DB. I have checked replication status and both are sync with master server binlog pos. But the rows reflected in slave and DR site DB only when we COMMIT on both Slave as well DR site DB. Below when I try at Slave DB: MariaDB [dr_repl]> select * from test_dr_repl; Empty set (0.00 sec) MariaDB [dr_repl]> commit; Query OK, 0 rows affected (0.00 sec) MariaDB [dr_repl]> select * from test_dr_repl; ++--+ | id | val | ++--+ | 1 |1 | | 2 |2 | | 3 |3 | ++--+ 3 rows in set (0.00 sec) I am wondering whether I did any weird mistake. Could anyone please help me to understand and a solution for this. Regards, Karthick ___ 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
Re: [Maria-discuss] MariaDB Server 10.3 notes
Hi all! On 10/14/2016 11:10 AM, jocelyn fournier wrote: Hi! Regarding the TokuDB bugs, they are so far fixed quite quickly by Percona. I want to clarify the source of the question (FUD) about Percona not fixing TokuDB bugs, and hopefully bring it to an end. It apparently comes from me, saying so during the discussion at the meetup. My impression was based on interaction with community bug reporters, which is one of my chores. We ask them to re-file TokuDB bugs to Percona, and then after a while they would tell me, via JIRA or otherwise, that there is no progress on the re-filed ticket. It happened several times at least, and given that TokuDB doesn't come up in our JIRA all that often, it did not seem to be negligible. But then again, it happens to everyone that bugs get stalled, MariaDB is certainly no saint in this regard, either. Besides, I must admit that my statistics is biased, because I only hear from TokuDB reporters again when things don't go well, while success stories might well remain unnoticed by me. So, since involved parties confirm that there is in fact good progress, I readily retrieve my previous statement with due apologies to everyone who I unwillingly upset by it. Regards, Elena https://bugs.launchpad.net/percona-server/+bug/1607300 was investigated and fixed in less than 10 days, and George answered the same day of the bug report. And hopefully, the latest remaining patch from Rich needed to enable optimistic parallel replication will be merged quickly as well ( https://github.com/percona/PerconaFT/pull/360 ). The same is true for the RFR patchs on the MariaDB side, ( https://github.com/MariaDB/server/pull/213 + https://github.com/percona/percona-server/pull/1070 ) and the tokubackup script ( https://jira.mariadb.org/browse/MDEV-8843 ) ;) As for the performance of TokuDB with concurrent read/write queries, from my experience it depends if you try to read immediately the rows which have just been inserted/updated or not. Is this case the messages in the fractal tree have to be propagated and you loose the TokuDB speed insert/update advantage, but they are still a few workload where TokuDB is performing quite well. Jocelyn Fournier Founder M : +33 6 51 21 54 10 https://www.softizy.com Softizy - At your side to Optimize your PHP / MySQL applications Le 14/10/2016 à 09:11, Sergei Golubchik a écrit : Hi, MARK! On Oct 13, MARK CALLAGHAN wrote: On Thu, Oct 13, 2016 at 4:00 PM, Sergei Golubchikwrote: There is development and support for TokuDB. The main reason for dropping it could be RocksDB based engine covering all TokuDB use cases, but better. I mean, *if* RocksDB will be able to do that, then TokuDB won't be needed (it's *if*, not a certainty). Is MariaDB.com providing the development for TokuDB or are you speculating about the commitment to it provided by someone else? If the latter, then it would be better to hear directly from them. If the former, can you be more specific about what MariaDB.com plans to do for TokuDB? I am speculating about the commitment to it provided by Percona. Based on what (I believe) Peter said, and it's in the Colin email too: Peter says that bugs are fixed for customers... and there is ongoing development to make it better but mainly based on the changes I see in TokuDB codebase inside Percona Server: 5.6.32-78.1 - 73 files changed, 4192 insertions(+), 3715 deletions(-) 5.6.31-77.0 - 35 files changed, 153 insertions(+), 79 deletions(-) 5.6.30-76.3 - 77 files changed, 862 insertions(+), 309 deletions(-) 5.6.29-76.2 - 25 files changed, 487 insertions(+), 153 deletions(-) 5.6.28-76.1 - 14 files changed, 997 insertions(+), 182 deletions(-) 5.6.27-76.0 - 206 files changed, 15619 insertions(+), 5969 deletions(-) 5.6.26-74.0 - initial checkin (into mergetrees/tree/merge-tokudb-5.6 repo) I agree that it doesn't prove that they will continue to do so in the future. If MariaDB.com has any plans about TokuDB - I don't know about them. Regards, Sergei Chief Architect MariaDB and secur...@mariadb.org ___ 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 ___ 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 ___ 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