Re: Query regarding implementation of parallel-replication
Hi Wagner. That is what I did as the last resort, and that is only what solved the issue. Thanks. On Fri, Sep 5, 2014 at 1:52 AM, wagnerbianchi.com m...@wagnerbianchi.com wrote: You can try these steps: 1-) Stop slave and write down the replication coordinates getting that in MySQL's error log (*very important step*); 2-) Issue the `reset slave` command on MySQL Slave; 3-) Issue the CHANGE MASTER TO considering the replication coordinates you've just written down on step 1; 4-) Give replication a start; 5-) Check if the issue has gone away. If you're not comfortable to do that, just share the SHOW SLAVE STATUS output with us. Let us know how's it going, cheers!! -- Wagner Bianchi, MySQL Database Specialist Mobile: +55.31.8654.9510 E-mail: m...@wagnerbianchi.com Twitter: @wagnerbianchijr 2014-09-04 7:24 GMT-03:00 Ajay Garg ajaygargn...@gmail.com: Hi all. Unfortunately, I have run into the logs, as described at http://bugs.mysql.com/bug.php?id=71495 Unfortunately, the issue does not go away, even after reverting back to slave-parallel-workers=0 in my.cnf, and restarting the mysql instance. Any quick idea, as to how we may get the mysql+replication up and running (even with the plain old non-multi-threaded mode)? On Tue, Sep 2, 2014 at 12:57 PM, Ajay Garg ajaygargn...@gmail.com wrote: Thanks Akshay for the reply. On Tue, Sep 2, 2014 at 12:25 PM, Akshay Suryavanshi akshay.suryavansh...@gmail.com wrote: Hello Ajay, I tried testing the slave-parallel-workers few months ago, what I can surely tell you its still under development, and at that time needed some critical bug fixing. It is helpful in situations where each schema has even workload. The case you mentioned above doesnt have so. DB2 is getting different type of load than the others, in that case the other slave workers should be able to proceed with their workload as opposed to db2 which is still executing the long running statement. Now just imagine what happens if we try to take a backup, what binlog position should be captured ? the show slave status will print what ? this is where it needs development, I tried testing backups on it, but there is no concrete documentation on what position it would fetch. db2-statement-1 (very, very long-running) db2-statement-2 (short-running) about the above scenario, the next db2-statement-2 it will wait for the long running statement-1 to complete. Surely.. !! :) However, my concern is how this tracking is done. That is, how is the db-wise segregation of statements done (from a single-binlog-file originally coming onto the slave) ? If this segregation is not done, then I cannot think of a way on how things would scale up, like for example, when the slave-relay-log-file contains a random mix of statements from tens of different databases. Any pointers on the actual current implementation of this db-wise statements-segregation will be a great confidence-booster !! :) Thanks and Regards, Ajay However db2-statement-2 can be picked up by any other sql worker thread. This is a good feature added in mysql, however still needs to go through lot of testing. Please share your observation and findings in case it differs from the above. Cheers!!! Akshay On Mon, Sep 1, 2014 at 8:27 AM, Ajay Garg ajaygargn...@gmail.com wrote: Hi all. We have replication set-up, where we cater to HUUGEE amounts of data. Since quite some time, we have been facing issues wherein the slave lags behind master quite a lot. So, yesterday we were able to setup parallel replication, by incorporating the following changes :: a) To begin with, partitioned some tables into dedicated databases. b) Set up the slave-parallel-workers parameter. The above seems to work functionally fine, but we have one doubt/query about the scalability of this solution. First, I will jot down the flow as far as I understand (please correct if wrong) :: Even in parallel-replication scenario, the master writes all the binlog (combined for all databases) in just one file, which then gets passed onto the slave as single-file itself. Thereafter, all the replication commands (combined for all databases) are written sequentially onto one slave-relay file. Thereafter, as per the documentation, the slave-SQL-Thread acts as the manager, handing over commands to worker-threads depending upon the databases on which the commands run. So far, so good. However, what would happen if the slave-relay file contains the following :: db1-statement-1 (short-running) db2-statement-1 (very, very long-running) db2-statement-2 (short-running) db1-statement-2 (short-running) db1-statement-3 (short-running) We will be grateful if someone could please clarifiy, as to how the above statements will be
Query on some MySQL-internals
Hi all. We are facing a very strange scenario. We have two mysql-instances running on the same machine, and they had been running functionally fine since about 6 years or so (catering to millions of records per day). However, since last few days, we were experiencing some elongated slowness on both the instances. So, we decided to OPTIMIZE TABLE slow_table on both the instances. We first ran the command on one instance. That speeded up things massively (select count(*) that was earlier taking 45 minutes was now running in less than 3 minutes). We then ran the command on the second instance. However, that seemed to have no effect. We ran the command again (on the same instance); again it had no effect. What could be the reason of this strange behavior? Both the instances run under fairly the same load, and both instances are mounted on the same partition (obviously, all the directories are different). Hoping for some light on this strange issue. Thanks and Regards, Ajay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Strange observation in OPTIMIZE TABLE command in InnoDB
Sorry, forgot to specify the engine. The table runs on InnoDB backend. Also, changed the subject to be more specific. On Sat, Sep 6, 2014 at 2:26 PM, Ajay Garg ajaygargn...@gmail.com wrote: Hi all. We are facing a very strange scenario. We have two mysql-instances running on the same machine, and they had been running functionally fine since about 6 years or so (catering to millions of records per day). However, since last few days, we were experiencing some elongated slowness on both the instances. So, we decided to OPTIMIZE TABLE slow_table on both the instances. We first ran the command on one instance. That speeded up things massively (select count(*) that was earlier taking 45 minutes was now running in less than 3 minutes). We then ran the command on the second instance. However, that seemed to have no effect. We ran the command again (on the same instance); again it had no effect. What could be the reason of this strange behavior? Both the instances run under fairly the same load, and both instances are mounted on the same partition (obviously, all the directories are different). Hoping for some light on this strange issue. Thanks and Regards, Ajay -- Regards, Ajay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Истинно Говорю Вам: БАЛАНС! [ 1164728870 ]
БАЛАНС - ЭФФЕКТИВНОЕ РЕШЕНИЕ ДЛЯ УМНОГО УЧЕТА http://djv-com.net/?attachment_id=381; * Ищем Партнеров: Беспроводная Система Комплексного Учета Энергоресурсов. Сбытовые компании: электричества, воды, тепла и газа. Производители средств измерений: электричества, воды, тепла и газа. Управляющие Компании, ЖЭК, ЖСК и компании Энергоаудита. Гос. учреждения, школы и больницы – контроль и экономия энергоресурсов. Фирмы - Арендодатели квартир, офисов и бутиков. Компании – интеграторы в области учета энергоресурсов. Биллинговые и Софтовые компании в области учета энергоресурсов. Для партнеров – производителей: передача технологии и развертывание производства. * БАЛАНС - Четвертое Поколение! Аналогия стационарного и мобильного телефона. Никаких проводов! Ремонты и вандалы не страшны. Полностью энергонезависимая система, работает без внешнего питания. Предоставление сервера для пилотных проектов и для массовых установок системы. Цена в разы ниже! Работаем с любыми приборами учета с импульсным выходом. Учитываем все! Электричество, Воду, Тепло и Газ! Комплексный учет. Монтаж производится в течение нескольких минут, 2 человека – 170 квартир в день. * Почему БАЛАНС: Аналогия черно-белого телевизора и светодиодной панели. Крупнейший беспроводной проект в СНГ – 190 000 точек учета, уже установлено 20 000. Российский, Молдавский и Украинский сертификат. Обработка и передача данных на базе последних технологий. Дистанционное управление потреблением! Работа в режиме Кредит или Предоплата. Проверено временем! БАЛАНС работает с 2007 года, в базе более 400 000 000 записей. Масштабируемость – до 10 000 000 точек учета на сервер. * В чем отличие БАЛАНС: Вы поймете, если хоть раз запускали систему учета. Вы пробивали межэтажные перекрытия, соединяли подъезды? БЕЗ ПРОВОДОВ. Вы заказывали проекты? Согласовывали со всеми организациями? БЕЗ ПРОЕКТА. Без Проводов, Желобов, Щитов, Перфораторов и Дрелей! БЕЗ ГОЛОВНОЙ БОЛИ. Вы запускали систему учета? Сколько надо времени и людей? PLUGPLAY. Какой процент данных учета вы получили? Стыдно сказать? БАЛАНС – 100% ДАННЫХ. Сколько стоил сервер, софт и запуск ПО? ВХОДИТ В СТОИМОСТЬ ОБОРУДОВАНИЯ. Какой % MBus вы «приручили»? РАБОТАЕМ С ЛЮБЫМ ИМПУЛЬСНЫМ ВЫХОДОМ. Тарифы по времени на счетчике с импульсным выходом! БЕЗ ЛИШНИХ РАСХОДОВ. * БАЛАНС: Развитая Аналитика! Только Аналитика уменьшает процент потерь. Аналитика выявление утечек, воровства и неисправных приборов учета. Контроль магнитного поля + температурного и рабочего диапазона прибора учета. Контроль суточных/часовых балансов по стояку/дому/району/городу/области. Доступ потребителей к данным через web-интерфейс + прогноз потребления. Удобство обслуживания – карта размещения приборов учета и фильтры аварий. Развитая функциональность – ваши пожелания учтены на десять лет вперед. * Мы будем рады, если выскажете свое мнение, замечания и пожелания. Контактная информация в прилагаемом файле. Дополнительная информация на наших сайтах: djv-com.net, djv-com.com Просим Вас, если письмо попало вам в папку спам, а предложения вам интересны, нажать в браузере не спам, для корректного получения наших писем. Спасибо за понимание. Для отписки от рассылки перейдите по ссылке: djv-com.net/sub_1/ [ 036bf96562c9ee7ebd3c13485f888925689618c3394e6608ae012772e2c08599 ] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Query on some MySQL-internals
Date: Sat, 6 Sep 2014 14:26:22 +0530 Subject: Query on some MySQL-internals From: ajaygargn...@gmail.com To: mysql@lists.mysql.com Hi all. We are facing a very strange scenario. We have two mysql-instances running on the same machine, and they had been running functionally fine since about 6 years or so (catering to millions of records per day). However, since last few days, we were experiencing some elongated slowness on both the instances. So, we decided to OPTIMIZE TABLE slow_table on both the instances. We first ran the command on one instance. That speeded up things massively (select count(*) that was earlier taking 45 minutes was now running in less than 3 minutes). We then ran the command on the second instance. However, that seemed to have no effect. We ran the command again (on the same instance); again it had no effect. What could be the reason of this strange behavior? Both the instances run under fairly the same loadMGHow do you extract the metrics to determine what the second instance is handling the same load as first instance? MGvmstat?MGiostat? MGSHOW GLOBAL STATUS ? and both instances are mounted on the same partition (obviously, all the directories are different). Hoping for some light on this strange issue. Thanks and Regards, Ajay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
ERROR in syntax...
Can anyone tell me why this query is generating an ERROR: DROP TRIGGER rtsadven_development.content_assets_after_insert_subtypes; CREATE TRIGGER `rtsadven_development`.`content_assets_after_insert_subtypes` AFTER INSERT ON `rtsadven_development`.`content_assets` FOR EACH ROW BEGIN IF NEW.content_asset_type_code = 'CURRI' THEN INSERT INTO curriculums (content_asset_id) VALUES (NEW.id); ELSEIF NEW.content_asset_type_code = 'COURS' THEN INSERT INTO courses (content_asset_id) VALUES (NEW.id); IF NEW.content_asset_type_code = 'DOC' THEN INSERT INTO documents (content_asset_id) VALUES (NEW.id); ELSEIF NEW.content_asset_type_code = 'MODUL' THEN INSERT INTO modules (content_asset_id) VALUES (NEW.id); ELSEIF NEW.content_asset_type_code = 'TOPIC' THEN INSERT INTO topics (content_asset_id) VALUES (NEW.id); ELSEIF NEW.content_asset_type_code = 'WEBPG' THEN INSERT INTO web_pages (content_asset_id) VALUES (NEW.id); ELSEIF NEW.content_asset_type_code = 'EMAIL' THEN INSERT INTO emails (content_asset_id) VALUES (NEW.id); ELSEIF NEW.content_asset_type_code = 'FBPOST' THEN INSERT INTO facebook_posts (content_asset_id) VALUES (NEW.id); ELSEIF NEW.content_asset_type_code = 'TWEET' THEN INSERT INTO tweets (content_asset_id) VALUES (NEW.id); ELSEIF NEW.content_asset_type_code = 'CHALL' THEN INSERT INTO challenges (content_asset_id) VALUES (NEW.id); ELSEIF NEW.content_asset_type_code = 'IMAGE' THEN INSERT INTO images (content_asset_id) VALUES (NEW.id); ELSEIF NEW.content_asset_type_code = 'VIDEO' THEN INSERT INTO videos (content_asset_id) VALUES (NEW.id); END IF; INSERT INTO content_asset_statistics (content_asset_id, statistic_type_code, seq) SELECT NEW.id, statistic_type_code, seq from content_asset_statistic_types where content_asset_type_code = NEW.content_asset_type_code; END; Appreciate it. Don Wieland -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
MySQL 5.5.33 History list not purging?
For some reason, the history list isn't purging on one of my masters. This is causing all kinds of weird issues/behavior with reads. Here's the last 8 or so hours of history list length: http://i.imgur.com/Q4DEeVi.png Here are my innodb%purge% settings. mysql show variables where Variable_name like 'innodb%purge%'\G *** 1. row *** Variable_name: innodb_max_purge_lag Value: 100 *** 2. row *** Variable_name: innodb_purge_batch_size Value: 100 *** 3. row *** Variable_name: innodb_purge_threads Value: 1 3 rows in set (0.09 sec) This machine is doing about 500-100 TPS. This manifested right after I did an ALTER TABLE ADD COLUMN to a massive table (600mil records, 100GB of data). What other things can I look at to figure out how to increase bandwidth for the purge thread? Thanks, *Brad Heller *| Director of Engineering | Cloudability.com | 541-231-1514 | Skype: brad.heller | @bradhe http://www.twitter.com/bradhe | @cloudability http://www.twitter.com/cloudability We're hiring! https://cloudability.com/jobs http://www.cloudability.com/jobs
Re: MySQL 5.5.33 History list not purging?
Am 06.09.2014 um 19:07 schrieb Brad Heller: For some reason, the history list isn't purging on one of my masters. This is causing all kinds of weird issues/behavior with reads so why do you not just try to update first? 5.5.39 is recent and contains *a lot* of bufixes look at the changelogs - there are tons of replication related bugixes with each minor update signature.asc Description: OpenPGP digital signature
Re: MySQL 5.5.33 History list not purging?
Good point. We'll try that out. I noticed our ibdata file is gigantic now, likely due to the alter table migration we ran. What's the relationship here, do you think? *Brad Heller *| Director of Engineering | Cloudability.com | 541-231-1514 | Skype: brad.heller | @bradhe http://www.twitter.com/bradhe | @cloudability http://www.twitter.com/cloudability We're hiring! https://cloudability.com/jobs http://www.cloudability.com/jobs On Sat, Sep 6, 2014 at 10:12 AM, Reindl Harald h.rei...@thelounge.net wrote: Am 06.09.2014 um 19:07 schrieb Brad Heller: For some reason, the history list isn't purging on one of my masters. This is causing all kinds of weird issues/behavior with reads so why do you not just try to update first? 5.5.39 is recent and contains *a lot* of bufixes look at the changelogs - there are tons of replication related bugixes with each minor update
RE: converting numeric to date-time?
2014/09/04 08:40 -0700, Jan Steinman From: Ed Mierzwa (emierzwa) emier...@micron.com FROM_UNIXTIME(1409304102.153) /*your epoch column here*/ I don't think the OP has a Unix timestamp. The number looks suspeciously like concatenation of date digits, 140930 at the beginning looks like September 30, 2014. If that's the case, you need to write something that will tear it apart. MySQL s interpretation of timestamps is already such that not much such code is needed: see Overview of Date and Time Types. If this, 140930, really were September 30, 2014 it would be enough to write SELECT DATE(140930) This also works: SELECT CAST(140930210215 AS DATETIME) One does not need to write apart-tearing code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: ERROR in syntax...
2014/09/06 09:06 -0700, Don Wieland Can anyone tell me why this query is generating an ERROR: Which error? The first IF statement is not properly ended? it isn't. (A series of equality tests against the same variable is done more conveniently with CASE ... END CASE.) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
