maria-discuss team mailing list archive

[Jan Kirchhoff <j.kirchhoff@xxxxxxxxxxxxxxx>]

Hi everybody,

I installed Galera on 2 servers, imported a database dump (took almost 2
days to import a 100GB gzipped sql-dump ...) and set up one of the two
servers as a replication slave of our main database.

Now as I try to understand all the new variables and want to get an idea
of do's and dont's some questions pop up:

While the traditional replication slave on the first galera-server is
still catching up (still 200000 seconds behind...) I had our main
data-proxy starting to write to that server.
Most of the data coming into our databases is not going its way trough
the replication but via our self-written proxy application. This
application connects to all databases, sets SQL_LOG_BIN=0 and then
starts updating just a few tables (up to 15-20GB of pure sql statements
per hour depending on the time of the day). This works fine, the server
was processing the statements no slower than the other non-galera servers.

I couldn't see the updates on the second galera-server and figured that
was because of the SQL_LOG_BIN=0. Now things got complicated. How to
resolve this? I gave it a try, hacked the proxy not to set SQL_LOG_BIN=0
for that specific server and started it again, but this made the second
server crash almost immidiatly with HA_ERR_KEY_NOT_FOUND - actually  I
was expecting something like that to happen as the tables were out of
sync now.

Is there something like slave_skip_counter, aka "I Know what I do, skip
that update"? I think I have to take a new snapshot to get the second
node up and consistent again. It tried to to that automatically but
stopped because of the hanging rsync-daemon problem. I'll let it catch
up with the replication lag first and then start that again...

If I take a node down or just restart mariadb after changing variables,
it pulls the changes it missed (IST) from another server, right? These
are saved in the galera-cache on all servers? How do I figure out what a
reasonable size for the gcache would be for me? The default size of
128MB is way to small for me if I want to take a node down for a few
hours to do backups/upgrades/whatever, but do I need 5, 10 or 50 GBs?
Some information on number of bytes written to the gcache I could look
at? Or is "wsrep_replicated_bytes" the number to look at?

Finding all documentation and understanding the details is a little bit
difficult and time-consuming, but I'm making my way...

btw: is maria-developers a better place to post this as I am not writing
on production software but alpha releases?

thanks
Jan

