INSERT DELAYED errors - failing in actual insert
I am using INSERT DELAYED ... to insert log records into a MyISAM table. Recently these inserts have stopped showing up in the table, and extended-status shows them coming in by increasing 'Delayed_writes', however 'Delayed_errors' is increasing by the same amount. Obviously there are errors with these inserts, but I have not been able to find any way to determine what these errors are. There is no trace of any problem in host.err. I have tried duplicating the inserts without 'DELAYED' and they work fine. Can anyone explain how to determine why these inserts are failing, ideally without having to bring down the server? Thanks. Chad.
HIGH_PRIORITY with UNION
Hello, I am using HIGH_PRIORITY in my SELECTs to force queries to take predcedence over updating due to replication. I have recently implemented UNION in some of my queries to optimize queries like WHERE table1.column1=something OR table1.column2=somethingelse. Anyway, I first tried formatting my UNION query like (SELECT HIGH_PRIORITY ...) UNION (SELECT HIGH_PRIORITY ...) but the server complained about the placement of HIGH_PRIORITY. I was finally able to get it to accept the query by only specifiying HIGH_PRIORITY in the first part of the UNION, like (SELECT HIGH_PRIORITY ...) UNION (SELECT ...), but it appears that my searches are not taking precedence as they should, and as non-UNION queries do. Could there be another explanation for why they are not taking precedence, or is there another way to specify HIGH_PRIORITY in UNION queries to make both sub-queries high-priority? Thanks a lot! Chad Attermann [EMAIL PROTECTED]
Renaming an index
Hello, Through some strange quirk in my MySQL console application (an older version of PHPMyAdmin), 4 indexes that I created on different columns of a table all resulted in indexes with the same keyname, that of the first column that was indexed. The field of each index is correct, just the keyname is wrong on 3 of them. Keyname Unique Field Action BUYAGENTID No BUYAGENTID Drop BUYAGENTID No SELLAGENTID Drop BUYAGENTID No BUYACCOUNTID Drop BUYAGENTID No SELLACCOUNTID Drop I noticed that the 3 indexes with the wrong keyname are never used, even with simple queries where they definitely should be used. I need to change the keyname of these indexes without having to drop and re-create them as this table is now *very* large and it would take hours to reindex 3 columns. Any assistance in changing the keyname, either through common commands or low-level file editing, would be greatly appreciated. Thanks a lot! Chad Attermann
Managing Very Large Tables
Hello, I am trying to determine the best way to manage very large (MyISAM) tables, ensuring that they can be queried in reasonable amounts of time. One table in particular has over 18 million records (8GB data) and is growing by more than 150K records per day, and that rate is increasing. Besides the obvious things like better hardware and load-balancing across multiple replicating databases, I am trying to determine how to keep these data sets optimized for fastest queries. In my particular situation, the most recent data is queried most often, and data over 30-45 days old is not queried much at all but still must remain accessible. Each record has an integer time column that is indexed for querying over periods of time. Currently I run a script regularly that moves records older than 45 days from tables in the main database into identical tables in another (archive) database running in the same server process. This seems to speed up the tables in the main database, but I realize that deleting records leaves holes in the tables, and that this slows inserts as well as makes it impossible to read and write concurrently from these tables. My question is, is it better to keep all of the data in the original tables to avoid holes, or is 'archiving' records to another database a wise approach? How much does the size of a table really affect performance when querying the more recent data? If archiving is reasonable, is there a way to optimize the tables to get rid of the holes without siginificantly impacting ongoing activity on these tables? Thanks for your time! Chad Attermann [EMAIL PROTECTED]
Best Performing Hardware/OS/MySQL?
Hello All, I have been a MySQL user for some time and have always run MySQL on older generation Sun servers running Solaris 8. I now seem to be outgrowing my setup and I (and I'm sure others on the list) would appreciate input from the MySQL community as to which hardware, OS, and MySQL flavor/version combinations are best for running MySQL. I expect responses from you all to be subjective, and that's OK. Any information about experiences by seasoned MySQL professionals, especially those that have experiemtned with many different combinations of hardware and OS, will save those of us planning ahead a lot of time and grief. Thanks a lot in advance for your input. Chad Attermann [EMAIL PROTECTED]
