Hi,

for me it sounds really evil to open 500 database connections, I don''t want to know how many CPU-seconds PostgreSQL needs just to open them and they may use all some memory and thus PostgreSQL has less memory for its cache.

Wifi 3.0 offers Connection Managers, which may help to reduce some load from Wildfire itself, but your database may still suffer so I can''t recommend to get 3.0 to fix it.

There is some code available to make Wildfire handle the connection pool a little bit faster (about 10x) but as far as I know Gato did not include it in Wifi 3.0 so you will have no benefit in using 3.0 regarding the DB connections. I assume you don''t want to test this code (it compiles fine with Wifi 2.6.2 anyhow) in your environment.

LG

Hi,

I wonder how many connections you are using during normal operation. You are running all  products on one server so monitoring the memory usage will be not as easy as if PostGreSQL would be running on another server, but top and Fn (sort memory) may be your friend to do this manually, "ps -someoptions" within cron may be better to monitor everything automatically. If you have during normal operation much more "free" cached memory than during startup then I''d decrease the maxDB connection value. You may have read already http://revsys.com/writings/postgresql-performance.html or similar articles "max_connections = ... Use this feature to ensure that you do not launch so many backends that you begin swapping to disk and kill the performance of all the children. Depending on your application it may be better to deny the connection entirely rather than degrade the performance of all of the other children.".

LG

Guy,

Ok, here''s the idea that''s taking shape in my head. We already know the last login time of users (I think). It''s probably a valid assumption that the users with the most recent login time will be the ones most likely to login first when the server starts up (based on auto-reconnect, etc). Therefore, we could have a new property "cache.readAheadUsers" or something. When set to a value of say 500, that would mean that the server would read the data of those 500 users into cache before the server starts accepting requests.

It should be possible to make the database queries to load the read-ahead users quite efficient. For example, a single query to load all roster data using an IN clause. So, instead of thousands of queries, the server would only need to make a couple when it''s first starting up.

Does this seem like the right approach? The nice thing is that individual implementations will be able to tune the readAhead value. Or, maybe we can even auto-tune the read-ahead size based on how many users connect to your server? It would probably only take about a day to implement this logic.

Regards,

Matt

Ahh, ok. So, lastLogin would be something we need to add at the same time. Seems like a useful bit of information to store anyway.

I''m not sure what you mean with reference to the cache. What caches are you thinking of?

Regards,

Matt

Guy,

I filed JM-764 -- please feel free to add comments. I''ve started doing some initial profiling and it looks like a user login requires about 12 database queries. That''s actually down from about 15: I already optimized away a few of the queries, for example as described in JM-762.

I''d like to get some more insight into which database queries are slow (if any). There are basically three scenarios I can think of:

1) The sheer number of database queries when thousands of users are logging in takes a long time to process, even if none of them is very expensive.

2) We''re not blocking on the database at all, but some other part of Wildfire code. For example, do you use LDAP?

3) There are a few database calls that are very expensive.

I''m adding in a database profiling tool to Wildfire that will help us answer these questions. In the meantime, it would be great if you could gather some more detailed information from your database. What''s the ordering of most common queries (top 20)? What''s the average length of time those queries run, total time?

Thanks,

Matt

Hi Guy,

Thanks for the kind words.

I actually found it very interesting tuning a Wildfire installation the size of yours. While working with you the two cache related items that really jumped out at me were:

1. Bumping the Roster cache from 0.5 MB to 5 MB increased its effictiveness from barely 20% to nearly 80% (that precentage is probably higher now with the size set to 10 MB).

2. Being able to reduce the number of database connection by 40% (550 -> 330) simply by allocating a total of ~25 MB to the User, Roster and vCard caches.

I think for a lot of situations little has to be done (if anything) when installing Wildfire beyond maybe increasing the memory you give it; I certainly wouldn''t start fiddling with the caches unless the effectiveness rate was real low after I had had Wildfire up and running for awhile. But, being able to view the various cache data in the Admin Console was extremely helpful in being able to tune Wildfire in Guy''s situation.

If anyone else has done some tweaking to their Wildfire installation feel free to share it here or in another thread.

Cheers,

Ryan