Re: daemon crash when shutting down large databases
Gleb Paharenko wrote: Hello. = 77591546 K Really - something is wrong with your memory settings - MySQL is using about 77G of memory Unfortunately getting the daemon to not go above the theoretical limit has tanked performance. In reality I never see the daemon go above 45% RAM usage when using the settings that can theoretically go to 77GB RAM usage. What if I added a 80GB swap file? Would this not make sure there is available RAM if the daemon really needs it and eliminate all memory exhaustion cases from the crash? Cheers, ds (or you have such a cool server :)! Please send the output of 'SHOW VARIABLES' statement, 'SHOW STATUS' statement and your configuration file. Include the amount of physical memory. David Sparks wrote: mysql usually crashes when being shutdown. The machine is a dual AMD64 w 8GB RAM running mysql-4.1.14 on Gentoo linux with a ~40GB database. I had similar crashes running 4.0.24 on an x86 running a ~275GB database. I always use `mysqladmin shutdown` rather than the init scripts to shutdown the daemon. Are there any known problems with shutting down large databases? Thanks, ds 050923 10:41:58 InnoDB: Starting shutdown... 050923 10:44:00InnoDB: Assertion failure in thread 1174235488 in file os0sync.c line 634 InnoDB: Failing assertion: 0 == pthread_mutex_destroy(fast_mutex) InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html InnoDB: about forcing recovery. mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=2147483648 read_buffer_size=33550336 max_used_connections=217 max_connections=768 threads_connected=0 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 77591546 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Where to store comments?
In article [EMAIL PROTECTED], Jonas Geiregat [EMAIL PROTECTED] writes: mysql create table foo (id int NOT NULL comment 'test foo en bar'); This is a comment on a column which apparently gets displayed by SHOW CREATE TABLE only by later versions, e.g. 4.1.14. Table comments go after the closing parenthesis: create table foo (id int NOT NULL) comment 'test foo en bar'; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Global Replace
We have a database on MySql 4 and it contains many tables. In each field in the table in the past were there was no data to display we simply left the field blank, we now want to replace a null entry with No Data I advise you to reconsider. You are reducing the flexibility and usefullness of your data by doing this. Functions designed to take advantage of NULL values will be lost to your applications. Your database will consume more disk space, and probably take longer to search. Leave the database fields as NULL, and design the output of your applications to display No Data where appropriate. -- Scott Noyes [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Global Replace
Yes, I don't think you have to do lot of changes to your application to achieve this. As scott mentioned, always try to keep minimum(whatever is really useful) data in the DB, either for more performance or for using less disk space. sujay -Original Message- From: Scott Noyes [mailto:[EMAIL PROTECTED] Sent: Friday, September 30, 2005 6:43 PM To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: Re: Global Replace We have a database on MySql 4 and it contains many tables. In each field in the table in the past were there was no data to display we simply left the field blank, we now want to replace a null entry with No Data I advise you to reconsider. You are reducing the flexibility and usefullness of your data by doing this. Functions designed to take advantage of NULL values will be lost to your applications. Your database will consume more disk space, and probably take longer to search. Leave the database fields as NULL, and design the output of your applications to display No Data where appropriate. -- Scott Noyes [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Global Replace
John Berman [EMAIL PROTECTED] wrote on 09/29/2005 06:05:42 PM: Hi We have a database on MySql 4 and it contains many tables. In each field in the table in the past were there was no data to display we simply left the field blank, we now want to replace a null entry with No Data I have no problem doing this on an individual field in each table like so UPDATE mc_centralgirls SET mc_centralgirls.notes = No Data WHERE (((mc_centralgirls.notes) Is Null)); but it's a big job. Can I do this at table level across all fields, or ideally at database level ? Regards John Berman You can't do it on a database level but you can do it to an entire table at once. You will still need to individually declare which columns need fixing, though. option A: 1) Copy the old table's design to a new table. In the new table redefine any nullable columns you no longer want to be NOT NULL and change the default value to 'No Data' (or whatever is appropriate for that column). 2) INSERT all of the records from your old table into the new table. INSERT new_tablename (col1, col2, ... , colN) SELECT col1, col2, ..., colN FROM old_tablename; 3) Verify the accuracy of your data import. Fix any problems and repeat until INSERT generates the data you want. 4) use RENAME TABLE to swap the names of the new table and the old table RENAME TABLE new_tablename to old_tablename, old_tablename to new_tablename; 5) use DROP TABLE to get rid of the old data under the new name. DROP TABLE `new_tablename`; option B: Use an UPDATE statement combined with the COALESCE() function to replace all NULL values with the value you want UPDATE target_table SET col1 = COALESCE(col1, 'No Data') , col2 = COALESCE(col2, 'No Data') , col3 = COALESCE(col3, 'No Data') , col4 = COALESCE(col4, 'No Data') ... , colN = COALESCE(colN, 'No Data'); Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: MySQL Query Browser
Rob Agar [EMAIL PROTECTED] wrote on 09/29/2005 08:27:37 PM: hi Scott How do I run more than 1 queries in MySQL Query Browser? The only way I've found is to put the queries in a .sql file and load it via File Open Script. It doesn't accept multiple typed in queries, even if they are separated by semicolons. hth Rob He can just start a new script tab, can't he? That's how I do it. I don't know why they have two different types of tabs (one for multi-statements and one for single statements) but they do. File - New Script Tab Shawn Green Database Administrator Unimin Corporation - Spruce Pine
How can I access results in a singel dimentional array?
Hi, Is there an easy way to access directly the results after a SELECT and a mysql_store_result() into a single dimentional char *array[], given that I already know the (fields * rows) value? Are the total results stored in a single buffer or is it done on a per row basis? How does it work? Thnx, Lefteris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL Query Browser
I am not saying MysQL Query Browser is anyway bad or inferior, but as for my experience sqlyog is very good. There is a free version which you can use for executing SQL queries, ofcourse you will be stripped of some advanced features. You can run multiple queries at once using shift+F5. I suggest you to try this out once. sujay -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, September 30, 2005 7:14 PM To: Rob Agar Cc: 'Mysql ' Subject: RE: MySQL Query Browser Rob Agar [EMAIL PROTECTED] wrote on 09/29/2005 08:27:37 PM: hi Scott How do I run more than 1 queries in MySQL Query Browser? The only way I've found is to put the queries in a .sql file and load it via File Open Script. It doesn't accept multiple typed in queries, even if they are separated by semicolons. hth Rob He can just start a new script tab, can't he? That's how I do it. I don't know why they have two different types of tabs (one for multi-statements and one for single statements) but they do. File - New Script Tab Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How can I access results in a singel dimentional array?
Mysql_store_result stores all the results in a single buffer. But at one
time you can only access one row. It is like a array of structures, where
each structure has one row info. And the call to mysql_fetch advances this
rowcount by one. And coming to storing all the results in one char*, it is a
simple loop you can use.
While(fetch from mysql) {
Catch all columns of a row in output bind vars
Append all these to the char * using some delimiter. (actually you don't
need to know the number of rows before hand, you can do a malloc here
itself)
}
Return (char*)
sujay
-Original Message-
From: Lefteris Tsintjelis [mailto:[EMAIL PROTECTED]
Sent: Friday, September 30, 2005 7:13 PM
To: mysql@lists.mysql.com
Subject: How can I access results in a singel dimentional array?
Hi,
Is there an easy way to access directly the results after a SELECT and a
mysql_store_result() into a single dimentional char *array[], given that I
already know the (fields * rows) value? Are the total results stored in a
single buffer or is it done on a per row basis? How does it work?
Thnx,
Lefteris
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Simpleupload/search
Hello. Probably some web-development mailing list of forum is a better place for your letter, than MySQL list. From MySQL side I can advice you not to store images in the MySQL DB, usually it is better to have them in the file system and to store links to the images in the database. John wrote: I am trying to make a script that has an admin and search area. The admin area let's the user upload 10 photos and a discription for a property, then select from drop down menus for bedrooms,bathrooms,price for rental, golfing or not comunity,comunity name and code numbereach has it's own for faster search if you know it i.e you own the place. then same for other end for search fields. Is there an easy way to do this or a program out now that does this in php/mysql? Thanks __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Administrator crashes upon launch...
Wolfram Stebel wrote: Am 30.09.2005 16:29 Uhr schrieb Nuno Pereira unter [EMAIL PROTECTED]: in Users/user/Library/Logs/CrashReporter/java.crash.log one mile of traces Regards Wolfram ** Host Name: Erde Date/Time: 2005-09-30 15:13:52.351 +0200 OS Version: 10.4.2 (Build 8C46) Report Version: 3 Command: MySQL Administrator Path:/Applications/MySQL Administrator.app/Contents/MacOS/MySQL Administrator Parent: WindowServer [70] Version: ??? (1.1.0) PID:2192 Thread: 0 Exception: EXC_BAD_ACCESS (0x0001) Codes: KERN_PROTECTION_FAILURE (0x0002) at 0x Thread 0 Crashed: 0 libSystem.B.dylib 0x900031e8 strlen + 8 1 libSystem.B.dylib 0x9001f880 sscanf + 88 2 com.mysql.MySQLToolsCommon 0x100354e0 myx_get_mysql_version + 96 3 com.mysql.MySQLToolsCommon 0x100355b4 myx_get_mysql_major_version + 24 4 com.mysql.MySQLToolsCommon 0x10001e50 -[MConnectionPanel(Private) connectionFinished:] + 216 5 com.apple.Foundation 0x92890760 __NSFireMainThreadPerform + 276 6 com.apple.CoreFoundation 0x9077c108 __CFRunLoopPerformPerform + 104 7 com.apple.CoreFoundation 0x9074bc8c __CFRunLoopDoSources0 + 384 8 com.apple.CoreFoundation 0x9074b1bc __CFRunLoopRun + 452 9 com.apple.CoreFoundation 0x9074ac3c CFRunLoopRunSpecific + 268 10 com.apple.HIToolbox0x93129ac0 RunCurrentEventLoopInMode + 264 11 com.apple.HIToolbox0x931290cc ReceiveNextEventCommon + 244 12 com.apple.HIToolbox0x93128fc0 BlockUntilNextEventMatchingListInMode + 96 13 com.apple.AppKit 0x93623e44 _DPSNextEvent + 384 14 com.apple.AppKit 0x93623b08 -[NSApplication nextEventMatchingMask:untilDate:inMode:dequeue:] + 116 15 com.apple.AppKit 0x9362006c -[NSApplication run] + 472 16 com.apple.AppKit 0x937108bc NSApplicationMain + 452 17 com.mysql.Administrator0x5a24 _start + 392 (crt.c:267) 18 com.mysql.Administrator0x5898 start + 48 Thread 1: 0 libSystem.B.dylib 0x9002ca78 semaphore_wait_signal_trap + 8 1 libSystem.B.dylib 0x9003125c pthread_cond_wait + 508 2 com.apple.Foundation 0x9288a0a0 -[NSConditionLock lockWhenCondition:] + 68 3 com.apple.AppKit 0x936c07f8 -[NSUIHeartBeat _heartBeatThread:] + 324 4 com.apple.Foundation 0x92882f34 forkThreadForFunction + 108 5 libSystem.B.dylib 0x9002c3b4 _pthread_body + 96 Thread 2: 0 libSystem.B.dylib 0x9002ca78 semaphore_wait_signal_trap + 8 1 libSystem.B.dylib 0x9003125c pthread_cond_wait + 508 2 com.apple.Foundation 0x92886448 -[NSConditionLock lock] + 52 3 com.apple.Foundation 0x928905a4 -[NSObject(NSMainThreadPerformAdditions) performSelectorOnMainThread:withObject:waitUntilDone:modes:] + 716 4 com.apple.Foundation 0x928902b8 -[NSObject(NSMainThreadPerformAdditions) performSelectorOnMainThread:withObject:waitUntilDone:] + 120 5 com.mysql.MySQLToolsCommon 0x10002040 -[MConnectionPanel(Private) connectThread:] + 144 6 com.apple.Foundation 0x92882f34 forkThreadForFunction + 108 7 libSystem.B.dylib 0x9002c3b4 _pthread_body + 96 Thread 0 crashed with PPC Thread State 64: srr0: 0x900031e8 srr1: 0x1200f030 vrsave: 0x cr: 0x82002202 xer: 0x lr: 0x9001f880 ctr: 0x900031e0 r0: 0x9001f880 r1: 0xbfffe310 r2: 0x0044 r3: 0x r4: 0x100ae024 r5: 0xbfffe4e8 r6: 0xbfffe4ec r7: 0x r8: 0x6e48476d r9: 0x r10: 0x10034a04 r11: 0xa00063fc r12: 0x900031e0 r13: 0x r14: 0x0001 r15: 0x r16: 0x0001 r17: 0xb2d0 r18: 0x r19: 0x0035ce40 r20: 0x r21: 0x r22: 0x0001 r23: 0x0030cb80 r24: 0x r25: 0x0030cb88 r26: 0x0030cc98 r27: 0x046a r28: 0xbfffe34c r29: 0x r30: 0x100ae024 r31: 0x9001f830 Binary Images Description: 0x1000 -0x54fff com.mysql.Administrator ??? (1.1.0) /Applications/MySQL Administrator.app/Contents/MacOS/MySQL Administrator 0x1000 - 0x100d6fff com.mysql.MySQLToolsCommon ??? (1.0) /Applications/MySQL Administrator.app/Contents/Frameworks/MySQLToolsCommon.framework/Versions/1. 0.0/MySQLToolsCommon 0x8fe0 - 0x8fe51fff dyld 43.1/usr/lib/dyld 0x9000 - 0x901a6fff libSystem.B.dylib /usr/lib/libSystem.B.dylib 0x901fe000 - 0x90202fff libmathCommon.A.dylib /usr/lib/system/libmathCommon.A.dylib 0x90204000 - 0x90257fff com.apple.CoreText 1.0.0 (???)
Re: MySQL Query Browser
2005/9/30, Sujay Koduri [EMAIL PROTECTED]: I am not saying MysQL Query Browser is anyway bad or inferior, but as for my experience sqlyog is very good. There is a free version which you can use for executing SQL queries, ofcourse you will be stripped of some advanced features. You can run multiple queries at once using shift+F5. I suggest you to try this out once. the no longer supported Mysql Control Center, can do it as well. It supports single/multiple query without tricks. You can even directly edit your dataset (and not do the annoying : edit+ do your stuff + apply changes of MysqlQuery Browser ) sujay -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, September 30, 2005 7:14 PM To: Rob Agar Cc: 'Mysql ' Subject: RE: MySQL Query Browser Rob Agar [EMAIL PROTECTED] wrote on 09/29/2005 08:27:37 PM: hi Scott How do I run more than 1 queries in MySQL Query Browser? The only way I've found is to put the queries in a .sql file and load it via File Open Script. It doesn't accept multiple typed in queries, even if they are separated by semicolons. hth Rob He can just start a new script tab, can't he? That's how I do it. I don't know why they have two different types of tabs (one for multi-statements and one for single statements) but they do. File - New Script Tab Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: HELP - Group_Concat broken after update
That's not true. Group_Concat can already return more than 6meg. It's set by group_concat_max_len variable. The fact is that I've been told there are security issues corrected in the 4.1.14a update and I can't apply them because it will break my already existing apps. Is there a fix? - Thanks Dan Nelson [EMAIL PROTECTED] 9/27/05 11:54:35 PM Probably to allow for a result longer than 255 characters, I guess. -- Dan Nelson [EMAIL PROTECTED]
Re: MySQL Query Browser
I think Control Center is a much better tool then the combination of Query Browser and Administrator. I still use Control Center for my day to day stuff and only check out Query Browser when new versions come out to see if they've solved the major problems. I think they've gone completely in the wrong direction with these tools and I even told members of MySQL-AB at this years user conference. I gave a written list of suggestions and not a single one of them has been addressed in the program. It's definitely not an intuitive application and could be so much better if they'd just take the best ideas from all the other tools that are already out here. I don't think they've done that; it seems as if they've created this thing based on the way they _want_ to use it and not the way users _need_ to use it. Pooly [EMAIL PROTECTED] 9/30/05 8:57:04 AM 2005/9/30, Sujay Koduri [EMAIL PROTECTED] : I am not saying MysQL Query Browser is anyway bad or inferior, but as for my experience sqlyog is very good. There is a free version which you can use for executing SQL queries, ofcourse you will be stripped of some advanced features. You can run multiple queries at once using shift+F5. I suggest you to try this out once. the no longer supported Mysql Control Center, can do it as well. It supports single/multiple query without tricks. You can even directly edit your dataset (and not do the annoying : edit+ do your stuff + apply changes of MysqlQuery Browser ) sujay -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, September 30, 2005 7:14 PM To: Rob Agar Cc: 'Mysql ' Subject: RE: MySQL Query Browser Rob Agar [EMAIL PROTECTED] wrote on 09/29/2005 08:27:37 PM: hi Scott How do I run more than 1 queries in MySQL Query Browser? The only way I've found is to put the queries in a .sql file and load it via File Open Script. It doesn't accept multiple typed in queries, even if they are separated by semicolons. hth Rob He can just start a new script tab, can't he? That's how I do it. I don't know why they have two different types of tabs (one for multi-statements and one for single statements) but they do. File - New Script Tab Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: HELP - Group_Concat broken after update
I meant 600k not 6meg. Sorry Ed Reed [EMAIL PROTECTED] 9/30/05 9:46:32 AM That's not true. Group_Concat can already return more than 6meg. It's set by group_concat_max_len variable. The fact is that I've been told there are security issues corrected in the 4.1.14a update and I can't apply them because it will break my already existing apps. Is there a fix? - Thanks Dan Nelson [EMAIL PROTECTED] 9/27/05 11:54:35 PM Probably to allow for a result longer than 255 characters, I guess. -- Dan Nelson [EMAIL PROTECTED]
How to sort Query Browser's connection list?
Hi all, It's not a big deal, but I can't seem to find how to sort or order my connection entries in Options-Connections view tree in MySQL Query Browser. (See attached screenshot.) Any ideas? Thanks! Mihail -- Mihail Manolov Government Liquidation, LLC Special Projects Leader 202 558 6227 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
timing queries?
Hi, Does anyone know how to execute SQL statements from a text file such that the summaries X rows in set (X.YZ sec) are printed for each query? Neither of these do it: mysql batch-file mysql -e 'source batch-file' 'source batch-file' run inside mysql command-line does it, but then I need to manually copy and paste the results. Thanks, Jacek -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign key support in MyISAM
Hi, I had asked similar question few days ago, and then checked with the developers as no one was able to answer on this mailing list. I was told it is very likely we'll get it in 5.2. Jacek Sujay Koduri wrote: does anyone have an idea when mysql guys are going to include foreign key support in myisam tables. i have read that it is slated for a future release, but it is not mentioned when it is actually scheduled for. reference -- http://dev.mysql.com/doc/mysql/en/ansi-diff-foreign-keys.html http://dev.mysql.com/doc/mysql/en/ansi-diff-foreign-keys.html sujay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Global Replace
Sound advice, I have no done as suggested and my application handles the Nulls's Thanks John B -Original Message- From: Sujay Koduri [mailto:[EMAIL PROTECTED] Sent: 30 September 2005 14:26 To: Scott Noyes; [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: RE: Global Replace Yes, I don't think you have to do lot of changes to your application to achieve this. As scott mentioned, always try to keep minimum(whatever is really useful) data in the DB, either for more performance or for using less disk space. sujay -Original Message- From: Scott Noyes [mailto:[EMAIL PROTECTED] Sent: Friday, September 30, 2005 6:43 PM To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: Re: Global Replace We have a database on MySql 4 and it contains many tables. In each field in the table in the past were there was no data to display we simply left the field blank, we now want to replace a null entry with No Data I advise you to reconsider. You are reducing the flexibility and usefullness of your data by doing this. Functions designed to take advantage of NULL values will be lost to your applications. Your database will consume more disk space, and probably take longer to search. Leave the database fields as NULL, and design the output of your applications to display No Data where appropriate. -- Scott Noyes [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.11.9/115 - Release Date: 29/09/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign key support in MyISAM
On Fri, 30 Sep 2005 13:08:31 -0700 Jacek Becla [EMAIL PROTECTED] wrote: Hi, I had asked similar question few days ago, and then checked with the developers as no one was able to answer on this mailing list. I was told it is very likely we'll get it in 5.2. Thanks - now that 5.0 is release candidate I wonder how soon it will be before 5.1 alpha is released? Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Making ORDER BY RAND() more random
is there a way to make ORDER BY RAND() at bit more spontaneous ? I am using it in a php statement: $sql = SELECT media.id, artist.name as artist, artist.spanish as bio, artist.purchaseLink, artist.picture, media.spanish as trackName, media.path, media.quality, mediaType.id as mediaType FROM artist, media, playlistItems, mediaType WHERE playlistItems.playlist_id = $myID AND playlistItems.media_id = media.id AND media.artist_id = artist.id AND media.mediaType_id = mediaType.id ORDER BY RAND() LIMIT 0, 30; the result seems to be pretty predictable is there a way to improve RAND() or is there something better ? g -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
config diff: query time went from 70 mins to 20 seconds
Here is a config diff that made mysql usable again. As the database grew in size, buffer sizes in the config were increased to try to boost mysql performance. Unfortunately it didn't work as expected. As the config was tweaked, mysql slowed down even more. Removing all settings from the my.cnf restored performance. So what was the setting below that was tanking mysql performance? I suspect that innodb_log_buffer_size=32M was the culprit. #skip-innodb key_buffer = 2048M max_allowed_packet = 1M -table_cache= 1536 -sort_buffer_size = 256M -net_buffer_length = 64K -read_buffer_size = 256M -read_rnd_buffer_size = 256M -myisam_sort_buffer_size= 256M +#table_cache = 1536 +#sort_buffer_size = 256M +#net_buffer_length = 64K +#read_buffer_size = 256M +#read_rnd_buffer_size = 256M +#myisam_sort_buffer_size = 256M language = /usr/share/mysql/english myisam_data_pointer_size = 6 # daves: this may not quite work ... -join_buffer_size = 256M +#join_buffer_size = 256M long_query_time= 10 log-long-format log-slow-queries @@ -117,9 +117,9 @@ # the rest of the innodb config follows: # don't eat too much memory, we're trying to be safe on 64Mb boxes. # you might want to bump this up a bit on boxes with more RAM -innodb_buffer_pool_size= 2048M +innodb_buffer_pool_size= 6144M # this is the default, increase if you have lots of tables -innodb_additional_mem_pool_size= 32M +innodb_additional_mem_pool_size= 4M # # i'd like to use /var/lib/mysql/innodb, but that is seen as a database :-( # and upstream wants things to be under /var/lib/mysql/, so that's the route @@ -134,7 +134,7 @@ # sensible values range from 1MB to (1/innodb_log_files_in_group*innodb_buffer_pool_size) innodb_log_file_size = 1G # this is the default, increase if you have very large transactions. -innodb_log_buffer_size = 32M +innodb_log_buffer_size = 1M # this is the default, and won't hurt you. # you shouldn't need to tweak it. set-variable = innodb_log_files_in_group=2 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
