[Stored Procedure] - Error handling
Hello folks, Any way to retrieve the error code/error number from a stored proc. Scenario -- calling a stored proc from PHP - using mysqli_multi_query() The stored proc has multiple queries. Lets say one of the queries generates an exception. How do I retrieve the error message within the procedure itself? OR Is there any way from PHP to get the last error msg? I tried with mysqli_error().. Did not work.. Any inputs will be appriciated. Thanks, Ratheesh
Replication - urgent
Hello All,
I set up replication between 2 servers recently. I just need one db to be
replicated and the SHOW SLAVE STATUS shows this:
Relay_Master_Log_File: gyana01-bin.02
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB: tallydb,tallydb
Now the problem is that there is another db named tallydbopextblob and the
tables from this db are also getting replicated as shown in the below line.
Last_Error: Error 'Table 'tallydbopextblob.TBL_EVAL_PITEM_OP_EXT_DETAILS'
doesn't exist' on query. Default database: 'tallydb'. Query: 'INSERT INTO
tallydbopextblob.TBL_EVAL_PITEM_OP_EXT_DETAILS(
FLD_OP_INPUT_DATA,FLD_OP_INDEX_DATA1,FLD_OP_INSTANCE_ID)
VALUES('type=FPCONNECTsource=TALLYrequest_type=ACTIVATIONoffline_flag=0serial=372123675major_version=7minor_version=2major_release=3minor_release=14build_num=rel7.2_3.14_2007-05-21_19.33platform=WINos=WINuser_name=serverhost_name=SERVERlic_ver=2fingerprint63=1mQ0nE8HkaBlNRg==aa,3GAwmgzLMbCGNJyMyaa,3GAwmgzLMbCGNJyMyaa,2NDkxODk0MjUyAA==finger_print=5qZEKE5HgchxNpnOZ3GyZaaserver_id=0activation_code
...
Why is this happening?
Thanks regards,
Ratheesh
Replication - urgent
Hello All, I need to add a couple of more tables to the list of tables to be replicated from a particular db in the salve my.cnf. Can this be done without restarting the mysql server on the slave? Thanks regards, Ratheesh
Replication - urgent
Hello all, I cannot afford to stop my slave server. I have list of tables of the master that are being replicated on the slave. Now I want to remove a couple of tables from this list without affecting the master and slave. How is this possible? Thanks regards, Ratheesh
[Replication] - load
@all, Currently we run all our complex reporting queries on a different server. We are not using replication though. What we are doing is to restore the backup of the live data every night onto the reporting server. So we are running reports on data that is not real time. And we are OK with that. We made this setup just to share the load between the two DB servers. SO on the reporting DB server there will be only select queries running. And so all the queries will be served from the Query cache and hence there is a big performance gain. Any updates that happen is only during the night when the backup from DB server 1 is restored onto the DB server 2 (Reporting DB server). So every morning all the queries will be slow for the first time on the DB server 2 and thereafter will be served by the query cache as they will be cached and never invalidated until the night. Now suppose we enable real time replication between DB server 1 and DB server 2 then: 1) Will our aim of load balancing be hit because there will be no overall performance gain as still INSERTS, UPDATES and DELETES will continue to run on DB server 2 due to replication ? i.e the load of the DML statements will still continue to be there. 2) Is this notion correct that Replication will provide a performance boost considering the context. Isnt the load same as when there was no replication? 3) And the query cache will get invalidated on DB server 2 when there is real time replication. So isnt it another perormance hit? These questions are just to get an hint of the performance benfit due to replication because conceptually I feel that there is still the same amount of load. On one hand I know that replication is not for load balancing ( am I right? ) and on the other hand I am doubting why Replication? if the load is same. Pleasecorrect me if I am wrong Thanks regards, Ratheesh
Re: [Replication] - urgent
Thanks, It helped me a lot. I wanted to know 1.. what are the various scenarios where my replication setup can fail? (considering even issues like network failure and server reboot etc). What is the normal procedure to correct the failure when something unpredicted happens? 2.. What are the scenarios where the SQL THREAD stops running and what are the scenarios where the IO THREAD stops running? 3.. Does SQL_SLAVE_SKIP_COUNTER skip the statement of the master binlog from being replicated to the slave relay log OR Has the statement already been copied into the slave relay log and has been skipped from the relay log? 4.. How do I know immediately that replication has failed? ( have heard that the enterprise edition has some technique for this )? Thanks regards, Ratheesh - Original Message - From: Jan Kirchhoff [EMAIL PROTECTED] To: Ratheesh K J [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, October 02, 2007 4:16 PM Subject: Re: [Replication] - urgent Ratheesh K J schrieb: Hello all, I issued a create table statement on the master for a table which was not present on the master but present on the slave. I did this purposely to see the error on slave. I am a newbie to replication. Now when i see SLave status on the slave machine it shows that the SQL Thread has stopped. When I start the SQL thread it does not start and gives the error message that the table exists. How do i correct this and how do I calculate the next position that the slave must start executing from the relay log. Is there any article on MySQL replication that tells me how to deal when errors occur. Thanks regards, Ratheesh You have 2 options: 1. on the slave, enter SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; and then SLAVE START; on the slave. This skips the upcoming entry in the binlog which is the create table command that causes your problem. 2. if you don't have any data in the table on the slave, just drop the table and do a slave start;, it will then create the table again as this is the next command in the binlog. Remember: never write on the slave without knowing what you do and you'll be happy with your replication ;) Jan
MySQL Configuration for a powerful server?
Hello all, What is the best possible values in my.cnf for a 8 processor (Quad core-2 cpu) 8 GB RAM machine dedicated for MySQL server only. No other application will run on this machine. the innodb_buffer_pool_size cannot accept values above 2000 MB due to 32 bit machine constraint. So what other parameters can be tweaked to make use of this powerful server to its best? NOTE: All our tables are of INNODB storage engine.
[Replication] - urgent
Hello all, I issued a create table statement on the master for a table which was not present on the master but present on the slave. I did this purposely to see the error on slave. I am a newbie to replication. Now when i see SLave status on the slave machine it shows that the SQL Thread has stopped. When I start the SQL thread it does not start and gives the error message that the table exists. How do i correct this and how do I calculate the next position that the slave must start executing from the relay log. Is there any article on MySQL replication that tells me how to deal when errors occur. Thanks regards, Ratheesh
Foreign key constraints - Known issues ?
Hello All, I just wanted to know whether there are any known issues in defining and using Foreign key constraints in MySQL 4 and MySQL 5. To be specific, are there any issues on using ON DELETE CASCADE and ON UPDATE CASCADE? Would there be any performance issues when we define Foreign key constraints? We dont define Foreign Key constraints here. But at the conceptual level we know that there are foreign keys. But we do not create foreign keys at the physical level. Is this right? Thanks Regards, Ratheesh
Blob data
Hello All, I want a clarification. Whe run a forum wherein people send messages with/without attachments. Attachments may contain images, documents etc.. We are actually storing the attachment in a blob column. Sometimes the attachments are big. And today the table size has grown to 40 GB. This has created a headache for any maintanance task, backup, restoration. etc. I want to know whether this is the right approach. Or should we actually store the attachments in directories and just stiore the attachment path in the database. Kindly suggest the best approach so that I can reduce the database size. Thanks in advance
Re: Data security - help required
Ok.. Will it be secure if the data is encrypted. mysqldump will show encrypted data right. Actually I want to know what is the best practice for such applications. Can I say that encryption alone is sufficient to secure my data. Or is there any other strategy used for data protection? - Original Message - From: Chris [EMAIL PROTECTED] To: Ratheesh K J [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, May 15, 2007 2:42 PM Subject: Re: Data security - help required Ratheesh K J wrote: Hello all, I have a requirement of maintaining some secret information in the database. And this information should not be visible/accessible to any other person but the owner of the data. Whilst I know that encryption/decryption is the solution for this, are there any other level of security that I can provide to this? Which is the best security technique used in MySQL to store seceret information. PS: Even the database admin should not be able to access anybody else's information Then you're stuffed - *someone* has to be able to see everything so you can do a mysqldump. *Someone* has to be able to see everything so you can grant permissions to the other users too :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[urgent] - Problem with index_merge
; - *** row 1 *** table: TFMM type: range possible_keys: PRIMARY key: PRIMARY key_len: 4, ref: NULL rows: 1059133 Extra: Using where - Thanks Ratheesh K J
Urgent - MySQL 5 - mysqld using a lot of memory consistently
Hello all, Recently we upgraded from MySQL 4.1.11 to MySQL 5.0.22. The queries are taking a lot of time to execute in the newer version. The queries which were executing within 10 secs are now taking more than 100 secs. Running an expalin on the queries showed that an index_merge optimization is being used which is a new concept in MySQL 5. My initial doubt was on this but now when I checked top it shows that mysqld is consistently using 59% of Memory and 25% of cpu even when there is no load. the SHOW STATUS command in mysql shows: Threads_created21863 Threads_cached1 Threads_connected38 Connections5784350 Running a SHOW VARIABLES shows: thread_cache_size8 It is evident that mysqld is creating a lots of threads... Could this be the problem? Thanks, Ratheesh K J
Re: Urgent - MySQL 5 - mysqld using a lot of memory consistently
Thanks, By how much should I be increasing the thread_cache? currently it is 8... Currently I can provide the EXPLAIN result of a query using index_merge on MySQL 5.0.22. EXPLAIN SELECT IFNULL(TAEM.FLD_FULL_NAME, TFMM.FLD_ASSIGNED_TO) AS ELE1 , TFMM.FLD_ASSIGNED_TO AS KEY_ID, SUM(1) AS ELE2, SUM( IF(TFMM.FLD_ESCALATED_FLAG= 0 , 1 , 0) ) AS ELE3, SUM( IF(TFMM.FLD_ESCALATED_FLAG = 0 AND ( TFMM.FLD_ISSUE_CLOSED IN ( 2, 4,5,1 ) ) , 1, 0) ) AS ELE4, SUM( IF(TFMM.FLD_ESCALATED_FLAG = 0 AND TFMM.FLD_ISSUE_CLOSED = 3, 1, 0) ) AS ELE5, SUM( IF(TFMM.FLD_ESCALATED_FLAG = 1 , 1, 0) ) AS ELE6,SUM( IF(TFMM.FLD_ESCALATED_FLAG = 1 AND ( TFMM.FLD_ISSUE_CLOSED IN ( 2, 4,5,1 ) ) , 1, 0) ) AS ELE7, SUM( IF(TFMM.FLD_ESCALATED_FLAG = 1 AND TFMM.FLD_ISSUE_CLOSED = 3, 1, 0) ) AS ELE8, ROUND(( SUM( IF(TFMM.FLD_ESCALATED_FLAG = 1 , 1, 0) )/SUM(1)) * 100 ,2 ) AS ELE9 FROM TBL_FORUMS_MSG_MAIN TFMM LEFT JOIN TBL_ADMIN_EMP_MASTER TAEM ON TAEM.FLD_EMP_ID = TFMM.FLD_ASSIGNED_TO INNER JOIN TBL_FORUMS_MSG_OP_TRACK TFMOT ON ( TFMM.FLD_MSG_ID=TFMOT.FLD_MSG_ID AND TFMOT.FLD_OP_ID=15 AND TFMOT.FLD_LAST_FLAG=1 ) WHERE TFMM.FLD_ACC_ID IN ( 6, 375 ) AND TFMM.FLD_MARK_AS_DELETED = 0 AND TFMM.FLD_BOUNCED_MAIL_FLAG = 0 AND TFMM.FLD_BLOCK_STATE = 0 AND TFMM.FLD_PARENT_ID = 0 AND TFMM.FLD_ASSIGNED_TO IN ( 935,805,563,543,1352,670,571,530,655,577,355,885,392,155,1579,693,1577,509,199,770,1535,78,54,993,594,557,132,859,99,1557,645,527,79,181,1520,200,1350,1534,1591,545,70,191,1550,189,726,40,228,97,196,860,303,1321,394,363,1412,597,1013,1377,1250,1299,3,301,756,170,1553,1578,1343,953,593,250,600,1552,494,311,146,664,589,631,495,4,1254,678,511,931,1020,410,592,822,933,1531,1507,858,453,1257,555,897,352,188,546,1544,291,1529,370,765,963,356,1303,1328,354,414,1581,1030,382,1356,1521,227,396,1333,591,1249,760,1334,1034,51,80,1276,794,145,295,934,544,165,1594,886,929,558,685,880,831,1592,882,320,566,174,796,1593,5,1361,1522,435,388,951,1362,369,806,20,1336,330,77,907,754,507,1330,1364,1,202,1501,289,1296,1378,1061,1500,952,1439,1369,1358,373,1548,294,338,30,1351,1575,728,207,1558,406,837,210,970,620,387,450,1586,38,1227,460,455,1347,841,386,318,130,492,961,590,229,463,284,1380,1580,422,362,1337,581,1490,568,950,1083,960,1329,825,532,404,936,1251,552,1089,1585,1225,708,1564,817,260,372,965,305,456,847,192,1465,962,1523,1590,745,180,1540,753,585,890,1537,1099,225,750,230,7,413,1554,578,572,820,1549,883,810,1105,1403,423,1524,969,542,286,797,1582,1301,384,930,308,854,742,1107,1108,1555,1338,1562,947,673,1506,417,236,798,1576,63,27,1210,371,1485,82,272,1274,529,1277,381,1342,689,185,1118,235,1120,37,598,724,205,946,203,608,405,610,19,958,126,307,967,1360,218,954,1525,891,116,135,75,715,1547,431,1138,879,1498,211,1140,1463,6,1528,344,956,595,91,826,1145,1584,1545,1258,443,643,632,1526,267,1530,58,945,314,1470,763,1491,1595,968,385,955,282,684,179,178,666,409,663,390,1447,1341,1546,1587,125,358,173,279,957,50,182,840,107,580,807,133,1248,892,690,513,898,365,821,325,669,121,62,827,106,219,1253,633,41,562,1489,162,101,861,561,839,153,1565,1583,395,447,217,1551,888,1574,1176,743,446,556,787,263,949,1178,1331,209,134,1505,1354,55,306,31,964,1348,850,1252,862,966,100,521,175,709,1542,942,1335,297,445,296,411,525,420,266,102,1559,418,438,109,661,804,662,1543,984,1556,1409,522,195,1471,439,341,1209,878,838,1464,881,271,36,83,1379,857,944,656,959,538,2,764,1588,672,520,503,531,462,729,528,204,201,93,677,564,426,606,855,234,676,889) AND TFMOT.FLD_OP_DATE_TIME BETWEEN '2007-01-19 00:00:00' AND '2007-01-23 23:59:59' AND TFMM.FLD_MEDIUM IN ( 1 ) AND TFMM.FLD_MSG_ID 0 GROUP BY IFNULL(TAEM.FLD_FULL_NAME, TFMM.FLD_ASSIGNED_TO) ORDER BY IFNULL(TAEM.FLD_FULL_NAME, TFMM.FLD_ASSIGNED_TO): *** row 1 *** table: TFMM type: index_merge possible_keys: PRIMARY,FLD_MEDIUM,FLD_PARENT_ID,FLD_ASSIGNED_TO,FLD_MARK_AS_DELETED,FLD_ACC_ID,FLD_BLOCK_STATE,FLD_BOUNCED_MAIL_FLAG key: FLD_BLOCK_STATE,FLD_MEDIUM,FLD_MARK_AS_DELETED,FLD_BOUNCED_MAIL_FLAG,FLD_PARENT_ID key_len: 2,1,2,2,4 ref: NULL rows: 34468 Extra: Using intersect(FLD_BLOCK_STATE,FLD_MEDIUM,FLD_MARK_AS_DELETED,FLD_BOUNCED_MAIL_FLAG,FLD_PARENT_ID); Using where; Using temporary; Using filesort *** row 2 *** table: TAEM type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: tallydb.TFMM.FLD_ASSIGNED_TO rows: 1 Extra: NULL *** row 3 *** table: TFMOT type: ref possible_keys: FLD_MSG_ID,FLD_OP_ID,FLD_OP_DATE_TIME,FLD_LAST_FLAG key: FLD_MSG_ID key_len: 4 ref: tallydb.TFMM.FLD_MSG_ID rows: 1 Extra: Using where - Original Message - From: Alex Arul To: Ratheesh K J Sent: Tuesday, January 23, 2007 11:57 AM Subject: Re: Urgent - MySQL 5 - mysqld using a lot of memory consistently you threads connected is 38 but your thread cache has
Re: Urgent - MySQL 5 - mysqld using a lot of memory consistently
J Cc: mysql@lists.mysql.com Sent: Tuesday, January 23, 2007 12:20 PM Subject: Re: Urgent - MySQL 5 - mysqld using a lot of memory consistently the monitor the threads-connected variable over a period of time and calcuate the value. you can even look at max used connections status variable and allot accordingly. BTW, is you application using demand based connections or connection pooling ? If it is using connection pooling then bumping thread-cache might not help. Please provide create table statement of the tables in question also. Thanks Alex On 1/23/07, Ratheesh K J [EMAIL PROTECTED] wrote: Thanks, By how much should I be increasing the thread_cache? currently it is 8... Currently I can provide the EXPLAIN result of a query using index_merge on MySQL 5.0.22. EXPLAIN SELECT IFNULL(TAEM.FLD_FULL_NAME, TFMM.FLD_ASSIGNED_TO) AS ELE1 , TFMM.FLD_ASSIGNED_TO AS KEY_ID, SUM(1) AS ELE2, SUM( IF(TFMM.FLD_ESCALATED_FLAG= 0 , 1 , 0) ) AS ELE3, SUM( IF(TFMM.FLD_ESCALATED_FLAG = 0 AND ( TFMM.FLD_ISSUE_CLOSED IN ( 2, 4,5,1 ) ) , 1, 0) ) AS ELE4, SUM( IF(TFMM.FLD_ESCALATED_FLAG = 0 AND TFMM.FLD_ISSUE_CLOSED = 3, 1, 0) ) AS ELE5, SUM( IF(TFMM.FLD_ESCALATED_FLAG = 1 , 1, 0) ) AS ELE6,SUM( IF(TFMM.FLD_ESCALATED_FLAG = 1 AND ( TFMM.FLD_ISSUE_CLOSED IN ( 2, 4,5,1 ) ) , 1, 0) ) AS ELE7, SUM( IF(TFMM.FLD_ESCALATED_FLAG = 1 AND TFMM.FLD_ISSUE_CLOSED = 3, 1, 0) ) AS ELE8, ROUND(( SUM( IF(TFMM.FLD_ESCALATED_FLAG = 1 , 1, 0) )/SUM(1)) * 100 ,2 ) AS ELE9 FROM TBL_FORUMS_MSG_MAIN TFMM LEFT JOIN TBL_ADMIN_EMP_MASTER TAEM ON TAEM.FLD_EMP_ID = TFMM.FLD_ASSIGNED_TO INNER JOIN TBL_FORUMS_MSG_OP_TRACK TFMOT ON ( TFMM.FLD_MSG_ID=TFMOT.FLD_MSG_ID AND TFMOT.FLD_OP_ID=15 AND TFMOT.FLD_LAST_FLAG=1 ) WHERE TFMM.FLD_ACC_ID IN ( 6, 375 ) AND TFMM.FLD_MARK_AS_DELETED = 0 AND TFMM.FLD_BOUNCED_MAIL_FLAG = 0 AND TFMM.FLD_BLOCK_STATE = 0 AND TFMM.FLD_PARENT_ID = 0 AND TFMM.FLD_ASSIGNED_TO IN ( 935,805,563,543,1352,670,571,530,655,577,355,885,392,155,1579,693,1577,509,199,770,1535,78,54,993,594,557,132,859,99,1557,645,527,79,181,1520,200,1350,1534,1591,545,70,191,1550,189,726,40,228,97,196,860,303,1321,394,363,1412,597,1013,1377,1250,1299,3,301,756,170,1553,1578,1343,953,593,250,600,1552,494,311,146,664,589,631,495,4,1254,678,511,931,1020,410,592,822,933,1531,1507,858,453,1257,555,897,352,188,546,1544,291,1529,370,765,963,356,1303,1328,354,414,1581,1030,382,1356,1521,227,396,1333,591,1249,760,1334,1034,51,80,1276,794,145,295,934,544,165,1594,886,929,558,685,880,831,1592,882,320,566,174,796,1593,5,1361,1522,435,388,951,1362,369,806,20,1336,330,77,907,754,507,1330,1364,1,202,1501,289,1296,1378,1061,1500,952,1439,1369,1358,373,1548,294,338,30,1351,1575,728,207,1558,406,837,210,970,620,387,450,1586,38,1227,460,455,1347,841,386,318,130,492,961,590,229,463,284,1380,1580,422,362,1337,581,1490,568,950,1083,960,1329,825,532,404,936,1251,552,1089,1585,1225,708,1564,817,260,372,965,305,456,847,192,1465,962,1523,1590,745,180,1540,753,585,890,1537,1099,225,750,230,7,413,1554,578,572,820,1549,883,810,1105,1403,423,1524,969,542,286,797,1582,1301,384,930,308,854,742,1107,1108,1555,1338,1562,947,673,1506,417,236,798,1576,63,27,1210,371,1485,82,272,1274,529,1277,381,1342,689,185,1118,235,1120,37,598,724,205,946,203,608,405,610,19,958,126,307,967,1360,218,954,1525,891,116,135,75,715,1547,431,1138,879,1498,211,1140,1463,6,1528,344,956,595,91,826,1145,1584,1545,1258,443,643,632,1526,267,1530,58,945,314,1470,763,1491,1595,968,385,955,282,684,179,178,666,409,663,390,1447,1341,1546,1587,125,358,173,279,957,50,182,840,107,580,807,133,1248,892,690,513,898,365,821,325,669,121,62,827,106,219,1253,633,41,562,1489,162,101,861,561,839,153,1565,1583,395,447,217,1551,888,1574,1176,743,446,556,787,263,949,1178,1331,209,134,1505,1354,55,306,31,964,1348,850,1252,862,966,100,521,175,709,1542,942,1335,297,445,296,411,525,420,266,102,1559,418,438,109,661,804,662,1543,984,1556,1409,522,195,1471,439,341,1209,878,838,1464,881,271,36,83,1379,857,944,656,959,538,2,764,1588,672,520,503,531,462,729,528,204,201,93,677,564,426,606,855,234,676,889) AND TFMOT.FLD_OP_DATE_TIME BETWEEN '2007-01-19 00:00:00' AND '2007-01-23 23:59:59' AND TFMM.FLD_MEDIUM IN ( 1 ) AND TFMM.FLD_MSG_ID 0 GROUP BY IFNULL(TAEM.FLD_FULL_NAME, TFMM.FLD_ASSIGNED_TO) ORDER BY IFNULL(TAEM.FLD_FULL_NAME, TFMM.FLD_ASSIGNED_TO): *** row 1 *** table: TFMM type: index_merge possible_keys: PRIMARY,FLD_MEDIUM,FLD_PARENT_ID,FLD_ASSIGNED_TO,FLD_MARK_AS_DELETED,FLD_ACC_ID,FLD_BLOCK_STATE,FLD_BOUNCED_MAIL_FLAG key: FLD_BLOCK_STATE,FLD_MEDIUM,FLD_MARK_AS_DELETED,FLD_BOUNCED_MAIL_FLAG,FLD_PARENT_ID key_len: 2,1,2,2,4 ref: NULL rows: 34468 Extra: Using intersect(FLD_BLOCK_STATE,FLD_MEDIUM,FLD_MARK_AS_DELETED,FLD_BOUNCED_MAIL_FLAG,FLD_PARENT_ID); Using where; Using temporary; Using filesort *** row 2 *** table: TAEM type
Index_Merge : Very slow
Hello All, Our queries were running fine on MySQL 4.1.11. Since we upgraded to MySQL 5.0.22 The same queries are taking a long long time to execute. Running an explain on the queries shows an index_merge in the type column. And it shows using intersect algorithm in Extra column of the output. Previously(MySQL 4.1.11) Explain showed the usage of primary Key as the index. How can this be resolved? Thanks Ratheesh K J
Re: Innodb log sequence error - urgent
Thanks, I have the previous ib_log* files on the app server. And every thing on the cnf file was perfect. Only the ib_log file's size was a mismatch. Whats the best work around? Can I copy the log files of the App server to the DB server and change the innodb_log_file_size to 256M and then restart the MySQL server. If I do so will I lose the updates to the database that happened today? So my actual problem is this: I have two sets of ib_logfile* files. To be particular there are a.. ib_logfile0, ib_logfile1, ib_logfile2 on the App server-each 257M (when i did a du -sh). In the my.cnf file of the App server innodb_log_file_size is set to 256M b.. ib_logfile0, ib_logfile1, ib_logfile2 on the DB server -each 5M. These log files were created freshly by the MySQL server as the log files from the App server was not copied to the DB server. In the my.cnf file of the DB server innodb_log_file_size is set to 5M by mistake. All the other settings were same as on the app server. The ibdata1 file is that of the App server. And I get the log sequence errors as shown in my previous post. But everything seems to be working fine. There have been no problems accessing the data. What I can I possibly do to get everything right. How can I correct the log sequence error? Should the log files of App server be in the DB server? Thanks, Ratheesh K J - Original Message - From: Jan Kirchhoff [EMAIL PROTECTED] To: Ratheesh K J [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Monday, December 11, 2006 1:25 PM Subject: Re: Innodb log sequence error - urgent Ratheesh K J schrieb: Hello all, yesterday we seperated our app server and db server. We moved our 70GB of data from our app server to a new DB server. We installed MySQL 4.1.11 on the DB server. Now the following happened. On the DB server the ibdata1 and all the databases are the old ones (which were copied from the app server). But when Mysql was installed the ib_logfile0 ib_logfile1 and ib_logfile2 were created freshly on the DB serever. Each of these log files were created with 5M size. on the app server these files were 256M in size (innodb_log_file_size = 256M). On the DB server it is (innodb_log_file_size = 5M). Today morning when I checked the error log, there seems to be a lot of error msg flowing in. 061211 11:41:47 InnoDB: Error: page 203046 log sequence number 87 3002891543 InnoDB: is in the future! Current system log sequence number 86 4025048037. InnoDB: Your database may be corrupt. You cannot just copy innodb-databases to other servers without adjusting your my.cnf: Once you created an innodb-database, you cannot change parameters like innodb_log_file_size any more. (this is explained in the manual, you should read the chapter about backing up and restoring innodb-databases) So when you copy the database to the new server, be sure to copy the settings from the my.cnf, too! Jan
Innodb log sequence error - urgent
Hello all, yesterday we seperated our app server and db server. We moved our 70GB of data from our app server to a new DB server. We installed MySQL 4.1.11 on the DB server. Now the following happened. On the DB server the ibdata1 and all the databases are the old ones (which were copied from the app server). But when Mysql was installed the ib_logfile0 ib_logfile1 and ib_logfile2 were created freshly on the DB serever. Each of these log files were created with 5M size. on the app server these files were 256M in size (innodb_log_file_size = 256M). On the DB server it is (innodb_log_file_size = 5M). Today morning when I checked the error log, there seems to be a lot of error msg flowing in. 061211 11:41:47 InnoDB: Error: page 203046 log sequence number 87 3002891543 InnoDB: is in the future! Current system log sequence number 86 4025048037. InnoDB: Your database may be corrupt. 061211 11:41:48 InnoDB: Error: page 90766 log sequence number 87 2007657570 InnoDB: is in the future! Current system log sequence number 86 4025048133. InnoDB: Your database may be corrupt. 061211 11:41:48 InnoDB: Error: page 101643 log sequence number 87 1555755135 InnoDB: is in the future! Current system log sequence number 86 4025048213. InnoDB: Your database may be corrupt. 061211 11:41:48 InnoDB: Error: page 126123 log sequence number 87 2434816015 InnoDB: is in the future! Current system log sequence number 86 4025048253. InnoDB: Your database may be corrupt. 061211 11:41:48 InnoDB: Error: page 91391 log sequence number 87 3435504059 InnoDB: is in the future! Current system log sequence number 86 4025048310. InnoDB: Your database may be corrupt. 061211 11:41:49 InnoDB: Error: page 7520 log sequence number 87 558983226 InnoDB: is in the future! Current system log sequence number 86 4025049185. InnoDB: Your database may be corrupt. 061211 11:41:49 InnoDB: Error: page 70232 log sequence number 87 3176686221 InnoDB: is in the future! Current system log sequence number 86 4025049185. InnoDB: Your database may be corrupt. 061211 11:41:49 InnoDB: Error: page 9339 log sequence number 87 3426386305 InnoDB: is in the future! Current system log sequence number 86 4025051173. InnoDB: Your database may be corrupt. Its not affecting the database as such till now. 1. What is the actual problem? 2. What is the possible work around?
Which to download
Hello all, Just wanted to know what is the difference between Red Hat Enterprise Linux 3 RPM (x86) downloads and Linux x86 generic RPM (dynamically linked) downloads Which one should I download for a RHEL 3 system? Thanks, Ratheesh Bhat K J
Re: Which to download
Hi Jacques, Thanks for your response. I can see the NDB cluster storage engine rpms under the Linux x86 generic RPM (dynamically linked) downloads but not under the Red Hat Enterprise Linux 3 RPM (x86) downloads. What If the same is needed for our system (later perhaps)? What exactly is the difference between the two set of rpms? Where (which system) are the Linux x86 generic RPM (dynamically linked) rpms installed then? Thanks, Ratheesh K J - Original Message - From: Jacques Marneweck [EMAIL PROTECTED] To: Ratheesh K J [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Thursday, October 19, 2006 3:16 PM Subject: Re: Which to download Ratheesh K J wrote: Hello all, Just wanted to know what is the difference between Red Hat Enterprise Linux 3 RPM (x86) downloads and Linux x86 generic RPM (dynamically linked) downloads Which one should I download for a RHEL 3 system? Hi Ratheesh, Use the RHEL3 one. Regards --jm Thanks, Ratheesh Bhat K J -- Jacques Marneweck http://www.powertrip.co.za/ http://www.powertrip.co.za/blog/ #include std/disclaimer.h
Mysql Stat - Help required
Hlo, I got this Stat of MySQL. I want to know is there something to worry about, especially the number of temp tables. Key Reads/Key Read Requests = 0.007094 (Cache hit = 99.992906%) Key Writes/Key Write Requests = 0.239130 Connections/second = 8.741 (/hour = 31467.279) KB received/second = 0.730 (/hour = 2628.283) KB sent/second = 1.795 (/hour = 6460.812) Temporary Tables Created/second = 0.914 (/hour = 3290.991) Opened Tables/second = 0.616 (/hour = 2217.483) Slow Queries/second = 0.014 (/hour = 48.639) % of slow queries = 0.032% Queries/second = 41.838 (/hour = 150618.094) We have the tmp_table_size variable set to 64M. And we saw that there are many temp tables created on disk in about 3 - 5 seconds. How do we analyze the optimum value for the tmp_table_size variable? Thanks, Ratheesh Bhat K J
Re: Mysql Stat - Help required
I have checked the queries. They look quite ok. We have a lot lot of CREATE TEMPORARY TABLE running every second. How else can tmp tables be created? What is the exact relation between missing index and tmp tables being created. Do you mean to say in sorting? - Original Message - From: Chris [EMAIL PROTECTED] To: Ratheesh K J [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Wednesday, September 27, 2006 12:50 PM Subject: Re: Mysql Stat - Help required Ratheesh K J wrote: Hlo, I got this Stat of MySQL. I want to know is there something to worry about, especially the number of temp tables. That looks like a sign of missing indexes. Temporary tables shouldn't be created that often if your queries are indexed properly, even though they're not showing up as slow queries. It'll be a pain but you could write a script to go through your queries and 'explain' then and see which ones are using temp tables and see whether they need additional indexes. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
key_buffer_size - need help
Hlo, We have all our tables as Innodb type. As I understand the variable key_buffer_size is used only for MyISAM tables. Currently we have this var set to 256M on a 4GB RAM machine. Only the Temporary tables created using (CREATE TEMPORARY TABLE) will be of MyISAM type. And there are a lot of temporary tables being created in our system. What is an optimal value to the key_buffer_size variable in such a case? Ratheesh Bhat K J
Moving database to another machine
Hlo all, I wanted to move around 50 GB of data on Machine A to Machine B. Both the machines are of same architecture ( LAMP ) Dumping and restoring takes a lot of time. Is there a faster method to accomplish the same? Is there a way to tar the whole thing and untar on Machine B? or any other way? Ratheesh Bhat K J
Moving database to another machine
Hlo all, I wanted to move around 50 GB of data on Machine A to Machine B. Both the machines are of same architecture ( LAMP ) Dumping and restoring takes a lot of time. Is there a faster method to accomplish the same? Is there a way to tar the whole thing and untar on Machine B? or any other way? Thanks, Ratheesh Bhat K J
How to find the no of Inserts and selects
Hello all, I need to find out to some point of accuracy the ratio of Number Inserts/Updates to that of Selects. This is because we are thinking of setting up a Replication sysytem with one master and one slave. Now replication would be optimal only if there if the Select queries are dominating, so how would I determine this figure? Ratheesh Bhat K J
Re: Seperating Application server and Database server
I am a bit confused here!!! We thought seperating our App server and DB server bcoz we spotted some performance problems. There are queries taking about 40 seconds to fetch about 300 odd rows ( dont kno if I can relate this to a fight b/w app and db for cpu and/or mem). We have a 4GB RAM. When we see the result of TOP from the box it shows this: 98 processes: 97 sleeping, 1 running, 0 zombie, 0 stopped CPU states:cpuuser nice system irq softirq iowait idle total 2.9%0.0%0.2% 0.0% 0.0% 10.9% 85.8% cpu001.8%0.0%0.6% 0.0% 0.2% 2.0% 95.4% cpu014.0%0.0%0.2% 0.0% 0.0% 19.8% 76.0% cpu025.8%0.0%0.0% 0.0% 0.0% 2.4% 91.8% cpu030.0%0.0%0.2% 0.0% 0.0% 19.6% 80.2% Mem: 4114248k av, 4087836k used, 26412k free, 0k shrd, 78148k buff 3185764k actv, 363900k in_d, 97588k in_c Swap: 2048248k av, 228832k used, 1819416k free 2495180k cached I have a question here: Of the available 4GB i can see only 26412k (25M) free, and there are around 30 Mysql threads consuming around 30% mem. as can be seen below PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND 24583 mysql 15 0 1222M 1.2G 3296 S 0.0 30.4 11:11 0 mysqld 24584 mysql 15 0 1222M 1.2G 3296 S 0.0 30.4 0:07 2 mysqld 24585 mysql 15 0 1222M 1.2G 3296 S 0.0 30.4 0:02 0 mysqld 24586 mysql 15 0 1222M 1.2G 3296 S 0.0 30.4 0:05 1 mysqld 24587 mysql 15 0 1222M 1.2G 3296 S 0.0 30.4 228:05 1 mysqld 24588 mysql 15 0 1222M 1.2G 3296 S 0.0 30.4 2:50 0 mysqld 24589 mysql 15 0 1222M 1.2G 3296 S 0.0 30.4 7:07 0 mysqld 24590 mysql 15 0 1222M 1.2G 3296 S 0.0 30.4 5:33 3 mysqld 24591 mysql 15 0 1222M 1.2G 3296 S 0.0 30.4 6:35 3 mysqld 24594 mysql 15 0 1222M 1.2G 3296 S 0.0 30.4 4:45 0 mysqld 27078 mysql 15 0 1222M 1.2G 3296 S 0.0 30.4 8:27 1 mysqld 27330 mysql 15 0 1222M 1.2G 3296 S 0.0 30.4 16:01 2 mysqld 29496 mysql 15 0 1222M 1.2G 3296 S 0.0 30.4 24:27 0 mysqld 26657 mysql 15 0 1222M 1.2G 3296 S 0.0 30.4 2:51 0 mysqld 28535 mysql 15 0 1222M 1.2G 3296 S 0.0 30.4 0:03 0 mysqld 30578 mysql 15 0 1222M 1.2G 3296 S 0.0 30.4 1:42 3 mysqld 1664 mysql 15 0 1222M 1.2G 3296 S 0.0 30.4 0:29 3 mysqld 2546 mysql 15 0 1222M 1.2G 3296 S 0.0 30.4 0:21 2 mysqld 2767 mysql 15 0 1222M 1.2G 3296 S 0.0 30.4 0:23 3 mysqld 2770 mysql 15 0 1222M 1.2G 3296 S 0.0 30.4 0:00 0 mysqld 2772 mysql 15 0 1222M 1.2G 3296 S 0.0 30.4 0:00 2 mysqld 16895 mysql 15 0 1222M 1.2G 3296 S 0.0 30.4 2:26 2 mysqld 17058 mysql 15 0 1222M 1.2G 3296 S 0.0 30.4 1:35 2 mysqld 17181 mysql 15 0 1222M 1.2G 3296 S 0.8 30.4 1:02 1 mysqld 17182 mysql 15 0 1222M 1.2G 3296 S 0.0 30.4 1:15 2 mysqld 17267 mysql 15 0 1222M 1.2G 3296 S 0.0 30.4 0:44 2 mysqld 17300 mysql 15 0 1222M 1.2G 3296 S 0.6 30.4 1:10 3 mysqld 17701 mysql 15 0 1222M 1.2G 3296 S 0.0 30.4 0:24 0 mysqld 18018 mysql 15 0 1222M 1.2G 3296 S 0.0 30.4 0:05 0 mysqld 18019 mysql 15 0 1222M 1.2G 3296 S 0.0 30.4 0:23 1 mysqld But there are no queries running and no major applications running either. Then how is that a lot of RAM is being used up? I m totally clueless as to what is happening In such a case should we be thinking of increasing the RAM capacity or is seperating app and db server better?? any suggestion ll be hlpful Thanks Ratheesh K J - Original Message - From: Brent Baisley [EMAIL PROTECTED] To: Ratheesh K J [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Monday, August 28, 2006 7:33 PM Subject: Re: Seperating Application server and Database server In addition to what the others told you, security would be a big reason. If the application server is compromised, whether through your application code or some other service on the box, then they also have local access to the database files. If you setup a database server, you can open just the port(s) that are required to communicate with the database and allow only the ip address of the application server. Yes, if the application server is compromised, it can be used to connect to database server, but at least it's another hurdle. Also, by splitting them, it allows much more flexibility, For instance, what if there is a problem with the application server or you want to upgrade it? If everything is on one box, you would need to take the database down with everything else even though you are not changing anything
Re: Anyone tried solidDB for MySQL?
What is this solidDB? where can i get much info on this?? Thanks, Ratheesh Bhat K J
what should be the value of innodb_flush_log_at_trx_commit
hello all, We do not run transactions at all on our db. All our queries are autocommit. So what should be the value set to this variable : innodb_flush_log_at_trx_commit We currently have it set to 1 and all our tables are Innodb. Since we are not running any transactions at all, is it better to set this var to 0 or 2? Thanks, Ratheesh Bhat K J
Seperating Application server and Database server
Hello all, Currently our application and MySQL server are on the same machine. When should these be seperated? What are the main reasons that we should be having a seperate DB server? Ratheesh Bhat K J
Query takes different times for execution...
Hello all, I wanted to know why a select query takes ,say, 18 sec to execute the first time I execute it and then for every successive execution it takes, say, 10 sec. I have disabled Query Caching on the server. If its not Query Caching then what else is causing this variation in exec time? 1) Also i would like to know something about table caching (what exactly gets cached here?) and its advantages 2) What is an optimal join order. Should it be a smaller result joined with a bigger one or vice-versa, Or how does MySQL do it? Thanks, Ratheesh Bhat K J
Some questions on Storage engine
Hello all, I have a couple of questions on storage engine types wrt performance 1.. Will there be any performance degrade when we do joins with tables having different storage engines ? 2.. Where are the temporary tables created? (by default why not memory storage engine?) will it be helpful if all the temp tables are created with storage engine :MEMORY Thanks, Ratheesh Bhat K J
Buffer size for innodb tables
Hello All, I wanted to know what is the best size for Innodb key cache. We are currently running MySQL 4.1.11 And we have set the buffer size to 1GB. innodb_buffer_pool_size = 1G The system has 4 GB RAM. 1) In such a case is the above setting ok? 2) All the tables are of Innodb type 3) We are using the same server for Apache and MySQL Ratheesh Bhat K J
Adding index -- Need help
Hello All, Need a suggestion for this: We have tables which have very few number of rows ( less than 600 ). For a long period of time the number of rows are going to remain almost the same. 1) Is it better to index the columns of such tables? 2) There are Joins on this table and then a search on certain fields of this table in the where clause. Does index make a difference? Example: TBL_XXX and TBL_YYY are related through FLD_ACC_ID. This fld is indxed on both the tables. None of the others are. Would adding indexes help on such tables ( only 342 rows in both ) DESC TBL_XXX;342 Rows - FLD_EMAIL_ID varchar(150) PRI FLD_ACC_ID int(11) unsigned PRI 0 FLD_PRIMARY_FLAG tinyint(1) YES 0 -- DESC TBL_YYY; 342 Rows FLD_ACC_ID int(11) unsigned PRI 0 FLD_ACC_NAME varchar(32) YES \N FLD_MAIN_ZONE tinyint(1) 0 FLD_FOR_ZONE_ID int(10) unsigned YES 0 FLD_STATE tinyint(1) unsigned 0 FLD_DOMAIN_ID tinyint(4) unsigned YES 0 FLD_SCAN_CUST_SERIAL_NUM_FLAG tinyint(1) unsigned YES 0 FLD_SCAN_CUST_NO_SUPPORT_FLAG tinyint(1) unsigned YES 0 FLD_INBOX varchar(64) YES \N FLD_INBOX_PASSWD varchar(16) YES \N FLD_INBOX_LOCK_FLAG tinyint(1) YES 0 FLD_INBOX_LOCK_DATE_TIME datetime YES \N FLD_EMAIL_INBOX_OUTWARD varchar(32) YES \N FLD_WEBMAIL_INDIVIDUAL_FLAG tinyint(1) unsigned YES 0 FLD_WEBMAIL_INDIVIDUAL_TYPE tinyint(5) unsigned YES 0 FLD_EMAIL_DONT_PROCESS_FLAG tinyint(1) unsigned 0 FLD_ATTACH_MAX_UPLD_SIZE tinyint(2) unsigned YES 0 FLD_ATTACH_MAX_RECV_SIZE tinyint(2) unsigned YES 0 FLD_ADD_BY int(11) 0 FLD_ADD_DATE_TIME datetime -00-00 00:00:00 FLD_UPDATE_BY int(11) YES \N FLD_UPDATE_DATE_TIME datetime YES \N FLD_PARENT_ID int(11) unsigned YES 0 FLD_THREAD_ID int(11) unsigned YES 0 FLD_POS tinyint(5) unsigned YES 0 FLD_LEVEL tinyint(5) unsigned YES 0 FLD_OWNER_COMPANY_ID int(10) unsigned 0 FLD_FOR_COMPANY_ID int(10) unsigned YES 0 FLD_NO_DIRECT_SUBMISSION_FLAG tinyint(1) YES 0 Thanks, Ratheesh Bhat K J
Cardinality
Hello all, Need an explanation for this: I did the following - SELECT DISTINCT COLUMN1 FROM TBL_XXX ; I got the foll result 1 2 3 4 5 7 8 10 11 12 13 14 16 17 18 19 20 21 23 24 25 26 27 28 29 30 -- Totally 26 rows Now when I saw the Cardinality of this col ( COLUMN1 ) by doing a SHOW INDEX on TBL_XXX, It shows 93. How can this be possible, as Cardinality should be the number of distinct values ( 26 in this case ) for that column right? I also did an ANALYZE TABLE on TBL_XXX. The result is still the same. Thanks, Ratheesh Bhat K J
Table size??
Helo all, Just wanted to know when should a Table be considered for partitioning ( or should it be archiving ). Almost all of our tables are of Innodb type. I am looking for an estimate rather than a Depends on situation kind of an answer. We have few of our table swhich are very huge ( in terms of number of rows ), 70 lac rows. Should this be a factor for table partitioning or should the actual data size be a factor. 1) In that case for a system with 80 GB reserved for MySQL when should we worry about Table sizes? 2) We have 3 specific tables which are of size 5GB, 3GB and 1.7GB respectively. The first two of them have more than 70 lakh rows. As a preventive measure what could be the best way to optimize these tables? Thanks, Ratheesh Bhat K J
Another question on Cardinality??
Hello all, Another question on cardinality. Consider a table with 1000 rows and columnns. Details of the columns are as below: FLD_1 - int - cardinality 1000 - PRIMARY KEY FLD_2 - tinyint- cardinality 400 FLD_3 - varchar - cardinality 10 FLD_4 - varchar - cardinality 2 FLD_5 - varchar - cardinality 5 Assuming that cardinality exactly is the number of distinct values for that column, Which are the fields that is best for indexing for the table. Is it meaningful for me to index FLD_3, FLD_4 and FLD_5 knowing that their cardinality is always going to be the same? There are certain scenarios wherein I have queries on the tables as below: 1) Select * from table where FLD_4 = 1; 2) Select * from table where FLD_5 = 3; 3) Select * from table where FLD_3 1 AND FLD_5 6; considering all the above cases, what should I conclude? should I have indexes on these three fields? Looking for a specific answer than a depend on situation kind of an answer. Thanks Ratheesh Bhat K J
Doubt on Index Merge??
Hello all, I have a doubt on Index Merge. We are currently using MySQL server 4.1.11. As a part of DB management I am currently doing an analysis on all the Tables. While looking into the table structures, their columns and Indexes I found that most of the tables have an Index on fields that have a very low cardinality. For an estimate let me say that there were indexes on fields with cardinality 17 for a table with 13 lac rows. So i decided to remove the Index on such fields. I made this decision because I assume that the probability of MySQL optimizer choosing such indexes is very low. MySQL would always choose a better index than this. Now i doubt my assumption when I move to MySQL server 5.0.X. In MySQL 5 there is a concept of Index Merge. So was it right for me to remove these indexes if we were to use MySQL 5? How much of a difference in terms of performance would removal of Index make in MySQL 4.1.11? How much of a difference in terms of performance would retaining of Index make in MySQL 5? Expecting a specific answer than a depends on situation kind of an answer... Thanks, Ratheesh Bhat K J
Table analysis - Help required urgently
Hello all, I am required to analyze all the tables of our system. I need to know the key parameters that should be taken into consideration for analysis. I am not speaking about ANALYZE TABLE tbl name. I am required to manually look into all table structures and pin point problems ( if any ). So it would be helpful if i could know about 1) what exactly I have to look for in the tables. 2) Index management. Which fields are to be indexed and which not 3) How big can a table be? We have tables which have more than 50 lakhs of rows. Any select queries, insert queries or update queries are taking more time to execute. So what is a preferable table size. 4) Any other suggestions Thanks, Ratheesh Bhat K J
How many columns??
Hello all, Just wanted to know how many columns are preferable in table. At present we are having nearly 50 - 60 columns in some of the tables. Is this ok or should we be splitting the tables for normalization. If we really need to split then how better would it be in terms of performance.? 1) Splitting into related tables would then require Joins for data retrieval 2) Inserts/ updates will have to be done on more than one table now It would be great to also know the best practices on number of rows that a table must hold. Ratheesh Bhat K J
Questions on PRIMARY KEY
Hello all, Need an explanation on this: SELECT col1, col2, ... FROM tbl1 JOIN tbl2 ON (some cond) JOIN tbl3 ON (some cond) . . . WHERE col1 something AND col2 something etc... Running an EXPLAIN on the above shows that the join type is ALL. I came to know that MySQL is not able to use any INDEX for the above query. The query is such that it does not require any search on PRIMARY KEY and that is why it is not used in the WHERE clause. To optimize this query I did the following: SELECT col1, col2, ... FROM tbl1 JOIN tbl2 ON (some cond) JOIN tbl3 ON (some cond) . . . WHERE col1 something AND col2 something etc... AND PRIMARYKEY 0; Now running an EXPLAIN showed join type as range and showed that it had to scan half the number of rows less than the previous time. It Was using PRIMARYKEY column as the INDEX this time. I need an explanation of whether what I did is an optimization or not? Or should i be looking into something else to actually optimize the query. Thanks Ratheesh Bhat K J
Need help on EXPLAIN in rating queries
Helo all, I need explanation on EXPLAIN here. I am trying to JOIN 2 tables TBL1 and TBL2 on TBL1.fld_id = TBL2.fld_id . And finally I filter out the results that i need in the where clause using where TBL1.fld_col = 100; Running an EXPLAIN shows that it is an impossible where condition. This may be because there may be no rows with fld_col = 100. But in future there could be rows with this value in fld_col. So how should I rate this query? Should I consider this query as a bad one just because it has an impossible where currently? Thanks Ratheesh Bhat K J
IN or OR? whats the diff?
Hello all, Just wanted to know if using IN in the where clause is better than OR in terms of performance. that is : Are these both same in terms of performance SELECT * FROM TABLE WHERE ( COLUMN = 1 OR COLUMN = 2 ); SELECT * FROM TABLE WHERE COLUMN IN ( 1, 2 ); thanks, Ratheesh Bhat K J
MySQL performing too badly under heavy load - urgent hlp needed
Hello all, Stuck up with a major problem. Urgent hlp required MySQL seems to be performing too bad during heavy load on the server. Queries which normally take around 5 secs to complete are taking more than 1000 secs to complete during load. What could be the reason. Show processlist shows many process in sending data state. All tables are of INNODB type. But we are not running any transactions as yet. The server is clogged due to many httpd requests (150 Max). All the httpd requests are in W state ( means sending response ). What could be causing this. Is it MySQL or is it Apache... Any suggestions would help... Thanks, Ratheesh K J
Select Query taking time
Hello All, I run a select query to see its speed. It took around 5 seconds. Now i run the same query simultaneously twice usng two instances of the client tool. It took 10 seconds for both the queris to complete. Its not 5 secs + 5 secs. Both the queries were running till 10 secs when i saw using mytop. In the 11th sec both the queries ended. Running it thrice simultaneously, it took 15 secs for all the three queries to complete. In such a case should this query be considered as slow? We are actually checking for queries which take longer than 12 secs and regarding such queries as slow. The moment we find such a query, a mail is sent to the DBA saying that the query is slow. So in a day there are more than 400 such slow query notifications flowing into the mail box. My questions are, Should the simultaneous queries take so long? Should'nt both queries have finished by 6 secs rather than 10 secs? Is this a right strategy to track slow queries? Any suggestions would help. Thanks, Ratheesh K J
Will UPDATE block on SELECT?
I would like to know whether a SELECT query would block an Update on the same table. The table is of InnoDB type. Since InnoDB tables apply row level locks should the Update queries be blocked until the select query completes? I experienced such a scenario wherein an update query had to wait until the select query completed. Also how different is the locking when there is a CREATE TEMPORARY TABLE tblname AS SELECT * FROM TBL_TEST and an Update on the table TBL_TEST simultaneously? Pls Note: We are not using transactions. Currently all our tables have been converted to InnoDB type. Very soon we are planning to use transactions. Thanks, Ratheesh K J
