Slow inserts after moving to new system
I recently moved to a new web server (running 5.5.32) on one of my production systems. The new server has more RAM, faster CPU, etc so we see queried results a lot quicker. However, with basically the default my.cnf config file on each system, we see simple inserts taking about 10x longer than on the old system. An example are simple inserts to the following InnoDB table: CREATE TABLE `test` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `num` int(10) unsigned DEFAULT NULL, `id_two` int(10) unsigned NOT NULL, `start` decimal(17,8) DEFAULT NULL, `stop` decimal(17,8) DEFAULT NULL, `length` int(10) unsigned DEFAULT NULL, UNIQUE KEY `ID` (`ID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; One of our production systems creates ~3-8 thousand simple inserts to this table like: INSERT INTO `test` (`ID`, `num`, `id_two`, `start`, `stop`, `length`) VALUES (1,33602184,11172955,56617.0278,56617.05347222,2220); Sending all of these on the old system takes 5-20 seconds, but writes to the new server can take nearly 10 minutes. Both systems are running on nearly the default my.cnf files from the mysql installs. I’ve even altered the newer my.cnf file to match what was on our old server with no change in the results. I’ve also allocated more RAM to InnoDB with also no change in insert time. I’m not sure what to try looking at next. Does anybody have any ideas? Cheers, Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Date comparison help
I recently upgraded a local MySQL installation to 5.5.32 and am trying to
figure out why the following query won't work as expected anymore. I'm just
trying to compare a set of dates to NOW() but since the upgrade, these don't
seem to work as expected.
SELECT
DATE_ADD(STR_TO_DATE('2013-350-00:00:00','%Y-%j-%H:%i:%S'),INTERVAL 2 DAY),
NOW(),
DATE_ADD(STR_TO_DATE('2013-350-00:00:00','%Y-%j-%H:%i:%S'),INTERVAL 2 DAY)NOW()
For instance, when I run it on my system, I get 1 for the third column even
though comparing the two by eye it should be false.
Cheers,
Michael
Re: Date comparison help
Thanks Sam.
It turns out that if I put the DATE_ADD.. within DATE(), it works as expected.
That is sufficient for my goals, but it would be nice to understand this issue
in case there may be other cases that I need to watch out for.
Cheers,
Michael
On Oct 22, 2013, at 6:18 PM, kitlenv kitl...@gmail.com wrote:
Hi Michael,
FYI: I'm using 5.6.13 and your query returns 0 for the third column with my
instance.
Cheers,
Sam
On Wed, Oct 23, 2013 at 2:35 AM, Michael Stroh st...@astroh.org wrote:
I recently upgraded a local MySQL installation to 5.5.32 and am trying to
figure out why the following query won't work as expected anymore. I'm just
trying to compare a set of dates to NOW() but since the upgrade, these don't
seem to work as expected.
SELECT
DATE_ADD(STR_TO_DATE('2013-350-00:00:00','%Y-%j-%H:%i:%S'),INTERVAL 2 DAY),
NOW(),
DATE_ADD(STR_TO_DATE('2013-350-00:00:00','%Y-%j-%H:%i:%S'),INTERVAL 2
DAY)NOW()
For instance, when I run it on my system, I get 1 for the third column even
though comparing the two by eye it should be false.
Cheers,
Michael
Nested join query?
Hello everyone. I'm trying to perform a query that acts on 3 tables at once. I'm thinking I need to produce a set of joins between all three tables to get the results that I want, but am not sure how to go about it with nesting or maybe there's even a better way. I need to check TableC for cases where the State is set to 'Yes' and then find the Num fields in TableB that correspond to the matching IDs between the two. I currently have a query that performs a right join on the two that will give me the results. The problem is that I now have this single column table (TableBC listed below) from the first query that I then need to perform a query on TableA to find the matching records. I'd like to make this into a single query but am not sure the proper way to combine them all or how to perform a join on the values in this temporary table that I'm using. TableA Val Num 1 2 2 3 3 3 4 4 5 4 6 7 7 3 TableB NumID 1 1 2 2 3 1 4 2 5 1 6 1 7 4 8 3 9 5 TableC ID State 1No 2Yes 3No 4Yes 5No Currently to get the single column list from TableB and TableC that I want to use to query TableA, I'm using: SELECT `TableB`.`Num` FROM `TableC` RIGHT JOIN `TableB` ON `TableC`.`ID` = `TableB`.`ID` WHERE (`TableC`.`State` LIKE 'Yes') GROUP BY `TableB`.`Num` ORDER BY `TableB`.`ID` It is possible to have multiple instances of Num in TableB, that is why I'm also doing some groupings. In this simplified example, the result I get from this query is: TableBC Num 2 4 7 And the preferred result once I add in TableA would be: TableABC Val 1 4 5 6 So I believe the problem is now is how to insert or merge this into a query that will look for the results in TableA that I'm really interested in. It looks to be similar to the query I've already performed, but I'm not sure how to perform a join like this since I'm referencing a column that only exists in this temporary table that is being built and I don't want to accidentally reference the fields in TableB or TableC. Thanks in advance! Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Nested join query?
Travis, Thanks a lot! That seems to work perfectly and also cleans up the syntax a bit so I think it's more understandable. Michael On Aug 30, 2010, at 7:21 PM, Travis Ard wrote: I think you could do away with your right outer join of table B (which will include all rows from B whether or not they match to C), since you are explicitly filtering for C.State like 'Yes'. The intermediate result doesn't necessarily need to be stored in a temporary table. You can include multiple tables in a single query by specifying each table and the join condition. Also, If you just want a distinct list of values, you could use the DISTINCT clause as opposed to grouping. Something like the following query should produce the results you're looking for: select distinct a.val from tablec c inner join tableb b on b.id = c.id inner join tablea a on a.num = b.num where c.state = 'Yes'; -Travis -Original Message- From: Michael Stroh [mailto:st...@astroh.org] Sent: Monday, August 30, 2010 4:39 PM To: MySql Subject: Nested join query? Hello everyone. I'm trying to perform a query that acts on 3 tables at once. I'm thinking I need to produce a set of joins between all three tables to get the results that I want, but am not sure how to go about it with nesting or maybe there's even a better way. I need to check TableC for cases where the State is set to 'Yes' and then find the Num fields in TableB that correspond to the matching IDs between the two. I currently have a query that performs a right join on the two that will give me the results. The problem is that I now have this single column table (TableBC listed below) from the first query that I then need to perform a query on TableA to find the matching records. I'd like to make this into a single query but am not sure the proper way to combine them all or how to perform a join on the values in this temporary table that I'm using. TableA Val Num 1 2 2 3 3 3 4 4 5 4 6 7 7 3 TableB NumID 1 1 2 2 3 1 4 2 5 1 6 1 7 4 8 3 9 5 TableC ID State 1No 2Yes 3No 4Yes 5No Currently to get the single column list from TableB and TableC that I want to use to query TableA, I'm using: SELECT `TableB`.`Num` FROM `TableC` RIGHT JOIN `TableB` ON `TableC`.`ID` = `TableB`.`ID` WHERE (`TableC`.`State` LIKE 'Yes') GROUP BY `TableB`.`Num` ORDER BY `TableB`.`ID` It is possible to have multiple instances of Num in TableB, that is why I'm also doing some groupings. In this simplified example, the result I get from this query is: TableBC Num 2 4 7 And the preferred result once I add in TableA would be: TableABC Val 1 4 5 6 So I believe the problem is now is how to insert or merge this into a query that will look for the results in TableA that I'm really interested in. It looks to be similar to the query I've already performed, but I'm not sure how to perform a join like this since I'm referencing a column that only exists in this temporary table that is being built and I don't want to accidentally reference the fields in TableB or TableC. Thanks in advance! Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=travis_...@hotmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=st...@astroh.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Yet another query question
Hi everyone and thanks in advance for the help. I have a query that I'd like to perform using two tables but am not sure what the best way to perform it short of creating a loop in my code and performing multiple queries. I have two tables. The first table acts as a master table of sorts and Num in Table1 maps directly to Num in Table2. One way to think of this is that I'm performing a query on Table2 and grouping the records by MAX(version) but I am not interested in records if state = new in Table1 for the value of Num in Table2. I've tried to give an example below. Table1: Num, state 1 final 2 new 3 final Table2: Num, ID,IDt, version 11 100 1 12 101 1 13 102 1 24 100 2 25 103 1 36 100 2 37 103 1 38 104 1 Preferred result: IDt, ID, Num, MAX(version) 100 6 3 2 101 2 1 1 102 3 1 1 103 7 3 1 104 8 3 1 Cheers, Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Yet another query question
Yes, sorry, you are correct. I am actually grouping on that other column. I'll take a look at this and see if it works for me. Thanks! Michael On Jul 26, 2010, at 6:10 PM, Geert-Jan Brits wrote: Aren't you grouping on IDt? something like ? : select t2.IDt,t2.ID,t2.Num,max(t2.version) from table1 as t1, tabl2 as t2 where t1.num=t2.num and t1.state!='new' group by t2.IDt Cheers, Geert-Jan 2010/7/26 Michael Stroh st...@astroh.org Hi everyone and thanks in advance for the help. I have a query that I'd like to perform using two tables but am not sure what the best way to perform it short of creating a loop in my code and performing multiple queries. I have two tables. The first table acts as a master table of sorts and Num in Table1 maps directly to Num in Table2. One way to think of this is that I'm performing a query on Table2 and grouping the records by MAX(version) but I am not interested in records if state = new in Table1 for the value of Num in Table2. I've tried to give an example below. Table1: Num, state 1 final 2 new 3 final Table2: Num, ID,IDt, version 11 100 1 12 101 1 13 102 1 24 100 2 25 103 1 36 100 2 37 103 1 38 104 1 Preferred result: IDt, ID, Num, MAX(version) 100 6 3 2 101 2 1 1 102 3 1 1 103 7 3 1 104 8 3 1 Cheers, Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gbr...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Help needed on query on multiple tables
Hi everyone. I'm trying to create a certain MySQL query but I'm not sure how to do it. Here is a stripped down version of the result I'm aiming for. I'm pretty new to queries that act on multiple tables, so apologize if this is a very stupid question. I have one table (data) that has two columns (names and progress). I have a second table (items) that has two columns (names and version). I'd like to do a query that produces the name of every record in data that has progress set to 0 and the number of records in the items table that have the same value in each table.names field. I can perform this by using two sets of queries, one that queries the data table and then loop through the names to do a count(names) query, but I'm not sure if I can somehow do it in one query. Thanks in advance! Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Help needed on query on multiple tables
Thanks! That did it perfectly! Michael On Jun 3, 2010, at 11:45 AM, Steven Staples wrote: How about this? SELECT `first_table`.`names` , `first_table`.`version` , (SELECT COUNT(`other_table`.`names`) FROM `other_table` WHERE `other_table`.`this_id` = `first_table`.`id`) AS 'count' FROM `first_table` WHERE `first_table`.`progress` 0; Granted, you have not provided structure or names of the tables so this is just my interpretation, but maybe something like this could give you a starting point? Steven Staples -Original Message- From: Michael Stroh [mailto:st...@astroh.org] Sent: June 3, 2010 11:24 AM To: MySql Subject: Help needed on query on multiple tables Hi everyone. I'm trying to create a certain MySQL query but I'm not sure how to do it. Here is a stripped down version of the result I'm aiming for. I'm pretty new to queries that act on multiple tables, so apologize if this is a very stupid question. I have one table (data) that has two columns (names and progress). I have a second table (items) that has two columns (names and version). I'd like to do a query that produces the name of every record in data that has progress set to 0 and the number of records in the items table that have the same value in each table.names field. I can perform this by using two sets of queries, one that queries the data table and then loop through the names to do a count(names) query, but I'm not sure if I can somehow do it in one query. Thanks in advance! Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=sstap...@mnsi.net No virus found in this incoming message. Checked by AVG - www.avg.com Version: 9.0.829 / Virus Database: 271.1.1/2895 - Release Date: 06/03/10 02:25:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=st...@astroh.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
InnoDB / Transactions question
Hello, I'm currently writing a python program that scans some web directories and then stores some information in a local database. I'm playing with using InnoDB for this application as a test bed for using InnoDB in further applications, but I'm running into a couple issues. When I try to write my code and send it to the server, it appears as if the commands don't actually get processed. But if I print out the MySQL queries and copy and paste them into the SQL input via phpmyadmin, the commands work as expected. However, I can wrap my statements in START TRANSACTION and COMMIT statements which then actually processes everything. I'm getting some 2014 Commands out of sync errors when I use transactions and try to perform a second or third set of queries. Is it required to use transaction statements when using InnoDB, and if not, are there any ideas on why my installation doesn't seem to autocommit the queries without it? If I do use transaction statements/InnoDB, is there some common mistake that I'm using or do I need to use a separate connection to the MySQL database for every query I send? I can try to send my code if it is helpful, but I hope that my questions are general enough to not need it. The basic structure of my program is to do the following. Scan a webpage and parse it for information to send to the table named folders. Then scan the folders table for every record that has 0 in the updated field. Then for every record that the last query matched, scan another page relevant to that record and update a second table named observations with the information parsed from that page. This could be thousands of records needing inserting. Then update the folder table to have the updated field equal to 1 for the records I just scanned. I do have a foreign key set in the observations table so that if I delete a record in the folders table, then everything in the observations table that contains that key will be deleted as well. This hopefully will make it so that I don't have to queue up deletion requests for all the observation request records. Thanks in advance! Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: InnoDB / Transactions question
Thanks for the clarification. Michael On May 17, 2010, at 2:28 PM, Michael Dykman wrote: MyISAM does not support transactions so it is inherently in 'autocommit mode' all the time. You will run into this with any transactional database, be it InnoDB, Falcon, or Oracle and DB2 installations for that matter. For many classes of application, avoiding autocommit and explicitly creating and commiting transactions is the only way to keep the data coherent. For lightweight purposes, this can be overkill On Mon, May 17, 2010 at 2:21 PM, Michael Stroh st...@astroh.org wrote: Thanks Michael, it seems that using that works. I have other python scripts that write to other tables on the same installation. The only difference that I can think of is that they are MyISAM type whereas these two are InnoDB. Does the different type require this flag to be set? Or is there be something else going on here? Michael On May 17, 2010, at 2:12 PM, Michael Dykman wrote: The autocommit option can be set globally in your config (there probably is an example in the my.conf file that came with your distro). Alternatively, you may explicitly turn on auto commit on your connection by issuing this command first: set autocommit = 1; As this is a session variable, this only affect the current connection. - michael On Mon, May 17, 2010 at 12:34 PM, Michael Stroh st...@astroh.org wrote: Hello, I'm currently writing a python program that scans some web directories and then stores some information in a local database. I'm playing with using InnoDB for this application as a test bed for using InnoDB in further applications, but I'm running into a couple issues. When I try to write my code and send it to the server, it appears as if the commands don't actually get processed. But if I print out the MySQL queries and copy and paste them into the SQL input via phpmyadmin, the commands work as expected. However, I can wrap my statements in START TRANSACTION and COMMIT statements which then actually processes everything. I'm getting some 2014 Commands out of sync errors when I use transactions and try to perform a second or third set of queries. Is it required to use transaction statements when using InnoDB, and if not, are there any ideas on why my installation doesn't seem to autocommit the queries without it? If I do use transaction statements/InnoDB, is there some common mistake that I'm using or do I need to use a separate connection to the MySQL database for every query I send? I can try to send my code if it is helpful, but I hope that my questions are general enough to not need it. The basic structure of my program is to do the following. Scan a webpage and parse it for information to send to the table named folders. Then scan the folders table for every record that has 0 in the updated field. Then for every record that the last query matched, scan another page relevant to that record and update a second table named observations with the information parsed from that page. This could be thousands of records needing inserting. Then update the folder table to have the updated field equal to 1 for the records I just scanned. I do have a foreign key set in the observations table so that if I delete a record in the folders table, then everything in the observations table that contains that key will be deleted as well. This hopefully will make it so that I don't have to queue up deletion requests for all the observation request records. Thanks in advance! Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
