Table Crashes Constantly on prod server!!
I am running 3.23.40 on a dual pentium III 800 with 1 GB Ram. I have been getting an table handler returns error 127 error If I fix the table with myisamchk -r or -o it will work for a while and then I will get the same error again. Here are some of the stats on the table rows:1126904 index len 48091136 auto inc:1819060 table size(MYD):166 MB index size(MYI):48 MB The table has a big int, primary key, auto increment here is the out put from a myisamchk -r - recovering (with sort) MyISAM-table '../var/table.MYI' Data records: 1126904 - Fixing index 1 Found link that points at 166818084 (outside data file) at 73992880 Found link that points at 166818044 (outside data file) at 83991788 Found link that points at 166818064 (outside data file) at 125436372 - Fixing index 2 - Fixing index 3 Data records: 1126901 Index 1 is the primary key on the bigint, auto increment column. Another point I have a varchar field which has an index on it. I have heard that this can cause some problems but it has never been an issue before. Load average on the server is low 90-95% idle cpu. However mysqld is consistently using about 30% of the Memory and total utilization of memory on the server is holding about 85% of standard memory but 0.2% of swap used. Could this have anything to do with the settings for the server? Thanks for any help Michael Blood +--+---+ | Variable_name| Value | +--+---+ | Aborted_clients | 2491 | | Aborted_connects | 5 | | Bytes_received | 153499291 | | Bytes_sent | 482722726 | | Connections | 3012 | | Created_tmp_disk_tables | 8 | | Created_tmp_tables | 54| | Created_tmp_files| 2 | | Delayed_insert_threads | 1 | | Delayed_writes | 114 | | Delayed_errors | 0 | | Flush_commands | 20| | Handler_delete | 76317 | | Handler_read_first | 9743 | | Handler_read_key | 7400844 | | Handler_read_next| 8428214 | | Handler_read_prev| 0 | | Handler_read_rnd | 29873 | | Handler_read_rnd_next| 190413426 | | Handler_update | 123277| | Handler_write| 6176687 | | Key_blocks_used | 249376| | Key_read_requests| 80361241 | | Key_reads| 173557| | Key_write_requests | 15295279 | | Key_writes | 1161751 | | Max_used_connections | 27| | Not_flushed_key_blocks | 0 | | Not_flushed_delayed_rows | 0 | | Open_tables | 40| | Open_files | 78| | Open_streams | 0 | | Opened_tables| 1163 | | Questions| 398056| | Select_full_join | 744 | | Select_full_range_join | 0 | | Select_range | 758 | | Select_range_check | 0 | | Select_scan | 4916 | | Slave_running| OFF | | Slave_open_temp_tables | 0 | | Slow_launch_threads | 0 | | Slow_queries | 65| | Sort_merge_passes| 0 | | Sort_range | 226 | | Sort_rows| 29639 | | Sort_scan| 753 | | Table_locks_immediate| 674329| | Table_locks_waited | 74| | Threads_cached | 5 | | Threads_created | 114 | | Threads_connected| 14| | Threads_running | 1 | | Uptime | 34266 | - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
long running queries
Occasionally I have to add an index on a table with almost 2M rows and it takes a long time. This is expected but when adding the index it seems to block all other queries from other users from executing. Is there a way to prevent this? Thanks Michael Blood - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
repairing error
I have been receiving error 127 from table handler when running some basic select queries on a large table 1.8 M rows. I have run myisamchk with the -o -r and -e options but for each of them I receive an error that is not fixed and I assume that this error is the same one that is causing the 127 error. Any idea on how to fix this corruption? when running - o # ../../bin/myisamchk -o tblhit.MYI - recovering (with keycache) MyISAM-table 'tblhit.MYI' Data records: 1863059 Wrong bytesec: 0- 0- 0 at 156040928; Skipped Data records: 1863004 when running - r # ../../bin/myisamchk -r tblhit.MYI - recovering (with sort) MyISAM-table 'tblhit.MYI' Data records: 1863354 - Fixing index 1 Wrong bytesec: 0- 0- 0 at 155194936; Skipped - Fixing index 2 Data records: 1862991 when running -e # ../../bin/myisamchk -e tblhit.MYI Checking MyISAM file: tblhit.MYI Data records: 1862994 Deleted blocks: 0 - check file-size ../../bin/myisamchk: error: Size of datafile is: 156040928 Should be: 156041128 - check key delete-chain - check record delete-chain - check index reference - check data record references index: 1 ../../bin/myisamchk: error: Found key at page 15202304 that points to record outside datafile - check records and index references ../../bin/myisamchk: error: Record-count is not ok; is 1862991 Should be: 1862994 ../../bin/myisamchk: warning: Found1862991 partsShould be: 1862994 parts MyISAM-table 'tblhit.MYI' is corrupted Fix it using switch -r or -o Any help would be appreciated Michael Blood - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
./configure error
Hi, I am attempting to compile mysql but when running ./configure it errors out when Checking for termcap functions library... configure: error: No curses/termcap library found And it returns me to the command prompt. I can't seem to find this library that it is asking me for. Any ideas. Michael Blood - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Extra Threads running
I have a mysql server 3.23.34a running on my valinux 700Mhz PIII with 1GB Ram. I have found that after running for a while mysqld starts to increase the number of threads running. For example today I had 20 threads running but only 6 processes in my mysqladmin processlist. Each of the threads takes up a large amount of memory and eventually drains my machine and things break. A simple stop and start of the mysql server fixes this but I was wondering if any one knows why this might happen. Also I know that sometimes I will receive a Broken Pipe error in my applications which are running on the same machine. I dont understand why I receive this error either but maybe it has to do with the same thing. Michael Blood - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Best Operating System
I am creating a database server with mysql. I have an adaptec 2100s card which is not supported by debian 2.2 (my first choice in Linux OSs) The card seems to say that it is only supported by Red hat and SuSe Linuxes. Any ideas on what would be the best OS to run a database server My config Dual 800 PIII 1 GB RAM 3 18GB SCSI Drive to become a single RAID 5 Array Thanks for any advice Michael Blood - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
file usage
Title: Blank I have found a large number of files which have names like this www-bin.040 www-bin.041 www-bin.042 www-bin.043 www-bin.044 which are using up huge amounts of disk space on my server. It looks to me like they are logs all of the queries that are run with a certain connection. Since my server averages just over 9 queries a second these logs are astronomical is size and they are also not necessary to me. Is there a way to get rid of this that any one knows of? Thanks Michael BloodMatraex Technologies[EMAIL PROTECTED]
Optimizing MySQL
I have a MySQL myISAM database that has about 300 MB of information. Recently the index files became corrupted and I ran the myisamchk *.MYI -r -S to repair them. Ever since then mySQLd process has been using up a ton of processor time. And some of the queries that I run have started taking a long time in the tables that I access frequently. The database also starting growing at that time as well so I am not sure what the problem is. Anyone run into a database that seems to be running very inefficiently and figure a way to optimize it? THe indexes don't seem to be the problem. Any and all suggestions are very much appreciated. Michael Blood [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Connection Timeout Limit.
Title: Blank I currently have several different systems accessing a mysql server 3.23.34 somewhere in there is a process that forgets about connections that it has open. So after a few days I get a "too many connections open error". Is there a way that I can set connections to time out after a while on the server? I have looked at all of the variables that are already set in the my.cnf and can't seem to find anything that would limit the length of time Thanks Michael BloodMatraex Technologies
Corrupt Tables??
Hello everyone. I am running a 2.2 Debian OS with 3.23.34a-log.. I had an entire database which was 2GB installed on a 2GB partition. Stupidly the database is not backed up and when I attempt to access the database an error is returned when accessing certain tables errno: 145 Saying that it can not open the table. I have looked up some infomration about repairing databases on the mysql site and found isamchk. however this only seems to work when you have *.ISM files. All of my files are .MYD .MYI and .frm. Does any one have any suggestions on how I can repair these tables? Michael Blood --:-- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Default Date value
Title: Blank I am trying to set the default value in a date field to be the time that it was created However I do not want the field to be a timestamp because I want to update it to a different value later. I have looked through the mysql documentation and I can not seem to find anywhere that explains how to set the default value of a column = to a function. Anyone have any help? Thanks in advance Michael BloodMatraex Technologies[EMAIL PROTECTED]
RE: Default Date value
Is it possible to set a function as a default value for a column at all. I apologize if I come off a bit dumb about this but I come from an MSSQL background where you can set a default to a function. Thanks Again -Original Message- From: Cal Evans [mailto:[EMAIL PROTECTED]] Sent: Sunday, March 25, 2001 4:06 PM To: Michael Blood; MYSQL Subject: RE: Default Date value BlankI don't think you can do that with a default value. You will need to include the current date in your insert statement. Cal http://www.calevans.com -Original Message- From: Michael Blood [mailto:[EMAIL PROTECTED]] Sent: Sunday, March 25, 2001 5:30 PM To: MYSQL Subject: Default Date value I am trying to set the default value in a date field to be the time that it was created However I do not want the field to be a timestamp because I want to update it to a different value later. I have looked through the mysql documentation and I can not seem to find anywhere that explains how to set the default value of a column = to a function. Anyone have any help? Thanks in advance Michael Blood Matraex Technologies [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
import out of MS SQL Server into mysql
Title: Blank Has any one heard of a good way to import from MS SQL Server into a new mysql database. I have a small (120 MB) database that I would like to convert to mysql and I was hoping that there was some sort of tool that will allow me to do it while maintaining the DDL Any suggestions are greatly appreciated Michael BloodMatraex Technologies[EMAIL PROTECTED]
