Re: [sqlalchemy] Sharding - same table definitions yet different (but predictable) table names... thousands of them
Thanks for the quick response, Michael. We are mapping the classes dynamically, and we patched an in-house copy of SQLAlchemy 0.5.6 with the stricter mutex from SQLAlchemy 0.6 to fix the sporadic mappers failed to compile errors we were seeing under production load. We also added a Least Recently Used cache for these dynamically mapped tables because we needed the memory usage to max out at something reasonable. When I get a chance, I'll play with UsageRecipes/EntityName to see if differs from our current approach in any significant ways. In the mean time, I'm stuck on another sharding question... which I might ask shortly ;) Thanks again for your time, --diana On Fri, Jan 8, 2010 at 10:32 AM, Michael Bayer mike...@zzzcomputing.com wrote: how does your application want to reference these tables ? while this might be a little memory consuming I'd probably look into generating mappers, Tables, and classes dynamically ala the recipe http://www.sqlalchemy.org/trac/wiki/UsageRecipes/EntityName . If the table is chosen based on the attributes of the class, I'd create new instances of the class using a factory function: for i in range(1, M): table = Table(circle_%d % i, ...) my_registry_of_classes[Circle_%d % i] = \ map_class_to_some_table(CircleBase, table, Circle_%d % i) def myobject(circle): return my_registry_of_classes[Circle_%d% circle]() x = myobject(circle=5) -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Sharding - same table definitions yet different (but predictable) table names... thousands of them
diana wrote: Case 3) In addition to the tables common across the N shards (table_orange, table_yellow, etc), there are a bunch of tables that have the same table definitions, yet different (but predictable) table names. And by a bunch, I mean thousands of each table type per shard. database_shard_1: -- table_circle_1 -- table_circle... -- table_circle_M -- table_ how does your application want to reference these tables ? while this might be a little memory consuming I'd probably look into generating mappers, Tables, and classes dynamically ala the recipe http://www.sqlalchemy.org/trac/wiki/UsageRecipes/EntityName . If the table is chosen based on the attributes of the class, I'd create new instances of the class using a factory function: for i in range(1, M): table = Table(circle_%d % i, ...) my_registry_of_classes[Circle_%d % i] = \ map_class_to_some_table(CircleBase, table, Circle_%d % i) def myobject(circle): return my_registry_of_classes[Circle_%d% circle]() x = myobject(circle=5) -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Sharding - same table definitions yet different (but predictable) table names... thousands of them
Attn Michael Bayer: First off, thank-you so much for SQLAlchemy! I've signed up for your two PyCon 2010 Atlanta tutorials, but I think I'm going to need some guidance sooner rather than later (if possible). (database details below) We've managed to make a legacy database mostly work with SQLAlchemy (using ShardedSession, shard_chooser, id_chooser, query_chooser, etc), but our solution is still falling over in a couple of ways. I am going to step back, create an isolated test environment, and exhaust a few more ShardedSession/BaseDeclarative/SessionQuery approaches before giving up on case #3 and instead migrating the case #3 data to case #2 tables. Once I have testable, isolated code, I'll try the asking the group for concrete suggestions -- but before I do, I wondered if you could answer the following: -- Do you think it's possible to map this crazy layout sufficiently? -- If so, do you have any suggestions/pointers? -- Are you available for fee-based consultation? In-house, corporate group tutorials? Finally, the details -- the three styles (cases) of databases tables I need to map: Case 1) A plain old database without any sharding. [I know how to do this] database_fruit: -- table_apples -- table_... -- table_bananas Case 2) A bunch of normally sharded databases 1 ... N. Let's say N is 100. [Mostly done -- I'm sure I'll figure out the remaining hurdles] database_shard_1: -- table_orange -- table_... -- table_yellow database_shard_...: -- table_orange -- table_... -- table_yellow database_shard_N: -- table_orange -- table_... -- table_yellow Case 3) In addition to the tables common across the N shards (table_orange, table_yellow, etc), there are a bunch of tables that have the same table definitions, yet different (but predictable) table names. And by a bunch, I mean thousands of each table type per shard. database_shard_1: -- table_circle_1 -- table_circle... -- table_circle_M -- table_ -- table_square_1 -- table_square... -- table_square_M database_shard_...: -- table_circle_1 -- table_circle... -- table_circle_M -- table_ -- table_square_1 -- table_square... -- table_square_M database_shard_N: -- table_circle_1 -- table_circle... -- table_circle_M -- table_ -- table_square_1 -- table_square... -- table_square_M Let's say M = 1000, table_circle_1 ... table_circle_M share the same definition, and table_square_1 ... table_square_M share the same definition, etc. Hope that was clear enough. Thanks for your time, --diana -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
