Re: slave is running slow in another data center
IO Thread will always be ahead,becuase this will keep interacting with master and any changes at master will be pulled by this IO thread and insert into RELAY LOG. Its the SQL THREAD that will execute all the statements in RELAY LOG. By the way how did u check IO thread is ahead of SQL Thread. On 6/18/08, Sagar, Sanjeev <[EMAIL PROTECTED]> wrote: > > do a show full processlist on the 4th slave and see what process is > running. This will the sql's running which will be the cause of the > slowness. > > > > >> This does not help because there is not a specific query, which is > running slow. Other 3 slaves are running same query and I"ve compared the > explain on all slaves for most of the updates and they looks same. All H/W > and S/W config are same including the cnf file settings. > > > > Also u need to check on the INTERNET bandwidth from the current data center > to the other data center. > > >> This is also not an issue because IO thread is way ahead then SQL > thread. It's a SQL thread, which is not able to keep up. > > > > > On 6/18/08, *Sagar, Sanjeev* <[EMAIL PROTECTED]> wrote: > > Hello All, > > > > I've 4 slaves of a master. Out of 4, 3 are in same data center and have > no issues. The fourth one is in another data center and some time that > slave sql_thread lag behind more then 5000 sec. It's only sql_thread, > which slow down. All H/W, S/W and my.cnf files are same for all slaves. > > > > I ran the mysqladmin extended-status in iterative mode and following > kind of results are there in my log file > > > > | Variable_name | Value | > > ++---+ > > | Bytes_received | 2117975 | > > | Com_insert | 39| > > | Com_show_slave_status | 4 | > > | Com_show_status| 1 | > > | Com_update | 49| > > | Connections| 1 | > > | Handler_read_key | 49| > > | Handler_read_next | 2 | > > | Handler_update | 3629 | > > | Key_blocks_not_flushed | 1 | > > | Key_blocks_unused | -21 | > > | Key_blocks_used| 21| > > | Key_reads | 21| > > | Key_write_requests | 94| > > | Key_writes | 75| > > | Max_used_connections | 1 | > > | Questions | 93| > > | Table_locks_immediate | 88| > > | Threads_connected | 1 | > > | Threads_created| 1 | > > ++---+ > > > > ++---+ > > | Variable_name | Value | > > ++---+ > > | Bytes_received | 1469626 | > > | Bytes_sent | 3837 | > > | Com_delete | 2 | > > | Com_insert | 143 | > > | Com_show_status| 1 | > > | Com_update | 1247 | > > | Handler_delete | 5 | > > | Handler_read_key | 1249 | > > | Handler_read_next | 73| > > | Handler_read_rnd | 8379 | > > | Handler_update | 9625 | > > | Handler_write | 8492 | > > | Key_blocks_not_flushed | 1 | > > | Key_blocks_unused | -158 | > > | Key_blocks_used| 158 | > > | Key_read_requests | 52672 | > > | Key_reads | 158 | > > | Questions | 1394 | > > | Table_locks_immediate | 1392 | > > | Threads_cached | 1 | > > | Threads_connected | -1| > > > > > > > > > > Any idea any one, that what can be tunned here for keeping sql_thread > running same as other slaves. My key_buffers are set to 1G in cnf file. > Also on this slave, no binary logging is enabled or no slow log is > enabled. And no application is connected. This is purely stand alone > slave for disaster recovery. > > > > Regards, > > > > > >
what codepage /colation to use?
hi there, I am usin mysql workbench to edit tables now I wonder what codepage I shoud use. what I want is to use utfo input and output. now ther is "utf8 - utf8_bin", "utf8 - utf8_unicode_ci" and about 7 million of others. which one do I use. thanks very much robert -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to guarantee an integrity in this case?
Hi, > I want to do this: > > [1] read a row from table table_1 with select > [2] change a row from [1] with update > [3] change another row in table table_1 with update > > But queries [2] and [3] in the time of invoking, assume that row from query > [1] wasn't changed. > > Should I use LOCK TABLES? - I've tried that in query browser, but that > doesn't prevent another thread to modify locked table. One of the easiest ways is to start a read consistent transaction, read the values for the rows you want to update. Next, update the rows, but not just include the primary key to locate the row, but also use the values you want to modify. eg: I want to update a customer record and change his address. update customer set address = 'New Address' where customer_id = 121 and address = 'Old Address' If this update fails to locate rows, you know that someone else changed the value in the Address field before you were able to update it. Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle & MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to guarantee an integrity in this case?
I want to do this: [1] read a row from table table_1 with select [2] change a row from [1] with update [3] change another row in table table_1 with update But queries [2] and [3] in the time of invoking, assume that row from query [1] wasn't changed. Should I use LOCK TABLES? - I've tried that in query browser, but that doesn't prevent another thread to modify locked table. -- View this message in context: http://www.nabble.com/How-to-guarantee-an-integrity-in-this-case--tp17988300p17988300.html Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: slave is running slow in another data center
do a show full processlist on the 4th slave and see what process is running. This will the sql's running which will be the cause of the slowness. >> This does not help because there is not a specific query, which is running slow. Other 3 slaves are running same query and I"ve compared the explain on all slaves for most of the updates and they looks same. All H/W and S/W config are same including the cnf file settings. Also u need to check on the INTERNET bandwidth from the current data center to the other data center. >> This is also not an issue because IO thread is way ahead then SQL thread. It's a SQL thread, which is not able to keep up. On 6/18/08, Sagar, Sanjeev <[EMAIL PROTECTED]> wrote: Hello All, I've 4 slaves of a master. Out of 4, 3 are in same data center and have no issues. The fourth one is in another data center and some time that slave sql_thread lag behind more then 5000 sec. It's only sql_thread, which slow down. All H/W, S/W and my.cnf files are same for all slaves. I ran the mysqladmin extended-status in iterative mode and following kind of results are there in my log file | Variable_name | Value | ++---+ | Bytes_received | 2117975 | | Com_insert | 39| | Com_show_slave_status | 4 | | Com_show_status| 1 | | Com_update | 49| | Connections| 1 | | Handler_read_key | 49| | Handler_read_next | 2 | | Handler_update | 3629 | | Key_blocks_not_flushed | 1 | | Key_blocks_unused | -21 | | Key_blocks_used| 21| | Key_reads | 21| | Key_write_requests | 94| | Key_writes | 75| | Max_used_connections | 1 | | Questions | 93| | Table_locks_immediate | 88| | Threads_connected | 1 | | Threads_created| 1 | ++---+ ++---+ | Variable_name | Value | ++---+ | Bytes_received | 1469626 | | Bytes_sent | 3837 | | Com_delete | 2 | | Com_insert | 143 | | Com_show_status| 1 | | Com_update | 1247 | | Handler_delete | 5 | | Handler_read_key | 1249 | | Handler_read_next | 73| | Handler_read_rnd | 8379 | | Handler_update | 9625 | | Handler_write | 8492 | | Key_blocks_not_flushed | 1 | | Key_blocks_unused | -158 | | Key_blocks_used| 158 | | Key_read_requests | 52672 | | Key_reads | 158 | | Questions | 1394 | | Table_locks_immediate | 1392 | | Threads_cached | 1 | | Threads_connected | -1| Any idea any one, that what can be tunned here for keeping sql_thread running same as other slaves. My key_buffers are set to 1G in cnf file. Also on this slave, no binary logging is enabled or no slow log is enabled. And no application is connected. This is purely stand alone slave for disaster recovery. Regards,
Re: Do I need to use GROUP BY to do this?
I happen to have worked on a similar query this morning, so it's in my mind :) SELECT SUBSTRING(sales_date,1,10), COUNT(sales_id) FROM sales_activity WHERE sales_type = 1 GROUP BY SUBSTRING(sales_date,1,10); should do the trick. On Tue, 2008-06-17 at 18:21 -0700, Grant Giddens wrote: > Hi, > > I have a table where I keep sales transactions, so I'm trying to do a > query that will count the number of transactions per day. > > My test data looks like: > > -- > -- Table structure for table `sales_activity` > -- > > CREATE TABLE `sales_activity` ( > `sales_id` int(11) NOT NULL auto_increment, > `sales_date` datetime NOT NULL default '-00-00 00:00:00', > `sales_type` tinyint(4) NOT NULL default '0', > PRIMARY KEY (`sales_id`) > ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ; > > -- > -- Dumping data for table `sales_activity` -- Ian Simpson System Administrator MyJobGroup This email may contain confidential information and is intended for the recipient(s) only. If an addressing or transmission error has misdirected this email, please notify the author by replying to this email. If you are not the intended recipient(s) disclosure, distribution, copying or printing of this email is strictly prohibited and you should destroy this mail. Information or opinions in this message shall not be treated as neither given nor endorsed by the company. Neither the company nor the sender accepts any responsibility for viruses or other destructive elements and it is your responsibility to scan any attachments.
Re: Do I need to use GROUP BY to do this?
Re: Do I need to use GROUP BY to do this? yes Grant Giddens schrieb: Hi, I have a table where I keep sales transactions, so I'm trying to do a query that will count the number of transactions per day. My test data looks like: What type of query do I need to get that information? SELECT `sales_date`, COUNT(*) FROM `sales_activity` WHERE `sales_type` = 1 GROUP BY `sales_date` -- Sebastian Mendel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Do I need to use GROUP BY to do this?
Hi, I have a table where I keep sales transactions, so I'm trying to do a query that will count the number of transactions per day. My test data looks like: -- -- Table structure for table `sales_activity` -- CREATE TABLE `sales_activity` ( `sales_id` int(11) NOT NULL auto_increment, `sales_date` datetime NOT NULL default '-00-00 00:00:00', `sales_type` tinyint(4) NOT NULL default '0', PRIMARY KEY (`sales_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ; -- -- Dumping data for table `sales_activity` -- INSERT INTO `sales_activity` VALUES (1, '2008-06-15 13:00:00', 1); INSERT INTO `sales_activity` VALUES (2, '2008-06-15 13:00:00', 1); INSERT INTO `sales_activity` VALUES (3, '2008-06-15 13:00:00', 1); INSERT INTO `sales_activity` VALUES (4, '2008-06-15 13:00:00', 1); INSERT INTO `sales_activity` VALUES (5, '2008-06-15 13:00:00', 2); INSERT INTO `sales_activity` VALUES (6, '2008-06-15 13:00:00', 2); INSERT INTO `sales_activity` VALUES (7, '2008-06-16 13:00:00', 1); INSERT INTO `sales_activity` VALUES (8, '2008-06-16 13:00:00', 1); INSERT INTO `sales_activity` VALUES (9, '2008-06-17 13:00:00', 1); INSERT INTO `sales_activity` VALUES (10, '2008-06-17 13:00:00', 1); I would like to get a count of the number of transactions where transaction id=1 for each date. ie, the result set should look like: 2008-06-15 4 2008-06-16 2 2008-06-17 2 What type of query do I need to get that information? Thanks!
Re: slave is running slow in another data center
do a show full processlist on the 4th slave and see what process is running. This will the sql's running which will be the cause of the slowness. Also u need to check on the INTERNET bandwidth from the current data center to the other data center. On 6/18/08, Sagar, Sanjeev <[EMAIL PROTECTED]> wrote: > > Hello All, > > > > I've 4 slaves of a master. Out of 4, 3 are in same data center and have > no issues. The fourth one is in another data center and some time that > slave sql_thread lag behind more then 5000 sec. It's only sql_thread, > which slow down. All H/W, S/W and my.cnf files are same for all slaves. > > > > I ran the mysqladmin extended-status in iterative mode and following > kind of results are there in my log file > > > > | Variable_name | Value | > > ++---+ > > | Bytes_received | 2117975 | > > | Com_insert | 39| > > | Com_show_slave_status | 4 | > > | Com_show_status| 1 | > > | Com_update | 49| > > | Connections| 1 | > > | Handler_read_key | 49| > > | Handler_read_next | 2 | > > | Handler_update | 3629 | > > | Key_blocks_not_flushed | 1 | > > | Key_blocks_unused | -21 | > > | Key_blocks_used| 21| > > | Key_reads | 21| > > | Key_write_requests | 94| > > | Key_writes | 75| > > | Max_used_connections | 1 | > > | Questions | 93| > > | Table_locks_immediate | 88| > > | Threads_connected | 1 | > > | Threads_created| 1 | > > ++---+ > > > > ++---+ > > | Variable_name | Value | > > ++---+ > > | Bytes_received | 1469626 | > > | Bytes_sent | 3837 | > > | Com_delete | 2 | > > | Com_insert | 143 | > > | Com_show_status| 1 | > > | Com_update | 1247 | > > | Handler_delete | 5 | > > | Handler_read_key | 1249 | > > | Handler_read_next | 73| > > | Handler_read_rnd | 8379 | > > | Handler_update | 9625 | > > | Handler_write | 8492 | > > | Key_blocks_not_flushed | 1 | > > | Key_blocks_unused | -158 | > > | Key_blocks_used| 158 | > > | Key_read_requests | 52672 | > > | Key_reads | 158 | > > | Questions | 1394 | > > | Table_locks_immediate | 1392 | > > | Threads_cached | 1 | > > | Threads_connected | -1| > > > > > > > > > > Any idea any one, that what can be tunned here for keeping sql_thread > running same as other slaves. My key_buffers are set to 1G in cnf file. > Also on this slave, no binary logging is enabled or no slow log is > enabled. And no application is connected. This is purely stand alone > slave for disaster recovery. > > > > Regards, > > > > > >
Re: create view not working
Aaron, > why doesn't this work? the select works perfectly Define "doesn't work"? What is the error message? > create view cost_report as > SELECT c_name, d_manuf as Manufacturer, d_model as Model, count(1) as > "Number Enrolled", > d_price as "Monthly Price", count(1)*d_price as "Cost" > FROM `b_devices` A > left join b_device_types B > on A.d_id = B.d_id > left join b_prices C > on A.d_id = C.d_id AND A.c_id = C.c_id > left join b_company D > on A.c_id = D.c_id > group by A.d_id, A.c_id > order by c_name Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle & MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
