Wow, Postgresql is an interesting beast. I can see how the architecture
would make it the perfect statistical database because it pulls out
larger character data into background tables and leaves the main table
nice 'n slim. Using MS SQL, I've had to normalize character data into
referencing tables for statistical solutions.<br>
<br>
In databases where character information in king (like most web solutions), I think the architecture is less desirable.<br>
<br>
But you're right in that there was no need to raise alarm about the Wifidog db schema while Postgresql is the intended server.<br>
<br>
Thanks for the info on Postgresql, I'm looking forward to using it (for statistics) in the future.<br>
<br>
Rein<br><br><div><span class="gmail_quote">On 1/24/06, <b class="gmail_sendername">Benoit Grégoire</b> &lt;<a href="mailto:bock@step.polymtl.ca">bock@step.polymtl.ca</a>&gt; wrote:</span><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
On January 24, 2006 12:13 pm, Rein Petersen wrote:<br>&gt; Hey Rob,<br>&gt;<br>&gt; Have you come across any discussion at ISF about their database schema<br>&gt; problem (if it is still a problem)? I wonder if they really know that it is
<br>&gt; a problem? I'm a little surprised that they're using a powerful db like pg<br>&gt; but have a completely screwed up db full of text fields - really bad<br>&gt; form... Maybe we just have an older build and it has been fixed in the
<br>&gt; build on the devauth server. I just need a pg client on devauth to check<br>&gt; that out... Rob can you set up phpPgAdmin on devauth ? Or, have you come<br>&gt; across some db.phpscript that creates the database and tables?
<br>&gt;<br>&gt; *If it hasn't changed*, and before I go rushing to change anything, I think<br>&gt; it is wise try to help ISF understand the problem of a db with tables full<br>&gt; of pointers to text blobs (changing a database schema in any collaborative
<br>&gt; software projects raises ire).<br>&gt;<br>&gt; Any database table or index is scanned in &quot;pages&quot; or &quot;extents&quot; (depending<br>&gt; on the nomenclature of the database you are using). In MS SQL a &quot;page&quot; is
<br>&gt; 64K, in Oracle an &quot;extent&quot; is 84K. This means that each query for a given<br>&gt; table or view will start at the beginning of that table and read the table<br>&gt; in chunks of 64 or 84 K (I don't know what it is for postgresql but I'm
<br>&gt; certain it is in that range).<br>&gt;<br>&gt; So, a table with an integer id field (32 bytes), a char(12), a char(20)<br>&gt; will produce rows that consume 64 bytes. Thats means, the first page that<br>&gt; returns to the db from a query of this table will hold 1024 rows (1024 * 64
<br>&gt; bytes = 64K). So this explains the importance of keeping tables slim as it<br>&gt; will be able to return more rows for each &quot;page&quot; or &quot;extent&quot; scan.<br>&gt;<br>&gt; table 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; table2
<br>&gt; ___________________&nbsp;&nbsp;&nbsp;&nbsp; ______________________________________<br>&gt;<br>&gt; This is a major factor in database design for performance.<br>&gt;<br>&gt; Now the 'Text' field or 'Blob' field is a little different and
<br>&gt; understanding the difference for good database design is very important.<br>&gt; Any 'Text' or 'blob' field actually contains a 32byte pointer to another<br>&gt; location on the harddrive where all such blobs are stored. And while you
<br>&gt; might argue that storing a name (ie.a longer field for storing a full venue<br>&gt; name) that might otherwise assume 64bytes (char64) can keep the row smaller<br>&gt; by only storing a 32 byte text pointer will provide greater speed - is
<br>&gt; actually causing a completely new &quot;extent&quot; or &quot;page&quot; scan on a different<br>&gt; place on the drive. Do this with several fields and you'll have wild disk<br>&gt; thrashing.<br><br>This is all a very interesting exposé on classic database implementation
<br>performance tips.&nbsp;&nbsp;However, before everyone thinks that no one at ISF knows<br>what they are talking about, I would kindly point you to postgresql<br>documentation (which is more authoritative than you and I):<br><a href="http://www.postgresql.org/docs/8.1/interactive/datatype-character.html">
http://www.postgresql.org/docs/8.1/interactive/datatype-character.html</a><br><br>There are NO performance difference in postgres between text, varchar(n),<br>varchar(10), or even char(10).&nbsp;&nbsp;Long values are stored in background tables
<br>so they don't interfere with fast access to shorter values.&nbsp;&nbsp;Shorter values<br>are stored in the the same extent as the rest of the data. In fact the manual<br>makes the following recommandation:<br><br>Tip:&nbsp;&nbsp;There are no performance differences between these three types, apart
<br>from the increased storage size when using the blank-padded type. While<br>character(n) has performance advantages in some other database systems, it<br>has no such advantages in PostgreSQL. In most situations text or character
<br>varying should be used instead.<br><br>&gt; Worse, both the db and middle tier db connector must make allowances for<br>&gt; the potential size of the results where each 'text' field usually consumes<br>&gt; 2048KB - memory is put aside to hold that for text field in a result row.
<br>&gt; So what should be a simple query is causing major disk thrashing and insane<br>&gt; (no really, really insane) memory consumption on both the db server and web<br>&gt; server.<br><br>Depending on the driver design, it may indeed decide to reserve large amounts
<br>of memory.&nbsp;&nbsp;I doubt it for a postgres ont, since the text is actually<br>unlimited in length.&nbsp;&nbsp;So how much memory a given driver pre-allocates is<br>implementation dependent.<br><br>Crazy trashing doesn't happen except for very long actual data (not data
<br>types).&nbsp;&nbsp;Even then, it would only happen if you do select * without needing<br>the whole data, which is a bad idea anyway for reasons I don't need to<br>explain.<br><br>&gt; I understand that in development, it is handy to have elbow room for fields
<br>&gt; that may need the potential to grow, but I think wifidog is well beyond<br>&gt; that point and a schema can always grow without losing data (reducing the<br>&gt; size is what causes problems).<br><br>Quite true, and since there are no performance in portgres we decided to
<br>expand most of the types to text early on.<br><br>&gt; Here is a the schema for the nodes table on the auth server:<br>&gt;<br>&gt; nodes ------------------------------------------------------------<br>&gt;
last_heartbeat_ip&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
char(16)&nbsp;&nbsp;&nbsp;&nbsp;[should be inet]<br><br>That was because MySql didn't support it.&nbsp;&nbsp;Now that we abandoned the idea of<br>supporting MySql, that is obviously no longer an issue.<br><br>&gt; last_heartbeat_timestamp&nbsp;&nbsp;&nbsp;&nbsp;timetz&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;[why no timezone?]
<br><br>I don't know, we could put a timezone.&nbsp;&nbsp;It doesn't really matter however since<br>the timestamp is generated by postgres, so it should always be in the same<br>timezone.<br><br>&gt;
description&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
text&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;[normalize!]<br>&gt;
mass_transit_info&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
text&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;[normalize!]<br><br>I'm not sure what you mean by normalize in that context.<br><br>&gt; node_deployment_status&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;char(32)&nbsp;&nbsp;&nbsp;&nbsp;[normalize or constraint!]<br><br>It's constrained by the node_deployment_status table.
<br><br>&gt;
venue_type&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;text&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;[normalize
or constraint!]<br><br>It's constrained by the venue_type table.<br><br>&gt;
max_monthly_incoming&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;int8&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;[should
be int4]<br>&gt;
max_monthly_outgoing&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;int8&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;[should
be int4]<br><br>No, that would limit to the maximum data transferred by a not to 4 gigabytes.<br><br>&gt; quota_reset_day_of_month&nbsp;&nbsp;&nbsp;&nbsp;integer&nbsp;&nbsp;&nbsp;&nbsp; [should be int2]<br><br>Fair enough.<br><br>(For clarity, I removed all columns you didn't comment on, and all those that
<br>had should be varchar(*), which I adressed above)<br><br>&gt; The truth of the matter is that this wifidog db needs to be trimmed in size<br>&gt; in a major way. The node and user tables in particular because they are hit
<br>&gt; frequently and most often. Altering all the unnecessary text fields to<br>&gt; appropriate types and sizes in this db schema will result in at least 1000<br>&gt; fold increase in performance (I don't know the actual number but I know it
<br>&gt; would actually be waaaay more than that so it is easy to boast 1000).<br><br>I'll be very humbled if it does, but I'm pretty certain it will be barely<br>measurable, if it exists.<br><br>&gt; Everybody scratches their head wondering why the simple node and user
<br>&gt; queries take painfully long and slams the hell out of our server - now you<br>&gt; know why. Before we go into production with our new server, this *must* be<br>&gt; addressed.<br><br>That is not the reason that simple node and user queries slam the hell out of
<br>your server.&nbsp;&nbsp;Postgres being a true ACID database, every time a hotspot<br>heartbeats it creates a additional row in the nodes table.&nbsp;&nbsp;It would appear<br>that you have a problem with your vacuuming.&nbsp;&nbsp;In postgres it's an externally
<br>controlled process (like MySQL 5).&nbsp;&nbsp;I recommend you try running VACUUM FULL<br>ANALYSE once, and see if your problem is solved.&nbsp;&nbsp;If it is, make sure you run<br>VACUUM ANALYSE once an hour or at least once a day from a script.
<br><br>Finallly, this kind of discussion should really be on the wifidog mailing<br>list.<br>--<br>Benoit Grégoire, <a href="http://benoitg.coeus.ca/">http://benoitg.coeus.ca/</a><br><br><br></blockquote></div><br><br clear="all">
<br>-- <br>Rein Petersen MCP MCP+I MCSE MCDBA MCAD<br>Software Engineer