Am 28.01.2013 17:18, schrieb Jan Kirchhoff:
> Alex,
> 
> regarding the transaction rates: we currently have a peak at around
> 400-500/sec at certain times of the day. We'll see how that works
> 
> I just found there are no mariadb-galera-debs around for ubuntu 12.10.
> Can we expect them soon? Otherwise I'd have to reinstall the test
> servers with 12.04 - couldn't get the dependency problems resolved when
> trying to get the 12.04-debs into 12.10 and I don't want to compile
> myself :-(
> 
> Jan
> 
> Am 25.01.2013 10:24, schrieb Alex Yurchenko:
>> Hi Jan,
>>
>> 1) the load and the database size you described is nothing special to
>> Galera, we have users running it on bigger databases and with higher
>> transaction rates, Specifically those bulk inserts are perfectly fine.
>>
>> 2) notice that to get most of Galera (e.g. parallel applying) you have
>> to use ROW binlog format at least on all Galera nodes. Using
>> statement-based replication from master should be fine, but inside
>> Galera cluster it should be ROW.
>>
>> 3) your plan looks fine, just don't forget to set log_slave_updates when
>> mixing Galera and native MySQL replication.
>>
>> 4) the latest MariaDB-Galera has support for xtrabackup for new node
>> provisioning.
>>
>> Regards,
>> Alex
>>
>> On 2013-01-24 00:09, Jan Kirchhoff wrote:
>>> Hi,
>>>
>>> I'd like to evaluate mariadb-galera on some test databases. Our load
>>> is at least 90% writes coming from a datastream and I would try to set
>>> up a real-world situation and test with real data.
>>> Most of the work load is "insert ... on duplicate key update"
>>> mass-inserts (actually updates) of about 4MB/10000 rows each. 99% of
>>> these run onto 3-4 tables (each around 10GB/7 million rows of size).
>>> Total database size is around 800 GB.
>>> We have a traditional statement-based master-slave replication, but
>>> all these mass-inserts run over a proxy with SET SQL_LOG_BIN=0 in
>>> parallel on all systems - no way to process this amount of data
>>> single-threaded on the slaves. I was hoping galera would make this
>>> easier and make our proxies obsolete.
>>>
>>> I have some servers I could use for galera as a first step. I wanted
>>> to do this for 4 months now but never got into it, I read your request
>>> to test the RC and think I should start now hoping to be able to give
>>> some valuable feedback:
>>>
>>> After reading through the documentation and some blogs I have some
>>> doubts, but my idea was to do the following:
>>> I'd clone the database of an existing slave to a new machine with
>>> mariadb-galera (name the host "testgalera1" and rsync the database or
>>> use mysqldump). I'd then set up this one as a "normal" statement based
>>> slave connected to one of our masters? Afterwards, the next host
>>> "testgalera2" would have to be added to the galera-cluster? And after
>>> that, I'd set up one of our older database servers and add that to the
>>> cluster, too. My idea was to take an older, slower system to see how
>>> much this slowes the cluster down and get an idea how much headroom
>>> I'd have when I leave those old machines out (the old machines have
>>> only 64GB of RAM and slower CPUs).
>>>
>>> I'd then modify my proxy for all the incoming inserts and add a
>>> writer-thread to one of the hosts in the cluster, that data should be
>>> replicated by galera to all other galera hosts. All other data should
>>> be coming in via the "traditional" replication thread of testgalera1.
>>> This should give me a 95-99% real world write situation on the
>>> cluster.
>>>
>>> Adding a host to a running cluster would mean cloning the whole
>>> database (of course), but this also means the host I copy from would
>>> have to block write access to its tables and buffer all modifications
>>> in memory (at least that's how I read the documentation) during the
>>> copy process. Looking at the database size and amount of inserts, I am
>>> afraid adding a host to the cluster without taking everything down
>>> would be impossible - or even if it works fine now it could cause
>>> trouble once the database gets just a little bit larger or my load
>>> increases a bit.
>>>
>>> before I start with this - is galera an insane idea regarding my
>>> load? Or is it worth a try?
>>>
>>> Jan
>>>
>>> some numbers of some of the old databases:
>>>
>>> innodb_buffer_pool_size between 60-120 GB depending on server memory
>>> configuration.
>>>
>>> db5: Uptime: 8052248  Threads: 44  Questions: 1413881794  Slow
>>> queries: 160049  Opens: 683779  Flush tables: 2  Open tables: 1024
>>> Queries per second avg: 175.588
>>>
>>> db6: Uptime: 8070552  Threads: 6  Questions: 873413218  Slow queries:
>>> 3208  Opens: 336584  Flush tables: 1  Open tables: 578 Queries per
>>> second avg: 108.222
>>>
>>> db7: Uptime: 4175032  Threads: 28  Questions: 639627677  Slow
>>> queries: 139337  Opens: 522673  Flush tables: 1  Open tables: 2016
>>> Queries per second avg: 153.203
>>>
>>> db7# mysql -e 'show global status like "%com\_%" ' |grep -v '\s0$'
>>> Variable_name    Value
>>> Com_admin_commands    289127
>>> Com_alter_table    6269
>>> Com_analyze    725
>>> Com_begin    3712141
>>> Com_change_db    80112
>>> Com_commit    3999309
>>> Com_create_db    1
>>> Com_create_table    253849
>>> Com_create_trigger    10
>>> Com_create_view    68
>>> Com_delete    12365037
>>> Com_delete_multi    7
>>> Com_drop_table    310771
>>> Com_drop_trigger    10
>>> Com_drop_view    65
>>> Com_empty_query    509
>>> Com_flush    48
>>> Com_grant    36
>>> Com_insert    314280963
>>> Com_insert_select    613565
>>> Com_kill    214
>>> Com_lock_tables    85
>>> Com_optimize    1
>>> Com_purge    2320
>>> Com_rename_table    11
>>> Com_repair    1
>>> Com_replace    14240825
>>> Com_replace_select    29920
>>> Com_revoke    8
>>> Com_rollback    13
>>> Com_select    178540996
>>> Com_set_option    6886915
>>> Com_show_binlogs    69960
>>> Com_show_charsets    1542
>>> Com_show_collations    1544
>>> Com_show_create_db    28
>>> Com_show_create_func    195
>>> Com_show_create_table    53282
>>> Com_show_create_trigger    1
>>> Com_show_databases    43584
>>> Com_show_events    3
>>> Com_show_fields    131874
>>> Com_show_function_status    45
>>> Com_show_grants    335
>>> Com_show_keys    2582
>>> Com_show_master_status    142
>>> Com_show_plugins    7224
>>> Com_show_procedure_status    45
>>> Com_show_processlist    116918
>>> Com_show_slave_status    83193
>>> Com_show_status    155732
>>> Com_show_storage_engines    25
>>> Com_show_table_status    7439
>>> Com_show_tables    1973739
>>> Com_show_triggers    538
>>> Com_show_variables    85034
>>> Com_show_warnings    766
>>> Com_stmt_close    363
>>> Com_stmt_execute    363
>>> Com_stmt_prepare    363
>>> Com_truncate    452
>>> Com_unlock_tables    79
>>> Com_update    34496744
>>> Com_update_multi    408212
>>>
>>>
>>> _______________________________________________
>>> 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
>>
> 
> _______________________________________________
> 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
>