Thomas Bruederli thomas@roundcube.net writes:
On Tue, Dec 29, 2015 at 8:30 PM, micah micah@riseup.net wrote:
Micah Anderson micah@riseup.net writes:
Thomas Bruederli thomas-tZJS5fsp0j0qcZcGjlUOXw@public.gmane.org writes:
On Sat, Dec 19, 2015 at 1:30 AM, Micah Anderson micah-sGOZH3hwPm2sTnJN9+BGXg@public.gmane.org wrote:
This should not happen. All INSERT/UPDATE/DELETE queries go to dsnw and also subsequent reads from the same PHP process should use that connection in order to not hit any replication delays. Can you log all SQL queries and compare that with the replication log?
So I turned on the general global query log on the slave (SET global general_log = 1;) and waited for replication to break again. Eventually it failed again and I looked at what queries were done on the replicated slave that were not SELECT queries and found these:
(in case the formatting is impossible to read, you can view these on this pastebin: https://share.riseup.net/#aqcIrGmnJ_2LmC2EtphXCQ)
[...]
As you can see, there are UPDATE and DELETE queries for the 'cache' and 'users' tables (last_login and preferences).
Hmm, that doesn't look good. Can you maybe share your database connection config options with us? Config options 'db_dsnw', 'db_dsnr', 'db_dsnw_noread', 'db_persistent' and 'db_table_dsn' are relevant for this. Replace sensitive information accordingly before posting them here.
These are implementation details, but I'm explaining for clarity: We run tunnels on the machines to connect to the two databases, that means that the db connection happens to the local machine, which then gets tunneled to the actual machine. So below you see the dsnw and dsnr both are on a 127.0.0.x address, the database 'master' is the one on 127.0.0.2, and the read-only slave is at 127.0.0.1, the stunnel configurations are setup to direct connections on each of those ips to the right machines:
$config['db_dsnw'] = "mysql://roundcube:xxx@127.0.0.2:3310/roundcube"; $config['db_dsnr'] = "mysql://roundcube:xxx@127.0.0.1:3310/roundcube"; $config['db_dsnw_noread'] = true; $config['db_persistent'] = false; $config['db_prefix'] = ''; $config['db_table_dsn'] = array( // 'cache' => 'r', // 'cache_index' => 'r', // 'cache_thread' => 'r', // 'cache_messages' => 'r', );
I'll note I also have session storage setup to use memcache, one on each machine, so these are also set (but probably irrelevant):
$config['imap_cache'] = 'memcache'; $config['session_storage'] = 'memcache'; $config['memcache_hosts'] = array('10.0.1.164:11211', '10.0.1.163:11211');
and in php.ini, I've also got the memcache settings:
session.save_path = "tcp://10.0.1.163:11211?persistent=1&weight=1&timeout=1&retry_interval=15,tcp://10.0.1.164:11211?persistent=1&weight=1&timeout=1&retry_interval=15" memcache.hash_strategy = "consistent" memcache.max_failover_attempts = 100 memcache.allow_failover = 1
The decision which connection to use is made in rcube_db::dsn_select() https://github.com/roundcube/roundcubemail/blob/master/program/lib/Roundcube...
This is done for each query and we need to investigate if there's a major issue in this part of the code. Although you're the first to report problems like this...
I have "ROW" based replication setup, instead of the default "STATEMENT", I believe that I did this because the slave couldn't keep up with all the statements that were being done to the cache tables.
I switched this back to STATEMENT based replication to see if this helps.
To work around this, I've done:
REVOKE ALL PRIVILEGES ON
roundcube.* FROM 'roundcube'@'localhost' GRANT SELECT onroundcube.* TO 'roundcube'@'localhost'; flush priviledges;But obviously something is wrong here.
That's not the preferred way to do it and will probably result in database failures due to missing prifileges.
Well the replication slave shouldn't have *any* write priviledges at all (except for the replication itself), so this shouldn't cause database failures because roundcube shouldn't be trying to write there.
You might look into the 'db_table_dsn' config option: https://github.com/roundcube/roundcubemail/blob/master/config/defaults.inc.p...
This was added for exactly this case. You can define 'r' or 'w' connections to be used on a per-table basis.
Thanks for that, although I don't quite understand how this works, the example you linked to seems to be related to the cache table, and it has 'r' set for cache, cache_index, cache_thread and cache_messages... what does this example do? It sets that table read-only on the master? How does this allow configuration on a per-table basis?
I'm still curious about how this config option works, I understand holidaze, etc. just dont want to lose this part in the thread :)
Yeah, sorry for the delay.
No problems, being off the computer and enjoying the holiday and family is more important! :)
Anyway, the 'db_table_dsn' property holds a map of table names and connection identifiers. You can set either 'r' for using the 'db_dsnr' or 'w' for using the 'db_dsnw' connection for all interactions with the according table. This will overrule the default determination whether to use the read or write connection.
The example given in the default config would keep all cache data in the local database. In such a scenario, you would not create these tables on the master and therefore also not replicate them to the slaves.
However, the database_attachments plugin uses the cache table for storing the uploaded attachments. This is something you want to share amongst all nodes and therefore you should not be using this configuration for the 'cache' table.
If I have two machines running roundcube, and they both connect to the same roundcube database on the same server, then they both have access to the cache table, so it is already shared amongst all the nodes involved. There is no reason to replicate this table to the database slave, if everything can be pulled from the master.
If I can configure that, then I can configure mysql to not replicate that table. If mysql doesn't replicate that table, then the replication errors will go away, and the high replication traffic that comes with that would also go away.