How to avoid Using temporary; Using filesort
I have a query that runs very slow and using Using temporary; Using filesort. Is there a way to avoid them using current table structure? Goal with the query is to find ads (ad_id) that have tags (tag_id) connected to them, order by antal is used to get the most relevant first. ( the one that conains all 5 tags and so on). Query: SELECT COUNT(*) antal,ad.ad_id FROM ad LEFT JOIN tag_ad_map tm ON tm.ad_id=ad.ad_id WHERE tm.tag_id IN (99, 10807, 20728, 21, 135) AND ad.is_removed = 0 AND ad.is_active=1 AND (ad.ant_feedback_alert = 5 OR ad.whitelisted = 1) AND tm.is_active=1 AND ad.ad_id != 32793 GROUP BY tm.ad_id ORDER BY antal DESC LIMIT 10 Explain: *** 1. row *** id: 1 select_type: SIMPLE table: tm type: ref possible_keys: PRIMARY,tag_id,ad_id,is_active key: is_active key_len: 4 ref: const rows: 177800 Extra: Using where; Using temporary; Using filesort *** 2. row *** id: 1 select_type: SIMPLE table: ad type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: rubbt.tm.ad_id rows: 1 Extra: Using where tag_ad_map: Create Table: CREATE TABLE `tag_ad_map` ( `ad_id` int(11) NOT NULL default '0', `tag_id` int(11) NOT NULL default '0', `termfreq` int(11) NOT NULL default '0', `weight` int(11) NOT NULL default '0', `is_active` int(11) NOT NULL default '0', PRIMARY KEY (`tag_id`,`ad_id`), KEY `tag_id` (`tag_id`), KEY `ad_id` (`ad_id`), KEY `is_active` (`is_active`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 tag_keys: Create Table: CREATE TABLE `tag_keys` ( `id` int(11) NOT NULL auto_increment, `tag` varchar(32) collate utf8_swedish_ci NOT NULL default '', `idf` double NOT NULL default '0', `url` varchar(64) collate utf8_swedish_ci NOT NULL default '', `termfreq` int(11) NOT NULL default '0', `weight` int(11) NOT NULL default '0', `disable_in_cloud` int(11) NOT NULL default '0', PRIMARY KEY (`id`), KEY `url` (`url`), KEY `tag` (`tag`) ) ENGINE=MyISAM AUTO_INCREMENT=49312 DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci
Unused and empty tables - what impact on mysql
Hiya I just inherited a project, and before I get started, Id like to do a little clean up. There a * load of unused and empty tables in the db. My question is, does this in any way affect the performance of mysql in anyway and if so how? Kind Regards Brent Clark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to avoid Using temporary; Using filesort
One optimization I see quickly is changing the left join to an inner join. You always look for records that exist in tag_ad_map (by checking for tm.is_active) so the left join is not necessary. That should at least speed this query up considerably. Walter Need MySQL advice? OlinData.com is the place to go! (http://www.olindata.com) SELECT COUNT(*) antal,ad.ad_id FROM ad LEFT JOIN tag_ad_map tm ON tm.ad_id=ad.ad_id WHERE tm.tag_id IN (99, 10807, 20728, 21, 135) AND ad.is_removed = 0 AND ad.is_active=1 AND (ad.ant_feedback_alert = 5 OR ad.whitelisted = 1) AND tm.is_active=1 AND ad.ad_id != 32793 GROUP BY tm.ad_id ORDER BY antal DESC LIMIT 10
Re: Unused and empty tables - what impact on mysql
Empty files, like empty tables is not synonym for useless or unused. Of course it depends from the storage engine used but in the list of performance tuning removing empty tables is way way down, they have almost no impact while not used. The bottom line, remove only when sure 100% of their uselessness. Cheers Claudio Nanni 2009/1/14 Brent Clark brentgclarkl...@gmail.com Hiya I just inherited a project, and before I get started, Id like to do a little clean up. There a * load of unused and empty tables in the db. My question is, does this in any way affect the performance of mysql in anyway and if so how? Kind Regards Brent Clark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com
Re: Unused and empty tables - what impact on mysql
Claudio Nanni wrote: Empty files, like empty tables is not synonym for useless or unused. Of course it depends from the storage engine used but in the list of performance tuning removing empty tables is way way down, they have almost no impact while not used. The bottom line, remove only when sure 100% of their uselessness. Cheers Claudio Nanni Thanks for your reply and feedback. Regards Brent Clark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
default storage engine
Hello mysql, Greetings in the New Year to everyone. Hope for only good things... I have been using a WAMP for maybe 5 years, started with Apache2Triad and eventually evolved to WAMPSERVER which is what I am running now. Over the years, I have upgraded my server many times and the procedure that has always worked was to do a clean install and drop my data into the data folder, fire it up and every thing works. I did have a hiccup when I switched from Apache2Triad because WAMPSERVER defaults to INNODB and ALL my work is MYISAM so after I figured out that I just need to change the default storage engine, everything worked as expected, copy the files, fire it up and it works. THIS TIME, I have a real problem because the default storage engine has disappeared from the my.ini file and if I put it in MySQL does not even start anymore. Not only that, when I copy the database files into the data folder, many of them seem to be corrupted now for some unknown reason. When I do a diff on the my.ini file the new one looks nothing at all like the previous version so my question is what happened? If anyone knows. -- Best regards, mikesz mailto:mik...@qualityadvantages.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Upgrage MYSQL 5.0 to 5.1 :: Using unsupported buffer type
hi all, We have a c application who work perfecty with mysql 5.0. We decide to upgrade and use mysql 5.1. Since that, nothing work, in owr log, we have always the follonig error Using unsupported buffer type: 254 (parameter: 2) when we call the mysql_stmt_bind_result function what we do is a SELECT COL_1,COL_2 FROM TAB_3 where col_1 et col_2 are declared as char(3) and contain abc and def (it's a test :D ). for the bind, we use MYSQL_TYPE_STRING for both parameters (the first and the second) mysql describe TAB_3; +---+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+---+ | COL_1 | char(3) | YES | | NULL| | | COL_2 | char(3) | YES | | NULL| | | COL_3 | text| YES | | NULL| | +---+-+--+-+-+---+ 3 rows in set (0.00 sec) any idea, cause I don't know by what to start to find the prob :D I looked in all mysql log, nothing special thank you -- http://deathboater.blogspot.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: default storage engine
You can only copy files liek that for MyISAM tables. Are you sure you didn't accidentally convert to INNODB somewhere along the line? If so, go back to your old install and dump everything so you can import it in the new install, or convert everything back to MyISAM in the old install before moving the files over. Of course you should chose the appropriate table type for each and every table in your database, but when load is very very low and you don't expect any growth I guess it is not your first concern. Kind regards, Walter Need MySQL advice? OlinData.com is the place to go! (http://www.olindata.com) On Wed, Jan 14, 2009 at 11:36 AM, mik...@qualityadvantages.com wrote: Hello mysql, Greetings in the New Year to everyone. Hope for only good things... I have been using a WAMP for maybe 5 years, started with Apache2Triad and eventually evolved to WAMPSERVER which is what I am running now. Over the years, I have upgraded my server many times and the procedure that has always worked was to do a clean install and drop my data into the data folder, fire it up and every thing works. I did have a hiccup when I switched from Apache2Triad because WAMPSERVER defaults to INNODB and ALL my work is MYISAM so after I figured out that I just need to change the default storage engine, everything worked as expected, copy the files, fire it up and it works. THIS TIME, I have a real problem because the default storage engine has disappeared from the my.ini file and if I put it in MySQL does not even start anymore. Not only that, when I copy the database files into the data folder, many of them seem to be corrupted now for some unknown reason. When I do a diff on the my.ini file the new one looks nothing at all like the previous version so my question is what happened? If anyone knows. -- Best regards, mikesz mailto:mik...@qualityadvantages.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=li...@olindata.com
Re: Upgrage MYSQL 5.0 to 5.1 :: Using unsupported buffer type
Could it be associated with one of the known issues listed at http://dev.mysql.com/doc/refman/5.1/en/upgrading-from-5-0.html ? quote: Incompatible change: In MySQL 5.1.25, a change was made to the way that the server handles prepared statements. This affects prepared statements processed at the SQL level (using the PREPARE statement) and those processed using the binary client-server protocol (using the mysql_stmt_prepare() C API function). Kind regards, Walter Heck -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Upgrage MYSQL 5.0 to 5.1 :: Using unsupported buffer type
heu... sorry but something I don't understand, what I did is remove the mysql dierctory (programFile\mysql) remove the data directory,( C:\Documents and Settings\All Users\Application Data) remove all key who are in regedit and contain mysql (more or less). remove the services, restart my computer. (so my computer never seen mysql) after taht, I downloaded the 5.1 mysql installer http://dev.mysql.com/downloads/mysql/5.1.html#win32 install it configure the server and then try my test and ... Using unsupported buffer type: 254 (parameter: 2) I made other test (with number and ) and then bind return ] Using unsupported buffer type: 268641644 (parameter: 3) and execute stmt return : No data supplied for parameters in prepared statement any idea?? thank you a++ On Wed, Jan 14, 2009 at 12:11 PM, Walter Heck li...@olindata.com wrote: Could it be associated with one of the known issues listed at http://dev.mysql.com/doc/refman/5.1/en/upgrading-from-5-0.html ? quote: Incompatible change: In MySQL 5.1.25, a change was made to the way that the server handles prepared statements. This affects prepared statements processed at the SQL level (using the PREPARE statement) and those processed using the binary client-server protocol (using the mysql_stmt_prepare() C API function). Kind regards, Walter Heck -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=ele...@gmail.com -- http://deathboater.blogspot.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to avoid Using temporary; Using filesort
Not really, the query took 4-5 seconds. The query runs through 13910 rows according to explain, that isnt alot is it? SELECT COUNT(*) antal,ad.ad_id FROM ad INNER JOIN tag_ad_map tm ON tm.ad_id=ad.ad_id WHERE tm.tag_id IN (99, 10807, 20728, 21, 135) AND ad.is_removed = 0 AND ad.is_active=1 AND (ad.ant_feedback_alert = 5 OR ad.whitelisted = 1) AND tm.is_active=1 AND ad.ad_id != 32793 GROUP BY tm.ad_id ORDER BY antal DESC LIMIT 10 2009/1/14 Walter Heck li...@olindata.com One optimization I see quickly is changing the left join to an inner join. You always look for records that exist in tag_ad_map (by checking for tm.is_active) so the left join is not necessary. That should at least speed this query up considerably. Walter Need MySQL advice? OlinData.com is the place to go! (http://www.olindata.com) SELECT COUNT(*) antal,ad.ad_id FROM ad LEFT JOIN tag_ad_map tm ON tm.ad_id=ad.ad_id WHERE tm.tag_id IN (99, 10807, 20728, 21, 135) AND ad.is_removed = 0 AND ad.is_active=1 AND (ad.ant_feedback_alert = 5 OR ad.whitelisted = 1) AND tm.is_active=1 AND ad.ad_id != 32793 GROUP BY tm.ad_id ORDER BY antal DESC LIMIT 10
Fwd: Upgrage MYSQL 5.0 to 5.1 :: Using unsupported buffer type
I m really sorry,I read and re Read but I understang nothing , maybe it's cause I m noob with mysql or maybe cause the subject is not explicit. when I say 5.0 to 5.1 it's just the code not the database. like I explain I TOTALY remove mysql 5.0 AND THE DATA , all data files, all registery, etc like that, my pc never understood speaking about mysql so on this virgin pc, i Install mysql 5.1 a (Re) create a db (called dbu) etc... So Im really sorry , but What I have to do?? the only thing I read is For applications that use prepared statements with the new server, an upgrade to the new client library is strongly recommended. but my applications use include who is in C:\Program Files\MySQL\MySQL Server 5.1\includes (coming from with the installation) or mysql client is an other thing that I don't know what is it ?? thanks for all a+++ -- Forwarded message -- From: Walter Heck li...@olindata.com Date: Wed, Jan 14, 2009 at 1:08 PM Subject: Re: Upgrage MYSQL 5.0 to 5.1 :: Using unsupported buffer type To: elekis ele...@gmail.com As I said before: carefully read http://dev.mysql.com/doc/refman/5.1/en/upgrading-from-5-0.html :0 Kind regards, Walter Heck Need MySQL advice? OlinData.com is the place to go! (http://www.olindata.com) On Wed, Jan 14, 2009 at 12:38 PM, elekis ele...@gmail.com wrote: heu... sorry but something I don't understand, what I did is remove the mysql dierctory (programFile\mysql) remove the data directory,( C:\Documents and Settings\All Users\Application Data) remove all key who are in regedit and contain mysql (more or less). remove the services, restart my computer. (so my computer never seen mysql) after taht, I downloaded the 5.1 mysql installer http://dev.mysql.com/downloads/mysql/5.1.html#win32 install it configure the server and then try my test and ... Using unsupported buffer type: 254 (parameter: 2) I made other test (with number and ) and then bind return ] Using unsupported buffer type: 268641644 (parameter: 3) and execute stmt return : No data supplied for parameters in prepared statement any idea?? thank you a++ On Wed, Jan 14, 2009 at 12:11 PM, Walter Heck li...@olindata.com wrote: Could it be associated with one of the known issues listed at http://dev.mysql.com/doc/refman/5.1/en/upgrading-from-5-0.html ? quote: Incompatible change: In MySQL 5.1.25, a change was made to the way that the server handles prepared statements. This affects prepared statements processed at the SQL level (using the PREPARE statement) and those processed using the binary client-server protocol (using the mysql_stmt_prepare() C API function). Kind regards, Walter Heck -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=ele...@gmail.com -- http://deathboater.blogspot.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=li...@olindata.com -- http://deathboater.blogspot.com/
Restarting MySQLD when all transactions are complete
Hi, Probably a simple question for someone who knows :) Is there a way to force MySQLD to restart after it has finished processing all current transactions? I seem to remember from the bit of Oracle work I did in the past we could do a Transactional Restart in Oracle 10g which caused the server to stop accepting new requests and restart when it has processed all current transactions. I now need to do a similar thing with MySQL 5.0, is this possible? It would also be handy if I could get it to do this 'transactional retstart' and when it comes back up force the slave to do the same, but we'll get one working first! Its needed so we can apply updates etc to the box without disrupting database access. Thanks in advance for any help. Regards John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: default storage engine
I don't know if this can be useful, just a few things I found out. INNODB engine does not start if you have already files into the directory and you change the file size in the my.cnf either of the innodb Datafile or innodb Logfile, remove innodb files before starting mysql. Moreover MyISAM is the default storage engine so it should not be specified. Cheers Claudio Nanni 2009/1/14 Walter Heck li...@olindata.com You can only copy files liek that for MyISAM tables. Are you sure you didn't accidentally convert to INNODB somewhere along the line? If so, go back to your old install and dump everything so you can import it in the new install, or convert everything back to MyISAM in the old install before moving the files over. Of course you should chose the appropriate table type for each and every table in your database, but when load is very very low and you don't expect any growth I guess it is not your first concern. Kind regards, Walter Need MySQL advice? OlinData.com is the place to go! (http://www.olindata.com) On Wed, Jan 14, 2009 at 11:36 AM, mik...@qualityadvantages.com wrote: Hello mysql, Greetings in the New Year to everyone. Hope for only good things... I have been using a WAMP for maybe 5 years, started with Apache2Triad and eventually evolved to WAMPSERVER which is what I am running now. Over the years, I have upgraded my server many times and the procedure that has always worked was to do a clean install and drop my data into the data folder, fire it up and every thing works. I did have a hiccup when I switched from Apache2Triad because WAMPSERVER defaults to INNODB and ALL my work is MYISAM so after I figured out that I just need to change the default storage engine, everything worked as expected, copy the files, fire it up and it works. THIS TIME, I have a real problem because the default storage engine has disappeared from the my.ini file and if I put it in MySQL does not even start anymore. Not only that, when I copy the database files into the data folder, many of them seem to be corrupted now for some unknown reason. When I do a diff on the my.ini file the new one looks nothing at all like the previous version so my question is what happened? If anyone knows. -- Best regards, mikesz mailto:mik...@qualityadvantages.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=li...@olindata.com
frequent issues restoring mysqldump file
Hi, I'm not asking this as a specific question, more a general gripe looking for some kind of explanation. I don't do mysql restores very often, but many times when I have tried I get nasty errors as if its bombing out due to dodgy characters, or quote problems etc. Normally I am doing restores across the same major and minor revision (ie 5.1), but quite possibly with a point release difference. Its just quite frustrating, and also for such an old and widely used product surprising that a simple backup and restore doesnt work at least 99% of the time. So I suppose, Im asking is there anything people like me regularly do wrong that might cause this, or do other people experience similar issues and is there anything that can be done to improve matters? :S thanks for any help :) PS Im no MySQL expert but have used it a fair bit as well as Oracle and many Unixs and open source bits and pieces and Im usually quite good at making them do what I need ;) cheers Andy. This message was sent using IMP, the Internet Messaging Program. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: VC++ 2008 / MySQL debug / Unhandled exception
Hi I tested with both release and debug versions and the problem is the same, it fails when calling mysql_real_connect() shared memory request function strcpy() asm code access violation to 0x Regards, Miguel On Sun, Jan 11, 2009 at 9:57 AM, Patrick Sherrill patr...@coconet.comwrote: I seem to recall the issue with the debug library, but don't recall the fix. Do you get the same permissions (access) error with the release library? Pat... - Original Message - From: Miguel Cardenas renit...@gmail.com To: mysql@lists.mysql.com Sent: Saturday, January 10, 2009 10:22 AM Subject: VC++ 2008 / MySQL debug / Unhandled exception Hello list I have a problem debugging a program that uses MySQL. The program itself does not have problems, it runs perfectly when run in the console (command prompt), but if it is executed inside the Visual C++ 2008 debugger it causes an error: Unhandled exception at 0x004b1560 in MyProgram.exe: 0xC005: Access violation reading location 0x when it reaches mysql_real_connect() I'm using mysql-6.0.8-alpha-win32 binary with setup.exe installer. Note that I don't try to enter inside mysql functions, even if no breakpoints are setup and I let the program to run freely it aborts there with the unhandled exception. My VC++ 2008 configuration is this: Includes: C:\Program Files\MySQL\MySQL Server 6.0\include Libraries: C:\Program Files\MySQL\MySQL Server 6.0\lib\debug Code generation: Multi-threaded /MT Precompiled headers: NO Aditional dependencies: wsock32.lib mysqlclient.lib libmysql.lib mysys.lib Ignore specific library: (as found in a forum post) LIBCMTD.lib Debugging: YES /DEBUG I based my configuration on this post for VC++ 6.0 http://forums.mysql.com/read.php?45,49606,49606#msg-49606 I guess it may be due to the ignored LIBCMTD.lib (debug version of MT?), but if don't ignore it the compilation fails at link time with lots of redefined symbols. My concrete question: is there something wrong with the configuration shown in that post that I adapted to VC++ 2008? are VC++ programs with MySQL unable to run inside VC++ debugger? I don't want to debut my MySQL code it is working already for *NIX, but there are other parts of the program that may require debug and it aborts when calling a mysql function. Thanks for any comment or help
Re: Restarting MySQLD when all transactions are complete
Read the mysqld man pages about what it does with kill -X signals. One of them may mean graceful stop Or not. If there is one, you'd still have to figure out how to tie that into a re-boot or whatever for updates. Sounds like a perfectly reasonable feature request if you find nothing at all... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Upgrage MYSQL 5.0 to 5.1 :: Using unsupported buffer type
Did you re-compile/re-link your C app with the new MySQL header files?... Sounds like you didn't. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Unused and empty tables - what impact on mysql
-Original Message- From: Claudio Nanni [mailto:claudio.na...@gmail.com] Sent: Wednesday, January 14, 2009 4:50 AM To: Brent Clark Cc: mysql@lists.mysql.com Subject: Re: Unused and empty tables - what impact on mysql Empty files, like empty tables is not synonym for useless or unused. Of course it depends from the storage engine used but in the list of performance tuning removing empty tables is way way down, they have almost no impact while not used. The bottom line, remove only when sure 100% of their uselessness. [JS] Rename them before deleting them. If that break things, you can recover very quickly. I was bitten by exactly this issue. I thought the application was creating the empty tables on the fly, but it turned out that (only) one of them was required to pre-exist. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: frequent issues restoring mysqldump file
Andy, I just had an interesting experience you might also find interesting. I rarely have to do restores, also (MySQL is very solid), but I just upgraded my (only) MySQL server. At the same time I upgraded the MySQL release from 5.0.45 to 5.0.67. While testing my procedure (which was a piece of cake for the most part) I got errors on one table that seemed to indicate that the mysqld daemon couldn't create the underlying files. It took a few iterations to discover that on the old server one of my co-admins had moved the files (MyISAM) from the data directory to a different file system and replaced them with symlinks in the data directory because we were running out of space in the data directory file system. That worked (fortunately since he didn't bother doing anything nice like stopping the server or locking tables when he did it). BUT I discovered that the .sql file created by mysqldump contained an option on the create table (I believe) for that table that said 'DATA DIRECTORY blah'. Of course, the mysqld daemon running as the mysql user couldn't create files in that directory! So editing the .sql file and removing that option fixed my problem. And there were no other problems in the migration. On Wed, Jan 14, 2009 at 10:56 AM, Andy Smith a.sm...@ukgrid.net wrote: Hi, I'm not asking this as a specific question, more a general gripe looking for some kind of explanation. I don't do mysql restores very often, but many times when I have tried I get nasty errors as if its bombing out due to dodgy characters, or quote problems etc. Normally I am doing restores across the same major and minor revision (ie 5.1), but quite possibly with a point release difference. Its just quite frustrating, and also for such an old and widely used product surprising that a simple backup and restore doesnt work at least 99% of the time. So I suppose, Im asking is there anything people like me regularly do wrong that might cause this, or do other people experience similar issues and is there anything that can be done to improve matters? :S thanks for any help :) PS Im no MySQL expert but have used it a fair bit as well as Oracle and many Unixs and open source bits and pieces and Im usually quite good at making them do what I need ;) cheers Andy. This message was sent using IMP, the Internet Messaging Program. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=sholme...@gmail.com -- The lame man who keeps the right road outstrips the runner who takes a wrong one. The more active and swift the latter is, the further he will go astray. -Francis Bacon, essayist, philosopher, and statesman (1561-1626) Truth never damages a cause that is just. -Mohandas Karamchand Gandhi (1869-1948)
Re: VC++ 2008 / MySQL debug / Unhandled exception
Hi I'm using /MT (LIBCMT.lib?) and it is multi-threaded since all my multi-thread code is working. LIBCMTD.lib is ignored because it is indicated in a post at MySQL forums http://forums.mysql.com/read.php?45,49606,49606#msg-49606; and anyway applications do not compile if not ignored because lots of duplicated symbols at link time. The issue is like this (as accurate as I remember) according the debugger call stack: 1) Call mysql_real_connect() 2) mysql_real_connect() calls a shared memory request function 3) the shared memory request fails in a internal strcpy() (asm, not source available) because a memory violation trying to acces 0x Note these conditions: - The program fails inside VC++ 2008 express debug session - The program runs perfect without debug session or called directly by user - I'm programming inside VirtualBox/WinXP Now I don't know if this problem is related to the VC++ debugger that fails with certain condition of the MySQL client library or the VirtualBox manages shared memory in a different way than a real computer. I emphasize that my program works perfect with Linux, FreeBSD and Solaris, even Windows if not run in a debug session, and the problem arises inside the MySQL library, not in the MySQL code but inside the shared memory function it calls. I hope this could help to get an idea Regards, Miguel
Re: Restarting MySQLD when all transactions are complete
Hi, On Wed, Jan 14, 2009 at 3:00 PM, John Daisley john.dais...@mypostoffice.co.uk wrote: Hi, Probably a simple question for someone who knows :) Is there a way to force MySQLD to restart after it has finished processing all current transactions? I seem to remember from the bit of Oracle work I did in the past we could do a Transactional Restart in Oracle 10g which caused the server to stop accepting new requests and restart when it has processed all current transactions. I now need to do a similar thing with MySQL 5.0, is this possible? Right, under Oracle you can do SHUTDOWN TRANSACTIONAL There is no such command available with MySQL but you can do the basically the same thing. Reduce the max_connections variable to 1, this will prevent any new non-super connections. Optionally set the server to read_only to prevent any existing non-super connections from initiating new updates. View the processlist, once all the transactions have completed you can kill the connections and issue a shutdown. It would also be handy if I could get it to do this 'transactional retstart' and when it comes back up force the slave to do the same, but we'll get one working first! Its needed so we can apply updates etc to the box without disrupting database access. Its not exactly what oracle is doing, but at least you can control access. Cheers, Ewen Thanks in advance for any help. Regards John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=ewen.fort...@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
Re: Query Optimization
The index hint is not in productoin code.. I was trying ot force it to use the index even when using the OR clause.. ment to take that out before I sent the email. The table structure is: CREATE TABLE `customer` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `ssn` varchar(32) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_ssn` (`ssn`) ) ENGINE=InnoDB CREATE TABLE `customer_id` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `customer_id` int(10) unsigned DEFAULT NULL, `id_num` varchar(32) DEFAULT NULL, PRIMARY KEY (`id`), KEY `customer_key` (`customer_id`), KEY `id_id_num` (`id_num`) ) ENGINE=InnoDB The explain output of the query using the OR clause: *** 1. row *** id: 1 select_type: SIMPLE table: customer type: ALL possible_keys: idx_ssn key: NULL key_len: NULL ref: NULL rows: 176680 Extra: Using where; Using temporary *** 2. row *** id: 1 select_type: SIMPLE table: customer_id type: ref possible_keys: customer_key key: customer_key key_len: 5 ref: aca_ecash.customer.id rows: 1 Extra: Using where; Distinct 2 rows in set (0.00 sec) Using a UNION results in: *** 1. row *** id: 1 select_type: PRIMARY table: customer type: range possible_keys: idx_ssn key: idx_ssn key_len: 35 ref: NULL rows: 1 Extra: Using where; Using temporary *** 2. row *** id: 1 select_type: PRIMARY table: customer_id type: ref possible_keys: customer_key key: customer_key key_len: 5 ref: aca_ecash.customer.id rows: 1 Extra: Using index; Distinct *** 3. row *** id: 2 select_type: UNION table: customer_id type: range possible_keys: customer_key,id_id_num key: id_id_num key_len: 35 ref: NULL rows: 1 Extra: Using where; Using temporary *** 4. row *** id: 2 select_type: UNION table: customer type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: aca_ecash.customer_id.customer_id rows: 1 Extra: Using where *** 5. row *** id: NULL select_type: UNION RESULT table: union1,2 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: NULL Extra: 5 rows in set (0.01 sec) The union is much faster. I've tested the same search for ID numbers on our test system (Windows 32-Bit, 2GB ram, P4 3Ghz) against the productoin system (RHEL 64-Bit 16GB ram, Dual Xeon 2Ghz).. the search in the test system is almost instant as compared to the production system its taking 4 to 6 seconds. There's not much traffic today on it. I'm going to put the UNION into production and see how it goes. Thanks for the replies. -johnny On Tue, Jan 13, 2009 at 7:39 PM, Andrew Garner andrew.b.gar...@gmail.comwrote: On Tue, Jan 13, 2009 at 7:07 PM, Baron Schwartz ba...@xaprb.com wrote: If you have separate indexes on ssn and id_num, MySQL may be able to efficiently use an index merge optimization . See http://dev.mysql.com/doc/refman/5.0/en/index-merge-optimization.html. This is only in 5.0+ - on older versions of MySQL you may find a union more efficient. And in newer versions, too. The optimizer frequently underestimates the cost of the merge operation and the required random I/O for row lookups. So, yes it can use an index merge, but... efficiency is another question. I've seen table scans outperform a two-way index merge by orders of magnitude. These appeared to be high selectivity indexes, but perhaps I assumed too much. :) -- - Johnny Withers 601.209.4985 joh...@pixelated.net
high-availability loadbalanced mysql server farm
Hi all, One website is based on LAMP(Linux+Apache+MySQL+PHP)(that is our case).We donot have very big tables or complicated database design.We only have one database. Because the php code is a third-party product we donot want to make much modification on the code. But when it comes to the underlying MySQL deployment,it is a problem. To loadbalance to write requests(insert,update...) from web program,we have some options to follow. One is master-master replication with a loadbalancer in front of the two MySQL master servers,and the loadbalancer could be LVS(it has been put into our production for years with stability and performance) or mysql-proxy(I am not sure of its stability in production). The other one is MySQL Cluster which is composed by some data nodes and mysql nodes and one management node. Our consideration is that the underlying MySQL server farm is transparent from the web program. Any suggestions will be welcomed. Thank you in advance. Yours Xu Feng -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Upgrage MYSQL 5.0 to 5.1 :: Using unsupported buffer type
oki, I found, it's the dll in system32 who was not suppress. thanks for all a++ On Wed, Jan 14, 2009 at 4:27 PM, c...@l-i-e.com wrote: Did you re-compile/re-link your C app with the new MySQL header files?... Sounds like you didn't. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=ele...@gmail.com -- http://deathboater.blogspot.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
