Re: [GENERAL] prelimiary performance comparison pgsql vs mysql
Mark Rae wrote: On Tue, Mar 15, 2005 at 12:51:03PM -0800, Jeff Davis wrote: Be careful assuming that. DB benchmarks are hard to do in a general sense. His results probably indicate a general trend, but you should test your application yourself to get a real result. His pattern of SQL queries might be very different from yours. Very true. You may have noticed that I had a very low query rate of 5.8 queries per second, because some of the queries have 12 tables to join and take about 20s to run. This tends to work in postgres' favour. If you have lots have simple queries, it will be better for mysql and the break even point will be higher. Also, while on the subject of scaling. I had the opportunity to try postgres on a 16CPU Altix and couldn't get it to scale more than about 4x, whereas Oracle got up to about 12x faster I assume this is because of the NUMA architecture. I was also told that Oracle had made no special optimizations to accomodate it. My guess is that because postgres allocates all its shared buffers as a contiguous chunk, it puts all the load on one memory bank. Oracle on the other hand, seems to use lots of smaller regions which would probably be spread throughout the physical memory. Perhaps one of the developers could comment on how difficult it would be to change the shared buffer handling to use multiple segments. As I'd definitely be willing to give it a go. -Mark a bit of info re mysql and speed with concurrent transactions. a community site I was working to get running a bit better was using phpnuke and mysql. ( not my site, was a contract ) with 56,000 members the site was bogged down almost to the point of timing out, this was with only 100 or so users online. another community site, with custom script using mysql backend, sperad over several servers rather than one machine, and 250,000 members. ( 4 terabytes data transfer a month minimum. ) it's often slow responding, but doesn't get close to a timeout. while these are subjective observations, they show that tuning, and structure of application will have a significant affect, more than would generally be assumed. mysql is a good application, for lower traffic applications [ local intranet with 100 users ], but I would never actually recommend using mysql on a large database.[ large query useage would be horrendously slow ] Jaqui smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] PostgreSQL still for Linux only?
Uwe C. Schroeder wrote:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On Tuesday 08 March 2005 07:24 pm, Tope Akinniyi wrote:
Hi,
I am wondering at this display of extreme Linux mentality being displayed
by the 'top bras' of the PostgreSQL community. And I ask, are we
encouraging Windows use of PostgreSQL at all?
Take a look at tools being rolled out at PgFoundry on daily basis; all for
Linux except the Windows installer. I ask myself what is being done to
encourage PostgreSQL Windows users. Nothing is available to them except
the Database and PgAdmin. No replication tool, no this, no that.
To be honest - I wouldn't encourage the use of PostgreSQL on Win.
Neither would I for any database or data warehouse application (which probably
is why SAP put onto their website that they prefer linux to windows
platforms).
I think it could even damage the quite good reputation of PostgreSQL - if your
windows box crashes and takes the DB with it - most likely it's not the fault
of a lousy OS, nor the fault of an incompetent sysadmin who forgot to make
backups - it will be this shitty free database system that's to blame.
I wrote quite some software that uses postgresql - never would I tell any
customer that he could now run it on windows. As a matter of fact I put code
like:
if os=win {
errormessage(this software is not ported to windows yet);
exit(99);
}
into the startup routine - just to make it impossible for the customer to run
it on windows.
I was troubled when CommandPrompt, the leading Windows support provider
responded to a post that their plPHP is for Linux only.
Sorry for this: Firebird provides equal tools for Linux and Windows users.
We are not the one to tell the Windows users whether they need them.
Firebird was a DOS ISAM DB. It just made it's way to *nix a couple years ago.
Whether Windows is bad or good; Linux is the angel and Windows the devil is
not the issue here. PostgreSQL has gone the Windows way and must not be
shown to be deficient.
The problem is, that it's a question of perception. Most windows fans don't
see that their OS is pretty instable. So it's not a question if the
community can do anything to make PostgreSQL look deficient - it's a question
of what people do with it on Win. I had a similar case recently with a
customer: His MS Office suite crashed at least 3 times a day. So I switched
him to OpenOffice. Now OO crashed once after a month of perfect operation -
guess what, the customer is back to MS Office because OO crashed on him and
MS has this new version that's sooo much better. Call it dumb - but that's
how a lot of people are. Well, he paid a couple $k to get new licenses and is
back where he was a month ago.
I am not holding anybody responsible, but I think we need to do a massive
re-orientation of the community not to carry the Linux-Windows game too
far.
It's just a fact: any unix is a better platform for databases than windows.
Windows was designed (and mostly still is) as a Desktop operating system -
and it's fairly good on the desktop.
according to billy boy himself,
windows is designed to make it easier and more entertaining for people
to play video games on thier home computer*
so not even dektop, it was never meant for professional use.
Never trust a server that needs a mouse
attached to operate properly. Unix was designed with scalability, stability
and multiuser-operation in mind - and that's what it's good at. I wouldn't
want my payroll on a windows box - much less my company data.
UC
*Bill Gates in press conference introducing windows 1.0 to the world.
personally, even the nt family, with the absolute requirement of using
video gaming technology, is not a professional os.
smime.p7s
Description: S/MIME Cryptographic Signature
Re: [GENERAL] postgresql vs mysql performance comparison
Rick Schumeyer wrote: I'm interested in comparing the performance of postgresql and mysql on various combinations of user loads and database sizes. I have seen a few statements to the effect of mysql is faster for small, low use applications, but postgresql is better for bigger stuff. I would like to run some independent tests to see if this is true. Before I do this, has anyone done this already, with the latest versions of both? I think I've seen some comparisons that are somewhat dated. Does anyone have any pointers on what to do or not do? Or would anyone be willing to comment on an experiment plan and suggest improvements? Any help or references are appreciated. mysql is finally getting to be closer in full capabilities to postgresql. some of the newest data isn't yet tested, as they are just releasing a version that can do what postgresql has done for a while. free webcast on wednessday this week about it: http://www.mysql.com/news-and-events/web-seminars/eds-goldengate-mysql.php mysql has been optimised for speed of porcesses, not security and enterprise class db apps. postgresql has been more focusses on the later, as that is where the real usage is. smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] Blob Fields
Alexandre da Siva wrote: Blobs is not Implemented on PostgreSQL, but I need to this field type on PosgreSQL databases, how I can to use this? I'm using delphi... ps: I readed PosgreSQL Manual and other lists and sites, but not get a answer for my specific problem http://www.postgresql.org/docs/8.0/interactive/datatype-binary.html definitions for blob, with usage. smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] regular expressions in query
Lincoln Yeoh wrote: At 09:57 AM 2/13/2005 +, Russ Brown wrote: I've thought about things like this in the past, and a thought that occurred to me was to add a functional index on just_digits(telephone) to the table. Would this not allow the above query to use an index while searching? I think it should. But for phone numbers it may be better to reverse the digits before indexing - usually whilst the area code changes, the last 4 or 5 digits don't change. This way you can do a LIKE search on *5678. Where the number ends with 5678. I'm not sure how to get Postgresql to index from the ending to the start of a string vs the normal from the start to the end, so in my webapp I reversed it at the application layer. If you are going to do this sort of thing at the application layer you might as well do the nondigit removal there too. e.g. $phone=~tr/0-9%_//cd; # I allowed the wildcards % and _ $phone=reverse $phone; You may still wish to store the phone numbers as is for display purposes. Link. make sure the table stores as text rather than as numeric data. then you can use the excellent perl string tools to pull the last 4 characters of the number. $base=((strlen-4,strlen) $base being the last 4 digits. then convert to numeric to test against search requirements. Jaqui ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] [pgsql-advocacy] MySQL worm attacks Windows servers
Jan Wieck wrote: On 1/30/2005 10:18 AM, Peter Eisentraut wrote: Dawid Kuroczko wrote: I think it is in good taste that when you find a bug/vulnerability/etc first you contact the author (in this case: core), leave them some time to fix the problem and then go on announcing it to the world. In this case, core is not the author of the object in question. And of course, to report a bug/vulnerability/etc you would write to pgsql-bugs, not core. No, Peter. Posting a vulnerability on a public mailing list before there is a known fix for it means that you put everyone who has that vulnerability into jeopardy. Vulnerabilities are a special breed of bugs and need to be exterminated a little different. Jan ain't that the truth. if a vulnerability is found, try to find a fix, or work around, post it privately to the developer, give them an opportunity to get it fixed before going public. when dealing with open souurce, this system works great. when dealing with proprietary / closed source [ specifically microsoft ] expect that it's the public announcement that's going to start them doing something about it. I personally would only give ms a week at most to fix the problem before going public. since open source if usually fixed in that time frame. Jaqui ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Unique Index
Tino Wildenhain wrote: Hi, Am Mittwoch, den 19.01.2005, 15:02 -0800 schrieb J. Greenlees: Roman Neuhauser wrote: # [EMAIL PROTECTED] / 2005-01-20 01:35:32 +1100: i have a unique index on a table over multiple columns. If now one of the records has a null value in one of the indexed columns i can insert the same record multiple times. Is this a problem within postgres or expected? In SQL, NULL means unknown value. How could you assert that two NULLs are equal? which doesn't make mathematical sense. mathwise null is an empty result. so setting the logic up using the math logic, null values are always equal. What kind of mathematics you are speaking? For example you have infinity where infinity is never equal to infinity. Same with null. Which is unknown or undefined So if x is undefined and y is undefined you cannot assume x=y - because if you assume this, then they would not be undefined anymore. q.e.d. Regards Tino or null as in empty. an empty result set is a null set, zero results. declare a variable, but never assign a value, it has a default value of null from the declaration. ( basically any content of memory space allocated that was not actually empty is the content, but it's a null value to the app. ) -- only plain text format email accepted. smaller file size, no virus transfer no proprietary file formats. smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] what happened to the website?
Dick Davies wrote: http://www.postgresql.org looks bloody awful in firefox on debian, until I switch font (on the site) from 'normal' to 'large'. Anyone else seeing that? I'm sure it was fine a couple of weeks back. don't know about the fonts, but 15 minutes and still trying to load it. several other sites, fast, fully loaded in less than a second. -- only plain text format email accepted. smaller file size, no virus transfer no proprietary file formats. smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] Unique Index
Roman Neuhauser wrote: # [EMAIL PROTECTED] / 2005-01-20 01:35:32 +1100: i have a unique index on a table over multiple columns. If now one of the records has a null value in one of the indexed columns i can insert the same record multiple times. Is this a problem within postgres or expected? In SQL, NULL means unknown value. How could you assert that two NULLs are equal? which doesn't make mathematical sense. mathwise null is an empty result. so setting the logic up using the math logic, null values are always equal. -- only plain text format email accepted. smaller file size, no virus transfer no proprietary file formats. smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] ntfs for windows port rc5-2
Magnus Hagander wrote: rc5-2 msi will not install at all on a fat32 filesystem even without initialising the database. Really? The code for checking the filesystem type is only executed if you chose to initdb, so I really don't see this happening. Exactly what message do you get? Log in the temp install dir: The Cacls command can be run only on disk drives that use the NTFS file system I'll have to rip half or more of the full log as it seems to be to large for the list to accept -- only plain text format email accepted. smaller file size, no virus transfer no proprietary file formats. smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] ntfs for windows port rc5-2
Magnus Hagander wrote: rc5-2 msi will not install at all on a fat32 filesystem even without initialising the database. Really? The code for checking the filesystem type is only executed if you chose to initdb, so I really don't see this happening. Exactly what message do you get? Log in the temp install dir: The Cacls command can be run only on disk drives that use the NTFS file system I'll have to rip half or more of the full log as it seems to be to large for the list to accept I assume you are talking about the initdb.log file? That file is created by initdb.bat, which should only be called when you choose to run initdb. Exactly which options did you specify during the installation? //Magnus with msi installer, options are only for where to install, until initdb stage. chose no at that point, and it installs, then errors and completely un-installs. leaving a dir struct under program files with a single file: pgperm.log under the directory with the msi files in it there is a full install log, which the list has twice refused to accept as being to large. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] ntfs for windows port rc5-2
Martijn van Oosterhout wrote: On Fri, Jan 14, 2005 at 08:39:28AM -0800, J. Greenlees wrote: why? since an app that I'm working on would be useless for 60% of potential clients, using posgresql with the requirement for ms' corrupted ntfs means postgresql isn't going to work for it. I think what you are referring to is the installer refusing to install on a NTFS partition. From the FAQ: http://pginstaller.projects.postgresql.org/FAQ_windows.html 2.4) Can I install PostgreSQL on a FAT partition? PostgreSQL's number one priority is the integrity of your data. FAT and FAT32 filesystems simply do not offer the reliabilty required to allow this. In addition, the lack of security features offered by FAT make it impossible to secure the raw data files from unauthorised modification. Finally, PostgreSQL utilises a feature called 'reparse points' to implement tablespaces. This feature is not available on FAT partitions. snip It is recognised however, that on some systems such as developer's PCs, FAT partitions may be the only choice. In such cases, you can simply install PostgreSQL as normal, but without initialising the database cluster. When the installation has finished, manually run the 'initdb.exe' program on the FAT partition. Security and reliability will be compromised however, and any attempts to create tablespaces will fail. since ms does not include a compiler, and the source for 8.0 won't cross compile from linux. ( gcc 3.3.0 ) To compile the native port on Windows you need MinGW. And there's always the Cygwin port still. See: http://www.postgresql.org/files/documentation/faqs/text/FAQ_MINGW Hope this helps, rc5-2 msi will not install at all on a fat32 filesystem even without initialising the database. sorry but whole purpose of putting it on a windows box was to make db app for a 250,000 person client base. with some still using win95, some win 98, some winme. all of which do not have ntfs support. since the app will not be world accessable, only through localhost, the lack of security isn't a major concern. -- only plain text format email accepted. smaller file size, no virus transfer no proprietary file formats. smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] ntfs for windows port rc5-2
Frank D. Engel, Jr. wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 You may wish to consider a different database for your project. SQLite may be a better choice, for example, depending on the project's specific needs (www.sqlite.org). Win95/98/ME is poor technology, no matter how many users it still has. It's probably about time for them to upgrade or switch to another OS (of course, I think Windows in general is a poor technology, but that's for another list...). OTOH, does anyone know if the cygwin version of postgresql enforces the NTFS requirement? That may be another option... I'll check sqllite out, thanks for the tip on it. not sure about the cygwin, but don't really want to cause clients to have to install and run extra services that shouldn't be needed. I agree about windows, not worth using at all. -- only plain text format email accepted. smaller file size, no virus transfer no proprietary file formats. smime.p7s Description: S/MIME Cryptographic Signature
[GENERAL] ntfs for windows port rc5-2
why? since an app that I'm working on would be useless for 60% of potential clients, using posgresql with the requirement for ms' corrupted ntfs means postgresql isn't going to work for it. since ms does not include a compiler, and the source for 8.0 won't cross compile from linux. ( gcc 3.3.0 ) whatever happened to targeting lowest common denominator, instead of highest? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
