Mysql Monitoring with Graphite
Hi all, I need to set up mysql monitoring graphs in Graphite. I am able to monitor basic system metrics after runnning example-client.py that comes with graphite installation. when i m looking for mysql plugin for graphite , i find MySQLPerfCollector.py but when i am running it after installing diamond , it is not sending status to graphite server. Can anybody pls let me know how can we plot mysql basic monitoing graphs in graphite. --Thanks
Re: replication fails after upgrade to 5.6
So I created a new test box on AWS, and just did one upgrade from 5.0.96 to 5.1, like I did before and replication will not work from a master with 5.0.96 to a slave with 5.1.68 I keep getting Error 1062, Duplicate Entry for key I get no errors when I do a mysql_upgrade, all comes back ok. I was curious if anyone had any ideas? Thanks On Wed, Feb 20, 2013 at 5:51 PM, Mike Franon kongfra...@gmail.com wrote: This is on a slave, i only upgraded on one box which is the slave i have not touched master On Wed, Feb 20, 2013 at 5:41 PM, Reindl Harald h.rei...@thelounge.net wrote: Am 20.02.2013 23:27, schrieb Mike Franon: So I successfully upgraded a test db server from 5.0.96 all the way up to 5.6 Replication as the slave, where the master is 5.0.96, started working for about 10 minutes and then got the following error: [ERROR] Slave SQL: Error 'Duplicate entry 'data' for key 'PRIMARY'' on query. Default database: 'test'. Query: 'UPDATE IGNORE , Error_code: 1062 All of our other slaves on 5.0.96 are fine, so I know it has to do with 5.6 but just not sure what, when ir an mysql_upgrade everything was OK did you surely upgrade and restart the slaves first? i personally would NOT go to 5.6 now it is a very young release and looking and the typical changelogs replication has always the most fixed bugs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: replication fails after upgrade to 5.6
update the master ASAP in a short timeframe too and re-init replication if needed normally both should have exactly the same version the slaves must be updated first because otherwise a master may write instructions in the binlog the older slave does not undersatdn at all, but as said normally both should have the same version Am 21.02.2013 18:03, schrieb Mike Franon: So I created a new test box on AWS, and just did one upgrade from 5.0.96 to 5.1, like I did before and replication will not work from a master with 5.0.96 to a slave with 5.1.68 I keep getting Error 1062, Duplicate Entry for key I get no errors when I do a mysql_upgrade, all comes back ok. I was curious if anyone had any ideas? Thanks On Wed, Feb 20, 2013 at 5:51 PM, Mike Franon kongfra...@gmail.com wrote: This is on a slave, i only upgraded on one box which is the slave i have not touched master On Wed, Feb 20, 2013 at 5:41 PM, Reindl Harald h.rei...@thelounge.net wrote: Am 20.02.2013 23:27, schrieb Mike Franon: So I successfully upgraded a test db server from 5.0.96 all the way up to 5.6 Replication as the slave, where the master is 5.0.96, started working for about 10 minutes and then got the following error: [ERROR] Slave SQL: Error 'Duplicate entry 'data' for key 'PRIMARY'' on query. Default database: 'test'. Query: 'UPDATE IGNORE , Error_code: 1062 All of our other slaves on 5.0.96 are fine, so I know it has to do with 5.6 but just not sure what, when ir an mysql_upgrade everything was OK did you surely upgrade and restart the slaves first? i personally would NOT go to 5.6 now it is a very young release and looking and the typical changelogs replication has always the most fixed bugs signature.asc Description: OpenPGP digital signature
Re: replication fails after upgrade to 5.6
Unfortunately that is not possible at the moment, I have 6 slaves off the one master, also I want to test it as much as possible before upgrading the master. Is the only way to really fix this is to upgrade master? I thought you can replicate from master - slave if version is higher on slave, just not the other way around? Thanks On Thu, Feb 21, 2013 at 1:03 PM, Reindl Harald h.rei...@thelounge.net wrote: update the master ASAP in a short timeframe too and re-init replication if needed normally both should have exactly the same version the slaves must be updated first because otherwise a master may write instructions in the binlog the older slave does not undersatdn at all, but as said normally both should have the same version Am 21.02.2013 18:03, schrieb Mike Franon: So I created a new test box on AWS, and just did one upgrade from 5.0.96 to 5.1, like I did before and replication will not work from a master with 5.0.96 to a slave with 5.1.68 I keep getting Error 1062, Duplicate Entry for key I get no errors when I do a mysql_upgrade, all comes back ok. I was curious if anyone had any ideas? Thanks On Wed, Feb 20, 2013 at 5:51 PM, Mike Franon kongfra...@gmail.com wrote: This is on a slave, i only upgraded on one box which is the slave i have not touched master On Wed, Feb 20, 2013 at 5:41 PM, Reindl Harald h.rei...@thelounge.net wrote: Am 20.02.2013 23:27, schrieb Mike Franon: So I successfully upgraded a test db server from 5.0.96 all the way up to 5.6 Replication as the slave, where the master is 5.0.96, started working for about 10 minutes and then got the following error: [ERROR] Slave SQL: Error 'Duplicate entry 'data' for key 'PRIMARY'' on query. Default database: 'test'. Query: 'UPDATE IGNORE , Error_code: 1062 All of our other slaves on 5.0.96 are fine, so I know it has to do with 5.6 but just not sure what, when ir an mysql_upgrade everything was OK did you surely upgrade and restart the slaves first? i personally would NOT go to 5.6 now it is a very young release and looking and the typical changelogs replication has always the most fixed bugs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: replication fails after upgrade to 5.6
Am 21.02.2013 19:11, schrieb Mike Franon: Is the only way to really fix this is to upgrade master? I thought you can replicate from master - slave if version is higher on slave, just not the other way around? normally no but take a look at the changelogs of myslq in the last years 80 out of 100 fixes are replication bugs On Thu, Feb 21, 2013 at 1:03 PM, Reindl Harald h.rei...@thelounge.net wrote: update the master ASAP in a short timeframe too and re-init replication if needed normally both should have exactly the same version the slaves must be updated first because otherwise a master may write instructions in the binlog the older slave does not undersatdn at all, but as said normally both should have the same version Am 21.02.2013 18:03, schrieb Mike Franon: So I created a new test box on AWS, and just did one upgrade from 5.0.96 to 5.1, like I did before and replication will not work from a master with 5.0.96 to a slave with 5.1.68 I keep getting Error 1062, Duplicate Entry for key I get no errors when I do a mysql_upgrade, all comes back ok. I was curious if anyone had any ideas? Thanks On Wed, Feb 20, 2013 at 5:51 PM, Mike Franon kongfra...@gmail.com wrote: This is on a slave, i only upgraded on one box which is the slave i have not touched master On Wed, Feb 20, 2013 at 5:41 PM, Reindl Harald h.rei...@thelounge.net wrote: Am 20.02.2013 23:27, schrieb Mike Franon: So I successfully upgraded a test db server from 5.0.96 all the way up to 5.6 Replication as the slave, where the master is 5.0.96, started working for about 10 minutes and then got the following error: [ERROR] Slave SQL: Error 'Duplicate entry 'data' for key 'PRIMARY'' on query. Default database: 'test'. Query: 'UPDATE IGNORE , Error_code: 1062 All of our other slaves on 5.0.96 are fine, so I know it has to do with 5.6 but just not sure what, when ir an mysql_upgrade everything was OK did you surely upgrade and restart the slaves first? i personally would NOT go to 5.6 now it is a very young release and looking and the typical changelogs replication has always the most fixed bugs signature.asc Description: OpenPGP digital signature
RE: MySQL 5.1: incorrect arithmetic calculation
They are both right. It is a matter of how many decimal places you want to display: mysql SELECT 365 * 1.67 * ( 1 - 0.10); +--+ | 365 * 1.67 * ( 1 - 0.10) | +--+ | 548.5950 | +--+ 1 row in set (0.00 sec) mysql SELECT ROUND(365 * 1.67 * ( 1 - 0.10), 2); ++ | ROUND(365 * 1.67 * ( 1 - 0.10), 2) | ++ | 548.60 | ++ 1 row in set (0.00 sec) -Original Message- From: Alex Keda [mailto:ad...@lissyara.su] Sent: Thursday, February 14, 2013 9:36 PM To: mysql@lists.mysql.com Subject: MySQL 5.1: incorrect arithmetic calculation bkp0# mysql h5000_bill Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1643184 Server version: 5.1.68-log FreeBSD port: mysql-server-5.1.68 Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql set names utf8; Query OK, 0 rows affected (0.00 sec) mysql SELECT * FROM `WorksCompliteAgregate` WHERE (`ContractID` = 10369 AND `Month` = 497); +--++---+---+--++-- ---++ | ID | ContractID | Month | ServiceID | Comment | Cost | Discont | Amount | +--++---+---+--++-- ---++ | 10551851 | 10369 | 497 | 1 | №20440 | 1.67 | 0.10 |365 | | 10551854 | 10369 | 497 | 2 | №20441 | 150.00 | 1.00 | 1 | +--++---+---+--++-- ---++ 2 rows in set (0.00 sec) mysql SELECT SUM(`Amount`*`Cost`*(1-`Discont`)) as `Summ` FROM `WorksCompliteAgregate` WHERE (`ContractID` = 10369 AND `Month` = 497); ++ | Summ | ++ | 548.59 | ++ 1 row in set (0.00 sec) mysql SELECT SUM(`Amount`*`Cost`*(1-`Discont`)*100)/100 as `Summ` FROM `WorksCompliteAgregate` WHERE (`ContractID` = 10369 AND `Month` = 497); ++ | Summ | ++ | 548.594985 | ++ 1 row in set (0.00 sec) mysql SELECT 365 * 1.67 * ( 1 - 0.10); +--+ | 365 * 1.67 * ( 1 - 0.10) | +--+ | 548.5950 | +--+ 1 row in set (0.00 sec) mysql === but, my desktop calculator gives the result 548.60 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: MyISAM table size vs actual data, and performance
* Smells like some huge LONGTEXTs were INSERTed, then DELETEd. Perhaps just a single one of nearly 500M. * Yes, there is an impact on full table scans -- it has to step over the empty spots. Or maybe not -- one big cow chip of 500MB would be easy to leap over. * OPTIMIZE TABLE is the primary way to recover the space. It _may_ be that space on the _end_ is automatically recovered. If so, you might see the .MYD shrink even when OPTIMIZE is not run. * LONGTEXT is almost never useful. Do you really think there are thingies that big? Consider changing it to MEDIUMTEXT -- that would truncate any biggies to 16MB. * Smells like a key-value (EAV) schema design. Such is destined to fail when trying to scale. Yeah, you are probably stuck with Drupal. Here are my comments and recommendations on EAV: http://mysql.rjweb.org/doc.php/eav * Please try to find a way in your Email client to display STATUS without losing the spacing. * When you switched to InnoDB, I hope you had innodb_file_per_table turned on. That way, you can actually recoup the space when doing ALTER. Otherwise, you will be stuck with a bloated ibdata1 file that you cannot easily shrink. * In InnoDB, the LONGTEXT will usually be stored separately, thereby making a full table scan relatively efficient. -Original Message- From: Johan De Meersman [mailto:vegiv...@tuxera.be] Sent: Friday, February 15, 2013 4:21 AM To: mysql. Subject: MyISAM table size vs actual data, and performance Hey list, I've got another peculiar thing going on :-) Let me give you a quick summary of the situation first: we host a number of Drupal sites, each site and it's db on separate VMs for reasons that are not important to this scenario. MySQL is 5.0.51a-24+lenny4-log (Debian); I don't have the exact Drupal version here but it's likely to be a 5.x branch. The easy thing to say would of course be upgrade your versions, but that's not an option right now. I don't really care if that means I have no actual *fix* for the problem - I know how to work around it. I'm just looking for a cause, ideally maybe even a specific known bug. Strangely enough, I'm seeing this on three distinct installs; but others with the same versions and setup (but different sites) seem to not exhibit the issue. So, what I'm seeing is this: Drupal's variable table keeps growing, but there does not seem to be more data. I understand how record allocation and free space in datafiles works, but this is well beyond the normal behaviour. http://www.tuxera.be/filestore/heciexohhohj/df-year.png As you can see here (the lime green line of /data), growth occurs gradually (and the issue happened in september, as well), until it seems to reach a certain point. At some point, however, performance on that table (notably select * - it's a drupal thing) pretty much instantly plummets, and the query takes around half a minute to run - whereas now, after reclaiming the free space, it takes 0.03 seconds. I don't have the exact numbers as I wasn't on-site yesterday evening, but since the disk is 5GB, the reclaimed space yesterday must have been around 850MB - for a table that is now 30MB. No records were deleted from the table, the workaround is as simple as OPTIMIZE TABLE variable - simply rebuild the table. The logs make no mention of a crashed table, so it's very unlikely that this is a borked index. Even if it were, I wouldn't expect a scan of 30MB in 1202 rows to take half a minute, on a table that is accessed so often that it's relevant blocks are bound to be in the filesystem cache. The table's structure is fairly simple, too: CREATE TABLE `variable` ( `name` varchar(128) NOT NULL DEFAULT '', `value` longtext NOT NULL, PRIMARY KEY (`name`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 I currently have another system that's also growing that table, here's a bit of session: blockquote mysql show table status like 'variable'; +--++-++--++--- --+-+--+---+--- -+-+-+-+--- --+--++-+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | | Data_length | Max_data_length | Index_length | Data_free | | Auto_increment | Create_time | Update_time | Check_time | Collation | | Checksum | Create_options | Comment | +--++-++--++--- --+-+--+---+--- -+-+-+-+--- --+--++-+ | variable | MyISAM | 10 | Dynamic | 1188 | 795 | 493277732 | | 281474976710655 | 41984 | 492332716 | NULL | 2011-12-13 16:18:53 | | 2013-02-15 12:35:18 | 2012-10-17 15:45:11 | utf8_general_ci | NULL | | | |
RE: replication fails after upgrade to 5.6
It is safer to have the Slave be a newer version. -Original Message- From: Reindl Harald [mailto:h.rei...@thelounge.net] Sent: Thursday, February 21, 2013 10:30 AM To: mysql@lists.mysql.com Subject: Re: replication fails after upgrade to 5.6 Am 21.02.2013 19:11, schrieb Mike Franon: Is the only way to really fix this is to upgrade master? I thought you can replicate from master - slave if version is higher on slave, just not the other way around? normally no but take a look at the changelogs of myslq in the last years 80 out of 100 fixes are replication bugs On Thu, Feb 21, 2013 at 1:03 PM, Reindl Harald h.rei...@thelounge.net wrote: update the master ASAP in a short timeframe too and re-init replication if needed normally both should have exactly the same version the slaves must be updated first because otherwise a master may write instructions in the binlog the older slave does not undersatdn at all, but as said normally both should have the same version Am 21.02.2013 18:03, schrieb Mike Franon: So I created a new test box on AWS, and just did one upgrade from 5.0.96 to 5.1, like I did before and replication will not work from a master with 5.0.96 to a slave with 5.1.68 I keep getting Error 1062, Duplicate Entry for key I get no errors when I do a mysql_upgrade, all comes back ok. I was curious if anyone had any ideas? Thanks On Wed, Feb 20, 2013 at 5:51 PM, Mike Franon kongfra...@gmail.com wrote: This is on a slave, i only upgraded on one box which is the slave i have not touched master On Wed, Feb 20, 2013 at 5:41 PM, Reindl Harald h.rei...@thelounge.net wrote: Am 20.02.2013 23:27, schrieb Mike Franon: So I successfully upgraded a test db server from 5.0.96 all the way up to 5.6 Replication as the slave, where the master is 5.0.96, started working for about 10 minutes and then got the following error: [ERROR] Slave SQL: Error 'Duplicate entry 'data' for key 'PRIMARY'' on query. Default database: 'test'. Query: 'UPDATE IGNORE , Error_code: 1062 All of our other slaves on 5.0.96 are fine, so I know it has to do with 5.6 but just not sure what, when ir an mysql_upgrade everything was OK did you surely upgrade and restart the slaves first? i personally would NOT go to 5.6 now it is a very young release and looking and the typical changelogs replication has always the most fixed bugs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: replication fails after upgrade to 5.6
and where did anybody say the opposite in this thread? Am 21.02.2013 22:25, schrieb Rick James: It is safer to have the Slave be a newer version. -Original Message- From: Reindl Harald [mailto:h.rei...@thelounge.net] Sent: Thursday, February 21, 2013 10:30 AM To: mysql@lists.mysql.com Subject: Re: replication fails after upgrade to 5.6 Am 21.02.2013 19:11, schrieb Mike Franon: Is the only way to really fix this is to upgrade master? I thought you can replicate from master - slave if version is higher on slave, just not the other way around? normally no but take a look at the changelogs of myslq in the last years 80 out of 100 fixes are replication bugs On Thu, Feb 21, 2013 at 1:03 PM, Reindl Harald h.rei...@thelounge.net wrote: update the master ASAP in a short timeframe too and re-init replication if needed normally both should have exactly the same version the slaves must be updated first because otherwise a master may write instructions in the binlog the older slave does not undersatdn at all, but as said normally both should have the same version Am 21.02.2013 18:03, schrieb Mike Franon: So I created a new test box on AWS, and just did one upgrade from 5.0.96 to 5.1, like I did before and replication will not work from a master with 5.0.96 to a slave with 5.1.68 I keep getting Error 1062, Duplicate Entry for key I get no errors when I do a mysql_upgrade, all comes back ok. I was curious if anyone had any ideas? Thanks On Wed, Feb 20, 2013 at 5:51 PM, Mike Franon kongfra...@gmail.com wrote: This is on a slave, i only upgraded on one box which is the slave i have not touched master On Wed, Feb 20, 2013 at 5:41 PM, Reindl Harald h.rei...@thelounge.net wrote: Am 20.02.2013 23:27, schrieb Mike Franon: So I successfully upgraded a test db server from 5.0.96 all the way up to 5.6 Replication as the slave, where the master is 5.0.96, started working for about 10 minutes and then got the following error: [ERROR] Slave SQL: Error 'Duplicate entry 'data' for key 'PRIMARY'' on query. Default database: 'test'. Query: 'UPDATE IGNORE , Error_code: 1062 All of our other slaves on 5.0.96 are fine, so I know it has to do with 5.6 but just not sure what, when ir an mysql_upgrade everything was OK did you surely upgrade and restart the slaves first? i personally would NOT go to 5.6 now it is a very young release and looking and the typical changelogs replication has always the most fixed bugs signature.asc Description: OpenPGP digital signature
Re: Mysql Monitoring with Graphite
Anyone has any idea about this. On Thu, Feb 21, 2013 at 7:42 PM, Adarsh Sharma eddy.ada...@gmail.comwrote: Hi all, I need to set up mysql monitoring graphs in Graphite. I am able to monitor basic system metrics after runnning example-client.py that comes with graphite installation. when i m looking for mysql plugin for graphite , i find MySQLPerfCollector.py but when i am running it after installing diamond , it is not sending status to graphite server. Can anybody pls let me know how can we plot mysql basic monitoing graphs in graphite. --Thanks
Re: Mysql Monitoring with Graphite
- Original Message - From: Adarsh Sharma eddy.ada...@gmail.com Anyone has any idea about this. Unless someone else here is using Graphite (I've never even heard of it, tbh) I think this may be something for the Graphite support channels, instead. -- Unhappiness is discouraged and will be corrected with kitten pictures. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
