I’m working for one of the biggest (computer related) webshops in the netherlands and there are some problems with the performance of the backoffice system and website. I’ll try to explain what the situation is at the moment:
-CURRENT SITUATION START--------------------------------------
Hardware:
-1 webserver (colocated)
-1 database (mysql 4.1) server (colocated)
-1 local webserver
-40 local backoffice users/clients on a 1.5MB/s line
-a lot of unique visitors a day to our public website
Public website:
-Most of the time, the performance is ok, but it’s not very fast. The whole site is built with PHP and contains a catalog with 20k+ components. Ofcourse there is also a shoppingcart/orderchanger/etc, just a normal webshop.
-about 250 unique visitors a minute at busy moments (mostly at lunchtime and other free moments)
Private backoffice:
-Used by about 30 people at a constant rate during working hours, during these hours it’s relatively slow, at night it’s super fast.
-Every part of the company works with it, it has “everything” )
(scanning received ‘products/orders’/orderpicking/sales at the shop/warrantycases/buying products from wholesellers/financial statistics, well, everything you can think of that’s needed, we don’t use any external software)
-Built with PHP
What we’ve already done:
-Optimized all the queries in the backoffice and website. It took us 3 weeks. We combined queries so they get executed faster, implemented joins (man, that really makes a difference!) and some other stuff… Everything got a lot faster then it was but it’s still slow.
Running processes:
-a maintenance script that runs every 5 minutes, checking for missed inserts with orders and other small stuff, turning this off is nog an option but we tried it as a test and it doesn’t get faster.
-backups(!!): every 2 hours there is a full backup that slows everything down, this takes about 3 minutes for the whole database. We would love to get rid of this but backups are important!
-CURRENT SITUATION END-----------------------------------------
-THOUGHTS AND QUESTIONS----------------------------------------
At the moment we are trying to find ways to speed up everything and we already have some ideas:
Problem: we found that table locks are slowing things down (especially in the backoffice).
Question: At the moment the whole database is in MyISAM, can we just go to InnoDB without problems? At least for some tables this would help because of the table locks. InnoDB uses row locks so other users can work in another part of the table, but we are afraid of encountering dead-locks.
Problem: We are thinking of getting new DB servers but we’re not sure what configuration would be the best.
Planned new hardware:
-same webservers, these work ok and are pretty fast and new(keep 1 colocated and 1 local) they both run at very low loads(0.01 ro 0.1)
-instead of 1 DB server we would like to get 3 in a multiple master config. 2 servers colocated and 1 local db server.
Question: What would be the best config for us (we think circular replication) and what if the local server loses connection to the 2 colocated servers for half an hour? Does mysql deal with this or do we need all kinds of scripts to deal with it?
UPDATE (14-11-2007):
I did some more research on a multiple master configuration and right now I think that the best would be to have 2 masters (1 at colo and 1 local), set up master-master replication and have a slave at both locations which can be master if the current active master fails. Is this a reliable option? Would it be better if we go to 2 clusters with master-master config on replication? (one at each physical location)
Because there are a lot of write actions in the backoffice it’s probably the best to have a local database server. To keep the website fast, we also need a database server in colocation.
Another (kind of weird) option would be splitting up the database. Put the product catalog in the colocated database so it’s fast accessible on the website and put the order tables in the local database. The downside of this would be, if we lose connection to the internet, we lose orders, and we don’t like single points of failure…
If you have any suggestions to make our (speed) problems go away, please let it know. I will try to make this post more complete tomorrow, because I probably have more questions then.
Thanks in advance!