Beefy Boxes and Bandwidth Generously Provided by pair Networks
Pathologically Eclectic Rubbish Lister
 
PerlMonks  

comment on

( #3333=superdoc: print w/replies, xml ) Need Help??
# 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 nametype
property_idINT
dateDATE
room_idINT
package_idINT
valid_priceBOOLEAN
available_room_countINT
room_capacityINT
package_min_guestsINT
package_max_guestsINT
available_to_travel_agentBOOLEAN

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.


In reply to Re^2: OT: Scalable web application architecture by badaiaqrandista
in thread OT: Scalable web application architecture by badaiaqrandista

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post; it's "PerlMonks-approved HTML":



  • Are you posting in the right place? Check out Where do I post X? to know for sure.
  • Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
    <code> <a> <b> <big> <blockquote> <br /> <dd> <dl> <dt> <em> <font> <h1> <h2> <h3> <h4> <h5> <h6> <hr /> <i> <li> <nbsp> <ol> <p> <small> <strike> <strong> <sub> <sup> <table> <td> <th> <tr> <tt> <u> <ul>
  • Snippets of code should be wrapped in <code> tags not <pre> tags. In fact, <pre> tags should generally be avoided. If they must be used, extreme care should be taken to ensure that their contents do not have long lines (<70 chars), in order to prevent horizontal scrolling (and possible janitor intervention).
  • Want more info? How to link or or How to display code and escape characters are good places to start.
Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others rifling through the Monastery: (2)
As of 2022-05-22 04:10 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Do you prefer to work remotely?



    Results (79 votes). Check out past polls.

    Notices?