Index Query Tunning
Hi All, I have situation here about Innodb locking. In transaction, We select from XYZ transaction table values and then updates it like below SESSION 1: START TRANSACTION; SELECT ID INTO vID FROM XYZ WHERE FLAG = 1 ORDER BY TIME LIMIT 1 FOR UPDATE; UPDATE XYZ SET FLAG=0 WHERE ID = vID; COMMIT; SESSION 2: UPDATE XYZ SET FLAG=1 WHERE ID = 2; We are keep on getting deadlock due to index locking, there is index on FLAG, we can allow phantom read in session 1, we tried with READ COMMITTED but still same, I think issue with next-key locking. If i do following in SESSION 1 would that help in locking or still it would lock index. Any suggestion. SESSION 1: START TRANSACTION; UPDATE XYZ SET FLAG=0,ID=(@oID:=ID) ORDER BY TIME LIMIT 1; SELECT @oID AS ID; COMMIT; --Anupam
Index locking Query
Hi All, I have situation here about Innodb locking. In transaction, We select from XYZ transaction table values and then updates it like below SESSION 1: START TRANSACTION; SELECT ID INTO vID FROM XYZ WHERE FLAG = 1 ORDER BY TIME LIMIT 1 FOR UPDATE; UPDATE XYZ SET FLAG=0 WHERE ID = vID; COMMIT; SESSION 2: UPDATE XYZ SET FLAG=1 WHERE ID = 2; We are keep on getting deadlock due to index locking, there is index on FLAG, we can allow phantom read in session 1, we tried with READ COMMITTED but still same, I think issue with next-key locking. If i do following in SESSION 1 would that help in locking or still it would lock index. Any suggestion. SESSION 1: START TRANSACTION; UPDATE XYZ SET FLAG=0,ID=(@oID:=ID) ORDER BY TIME LIMIT 1; SELECT @oID AS ID; COMMIT;
Re: Monitoring Sessions
Thanks Johan for info, We already tried with tcpdump and wireshark it was helpfull. Percona tool kit i need to try. From: Johan De Meersman To: Anupam Karmarkar Cc: mysql@lists.mysql.com Sent: Wednesday, 10 October 2012 5:16 PM Subject: Re: Monitoring Sessions - Original Message - > From: "Anupam Karmarkar" > > How to monitor individual session number of rows selected or updated > by sessions, number of bytes sent and reviewed by session in a given > time period, sessions connects runs command and then disconnects, > SHOW GLOBAL STATUS is not helping me in this case as i want low > level session details, there are nearly 50's application server > requesting to 1 databases server, which server is generating more > traffic need to monitor and what kind of queries it is firing, > binlog file are genrating nearly 7-8 GB daily. > > Data trafic we can also get on network level but can we get more > details as mention. Well, you can look at the local (session) status, but that would require each session to actually store those, as you can't access them outside of the session. Alternatively, you can use tcpdump and wireshark to capture traffic as it goes through the wire and look at what's happening. Percona Toolkit's pt-query-digest tool can also work with tcpdump logs. The general log file will also save you entire sessions, but will do so for EVERY session that happens - which is going to make for a huge overhead on the busy machine you seem to be describing, so is definitely not recommended. Someone also recently posted links towards an init-sql based approach which might be adapted, and towards a McAfee Auditing module for MyQSL that also seemed to hold some promise. -- Linux Bier Wanderung 2012, now also available in Belgium! August, 12 to 19, Diksmuide, Belgium - http://lbw2012.tuxera.be -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
Monitoring Sessions
Hi All, How to monitor individual session number of rows selected or updated by sessions, number of bytes sent and reviewed by session in a given time period, sessions connects runs command and then disconnects, SHOW GLOBAL STATUS is not helping me in this case as i want low level session details, there are nearly 50's application server requesting to 1 databases server, which server is generating more traffic need to monitor and what kind of queries it is firing, binlog file are genrating nearly 7-8 GB daily. Data trafic we can also get on network level but can we get more details as mention. --Anupam
Re: Need Query Help
Thanks Rick for your reply, Here i am asking about logic to perpare query or whole query itself. --Anupam From: Rick James To: Anupam Karmarkar ; "mysql@lists.mysql.com" Sent: Wednesday, 20 June 2012 10:52 PM Subject: RE: Need Query Help http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_timediff and SEC_TO_TIME()/3600 > -Original Message- > From: Anupam Karmarkar [mailto:sb_akarmar...@yahoo.com] > Sent: Wednesday, June 20, 2012 2:39 AM > To: mysql@lists.mysql.com > Subject: Need Query Help > > Hi All, > > I need query help for following table struture, where we need to > calculate login duration of that employee for give period. > > Example table > > > EmployeeID LoginTime LogoutTIme > > 101 2012-05-01 10:00:00 2012-05-01 12:30:00 > 102 2012-04-31 23:00:00 2012-05-02 05:00:00 > > 103 2012-05-01 14:00:00 NULL > 104 2012-05-02 00:10:00 2012-05-02 05:00:00 > > > I tried to fit all scenario in above table, Consider NULL as yet to > logout > > > How would i calcuate Employee and it Login duration for period say from > 2012-05-01 08:00:00 to 2012-05-01 22:00:00 > > > --Anupam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
Need Query Help
Hi All, I need query help for following table struture, where we need to calculate login duration of that employee for give period. Example table EmployeeID LoginTime LogoutTIme 101 2012-05-01 10:00:00 2012-05-01 12:30:00 102 2012-04-31 23:00:00 2012-05-02 05:00:00 103 2012-05-01 14:00:00 NULL 104 2012-05-02 00:10:00 2012-05-02 05:00:00 I tried to fit all scenario in above table, Consider NULL as yet to logout How would i calcuate Employee and it Login duration for period say from 2012-05-01 08:00:00 to 2012-05-01 22:00:00 --Anupam
Re: Architectural Help
Dear Nigel, Thank for reply.. See my comments below --Anupam From: Nigel Wood To: Anupam Karmarkar ; "mysql@lists.mysql.com" Sent: Thursday, 24 May 2012 3:35 PM Subject: RE: Architectural Help A few questions: which is more or a problem: network outages, network capacity or query latency? >>Network Outages is problem, between data centres When you say "near real-time" do you need transactional consistent view on all 49 servers or can some lag be tolerated? >>Some lag can be tolerated considering network outage Can any one of the 49 local servers potentially update/delete the same rows or data? >>Yes central server can update same row or local server but not at same time, >>Few data will be updated in central location and others majorly in local.One >>Application Writes in Centeral and Others in local, Local app and central app >>shares some data. Is there any natural segmentation point within the data? >>No Do the data centers have diverse networks so that connections to some data centers may remain when others? >>Yes, we have diverse nework , so connectivity will be avaiable in other data >>centers. In the event that a local data centre is totally isolated from the others what data should it be allowed to update? >>Loca application should always write in local database, Central Application >>Updated will not be available to local. Do your applications produce/examine large data set querying by secondary keys or using tull text search? >>We dont have text search or we dont query large data Are you in a position to modify the applications? >>No, Micro changes ok __ From: Anupam Karmarkar [sb_akarmar...@yahoo.com] Sent: Thursday, May 24, 2012 10:17 AM To: mysql@lists.mysql.com Subject: Architectural Help Hi All, I need architectural help for our requirement, We have nearly 50 data centre through out different cities from these data center application connect to central database server currently, there are conectivity and nework flcutions issues for different data center, so we comeup with solution each data center we should have local database server which will keep syncing with other server so that application doesnt fail , User data can be updated in any of server and should reflect in every server. Application consists of write/read/delete operations, Current writes each day central server 1million. Only 1/1000 need to be distrubuted acrross servce rest need to be in central server. How can we achive this ? solution needs very much real time data accepting nework lags. Solution Collect all changes in other 49 server into 1 central server(How can we collect data) 49 keeps updating data into local database from central server(Using Repliation Can be done) --Anupam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
Architectural Help
Hi All, I need architectural help for our requirement, We have nearly 50 data centre through out different cities from these data center application connect to central database server currently, there are conectivity and nework flcutions issues for different data center, so we comeup with solution each data center we should have local database server which will keep syncing with other server so that application doesnt fail , User data can be updated in any of server and should reflect in every server. Application consists of write/read/delete operations, Current writes each day central server 1million. Only 1/1000 need to be distrubuted acrross servce rest need to be in central server. How can we achive this ? solution needs very much real time data accepting nework lags. Solution Collect all changes in other 49 server into 1 central server(How can we collect data) 49 keeps updating data into local database from central server(Using Repliation Can be done) --Anupam
Re: Join 2 tables and compare then calculate something
Try out query with UNION also select A,B,C from (select A,B,C from X UNION select A,B,C from Y) group by A,B,C having count(*)>1 From: Gian Karlo C To: mysql@lists.mysql.com Sent: Sunday, 2 October 2011 12:49 PM Subject: Join 2 tables and compare then calculate something Hi All, I decided to join and write to the list hoping someone could help and shed a light on me. Here's the scenario. I have a database running in mysql 5.x in Centos 5. The database has 2 tables that is almost identical with some additional fields. Table 1 Name, IPAddress, Description, Issue, Severity, Timestamp, DateReceived Table 2 Name, IPAddress, Description, Issue, Severity, Timestamp, DataReceived, Owner Here's my SQL statement to compare both tables if fields data are the same then consider it as a valid record. select Table1.Name, Table1.IPAddress, Table1.Description, Table1.Issue, Table1.Severity, Table1.Timestamp FROM Table1 LEFT JOIN Table2 ON Table1.Name = Table2.Name WHERE Table1.Name = Table2.Name AND Table1.IPAddress = Table2.IPAddress AND Table1.Description = Table2.Description AND Table1.Issue = Table2.Issue AND Table1.Severity = Table2.Severity AND Table1.Timestamp = Table2.Timestamp group by 1; I need to compare Name, IPAddress, Description, Issue, Severity and Timestamp to consider as I valid data then I group it so that only one record will show although there is no duplicate entry on the results. Just want to make sure. Using that SQL statement I was able to get and compare data (which I don't know if this is a good approach), now when I get a valid results, I want to compute the DateReceived. Table1 DateReceived = "10:05" Table2 DateReceived = "10:15" So the computation is to get the difference between DateReceived which the result should be 10 minutes. How would I add that computation to my existing SQL statement and maybe someone suggest a good approach with regards to my current statement. Thanks in advance.
Re: replication between two tables in same database
Try out functionality you desire with trigger to replicate data From: Derek Downey To: [MySQL] Sent: Thursday, 29 September 2011 10:46 PM Subject: Re: replication between two tables in same database You could look into the sphinx engine http://sphinxsearch.com/about/sphinx/ No experience with this personally though - Derek On Sep 29, 2011, at 1:07 PM, Tompkins Neil wrote: > Yes, unless I can set-up some sort of replication between the two tables. > > On Thu, Sep 29, 2011 at 6:05 PM, Reindl Harald wrote: > >> please do NOT post off-list! >> >> so your only workaround is like '%whatever%' currently >> >> Am 29.09.2011 19:04, schrieb Tompkins Neil: >> The reason I'm using Innodb is because the usage of foreign keys in short >> however, we need a couple of the tables to support FULLTEXT searching which >> Innodb >> does not support. >> >> >> On Thu, Sep 29, 2011 at 6:01 PM, Reindl Harald wrote: >> >>> >>> >>> Am 29.09.2011 18:56, schrieb Tompkins Neil: Hi I've a Innodb and MyISAM table in the SAME database that I wish to >>> replicate the data between the two because I need to use FULLTEXT searching on the MyISAM table. Is this possible ? If so how do I do it. >>> >>> in short: no >>> >>> in longer: >>> why in the world are you using the wrong engine if you need >>> fulltext-search? >>> mysql 5.6 semmes to start support this in innodb, but currently not >>> >>> replication is based on binlogs and contains database/table so there is >>> no dirty trick do this on one server except let run a script and copy >>> the table-contents per cronjob >>> >>> >> >> -- >> >> Mit besten Grüßen, Reindl Harald >> the lounge interactive design GmbH >> A-1060 Vienna, Hofmühlgasse 17 >> CTO / software-development / cms-solutions >> p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40 >> icq: 154546673, http://www.thelounge.net/ >> http://www.thelounge.net/signature.asc.what.htm >> >> -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=sb_akarmar...@yahoo.com
Schema list
There is one database name 'XYZ' on linux system, some one has mistakenly created new database name with 'xYZ', database is created when I use 'xYZ' database, there are no tables with SHOW TABLES, but when i run SHOW PROCEDURE STATUS it show procedures from 'XYZ' database. My concern is that if i drop new database created 'xYZ' will it drop procedures also listed from 'XYZ' database
Replication Sync Error
Hi All, We are running semi-sync replication on production server, last week found out that replication suck at some event schedule created at master, as that was not important i skipped that started replication again, found out that slave is days behind at master, i invested gate more and found that it is read 200 MB of binary file and few more need to be read by salve get in sync, still i think 200 MB file should not take hours to read, any ways but in error logs i am keep on getting following error 110623 15:50:56 [Note] Semi-sync replication switched OFF. 110623 15:50:56 [Note] Semi-sync replication switched ON with slave (server_id: 2) at (master-bin.31, 15494716) 110623 15:51:02 [Warning] Timeout waiting for reply of binlog (file: master-bin.31, pos: 15505733), semi-sync up to file master-bin.31, position 15505129. can any one help on this. --Anupam
Re: Fastest Select
I am loading data using LOAD DATA as source is csv file. My selection is very simple with like select * from XYZ where key = 123; for 1 million sample record I created innodb table with key, to load data from csv it took nearly 1 and 1/2 hour on modest PC I created MyISAM table with key it took nearly 7 min to load data into table, I created MyISAM table with hash partition it took nearly 1 and 1/2 hour with InooDB and Partition got descent result for select Now if i have to load data feed of 10 million once in week i need to consider loading time also --Anupam --- On Wed, 8/6/11, Johan De Meersman wrote: From: Johan De Meersman Subject: Re: Fastest Select To: "Claudio Nanni" Cc: mysql@lists.mysql.com, "Anupam Karmarkar" Date: Wednesday, 8 June, 2011, 10:53 AM - Original Message - > From: "Claudio Nanni" > > how do you load data? is it naturally ordered in any way? Also, what's the record structure, and what are the criteria you use to check if a record exists? If you only need to know wether a given record exists, your best bet is probably going to be InnoDB with the lookup fields as primary key - assuming they're unique - as that will give you index lookup without additional data block fetch if you request no other fields than those in the index. Caveats always apply, of course, so extra information may be useful. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=sb_akarmar...@yahoo.com
Fastest Select
Hi All, We have very big table with few column contains nearly 10 million records, We need to tune this table for simple select statement where we check record exists in table or not and requirement is response time should be less than 10 million second for nearly 1000 concurrent requests, what is best way to tune this table, table is kind of very static and we load data weekly once only, Which engine is good MyISAM or InnoDB with index ? Will any partitioning on this table help? --Anupam
Re: Help with a query
Hi Aveek, You need to use something like union all and having to get desire result Follow example below select file, digest from ( SELECT file, digest,Count(*) as Cnt FROM A GROUP BY file, digest union all SELECT file, digest,Count(*) as Cnt FROM B GROUP BY file, digest ) tmp group by file, digest HAVING Sum(Cnt) <> (Select sum(refcount) from C WHERE tmp.file = C.file and tmp.digest = C.digest); --Anupam --- On Tue, 17/5/11, Aveek Misra wrote: From: Aveek Misra Subject: Help with a query To: "mysql@lists.mysql.com" Date: Tuesday, 17 May, 2011, 1:23 PM I have a table A and table B of the same type as CREATE TABLE A ( `file` varbinary(255) not null, `digest` binary(40) not null ) CREATE TABLE B ( `file` varbinary(255) not null, `digest` binary(40) not null ) I have another table C of the following type CREATE TABLE C ( `file` varbinary(255) not null, `digest` binary(40) not null, `refcount` bigint(20) not null ) I need to write a query where for the same file and digest in table A and table B, the refcount is table C is not the same. So: SELECT COUNT(*) as count 1 FROM A WHERE file='file1' AND digest='digest1'; SELECT COUNT(*) as count 2 FROM B WHERE file='file1' AND digest='digest1'; and then adding up the two counts from these queries and comparing it with the result of the following query: SELECT refcount FROM C WHERE file='file1' AND digest='digest1'; basically (refcount == (count1 + count2)) should be true and I am interested in finding out all such records in table C where this is not the case. Thanks Aveek -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=sb_akarmar...@yahoo.com
RE: Complex Query
Hi Guido, You need to add group by in your query get desire result SELECT IFNULL(image,'no_image.jpg') as image,title, subtitle, group_concat(concat(fname,' ',initial,' ',lname)) as author, pkisbn, publisher FROM book INNER JOIN publisher on pkpublisher_id = fkpublisher_id INNER JOIN module_book on    pkisbn = fkbook_isbn INNER JOIN module on fkmodule_nr = module_nr INNER JOIN book_author on pkisbn = fkisbn INNER JOIN author on fkauthor_id = pkauthor_id WHERE module_nr = 'MSING0010' group by IFNULL(image,'no_image.jpg') as image,title, subtitle, pkisbn, publisher It should give desire result. As group concat will return group by 1 if you dont specify group by, which will return only one row, to get result you need to specify group by as given in above query. --Anupam --- On Fri, 20/5/11, Mimi Cafe wrote: From: Mimi Cafe Subject: RE: Complex Query To: "'Guido Schlenke'" , mysql@lists.mysql.com Date: Friday, 20 May, 2011, 5:07 AM Hi Guido I like your powerful query below, but it doesn't answer my question. I guess some clarification is needed here. OK, I have books in the database with more than one author. When I query for the title of books and name of author, I get more rows for books that have more than one author. I need one row per book even if there is more than one author (concatenate the names of all the authors as authors). >From the tables below, it can be seen that the book titled "Technology >Ventures: From Idea to Enterprise" is written by three authors. I want to get >the record for the book, including the names of all three authors in one row. For my purchase, I got as far retrieving all the information I need (except the names of authors) using the following query: mysql> SELECT IFNULL(image,'no_image.jpg') as image,title, subtitle, concat(fname,' ',initial,' ',lname) as author, pkisbn, publisher FROM book INNER JOIN publisher on pkpublisher_id = fkpublisher_id INNER JOIN module_book on    pkisbn = fkbook_isbn INNER JOIN module on fkmodule_nr = module_nr INNER JOIN book_author on pkisbn = fkisbn INNER JOIN author on fkauthor_id = pkauthor_id    WHERE module_nr = 'MSING0010'; +--+--+--+---+---++ | image    | title                  | subtitle | author    | pkisbn    | publisher       | +--+--+--+---+---++ | no_image.jpg | Financial Intelligence for Entrepreneurs |     | Karen Berman | 9781422119150 | Harvard Business Press | | no_image.jpg | Financial Intelligence for Entrepreneurs |     | Joe Knight   | 9781422119150 | Harvard Business Press | +--+--+--+---+---++ In the above example, I get 2 correct rows, but I want to have one row for this, with the names of both authors concatenated separated by comma. Using GROUP_CONCAT, I am able to do just that, but it looks as if my query isn't optimise or it's wrong as I don't get all the expected rows. # Looks like this works, but as you can see below, it doesn't. mysql> SELECT IFNULL(image,'no_image.jpg') as image,title, subtitle, group_concat(concat(fname,' ',initial,' ',lname)) as author, pkisbn, publisher FROM book INNER JOIN publisher on pkpublisher_id = fkpublisher_id INNER JOIN module_book on    pkisbn = fkbook_isbn INNER JOIN module on fkmodule_nr = module_nr INNER JOIN book_author on pkisbn = fkisbn INNER JOIN author on fkauthor_id = pkauthor_id WHERE module_nr = 'MSING0010'; +--+--+--+---+---++ | image    | title                  | subtitle | author          | pkisbn    | publisher       | +--+--+--+---+---++ | no_image.jpg | Financial Intelligence for Entrepreneurs |     | Karen Berman,Joe Knight | 9781422119150 | Harvard Business Press | +--+--+--+---+---++ 1 row in set (0.00 sec) With the WHERE clause above, I get the correct record, but below, I remove the condition (hoping to get all the books), but instead, I get one row only. Why is this? # Now is doesn't work. mysql> SELECT IFNULL(image,'no_image.jpg') as image,title, subtitle, group_concat(concat(fname,' ',initial,' ',lname)) as authors, pkisbn, publisher FROM book INNER JOIN publisher on pkpublisher_id = fkpublisher_id INNER JOIN
Re: innodb system variable
Hi All innodb_rollback_on_timeout=1 Specifies when there is transaction open by session and not committed, If such session is inactive for long time, MySQL by default kicks out such session and transaction perform by session would be rollback innodb_lock_wait_timeout=600 Specify wait for lock on row up to provided seconds, if it is time out, it not try to process transaction further, but roll back will not happen, You need to start you transaction from point it was timeout, else need to be rollback explicitly and re run transaction or wait till session timeout and re run transaction --Anupam K From: Mohan L To: mysql@lists.mysql.com Sent: Tue, 29 March, 2011 12:54:12 PM Subject: innodb system variable Dear All, I have the following two system variable set in my MySQL configuration file under mysqld section. But I am not fully understand what the two variable internally does. innodb_rollback_on_timeout=1 innodb_lock_wait_timeout=600 Any help will be appreciated. Thanks for Your Time Mohan L
