Re: [openstack-dev] Does DB schema hygiene warrant long migrations?
On Oct 24, 2013 11:38 PM, Michael Still mi...@stillhq.com wrote: On Fri, Oct 25, 2013 at 9:07 AM, Boris Pavlovic bo...@pavlovic.me wrote: Johannes, +1, purging should help here a lot. Sure, but my point is more: - pruning isn't done by the system automatically, so we have to assume it never happens I thought this was done to preserve records , as at the time ceilometer didn't exist, and we wanted to make sure nova kept records by default. And we couldn't assume deployers were using db snapshots either. But once we can say don't use nova DB for records, we can start automatically pruning or optionally move over to hard delete. I think there have been some implicit assumptions that all large deployments know about this and manually prune or do something similar. IMHO OpenStack in general needs a stronger record keeping story. As these issues will (or do?) affect other services besides nova. - we need to have a clearer consensus about what we think the maximum size of a nova deployment is. Are we really saying we don't support nova installs with a million instances? If so what is the maximum number of instances we're targeting? Having a top level size in mind isn't a bad thing, but I don't think we have one at the moment that we all agree on. Until that happens I'm going to continue targeting the largest databases people have told me about (plus a fudge factor). Agreed, I am surprised that the 30 million entry DB people aren't pruning somehow (so I assume). I wonder what that DB looks like with pruning. Michael -- Rackspace Australia ___ OpenStack-dev mailing list OpenStack-dev@lists.openstack.org http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev ___ OpenStack-dev mailing list OpenStack-dev@lists.openstack.org http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
Re: [openstack-dev] Does DB schema hygiene warrant long migrations?
-2 to 10 minute downtimes. +1 to doing the evolution gracefully. There is a spec for doing that from the H summit; someone just needs to implement it. -Rob On 25 October 2013 09:30, Michael Still mi...@stillhq.com wrote: Hi. Because I am a grumpy old man I have just -2'ed https://review.openstack.org/#/c/39685/ and I wanted to explain my rationale. Mostly I am hoping for a consensus to form -- if I am wrong then I'll happy remove my vote from this patch. This patch does the reasonably sensible thing of converting two columns from being text to varchar, which reduces their expense to the database. Given the data stored is already of limited length, it doesn't impact our functionality at all either. However, when I run it with medium sized (30 million instances) databases, the change does cause a 10 minute downtime. I don't personally think the change is worth such a large outage, but perhaps everyone else disagrees. Discuss. Thanks, Michael PS: I could see a more complicated approach where we did these changes in flight by adding columns, using a periodic task to copy data to the new columns, and then dropping the old. That's a lot more complicated to implement though. -- Rackspace Australia ___ OpenStack-dev mailing list OpenStack-dev@lists.openstack.org http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev -- Robert Collins rbtcoll...@hp.com Distinguished Technologist HP Converged Cloud ___ OpenStack-dev mailing list OpenStack-dev@lists.openstack.org http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
Re: [openstack-dev] Does DB schema hygiene warrant long migrations?
Hi, 1) If you have 30 million instance it means that you have 300 million instance_system_metadata records All these records will be downloaded every 6 seconds (in periodic tasks) to compute_nodes = which means that OpenStack on scale out of box doesn't work. If you have 3 million instance the situation is the same (OpenStack doesn't work). but migration will be done for 1 minute. So it is maximum 1 minute downtime (in actually non real case). This change is actually very important because VARCHAR works much much faster then BLOB (Text) records. So this is important change and shouldn't be -2. Best regards, Boris Pavlovic On Fri, Oct 25, 2013 at 12:30 AM, Michael Still mi...@stillhq.com wrote: Hi. Because I am a grumpy old man I have just -2'ed https://review.openstack.org/#/c/39685/ and I wanted to explain my rationale. Mostly I am hoping for a consensus to form -- if I am wrong then I'll happy remove my vote from this patch. This patch does the reasonably sensible thing of converting two columns from being text to varchar, which reduces their expense to the database. Given the data stored is already of limited length, it doesn't impact our functionality at all either. However, when I run it with medium sized (30 million instances) databases, the change does cause a 10 minute downtime. I don't personally think the change is worth such a large outage, but perhaps everyone else disagrees. Discuss. Thanks, Michael PS: I could see a more complicated approach where we did these changes in flight by adding columns, using a periodic task to copy data to the new columns, and then dropping the old. That's a lot more complicated to implement though. -- Rackspace Australia ___ OpenStack-dev mailing list OpenStack-dev@lists.openstack.org http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
Re: [openstack-dev] Does DB schema hygiene warrant long migrations?
On Fri, Oct 25, 2013, Michael Still mi...@stillhq.com wrote: Because I am a grumpy old man I have just -2'ed https://review.openstack.org/#/c/39685/ and I wanted to explain my rationale. Mostly I am hoping for a consensus to form -- if I am wrong then I'll happy remove my vote from this patch. This patch does the reasonably sensible thing of converting two columns from being text to varchar, which reduces their expense to the database. Given the data stored is already of limited length, it doesn't impact our functionality at all either. However, when I run it with medium sized (30 million instances) databases, the change does cause a 10 minute downtime. I don't personally think the change is worth such a large outage, but perhaps everyone else disagrees. I'm not sure how you could have 30 million instances. That's a lot of hardware! :) However, in our Rackspace sized deploys (less than 30 million instances), we've seen many migrations take longer than 10 minutes. DB migrations are one of the biggest problems we've been facing lately. Especially since a lot of migrations have been done over the past number of months ended up causing a lot of pain considering the value they bring. For instance, migration 185 was particularly painful. It only renamed the indexes, but it required rebuilding them. This took a long time for such a simple task. So I'm very interested in figuring out some sort of solution that makes database migrations much less painful. That said, I'm hesitant to say that cleanups like these shouldn't be done. At a certain point we'll build a significant amount of technical debt around the database that we're afraid to touch. PS: I could see a more complicated approach where we did these changes in flight by adding columns, using a periodic task to copy data to the new columns, and then dropping the old. That's a lot more complicated to implement though. You mean an expand/contract style of migrations? It's been discussed at previous summits, but it's a lot of work. It's also at the mercy of the underlying database engine. For instance, MySQL (depending the version and the underlying database engine) will recreate the table when adding columns. This will grab a lock and take a long time. JE ___ OpenStack-dev mailing list OpenStack-dev@lists.openstack.org http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
Re: [openstack-dev] Does DB schema hygiene warrant long migrations?
On 10/24/2013 04:40 PM, Boris Pavlovic wrote: Hi, 1) If you have 30 million instance it means that you have 300 million instance_system_metadata records All these records will be downloaded every 6 seconds (in periodic tasks) to compute_nodes = which means that OpenStack on scale out of box doesn't work. If you have 3 million instance the situation is the same (OpenStack doesn't work). but migration will be done for 1 minute. So it is maximum 1 minute downtime (in actually non real case). This change is actually very important because VARCHAR works much much faster then BLOB (Text) records. This is actually not always true. It depends on: * What RDBMS you are using * What storage engine within MySQL, if using MySQL * What the data access/modification patterns on the field are The last bullet is very important. For fields that: * Are not used in predicates or aggregates (i.e. not used in JOIN conditions, WHERE clauses, or GROUP BY/HAVING clauses) * Are rarely read or updated * Are typically longer than 200-300 bytes of data It's typically more efficient (both from a fill factor perspective and from a log structure perspective) to leave the field as TEXT rather than using VARCHAR. The reason is twofold: 1) Using TEXT saves space in the main data pages of the storage engine since a pointer to external data file is stored in the data page instead of the data itself, meaning more records of that table can fit in a single fixed-byte-size data page, which means fewer disk and memory reads to find a record, which means faster seeks and scans. 2) When modifying the value of the TEXT field, there is no chance that a clustered index-organized table layout (like, say, InnoDB uses) would need to perform a rebalancing action due to the new size of the TEXT data causing the record to not fit on its containing data page -- something that would happen a lot more if VARCHAR was used. If the data is: 1) Often used in predicates or aggregates 2) Often updated and the size of the updated field stays a similar range 3) ALWAYS within a small, defined range of bytes (say... 32-64 bytes) Then it's often advantageous to use VARCHAR (or CHAR) over TEXT. But it's wrong to say that it is ALWAYS faster to use VARCHAR vs. BLOB/TEXT. Best, -jay So this is important change and shouldn't be -2. Best regards, Boris Pavlovic On Fri, Oct 25, 2013 at 12:30 AM, Michael Still mi...@stillhq.com mailto:mi...@stillhq.com wrote: Hi. Because I am a grumpy old man I have just -2'ed https://review.openstack.org/#/c/39685/ and I wanted to explain my rationale. Mostly I am hoping for a consensus to form -- if I am wrong then I'll happy remove my vote from this patch. This patch does the reasonably sensible thing of converting two columns from being text to varchar, which reduces their expense to the database. Given the data stored is already of limited length, it doesn't impact our functionality at all either. However, when I run it with medium sized (30 million instances) databases, the change does cause a 10 minute downtime. I don't personally think the change is worth such a large outage, but perhaps everyone else disagrees. Discuss. Thanks, Michael PS: I could see a more complicated approach where we did these changes in flight by adding columns, using a periodic task to copy data to the new columns, and then dropping the old. That's a lot more complicated to implement though. -- Rackspace Australia ___ OpenStack-dev mailing list OpenStack-dev@lists.openstack.org http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev ___ OpenStack-dev mailing list OpenStack-dev@lists.openstack.org http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
Re: [openstack-dev] Does DB schema hygiene warrant long migrations?
On Fri, Oct 25, 2013 at 8:19 AM, Johannes Erdfelt johan...@erdfelt.com wrote: On Fri, Oct 25, 2013, Michael Still mi...@stillhq.com wrote: However, when I run it with medium sized (30 million instances) databases, the change does cause a 10 minute downtime. I don't personally think the change is worth such a large outage, but perhaps everyone else disagrees. I'm not sure how you could have 30 million instances. That's a lot of hardware! :) This has come up a couple of times on this thread, so I want to reinforce -- that database is a real user database. There are users out there _right_now_ with 30 million rows in their instance tables and using nova quite happily. Now, not all those instances are _running_, but they're still in the table. Michael -- Rackspace Australia ___ OpenStack-dev mailing list OpenStack-dev@lists.openstack.org http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
Re: [openstack-dev] Does DB schema hygiene warrant long migrations?
On Fri, Oct 25, 2013, Michael Still mi...@stillhq.com wrote: On Fri, Oct 25, 2013 at 8:19 AM, Johannes Erdfelt johan...@erdfelt.com wrote: On Fri, Oct 25, 2013, Michael Still mi...@stillhq.com wrote: However, when I run it with medium sized (30 million instances) databases, the change does cause a 10 minute downtime. I don't personally think the change is worth such a large outage, but perhaps everyone else disagrees. I'm not sure how you could have 30 million instances. That's a lot of hardware! :) This has come up a couple of times on this thread, so I want to reinforce -- that database is a real user database. There are users out there _right_now_ with 30 million rows in their instance tables and using nova quite happily. Now, not all those instances are _running_, but they're still in the table. Why no pruning? The easiest way to reduce the amount of time migrations take to run is to reduce the amount of rows that need to be migrated. The amount of unnecessary data in tables has been steadily increasing. I'm looking at you reservations table. JE ___ OpenStack-dev mailing list OpenStack-dev@lists.openstack.org http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
Re: [openstack-dev] Does DB schema hygiene warrant long migrations?
On 25 October 2013 10:04, Chris Behrens cbehr...@codestud.com wrote: On Oct 24, 2013, at 1:33 PM, Robert Collins robe...@robertcollins.net wrote: -2 to 10 minute downtimes. +1 to doing the evolution gracefully. There is a spec for doing that from the H summit; someone just needs to implement it. +1. IMO, we need to move to a model where code can understand multiple schemas and migrate to newer schema on the fly. The object code in nova will be able to help us do this. Combine this with some sort of background task if you need to speed up the conversion. Any migrations that need to run through all of the data in a table during downtime is just not going to scale. I am personally tired of having to deal with DB migrations having to run for 1 hour during upgrades that happened numerous times throughout the Havana development cycle. We had a clear design at the H summit, and folk committed to working on it (Johannes and Mark W); not sure what happened... https://etherpad.openstack.org/p/HavanaNoDowntimeDBMigrations -Rob -- Robert Collins rbtcoll...@hp.com Distinguished Technologist HP Converged Cloud ___ OpenStack-dev mailing list OpenStack-dev@lists.openstack.org http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
Re: [openstack-dev] Does DB schema hygiene warrant long migrations?
Johannes, +1, purging should help here a lot. Best regards, Boris Pavlovic On Fri, Oct 25, 2013 at 2:01 AM, Johannes Erdfelt johan...@erdfelt.comwrote: On Fri, Oct 25, 2013, Michael Still mi...@stillhq.com wrote: On Fri, Oct 25, 2013 at 8:19 AM, Johannes Erdfelt johan...@erdfelt.com wrote: On Fri, Oct 25, 2013, Michael Still mi...@stillhq.com wrote: However, when I run it with medium sized (30 million instances) databases, the change does cause a 10 minute downtime. I don't personally think the change is worth such a large outage, but perhaps everyone else disagrees. I'm not sure how you could have 30 million instances. That's a lot of hardware! :) This has come up a couple of times on this thread, so I want to reinforce -- that database is a real user database. There are users out there _right_now_ with 30 million rows in their instance tables and using nova quite happily. Now, not all those instances are _running_, but they're still in the table. Why no pruning? The easiest way to reduce the amount of time migrations take to run is to reduce the amount of rows that need to be migrated. The amount of unnecessary data in tables has been steadily increasing. I'm looking at you reservations table. JE ___ OpenStack-dev mailing list OpenStack-dev@lists.openstack.org http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev ___ OpenStack-dev mailing list OpenStack-dev@lists.openstack.org http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
Re: [openstack-dev] Does DB schema hygiene warrant long migrations?
On Fri, Oct 25, 2013 at 9:07 AM, Boris Pavlovic bo...@pavlovic.me wrote: Johannes, +1, purging should help here a lot. Sure, but my point is more: - pruning isn't done by the system automatically, so we have to assume it never happens - we need to have a clearer consensus about what we think the maximum size of a nova deployment is. Are we really saying we don't support nova installs with a million instances? If so what is the maximum number of instances we're targeting? Having a top level size in mind isn't a bad thing, but I don't think we have one at the moment that we all agree on. Until that happens I'm going to continue targeting the largest databases people have told me about (plus a fudge factor). Michael -- Rackspace Australia ___ OpenStack-dev mailing list OpenStack-dev@lists.openstack.org http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
Re: [openstack-dev] Does DB schema hygiene warrant long migrations?
On Thu, Oct 24, 2013 at 3:06 PM, Robert Collins robe...@robertcollins.netwrote: On 25 October 2013 10:04, Chris Behrens cbehr...@codestud.com wrote: On Oct 24, 2013, at 1:33 PM, Robert Collins robe...@robertcollins.net wrote: -2 to 10 minute downtimes. +1 to doing the evolution gracefully. There is a spec for doing that from the H summit; someone just needs to implement it. +1. IMO, we need to move to a model where code can understand multiple schemas and migrate to newer schema on the fly. The object code in nova will be able to help us do this. Combine this with some sort of background task if you need to speed up the conversion. Any migrations that need to run through all of the data in a table during downtime is just not going to scale. I am personally tired of having to deal with DB migrations having to run for 1 hour during upgrades that happened numerous times throughout the Havana development cycle. We had a clear design at the H summit, and folk committed to working on it (Johannes and Mark W); not sure what happened... /me runs from room crying https://etherpad.openstack.org/p/HavanaNoDowntimeDBMigrations -Rob -- Robert Collins rbtcoll...@hp.com Distinguished Technologist HP Converged Cloud ___ OpenStack-dev mailing list OpenStack-dev@lists.openstack.org http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev ___ OpenStack-dev mailing list OpenStack-dev@lists.openstack.org http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
Re: [openstack-dev] Does DB schema hygiene warrant long migrations?
Michael, - pruning isn't done by the system automatically, so we have to assume it never happens We are working around it https://blueprints.launchpad.net/nova/+spec/db-purge-engine - we need to have a clearer consensus about what we think the maximum size of a nova deployment is. Are we really saying we don't support nova installs with a million instances? If so what is the maximum number of instances we're targeting? Having a top level size in mind isn't a bad thing, but I don't think we have one at the moment that we all agree on. Until that happens I'm going to continue targeting the largest databases people have told me about (plus a fudge factor). Rally https://wiki.openstack.org/wiki/Rally should help us to determine this. At this moment I can just use theoretical knowledges. (and they said even 1mln instances in current nova implementation won't work) Best regards, Boris Pavlovic On Fri, Oct 25, 2013 at 2:35 AM, Michael Still mi...@stillhq.com wrote: On Fri, Oct 25, 2013 at 9:07 AM, Boris Pavlovic bo...@pavlovic.me wrote: Johannes, +1, purging should help here a lot. Sure, but my point is more: - pruning isn't done by the system automatically, so we have to assume it never happens - we need to have a clearer consensus about what we think the maximum size of a nova deployment is. Are we really saying we don't support nova installs with a million instances? If so what is the maximum number of instances we're targeting? Having a top level size in mind isn't a bad thing, but I don't think we have one at the moment that we all agree on. Until that happens I'm going to continue targeting the largest databases people have told me about (plus a fudge factor). Michael -- Rackspace Australia ___ OpenStack-dev mailing list OpenStack-dev@lists.openstack.org http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev ___ OpenStack-dev mailing list OpenStack-dev@lists.openstack.org http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
Re: [openstack-dev] Does DB schema hygiene warrant long migrations?
So, I observe a consensus here of long migrations suckm +1 to that. I also observe a consensus that we need to get no-downtime schema changes working. It seems super important. Also +1 to that. Getting back to the original review, it got -2'd because Michael would like to make sure that the benefit outweighs the cost of the downtime. I completely agree with that, so far we've heard arguments from both Jay and Boris as to why this is faster/slower but I think some sort of evidence other than hearsay is needed. Can we get some sort of benchmark result that clearly illustrates the performance consequences of the migration in the long run? -Mike On Thu, Oct 24, 2013 at 4:53 PM, Boris Pavlovic bo...@pavlovic.me wrote: Michael, - pruning isn't done by the system automatically, so we have to assume it never happens We are working around it https://blueprints.launchpad.net/nova/+spec/db-purge-engine - we need to have a clearer consensus about what we think the maximum size of a nova deployment is. Are we really saying we don't support nova installs with a million instances? If so what is the maximum number of instances we're targeting? Having a top level size in mind isn't a bad thing, but I don't think we have one at the moment that we all agree on. Until that happens I'm going to continue targeting the largest databases people have told me about (plus a fudge factor). Rally https://wiki.openstack.org/wiki/Rally should help us to determine this. At this moment I can just use theoretical knowledges. (and they said even 1mln instances in current nova implementation won't work) Best regards, Boris Pavlovic On Fri, Oct 25, 2013 at 2:35 AM, Michael Still mi...@stillhq.com wrote: On Fri, Oct 25, 2013 at 9:07 AM, Boris Pavlovic bo...@pavlovic.me wrote: Johannes, +1, purging should help here a lot. Sure, but my point is more: - pruning isn't done by the system automatically, so we have to assume it never happens - we need to have a clearer consensus about what we think the maximum size of a nova deployment is. Are we really saying we don't support nova installs with a million instances? If so what is the maximum number of instances we're targeting? Having a top level size in mind isn't a bad thing, but I don't think we have one at the moment that we all agree on. Until that happens I'm going to continue targeting the largest databases people have told me about (plus a fudge factor). Michael -- Rackspace Australia ___ OpenStack-dev mailing list OpenStack-dev@lists.openstack.org http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev ___ OpenStack-dev mailing list OpenStack-dev@lists.openstack.org http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev ___ OpenStack-dev mailing list OpenStack-dev@lists.openstack.org http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
Re: [openstack-dev] Does DB schema hygiene warrant long migrations?
On 10/24/2013 05:19 PM, Johannes Erdfelt wrote: On Fri, Oct 25, 2013, Michael Still mi...@stillhq.com wrote: Because I am a grumpy old man I have just -2'ed https://review.openstack.org/#/c/39685/ and I wanted to explain my rationale. Mostly I am hoping for a consensus to form -- if I am wrong then I'll happy remove my vote from this patch. This patch does the reasonably sensible thing of converting two columns from being text to varchar, which reduces their expense to the database. Given the data stored is already of limited length, it doesn't impact our functionality at all either. However, when I run it with medium sized (30 million instances) databases, the change does cause a 10 minute downtime. I don't personally think the change is worth such a large outage, but perhaps everyone else disagrees. I'm not sure how you could have 30 million instances. That's a lot of hardware! :) However, in our Rackspace sized deploys (less than 30 million instances), we've seen many migrations take longer than 10 minutes. DB migrations are one of the biggest problems we've been facing lately. Especially since a lot of migrations have been done over the past number of months ended up causing a lot of pain considering the value they bring. For instance, migration 185 was particularly painful. It only renamed the indexes, but it required rebuilding them. This took a long time for such a simple task. So I'm very interested in figuring out some sort of solution that makes database migrations much less painful. That said, I'm hesitant to say that cleanups like these shouldn't be done. At a certain point we'll build a significant amount of technical debt around the database that we're afraid to touch. PS: I could see a more complicated approach where we did these changes in flight by adding columns, using a periodic task to copy data to the new columns, and then dropping the old. That's a lot more complicated to implement though. You mean an expand/contract style of migrations? It's been discussed at previous summits, but it's a lot of work. It's also at the mercy of the underlying database engine. For instance, MySQL (depending the version and the underlying database engine) will recreate the table when adding columns. This will grab a lock and take a long time. http://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl.html http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html#innodb-online-ddl-summary-grid Add column is an online operation in modern MySQL. If you are running a real production system, you should ALWAYS use current MySQL. If you are out there, and you have a schema large enough for this to be an issue, you need to be running modern MySQL. That said - I TOTALLY support all of the statements above about doing the schema upgrades in a sane manner. It's the right thing to do. ___ OpenStack-dev mailing list OpenStack-dev@lists.openstack.org http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
