We run a server network of 2 director servers, 4 pawn servers, 2 image servers and 1 database server. This is an LVS system in which the request are made and the master director points the user to one of the four slaves.

We are running Red Hat LInux 6.1 and MySQL version 3.23. We deliver about 5,000,000 request per day in which about 50% of them are web page request.

On occasion, as you can imagine, we have to backup all database files.

When we dump the mysql data, it locks the tables. When it locks the tables, every single one of the pawn servers stops working. Not completely, but anything that requires PERL doesn't work. Since 99% of our pages and content is delivered using software, this becomes a problem.

We have checked the error_logs and nothing is recorded.

We have tried to check the mysql server logs and nothing.

WeWhen ever we try to run a script through the web, we receive a server error but when we try to run it through SSH it runs???

Any ideas, suggestions ???

Hi,

You say you want to backup MySQL. What program do you use?

From what you're telling, it sounds like you're trying to use mysqldump - don't.

Instead use mysqlhotcopy -- it will probably do what you want!

--
Jens Kristian Søgaard, Mermaid Consulting I/S,
jens@mermaidconsulting.dk,
http://www.mermaidconsulting.com/

Yes, we do use mysqldump - can you tell me why this is bad and why it would freeze the other systems????

Also, we just tried mysqlhotcopy. It locks all the tables, freezes all the systems. The great advantage is that it takes only two minutes rather than the 60 that mysqldump took but it still freezes the systems.

Any ideas why all this would freeze all the systems?? Especially Perl??

Any ideas???

It freezes the whole DB because MySQL is essentially a fancy SQL interface to the file system. MySQL no longer has greater performance than other databases, which is ironic, because performance was the very thing MySQL was aiming for when it was originally coded.

Basically, when you do any sort of MySQL query on a table, it locks that table. This is so that if you run an insert query, and before that ends, do a select query, then you won't get incomplete results. Just about every other DB uses isolation so that until a query is completed, it's not committed.

It's probably too late, but you should seriously consider switching DBs if you are using it for critical data and you have such high-performance needs...just my opinion...

What DB would you suggest??

We considered Oracle but the cost are so high.

MySQL offers the greatest level of support and the best pricing, free.

Any suggestions??

Any of these would probably be fine...
PostgreSQL
Sybase (I've heard really good things about it)
Interbase

I'm sure there are other free ones but I can't think of any now.

qslack: Why do you think that PostgreSQL, Sybase or Interbase would be any different from MySQL regarding locking? (I suppose their backup utitilies don't use row-level locking...)

I think that inbuco should look at the following possibilities:

1. "Backups" not visible from user-land... i.e. using RAID.

2. Re-thinking the database layout and split tables up. This gives smaller tables, but more of them. The trick is, that the mysqlhotcopy utility locks per table -- and so it will lock for a shorter time, and only block smaller parts of your website.

3. Doing backups at night -- having to block your website for 2 minutes at night time - that's a small price to pay for backups.

4. Setup another server and use the replication features of MySQL to create a hot backup without having to block anything. (if you're really smart, this can also be done on just one server).

(substitute night time for the time of day where you have the lowest number of visitors)

--
Jens Kristian Søgaard, Mermaid Consulting I/S,
jens@mermaidconsulting.dk,
http://www.mermaidconsulting.com/

Sybase offers on-line backups with little / no performance penalty. You don't have to offline your DB to backup.