I'm trying to optimize postgres performance on a headless solid state
hardware platform (no fans or disks). I have the database stored on a
USB 2.0 flash drive (hdparm benchmarks reads at 10 MB/s). Performance is
limited by the 533Mhz CPU.
Hardware:
IXP425 XScale (big endian) 533Mhz 64MB RAM
USB 2.0 Flash Drive
Software:
Linux 2.6.21.4
postgres 8.2.5
I created a fresh database using initdb, then added one table.
Here is the create table:
CREATE TABLE archivetbl
(
"DateTime" timestamp without time zone,
"StationNum" smallint,
"DeviceDateTime" timestamp without time zone,
"DeviceNum" smallint,
"Tagname" character(64),
"Value" double precision,
"Online" boolean
)
WITH (OIDS=FALSE);
ALTER TABLE archivetbl OWNER TO novatech;
I populated the table with 38098 rows.
I'm doing this simple query:
select * from archivetbl;
It takes 79 seconds to complete the query (when postgres is compiled
with -O2). I'm running the query from pgadmin3 over TCP/IP.
oprofile is showing that memset (via dopr) is using about 60% of the CPU. I traced back further and noticed most of the
usagewas coming from EncodeDateTime.
I'm not quite sure why oprofile is showing that memset is hogging so much CPU. Regardless, I found way to eliminate
mostof the sprintf calls that were taking place in my situation.
I made some modifications to EncodeDateTime and have attached them as a patch. These changes alone reduced the query
timeof the "select * from archivetbl;" from 79 seconds to just 35 seconds.
This patch is against 8.2.5. Since I'm cross compiling changing versions is a bit of a pain, but if someone thinks the
performancehas changed much in this area I could probably get the latest version cross compiling.
Regards,
George McCollister
diff -Naur postgresql-8.2.5/src/backend/utils/adt/datetime.c postgresql-8.2.5.new/src/backend/utils/adt/datetime.c
--- postgresql-8.2.5/src/backend/utils/adt/datetime.c 2007-06-12 10:58:39.000000000 -0500
+++ postgresql-8.2.5.new/src/backend/utils/adt/datetime.c 2008-09-15 12:16:32.000000000 -0500
@@ -3287,6 +3287,53 @@
return TRUE;
} /* EncodeTimeOnly() */
+void ymdhm(char * buf, int year, int mon, int day, int hour, int min)
+{
+ buf[0] = (char)((year / 1000) % 10) + '0';
+ buf[1] = (char)((year / 100) % 10) + '0';
+ buf[2] = (char)((year / 10) % 10) + '0';
+ buf[3] = (char)(year % 10) + '0';
+ buf[4] = '-';
+ buf[5] = (char)((mon / 10) % 10) + '0';
+ buf[6] = (char)(mon % 10) + '0';
+ buf[7] = '-';
+ buf[8] = (char)((day / 10) % 10) + '0';
+ buf[9] = (char)(day % 10) + '0';
+ buf[10] = ' ';
+ buf[11] = (char)((hour / 10) % 10) + '0';
+ buf[12] = (char)(hour % 10) + '0';
+ buf[13] = ':';
+ buf[14] = (char)((min / 10) % 10) + '0';
+ buf[15] = (char)(min % 10) + '0';
+ buf[16] = '\0';
+}
+
+void append_seconds(char * buf, int sec)
+{
+ buf[0] = ':';
+ buf[1] = (char)((sec / 10) % 10) + '0';
+ buf[2] = (char)(sec % 10) + '0';
+ buf[3] = '\0';
+}
+
+#ifdef HAVE_INT64_TIMESTAMP
+void append_seconds_and_fsecs(char * buf, int sec, fsec_t fsec)
+{
+ buf[0] = ':';
+ buf[1] = (char)((sec / 10) % 10) + '0';
+ buf[2] = (char)(sec % 10) + '0';
+ buf[3] = '.';
+ buf[4] = (char)((fsec / 100000) % 10) + '0';
+ buf[5] = (char)((fsec / 10000) % 10) + '0';
+ buf[6] = (char)((fsec / 1000) % 10) + '0';
+ buf[7] = (char)((fsec / 100) % 10) + '0';
+ buf[8] = (char)((fsec / 10) % 10) + '0';
+ buf[9] = (char)(fsec % 10) + '0';
+ buf[10] = '\0';
+}
+#endif
+
+
/* EncodeDateTime()
* Encode date and time interpreted as local time.
@@ -3315,9 +3362,8 @@
case USE_ISO_DATES:
/* Compatible with ISO-8601 date formats */
- sprintf(str, "%04d-%02d-%02d %02d:%02d",
- (tm->tm_year > 0) ? tm->tm_year : -(tm->tm_year - 1),
- tm->tm_mon, tm->tm_mday, tm->tm_hour, tm->tm_min);
+ ymdhm(str, (tm->tm_year > 0) ? tm->tm_year : -(tm->tm_year - 1),
+ tm->tm_mon, tm->tm_mday, tm->tm_hour, tm->tm_min);
/*
* Print fractional seconds if any. The field widths here should
@@ -3329,7 +3375,7 @@
#ifdef HAVE_INT64_TIMESTAMP
if (fsec != 0)
{
- sprintf(str + strlen(str), ":%02d.%06d", tm->tm_sec, fsec);
+ append_seconds_and_fsecs(str + strlen(str), tm->tm_sec, fsec);
TrimTrailingZeros(str);
}
#else
@@ -3340,7 +3386,7 @@
}
#endif
else
- sprintf(str + strlen(str), ":%02d", tm->tm_sec);
+ append_seconds(str + strlen(str), tm->tm_sec);
/*
* tzp == NULL indicates that we don't want *any* time zone info