11

To those of you that are trying to be good little developers and version control their ExpressionEngine sites with git, how do you handle your database?

In my limited experience with multiple developers working on one ExpressionEngine site, we've had to all run off of a single MySQL development database running on a remote web server. For those of you that have tried this, it is PAINFULLY slow. Page loads can easily take 5-10 seconds making development extremely difficult. It would be quicker to work off of a remote development server. I am trying to steer away from working off of a remote MySQL server in order to be able to work from anywhere and not depend on Internet connection speed/quality.

Just wondering how others handle their MySQL databases.

Do all of your developers run off of one central database? Have you dealt with slowness issues like we have?

Do you keep your database under version control? How do you handle export/imports among multiple developers and multiple branches?

With one developer I can import/export/commit the database very easily but as soon as you add another developer to the mix, it gets very VERY muddy. Looking forward to hearing everyone's thoughts on this mammoth topic.

Thanks!

Jacob Graf
  • 323
  • 1
  • 9

4 Answers4

7

It seems there is a lot of time lost on failing DNS requests, with a remote database.

Start your MySQL server with start mysqld with --skip-name-resolve. (More information on this topic can be found here: http://dev.mysql.com/doc/refman/5.0/en/host-cache.html)

Having a remote database still seems to be the best way for us to work on a project with multiple developers.

Filipvds
  • 71
  • 1
  • Thanks for the input. I gave the skip-name-resolve flag a try and it didn't speed anything up. When you work on remote databases, do pages take 5-10 seconds to load? – Jacob Graf Nov 07 '12 at 15:26
  • 2
    In fact if you are using MAMP or MAMP Pro, the TCP listener is disabled by default, for better security and performance. – pvledoux Nov 07 '12 at 16:20
  • pvledoux, did I miss something? How does your response fit in? – Jacob Graf Nov 07 '12 at 20:56
  • @JacobGraf Sorry my response was a bit rough. skip-name-resolve 'just' disabled DNS name resolution for client connecting to Mysql. But if you use MAMP/MAMP Pro for local dev, you do not need TCP/IP as you connect directly to Mysql through the socket. On Windows, you can do the same by using shared memory instead of file socket. – pvledoux Nov 08 '12 at 09:42
5

I almost always use a central database for development. Depending which host you use, the speed difference may not be huge.

Obviously, if you're not making changes to the database, i.e. only doing template development, keeping the database in sync is not as needed, so you could potentially bring up a local copy of the database. You just have to remember to repeat any database changes, if you do end up making some.

As far as version control, I keep a copy of my base EE install's SQL file in my base repository. Other than that I don't usually keep copies of the database in Git, so I don't do a lot of importing/exporting, etc.

Jeremy Gimbel
  • 371
  • 2
  • 5
  • I get about 5-10 seconds of lag per page when running off a remote DB. I wonder if something else is going on here. I understand it maybe being a little slower (e.g. 1 sec load times) but 5-10 is outrageous. I wonder if something else is hanging it up. I am on a WHM/cPanel dedicated virtual server. Not sure even where to START troubleshooting! :-) – Jacob Graf Nov 07 '12 at 15:29
  • Yeah, I'm definitely not a MySQL expert, and I've definitely experienced lag before. Generally hosts that do fancy things with database pools tend to be really slow from the outside. But I guess it really just depends. – Jeremy Gimbel Nov 07 '12 at 15:33
2

In my company (4 developers) we each run our own DB locally. But recently I tested Rackspace Cloud Databases (but there are other cloud db providers) for a heavy DB that could become difficult to run on a little laptop. It's relatively less expensive than running our own db server, and it can be setup or deleted in the minute.

pvledoux
  • 973
  • 1
  • 10
  • 23
  • Interesting. I am going to look into this JUST for development and then move it to localhost after launch. Thanks! – Jacob Graf Nov 07 '12 at 15:30
  • 1
    I provisioned a remote MySQL database on a MediaTemple (gs) account and it made it worse so I thought I would try Amazon RDS. Started with a micro instance. It helped speed things up WAY more than MediaTemple, but only a little bit faster than my regular VPS. I thought I would try a little experiment so I provisioned a quadruple ram instance on RDS (64GB RAM, mega-instance) If I would have left it run, it would have cost me $1700/mo. I tested it and it was a LITTLE better than my VPS, but DEFINITELY not $1700/mo better so I killed it. :-) – Jacob Graf Nov 07 '12 at 18:19
2

Have you looked at the EE Profiler recently? You'll probably notice in the neighborhood of 20-80 queries on your home page depending on it's complexity.

The problem is that, for each query, MySQL must execute a remote request for data, download the response, and then present ExpressionEngine it's data. The 20-80 round trips to the database is what's causing your delay and I don't think there is much you can do about it. When using a remote (outside our network) database, I get the same delay as you.

When MySQL is running on your machine or the production server, it doesn't have the added network requests causing latency in it's requests for data. This is the difference.

As for fixes, all you can do is move to a database hosted on your internal network. We have a Linux machine that mimics our production environment that we use for staging. Since it's on our network, we can use the local IP address in our database.php file. This is much faster.

The problem that we still have is the issue of channels/fields/entries. When a developer is working on a new section, they'll likely need to create a new channel and fields and/or new entries. When we're ready to push that functionality to production, we have to manually make those changes on the production server as there is no way to reliably export them. I am hopeful of this addon though---we'll see.

Jesse Bunch
  • 6,651
  • 4
  • 36
  • 59
  • Thanks for the detailed response! Makes total sense! The only issue is that usually if I am working with a developer, they are not in the same state as me let alone the same office! :-) If someone could figure out a way to crack the version-controlled database case, they will make a LOT of money. I also am hopeful of Site Manager, but it's not as seamless as a git-like database syncing tool. – Jacob Graf Nov 07 '12 at 18:23
  • Agreed. Frameworks use a concept called *migrations* which allows you to script such changes to the DB. Then, you could run the migration scripts to make the changes. The problem here is that there could be conflicts with the data. Even if you could reliably resolve those conflicts, things like entry IDs won't match and may break your code--depending on how you've built it. A real conundrum for sure. – Jesse Bunch Nov 07 '12 at 19:50
  • Oh, and all of that work would probably be out of the normal day-to-day knowledge that a typical EE designer/developer would have. – Jesse Bunch Nov 07 '12 at 19:52