Thread: a MySQL to PostgreSQL successful port, but performance is bad...
I just converted a working app from MySQL to PostgreSQL. I used fabForce's DBDesigner 4 to build and maintain my data model in MySQL. To port the schema, I exported the data model to an SQL file, and hand tweaked the file to change datatypes and move INDEX statements out of the Create Table statements. Everything worked like a charm. I can switch back and forth between the two databases by simply changing the ODBC connect string. Here's my problem, though: queries in mySQL are almost instantaneous, whereas those same queries in PostgreSQL can take four seconds or more - even when I'm hitting a table with only one row. I'll bet I have something misconfigured, but I'm a newbie FNG, and I don't yet know my way around yet. BTW, I did a search on mySQL in the novice threads, and performance seems like a hot-button issue. Please be assured that I have no issues or agendas with either product, and fully assume that I have something misconfigured. I'm slightly overwhelmed by the volume of documentation (on-line, and I bought books by Douglas&Douglas and Matthew&Stone) and would like a few points in the correct direction. Help is absolutely appreciated. Regards, Jim Brown PostgreSQL 8.1.3 PostgreSQL ANSI ODBC driver 8.01.02.00 PSQLODBCA.DLL 1/31/2006 __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Jim Brown <jimbrown32rb@yahoo.com> writes:
> Here's my problem, though: queries in mySQL are almost
> instantaneous, whereas those same queries in
> PostgreSQL can take four seconds or more - even when
> I'm hitting a table with only one row. I'll bet I have
> something misconfigured, but I'm a newbie FNG, and I
> don't yet know my way around yet.
Two things to check right away: have you ANALYZEd all your tables, and
did you remember to make all the same indexes you have in the MySQL
database? (I believe PG and MySQL have slightly different ideas about
when to make indexes automatically.) psql's "\d table" command is a
handy way to verify what indexes you have on a table.
If you're still having problems, feel free to post EXPLAIN ANALYZE
output for the problem queries on pgsql-performance. Also mention
which postgresql.conf settings you're using.
regards, tom lane
> I just converted a working app from MySQL to > PostgreSQL. I used fabForce's DBDesigner 4 to build > and maintain my data model in MySQL. To port the > schema, I exported the data model to an SQL file, and > hand tweaked the file to change datatypes and move > INDEX statements out of the Create Table statements. > Everything worked like a charm. I can switch back and > forth between the two databases by simply changing the > ODBC connect string. > > Here's my problem, though: queries in mySQL are almost > instantaneous, whereas those same queries in > PostgreSQL can take four seconds or more - even when > I'm hitting a table with only one row. I'll bet I have > something misconfigured, but I'm a newbie FNG, and I > don't yet know my way around yet. > > BTW, I did a search on mySQL in the novice threads, > and performance seems like a hot-button issue. Please > be assured that I have no issues or agendas with > either product, and fully assume that I have something > misconfigured. I'm slightly overwhelmed by the volume > of documentation (on-line, and I bought books by > Douglas&Douglas and Matthew&Stone) and would like a > few points in the correct direction. Jim, you might also want to post this email on the preformance list. You will probably get more assistance that way. I will try to give you general ideas that you can use to get your answers. First, It will be helpful to everyone on the list if you post your: hardware specs operating systems information postgresql version conf files explain (and if later ask explain analyze) on the offending queries. The answers that you will be getting will be the following: advice on maintaining your database i.e. Vaccuming, & analyzing, & clustering for improved preformance. advice on query tuning/sql restructuring advice on seting conf variable and runtime variables advice on query caeching / transaction on multiple update statements. and finally advice of hardware restructuring for improved disc i/o. Also, I should point out that you will be able to determine what you can do with most of these items on your own by reading the user manual. hope this is helpful. Regards, Richard Broersma
jimbrown32rb@yahoo.com (Jim Brown) writes:
> I just converted a working app from MySQL to
> PostgreSQL. I used fabForce's DBDesigner 4 to build
> and maintain my data model in MySQL. To port the
> schema, I exported the data model to an SQL file, and
> hand tweaked the file to change datatypes and move
> INDEX statements out of the Create Table statements.
> Everything worked like a charm. I can switch back and
> forth between the two databases by simply changing the
> ODBC connect string.
>
> Here's my problem, though: queries in mySQL are almost
> instantaneous, whereas those same queries in
> PostgreSQL can take four seconds or more - even when
> I'm hitting a table with only one row. I'll bet I have
> something misconfigured, but I'm a newbie FNG, and I
> don't yet know my way around yet.
>
> BTW, I did a search on mySQL in the novice threads,
> and performance seems like a hot-button issue. Please
> be assured that I have no issues or agendas with
> either product, and fully assume that I have something
> misconfigured. I'm slightly overwhelmed by the volume
> of documentation (on-line, and I bought books by
> Douglas&Douglas and Matthew&Stone) and would like a
> few points in the correct direction.
>
> Help is absolutely appreciated.
First thing to do...
Connect to that database, and run the command:
VACUUM ANALYZE VERBOSE;
This will run for a while, listing the various tables that it
processes.
It is highly likely that this will have a significant impact on many
of the badly-performing queries that you are encountering, either
because:
a) It cleans out some outdated data, or
b) It provides the query optimizer legitimate statistics to
correctly evaluate a fast way of running the queries.
That's about the first place to go.
If there are queries that are unexpectedly slow, posting query plans
on the .performance list may allow people to provide some help.
You can get details about the query plan via prefacing your query with
the keywords:
EXPLAIN ANALYZE
Thus...
EXPLAIN ANALYZE select this, that, other from thistab, thattab
where thistab.trans_on between '2006-01-01' and '2006-02-01' and
thattab.id = thistab.id;
--
let name="cbbrowne" and tld="acm.org" in String.concat "@" [name;tld];;
http://cbbrowne.com/info/nonrdbms.html
Rules of the Evil Overlord #202. "All crones with the ability to
prophesy will be given free facelifts, permanents, manicures, and
Donna Karan wardrobes. That should pretty well destroy their
credibility." <http://www.eviloverlord.com/>