Recently I had a customer ask me about loading two huge files into InnoDB with LOAD DATA INFILE. The goal was to load this data on many servers without putting it into the binary log. While this is generally a fast way to load data (especially if you disable unique key checks and foreign key checks), I recommended against this. There are several problems with the very large transaction caused by the single statement. We didn't want to split the file into pieces for the load for various reasons. However, I found a way to load the single file in chunks as though it were many small files, which avoided splitting the file and let us load with many transactions instead of one huge transaction.

The smaller file is 4.1GB and has 260M lines in it; each row is just two bigints. The bigger file was about 20GB and had wider rows with textual data and about 60M lines (as I recall).

When InnoDB loads the file, it creates one big transaction with …

News from Sacha (and covered by InfoWorld) that JBoss Application Server 5.0 is close to GA kicked off a debate at TSS. Some commented that they were "Suspicious of anything that takes three years to develop," while others questioned if there was anything new in JBoss AS5 that SpringSource and GlassFish (or for that matter Apache Geronimo) hadn't already delivered. Others congratulated JBoss on closing in on JEE5 certification and refactoring their runtime to be more flexible. What caught my attention is the way that Sacha (JBoss CTO) responded to two comments from Douglas Dooley. When Douglas suggested that JBoss... READ MORE

Apache2: Logging To A MySQL Database With mod_log_sql (Debian Etch)

This guide shows how you can write the Apache2 access log to a MySQL database instead of a file. To achieve this, I use the Apache2 module mod_log_sql. I'm using a Debian Etch server in this tutorial.

Apache2: Logging To A MySQL Database With mod_log_sql (Debian Etch)

This guide shows how you can write the Apache2 access log to a MySQL database instead of a file. To achieve this, I use the Apache2 module mod_log_sql. I'm using a Debian Etch server in this tutorial.

Today at 1PM EST I am presenting the second part of memcached for MySQL webinar. I was told that the registration numbers look as good as the previous one. This one will be a bit more technical than the previous webinar. Sorry for the late notice but hope you can join!

While reading my RSS feeds this morning I picked up this:
YouTube Must Give All User Histories To Viacom

After Scientology's DMCA request on Slashdot we made an active choice to squash data on users to limit the possibility of this sort of request. We randomized incoming trackable data on users and tossed everything but aggregate data for long term storage.

Why?

For one, we simply did not need to keep terabytes of log data sitting around collecting dust. Secondly, while the data might be useful for determining trends we risked our user's privacy. We believed this was unacceptable. One court request and we could be handing over who knows what to any company that could find an uneducated judge to sign away the privacy of …

SELECT ... FROM
tblProduct as prd FORCE INDEX(vProductName,iCategoryId,eProductType,vProductImage,fPrice,eProductStatus,dProductDateAdded)
LEFT OUTER JOIN (SELECT bidjoin.* FROM tblProduct as prdjoin, tblBid as bidjoin WHERE bidjoin.iProductId = prdjoin.iProductId) bid ON prd.iProductId = bid.iProductId
, tblCategory as cat FORCE INDEX(vCategoryName,eCategoryStatus,dCategoryDateAdded) 
, tblAuctionProduct as aucprd FORCE INDEX(iRequiredBids,iProductId,eAuctionType,iFreeBidLevel,fAdminFee,dDateClosed)
, tblPurchaseProduct as purprd FORCE INDEX(dProductCloseDate,iProductId,iTotalInventory,dLastUpdated)

I know, unreadable. But the main issue today is the FORCE INDEX. This is just one query (real, with permission - I just adjusted some identifiers) from an app that is litterally full of queries using FORCE INDEX. So what does FORCE INDEX do? It forces the MySQL optimiser to use the specified index (or choose from one of the specified, if multiple), …

Mike is asking I`m hoping that MySQL stored procedures will work properly. Especially multiple calls in one request. in reply to PDO_MYSQLND: R[a|u]mbling and a breeze of progress. Mike, it depends what you mean by "properly". If you use the API properly it works fine with mysqlnd. Read on for code examples.

Mike is asking I`m hoping that MySQL stored procedures will work properly. Especially multiple calls in one request. in reply to PDO_MYSQLND: R[a|u]mbling and a breeze of progress. Mike, it depends what you mean by "properly". If you use the API properly it works fine with mysqlnd. Read on for code examples.

Mike is asking I`m hoping that MySQL stored procedures will work properly. Especially multiple calls in one request. in reply to PDO_MYSQLND: R[a|u]mbling and a breeze of progress. Mike, it depends what you mean by "properly". If you use the API properly it works fine with mysqlnd. Read on for code examples.