MySQL: index for a very large table
This is the first time I try to use MySQL for very large tables. Experienced users warned me against possible problems with *index* definition for large tables: they claim MySQL indices are stored completely in memory, which is problematic to me : I have a huge table and limited memory size (RAM). I did the math, and the entire index just won't fit into the RAM. Unfortunatelly it will be a while before management approves hardware improvements. Could anyone please advise whether MySQL really needs to store the entire index in memory ? Or is there some way to configure this ? Thanks very much ! -- View this message in context: http://www.nabble.com/MySQL%3A-index-for-a-very-large-table-tf2172085.html#a6005797 Sent from the MySQL - General forum at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: confirm subscribe to mysql@lists.mysql.com
Am Sonntag, 27. August 2006 15:23 schrieb [EMAIL PROTECTED]: To confirm that you would like [EMAIL PROTECTED] added to the mysql mailing list, please click on the following link: http://lists.mysql.com/s/mysql/44f19ce8d8f3f021/karel.dingeldey=criena.net This confirmation serves two purposes. First, it verifies that we are able to get mail through to you. Second, it protects you in case someone forges a subscription request in your name. --- Administrative commands for the mysql list --- I can handle administrative requests automatically. Please do not send them to the list address! Instead, send your message to the correct command address: For help and a description of available commands, send a message to: [EMAIL PROTECTED] To subscribe to the list, send a message to: [EMAIL PROTECTED] To remove your address from the list, just send a message to the address in the ``List-Unsubscribe'' header of any list message. If you haven't changed addresses since subscribing, you can also send a message to: [EMAIL PROTECTED] or for the digest to: [EMAIL PROTECTED] For addition or removal of addresses, I'll send a confirmation message to that address. When you receive it, simply reply to it to complete the transaction. If you need to get in touch with the human owner of this list, please send a message to: [EMAIL PROTECTED] Please include a FORWARDED list message with ALL HEADERS intact to make it easier to help you. --- Enclosed is a copy of the request I received. Received: (qmail 5945 invoked by uid 48); 27 Aug 2006 13:21:00 - Date: 27 Aug 2006 13:21:00 - Message-ID: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Unsubscribe request From: [EMAIL PROTECTED] This message was generated because of a request from 213.157.15.165. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: confirm subscribe to mysql@lists.mysql.com
- Original Message - From: Karel W. Dingeldey [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Sunday, August 27, 2006 6:57 PM Subject: Re: confirm subscribe to mysql@lists.mysql.com Am Sonntag, 27. August 2006 15:23 schrieb [EMAIL PROTECTED]: To confirm that you would like [EMAIL PROTECTED] added to the mysql mailing list, please click on the following link: http://lists.mysql.com/s/mysql/44f19ce8d8f3f021/karel.dingeldey=criena.net This confirmation serves two purposes. First, it verifies that we are able to get mail through to you. Second, it protects you in case someone forges a subscription request in your name. --- Administrative commands for the mysql list --- I can handle administrative requests automatically. Please do not send them to the list address! Instead, send your message to the correct command address: For help and a description of available commands, send a message to: [EMAIL PROTECTED] To subscribe to the list, send a message to: [EMAIL PROTECTED] To remove your address from the list, just send a message to the address in the ``List-Unsubscribe'' header of any list message. If you haven't changed addresses since subscribing, you can also send a message to: [EMAIL PROTECTED] or for the digest to: [EMAIL PROTECTED] For addition or removal of addresses, I'll send a confirmation message to that address. When you receive it, simply reply to it to complete the transaction. If you need to get in touch with the human owner of this list, please send a message to: [EMAIL PROTECTED] Please include a FORWARDED list message with ALL HEADERS intact to make it easier to help you. --- Enclosed is a copy of the request I received. Received: (qmail 5945 invoked by uid 48); 27 Aug 2006 13:21:00 - Date: 27 Aug 2006 13:21:00 - Message-ID: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Unsubscribe request From: [EMAIL PROTECTED] This message was generated because of a request from 213.157.15.165. -- 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: MySQL: index for a very large table
For best performance, you would want to have all your indexes stored in RAM, certainly. Generally true of databases. However, you do not have to have enough RAM to keep the indexes loaded. You can configure MySQL to use as much RAM as is appropriate for your machine. The online manual has some good configuration guidelines, and the sample my.cnf files included with your installation may be a good starting point as well. Dan On 8/27/06, solmyr72 [EMAIL PROTECTED] wrote: This is the first time I try to use MySQL for very large tables. Experienced users warned me against possible problems with *index* definition for large tables: they claim MySQL indices are stored completely in memory, which is problematic to me : I have a huge table and limited memory size (RAM). I did the math, and the entire index just won't fit into the RAM. Unfortunatelly it will be a while before management approves hardware improvements. Could anyone please advise whether MySQL really needs to store the entire index in memory ? Or is there some way to configure this ? Thanks very much ! -- View this message in context: http://www.nabble.com/MySQL%3A-index-for-a-very-large-table-tf2172085.html#a6005797 Sent from the MySQL - General forum at Nabble.com. -- 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]
ALTER TABLE
Hi, I am doing this thru the phpmyadmin interface: ALTER TABLE doc ALTER docts SET DEFAULT CURRENT_TIMESTAMP However, it returns #1064 - You have an error in your SQL syntax near 'DEFAULTCURRENT_TIMESTAMP' at line 1 As you can see the error shows that DEFAULT an CURRENT_TIMESTAMP has been written together, strange, or is my syntax in the ALTER TABLE wrong? /Peter
Server-Crash - What to do?
Hello, yesterday my server crashed. The harddisk got corrupted, including some MySQL table files. So MySQL can't see some of its tables, although the table files are visible on the filesystem. Is it possible to repair the files? It's a MySQL 4.1 (Debian Sarge). From one table I only have the MYD file. The documentation says, that this is the file with the data in it. I hope it's possible to reconstruct this table, too?! Thanks, Karel W. Dingeldey PS: I already tried with myisamchk -r -o *.MY*, but then all datasets are gone and the table is empty. :-( -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Server-Crash - What to do?
PS: I already tried with myisamchk -r -o *.MY*, but then all datasets are gone and the table is empty. :-( man myisamchk I tried myisamchk already. But after running the above rescue attempt, the tables are empty. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Server-Crash - What to do?
At first - do not write anything on the corrupted disk until You have finished rescuing data. As I understad the crash is connected to HW or filesystem (FS) failure (HDD). In that case try to use fsck to repair FS (if it is regular linux FS, like ext2 or ext3, for xfs there were some other utils), do not use myisamchk at first. If Your data is really valuable I would recommend bit-by-bit copy of disk content to similar disk using dd, then try to restore data on that disk (the copy). If You make some mistakes or something goes wrong (on copy), You will still have source disk to start over again (with bit by bit copy). After You have done rescuing FS, use myisamchk on rescued tables. Martins Karel W. Dingeldey wrote: Hello, yesterday my server crashed. The harddisk got corrupted, including some MySQL table files. So MySQL can't see some of its tables, although the table files are visible on the filesystem. Is it possible to repair the files? It's a MySQL 4.1 (Debian Sarge). From one table I only have the MYD file. The documentation says, that this is the file with the data in it. I hope it's possible to reconstruct this table, too?! Thanks, Karel W. Dingeldey PS: I already tried with myisamchk -r -o *.MY*, but then all datasets are gone and the table is empty. :-( -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Server-Crash - What to do?
PS: I already tried with myisamchk -r -o *.MY*, but then all datasets are gone and the table is empty. :-( man myisamchk I tried myisamchk already. But after running the above rescue attempt, the tables are empty. Then I hope you made backups You, do keep backups right? -- Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why can't I delete these records?
Evert wrote: Hi all! Who can tell me why the following does not work...? When I do: SELECT COUNT(*) AS counter FROM table1 WHERE condition1 = 'A' AND condition2 IS NULL; it returns: +-+ | counter | +-+ | 2 | +-+ Then I do: DELETE FROM table1 WHERE condition1 = 'A' AND condition2 IS NULL; it returns: Query OK, 0 rows affected (0.00 sec) But... when I give a SELECT COUNT(*) AS counter FROM table1 WHERE condition1 = 'A' AND condition2 IS NULL; How come the 2 records did not get deleted...? Regards, Evert What you have shown us is perfectly valid SQL that should produce the desired results. Your mysql version (4.1.21) should make no difference. I see only 2 possibilities: 1. There is actually a difference between the two queries (some typo) that your editing has hidden. I know you said you've only done a search and replace on the table name (really?), so this is unlikely, but we on the list can't conclusively rule it out without seeing the unmodified original queries. Sorry for the rant, but it has frequently been the case that the problem has turned out to be in the real query but not in the edited-for-the-list version. 2. Something odd is going on. One possibility is that an index used by one query but not the other is out of sync with the actual data. Either you have two rows which are not found by the delete, or your select is counting two nonexistent rows. It would be nice to know which. Have you verified that the two rows actually exist by inspecting their contents rather than simply counting them? That is, what is the output of SELECT condition1, condition2 FROM table1 WHERE condition1 = 'A' AND condition2 IS NULL; Do you get two rows of output? What about SELECT * FROM table1 WHERE condition1 = 'A' AND condition2 IS NULL; Do you get the same two rows (including the other columns)? Have you tried CHECK/REPAIR? http://dev.mysql.com/doc/refman/4.1/en/check-table.html http://dev.mysql.com/doc/refman/4.1/en/repair-table.html If you still need help, include the output of SHOW CREATE TABLE table1; Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Compiling MySQL to work with OpenSSL
Question, I specify with openssl is with --wth-openssl=/usr/contrib and the config script chokes. What is this happening? -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Server-Crash - What to do?
At first - do not write anything on the corrupted disk until You have finished rescuing data. As I understad the crash is connected to HW or filesystem (FS) failure (HDD). Ok, I will do it more clearly. The server has two harddisks, one for the system, one for the data. The system harddisk breaked, hardware failure. It seems that this happed while mysqld was writing some data, so that some tables are corrupted. Most tables are reconstructable, only the one where I have only the MYD file is very important. On filesystem level I made all rescue trials. My problem is, that only the MYD file is still available. I tried reconstructing it with myisamchk -r -o table_name.MYD, but as I said without the wanted result. My question is, if there is any solution to get a working table from this MYD file. Because the data is saved in this file, IMHO it should. Am I right? Then I hope you made backups You, do keep backups right? Thanks for asking, but I'm that only stupid guy in the world who has no backup with actual data. ;-) No, that's not really funny. :-( I would bite my ass if I could! Karel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Server-Crash - What to do?
On filesystem level I made all rescue trials. My problem is, that only the MYD file is still available. I tried reconstructing it with myisamchk -r -o table_name.MYD, but as I said without the wanted result. MYD is your data, MYI is your indexes (Indexes are easy to rebuild, you should not worry about that). If myisamchk can't read the MYD, then I don't know what else there is. My guess would be that the data file is damage beyond repair :-( If you have a complete and working set of binary logs (incase you used replication which I doubt), you should also be able to get your data back out of that... But otherwise, I'd say you're out of luck. -- Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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
Re: Seperating Application server and Database server
Ratheesh K J wrote: 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? There are at least three possible reasons. First, if your applications are using lots of CPU power, moving them to their own server should speed up the DB. If your applications use a lot of disk I/O, other than the DB, that's another reason. Memory is another. If the machine is maxed out on RAM and you are using all of it, having the applications on their own machine should also help. If you move it, you need to be sure to have enough network band width between the two machines. -- Chris W KE5GIX Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Seperating Application server and Database server
Ratheesh K J wrote: Hello all, Currently our application and MySQL server are on the same machine. When should these be seperated? When: - your performance is dropping, and - you have identified that your bottleneck is CPU usage, and - both your MySQL server and your application server are fighting for CPU usage at the same time, and - you can't add more CPUs If any of the above don't apply, then you should be doing something else instead - maybe increasing your RAM or upgrading your SCSI disks. What are the main reasons that we should be having a seperate DB server? Performance. And keep in mind that communication between MySQL and your application server will be *considerably* faster when they're both on the same system. If you put them on separate systems, obviously communication between the 2 will now be over the network. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
