mysql, postgresql or both?

rulian

Hello All,

I'm currently in the planning stages of developing my first "grand scale" database driven website that will require alot of user information and user billable account transactions.

I've been researching MySQL relating to performance and reliability and have concluded that mysql can handle the application side of the website fine, since the data isnt life or death stuff, I'm not terribly worried about things going corrupt here or there, as my research has pointed out can (but rare does) occur in mysql, and granted can occur in any rdbms.

However, it's been suggested to me, and I've read conflicting articles that PostgreSQL is a more reliable transaction server for handling the billing of user accounts, I've worked a little bit with PostgreSQL before, not enough to be comfortable and would rather stay in mysql.

I'd like to know your opinion on MySQL vs PostgreSQL for handling large volumes of data, and transactions, and if the case the MySQL should be trusted for transactions, even with InnoDB, is it smart to use postgre for user and mysql for app?

Sxooter

See this previous post on the subject.

rulian

I'm running tests to see if my queries are optimized and my database can handle what the website needs

Part of the application will be basically a friends bucket like myspace or facebook.

I have the following two tables in my test


CREATE TABLE `users` (
  `uid` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `age` int(11) NOT NULL,
  `type` int(11) NOT NULL,
  PRIMARY KEY (`uid`),
  KEY `name` (`name`),
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


CREATE TABLE `friends` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `myid` int(11) NOT NULL,
  `uropt` int(11) NOT NULL,
  `fid` int(11) NOT NULL,
  `uiopt` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `myid` (`myid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Us

er table is obviously name, age, account type,

the friends table is basiically a opt in / out firend relationship thing

it's user request | user opt in status (1=pending, 2=approve, 3=denied, 4=banned) | friend id requeted | friend opt status (same thing as user)

So some basic infor for user would be
ID | SHIRLY | 23 | 2
ID | BOB | 32 | 2
ID | KELLY | 18 | 2
ID | RICH | 22 | 2

The Friend table basic info is

ID | 232 | 2(defualt for request) | 9232 | 1 -- pending
ID | 223 | 2 | 9232 | 2 - friends
ID | 223 | 3 | 9232 | 2 - user 1 deleted
ID | 223 | 2 | 9232 | 4 - user 2 banned

I have 1500 rows in the USER Table
and 1,500,000 Rows in the friends table for testing.

This SQL ranges from 1.5 secs to .00226 ses:

SELECT `t1`.`name`, `t1`.`age`, `t3`.`name` AS `friend` FROM `users` AS `t1`
LEFT JOIN `friends` AS `t2` ON (`t1`.`uid` = `t2`.`myid`)
LEFT JOIN `users` AS `t3` ON (`t2`.`fid` = `t3`.`uid`)
WHERE `t1`.`uid` = '150' LIMIT 0, 1000

while this sql take a range from .5 to .0125

SELECT `id` FROM `friends` WHERE `myid` = '232' LIMIT 1000

What I want is to be able to procude ID numbers of a person's friends, and their friend's friends' To allow for who can click on what

So far considering the rows number in the 1.5 million, i think my times are alright on my working computer that also runs as my develpment server.

One question I do have,
What is the best approach to pair id's from the friends table with info from the User table without doing 3 levels of left joins?

Sxooter

That's pretty much it. You've already got indexes on those fields, so there's not much else you can do.

You should look at how it scales as your table size grows.

Eventually you'll reach a point where your data set is bigger than the memory on your database server, if the site is popular, and that's when the performance of your db engine really starts to matter.

MarkR

You should definitely NOT use more than one database - it will just consume unnecessary resources and make the application less maintainable.

Although MySQL and Postgres can both do joins, I'm willing to bet that it's tricky to joins between tables some of which are stored in each.

Unless your planned architecture already includes several different database servers, don't install more than one different product.

Even then, be very wary of doing so - your developers will need to learn several sets of technology, you'll need your support staff to be able to support them both etc - plus it will waste space and RAM.

A properly supported database with competent staff managing it should NOT lose ANY data in normal operation. Even in the event of an unlikely type of failure (say both redundant discs failing on the master database server), the amount of data lost should be very small (e.g. back to the last backup - or perhaps you have a replica as a hot spare or something)

Mark

Sxooter

I have to agree with MarkR on this one, running two different databases rarely makes sense.

Design a good db server to handle the load. The most overlooked part of db servers for beginners is the disk subsystem. A good RAID controller with battery backed cache and lots of drives is essential for a fast db server. RAID10 for for transactions, RAID6 (or 5 if your controller doesn't support 6) for reporting / OLAP generally. Then lots of memory, then lots of CPU power, in that order.

jazz_snob

This site is running mysql 4 in your usual sham set up provided by shared hosts (no transactions, no foreign keys, myisam tables). It has lots of data, interconnected tables and lots of traffic.

The original code was developed on mysql 4 w/ innoDB tables and includes transactions and foriegn keys. They are still there, just being ignored by the production database.

I don't care about benchmarking until I actually have a problem. It happened on this site. I had to split a big query that tried to get all the necessary data at once into a couple querries. The performance was like 10x better.

I did use explain to analyze my select statements:
http://www.databasejournal.com/features/mysql/article.php/1382791

Overall, I consider postgresql a superior database, but if you want fast and not guaranteed accurate is OK, use mysql.

Sxooter

jazz_snob wrote:
I don't care about benchmarking until I actually have a problem. It happened on this site. I had to split a big query that tried to get all the necessary data at once into a couple querries. The performance was like 10x better.

Well, there's a fine line between avoiding premature optimization and waiting until you have a fire to put out.

Some databases are simply the wrong choice for some loads, and starting with them is a bad idea (TM). For instance, if you need to handle terabytes of data, you should really test the databases you're looking at with terabytes of data. Waiting until you have terabytes of data is waiting too long.

OTOH, if you're going to handle a few gigabytes of data, you wait a while to get worried, as long as your queries aren't too insane or anything.

Overall, I consider postgresql a superior database, but if you want fast and not guaranteed accurate is OK, use mysql.

I think they're both fast. But if you're already familiar with MySQL, it could take a while to get really proficient with another db, and the time spent doing so might not gain you any real advantage.

OTOH, if you have the time and inclination, it might be a nice bit of self education to work with a different db.

jazz_snob

Actually the differences between using the postgresql and mysql should be minimal if you start out with mindset that you are going to program to sql standards and not to special features that a certain vendor might offer. Sure, setting up the tables is completely different. I did a classifieds site in perl that could use either database, and there were literally only a couple lines different.

[edit]
just to clarify, I mean the actually query strings shouldn't be that different...like I've already mentioned postgresql is, to me, a better database; for example: if you tell it a column must have a certain type of data it will complain noisily if you try to put something else in there rather than mindlessly pollute your database with junk, which is what mysql will happily do
[/edit]

MarkR

Regardless of whether you consider the differences between pg and mysql "minimal", it won't exempt you from thorough testing.

You can't assume something will just work the same because it uses a standards-compliant query.

Trying to make the code compatible with more than one software set is something which gives no advantage and massively increases your testing workload.

The most important performance to optimise is developer-time - this is in generally short supply regardless of what technology you're using.

You MUST only deploy on to the exact platform you've developed against, and must have carried out rigorous testing on that, and only that platform. Testing or development on other platforms is irrelevant and proves nothing.

Mark

Roger_Ramjet

MarkR wrote:
The most important performance to optimise is developer-time - this is in generally short supply regardless of what technology you're using.

Mark

No, the most important performance to optimise is USER time since this will always have the biggest impact on the bottom line.

It is the missing component from most TOC formulas and calculations but us also the biggest - the wages and salaries of the hundreds of staff who will use a system for probably many years far outweighs both the capital expenditure and running costs of any system. If you can save 5 minutes per user per week for hundreds of users you can pay for several IT staff and still be in profit.

If your user community is external then they will be running that calculation in the back of their own minds: do the benefits of using your web service outweigh the personal costs in time and effort required to do so. If they feel they don't then you will loose them. System response is a key factor in the user experience; and a great user experience is critical to the success of any web service. After all, no matter how original your idea and service is, once it is out there anyone can copy it.