a few suggestions... Your slow inserts could be a concurrancy issue. If lots of users are doing selects that could be interfering with your inserts, especially if they use a n odbc/jdbc app that locks the table for the entire read. Jdbc reads do that when you tell it to "stream" the contents of the query, because they wouldn't all fit in memory.
------ Does your import script do an INSERT for each line, or does it combine lines into multi-value inserts? doing an insert into ... values (...),(...),(...) will be much faster than doing one insert for each row. since your max packet size is pretty big, you should be able to consolidate a fairly large number of rows into one insert. ------ What settings are you using on the Percs? What stripe size? What write cache are you using (back or thru)? Are you using read-ahead cacheing (that can hurt index performance)? ------ Defragging the filessytem probably won't have any major impact on the speed of your application. ------- Is there another way you can approach the duplicate problem? For instance, if duplicate data can only be generated in the last few days worth of imports, you might not have to rely on your primary key on the main table for importing. This only works if you can drop the primary key because it is only used for duplicate checking and isn't used to speed queries. You could instead create another table that you do all your importing to, taking care of dupes with a primary key on that table, then insert from that table into the main one. Keep a timestamp in that table and purge the older records periodically. ---- the last thing i could think of would be a "dupe checker" table. Create an innodb table that consists of only the columns from your big table and make all the columns the primary key. Essentially you have just created an "index only table." Insert your new data into a temporary heap table, then delete from the heap table where the key is in your "dupe table". Then insert everything from the heap table into the big table. Once again, this only works if you don't need the primary key on the big table. This will use more CPU/memory but it may get around your read problems. Justin --- matt ryan <[EMAIL PROTECTED]> wrote: > > > > > You might be out of luck with MySQL ... sorry. > > > > You may need to switch to a database that has a > parallel query > > facility. Then - every query becomes a massive > table scan but gets > > divided into multiple concurrent subqueries - and > overall the job > > finishes in a reasonable amount of time. The > epitomy of brute force. > > It's hard to rationalize initially but after a > while you see it's the > > only way to go. Remember - indexes are no longer > required. > > > > We have a billion row 100GB table the users search > any and every way. > > Response time is less than a minute. > > > > We are anxiously waiting to see this technology > added to MySQL. Maybe > > one day we'll have some money to contribute to the > effort. Parallel > > query is not trivial. That's why these databases > are expensive. > > > > I can send you more details privately if you are > interested. > > > I've used it, with oracle, but oracles index > searches are better, hit > the best one first, then 2nd best, then 3rd, but I > really dont want to > go to oracle, it's too complicated for my tech's > > vs mysql, hit the best one first, and use no other > > Query time is a non issue at this point, it's load > time, load daily file > into temp table, then insert ignore into main table, > on key violation > the violating record is ignored > > load time is the issue, the server loads files 15 > hours a day, that big > primary key makes loading any table over 2-3 gig > VERY slow > > I thought it was a bug, everything was great untill > you get up to about > 3 or 4 gig, then it gets SLOW > > -- > 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]
