# Are you using MyISAM tables? Consider switching the tables that are getting "congested" to InnoDB.

Yes I am. But I did change to InnoDB but I got more "Can't connect" error than when using MyISAM tables, so I rolled it back to MyISAM. I haven't known much about InnoDB back then, so I probably didn't set its options correctly. The "Can't connect" error happen when multiple properties are trying to fill up the cache (see next answer).

# Is it the caching table that's getting congested with reads and writes? Maybe you can do your caching more effectively? (Maybe use memcached?)

Yes, it is. The cache is basically just a place to hold intermediate search results on a given date, so that future searches in that date don't have to do all the computation to decide whether a room-package combination is available on that date.

Following is a simplified list of the cache table fields:

field name	type
property_id	INT
date	DATE
room_id	INT
package_id	INT
valid_price	BOOLEAN
available_room_count	INT
room_capacity	INT
package_min_guests	INT
package_max_guests	INT
available_to_travel_agent	BOOLEAN

The search only need to execute one SELECT query on this table if the content reflect the actual data. But when the actual data is modified, updating this table takes a long time, because the UPDATE query competes with massive SELECT queries. The update becomes worse when the update must be done for each date.

Therefore, simple cache mechanism like memcached is not suitable for this. I do use it to cache other fairly static data, though.

# Maybe you can partition your data? Have "cat reservations" on one database server and "dog reservations" on another.

Because I am the only programmer and the only DBA, I hesitate in doing this. Even now, I have difficulty maintaining such a large code base I've written in the past 6 month.

# Also be sure to do all the "usual" MySQL optimization tricks (is it using the proper amount of memory? Do you have proper indexes on all tables? Enable the slow query log and check with EXPLAIN SELECT ... that all your queries are using indexes when possible. If it's not possible, rethink your table schema.

That's why I am asking on perlmonks. I want to know what's the optimal database schema should look like. Currently I have one database for each property, hence we have more than 400 databases with the same schema. I know it could be better. About optimization, I have done as much as possible everything in the Optimization chapter of mysql documentation.