I am evaluating options to improve the performance of Moodle. Can anyone confirm, one way or another, whether pgpool can be used with Moodle? Specifically does Moodle contain SELECT queries that have side effects, or UPDATE or INSERT queries that have undeterministic functions like "random"?
I did a quick grep and couldn't find anything that rules out pgpool (or will require modification).
Thanks in advance for any information!
I've had a few discussions about using pgpool before, and the general consensus was it doesn't have enough built-in smarts to differentiate between reads and writes at this point.
You would really have to handle this at the application level by making the database abstraction layer be aware of reads vs writes and behave accordingly (send writes to all databases, fetch reads from a pool)
In Moodle, 99% of the queries go through a function that knows whether it is read or write (get_records vs insert_record) for example, but that doesn't solve the problem of sending raw sql (execute_sql for example).
You end up either having to parse the query in the database abstraction layer (bad, because as above, SELECTs can actually be writes), or in the pgpool layer (bad, because it doesn't, but is philosophically a better place for it), or in the caller to the database abstraction layer, I mean, where the sql is written (good, because that place knows what sort of query it is -read or write, but bad because it really should be handled somewhere more central).
You would really have to handle this at the application level by making the database abstraction layer be aware of reads vs writes and behave accordingly (send writes to all databases, fetch reads from a pool)
In Moodle, 99% of the queries go through a function that knows whether it is read or write (get_records vs insert_record) for example, but that doesn't solve the problem of sending raw sql (execute_sql for example).
You end up either having to parse the query in the database abstraction layer (bad, because as above, SELECTs can actually be writes), or in the pgpool layer (bad, because it doesn't, but is philosophically a better place for it), or in the caller to the database abstraction layer, I mean, where the sql is written (good, because that place knows what sort of query it is -read or write, but bad because it really should be handled somewhere more central).