I have exceeded the 250k click line in my Prosper installation and sure enough stats generation is getting slow... as in get cup of coffee slow :)

I decided to upgrade my Ubuntu Dapper 6.06 to MySQL 5.1. Ubuntu (or debian) don't include .deb packages for MySQL 5.1 since technically 5.1 isn't ready for public consumption yet.

I ended up building MySQL 5.1 from source. All works great but every time I'd reinstall Prosper, I'd get a cryptic Out of Resources error. After rebuilding MySQL many times with diff ./configure options, I finally figured out the problem.

Prosper202 creates a metric F-ton of partition files for one of the tables, and MySQL was exceeding its max open files limit. Of course the error says nothing like this.

So, as a tip, be sure to add:

open_files_limit = 4096

to your /etc/mysql/my.cnf file.

(4096 can be higher, but make sure your Linux distro allows a lot of open files first, otherwise you need to tweak a few other things).

Now restart mysql and reinstall prosper, and it will work great. It took me 12 hours to figure this out, let it save you some time.

And for the curious, here's what my ./configure settings came out to. (for building mysql 5.1 on ubuntu 6.06 LTS) There are some other debian-related tweaks you need to do to keep the apt-get package manager happy. I ended up putting this in /usr/local/bin where the distro install was in /usr/bin and /usr/sbin

./configure --with-plugin-partition --with-plugin-myisam --with-plugin-innobase --with-plugin-archive --with-plugin-heap --with-unix-socket-path=/var/run/mysqld/mysqld.sock --with-mysqld-user=mysql --enable-assembler

Thanks for the heads up goddfadda

Awesome, your right, the partitions is for that big click table!

Is the click reports loading faster now? Thanks for the tip, this will be helpful to someone very soon, when everyone else hits the limit as well!

-Wes

Well I only have a few hundred clicks so far so its too early to tell, but I'm sure it will perform much better. Splitting up that table into so many partitions was a good thing :)

I didn't even know mysql had a file limit, but since I compiled from source using a fresh config, most likely everyone who installs MySQL 5.1 from source will run into that. Pre-packaged 5.1 may not have the issue.

The software rocks. Thanks guys. Hope to be out at the next Affiliate Summit to meet up.

Nick

Sounds good nick, we will absolutely be there!

I'm over 250k clicks and am getting over 5k clicks a day. Today MySQL was flaking in and out, probably due to all the traffic.

The problem is, my server tech tells me there's not a confirmed stable version of MySQL 5.1 that works with Plesk, so he suggested that I hold off installing it. Meanwhile, I had them recompile PHP with memcache, but I'm wondering if anyone else has 5.1 on a Plesk machine? (I'm on a dedicated server)

Adam

The traffic shouldn't effect the mysql, unless the server is generally slow. the mysql 5.1 will mainly just speed up the reporting .