Has anyone here tried SQLLogger?

[CrimsonScythe]

Well, as the title says, I'm wondering if anyone here have tried SQLLogger. I've just installed it, but I'm having some serious performance issues. It would be nice if anyone else have tried it too, and can shed some light on what to expect performance-wise. I'd like to think that I just did something wrong during installation.

[The_Tick]

Performance issues how?

[CrimsonScythe]

When I click on the "Viewer" link in the menubar on the sqllogger page (http://localhost:8080/sqllogger), it takes about a minute to a minute and a half of 100% cpu usage before the page is loaded. (On a 1.5GHz G4 Powerbook.) There are several other problems too, but the performance issue is the biggest problem. I guess it's the PostgreSQL server which is having problems, but I'm not sure how to analyze it properly. Unless PostgreSQL is supposed to be insanely slow?

[jmelloy]

That's definitely slower than it should be, but without knowing more information I couldn't tell you where the bottleneck is.

[CrimsonScythe]

What info do you want? Let me know, and I'll get you what you need
I must say I really like the idea of SQLLogger

BTW, do you think it would be possible/easy to make this plugin use MySQL?

[CrimsonScythe]

Oh, and I also have some problems with the SQLLogger jsp page (localhost one), and I ran into a few snags when following the installation instructions. I should probably write those down while they're fresh in my memory. Do you want feedback here, or via email?

[jmelloy]

I'm not sure what information would be helpful. How much RAM do you have? Are you running another database (MySQL or Apple Remote Desktop)? Etc.

You can send stuff here or to jmelloy@visualdistortion.org, it doesn't matter.

Won't work with MySQL; I have no intention of trying. You're welcome to, but it would probably be an exercise in futility.

[CrimsonScythe]

Regarding MySQL, I was just curious. It wasn't meant as any type of critique on you or your work.

I've got 1.25GB of RAM, with 400MB unused at this moment, so the amount of memory is not a problem. I'm not running any other database or Apple Remote Desktop either. The only things running are Safari, Opera, Preview, Quicksilver and Mail, so I can't really say that the computer is taxing itself. BTW, I installed PostgreSQL 8.0.1. It may be that there's a problem with that version. Maybe I'll try the 7.4 series instead.

I'll come back with more details later. I've got a few errands to run.

[jmelloy]

I had a thought.

Open up a terminal window, go into psql, and type:

Code: Select all

select count(*) from users;

followed by

Code: Select all

select count(*) from user_display_name;

If the second number is a lot bigger than the first, do this:

Code: Select all

delete from user_display_name;
insert into user_display_name (select user_id, username, '-infinity' from users);

[jmelloy]

Another, very basic thought. (First step in any PostgreSQL performance things):
Run

Code: Select all

vacuum analyze;

[CrimsonScythe]

I already tried to vacuum, with no effect, so I tried these:

Code: Select all

=> select count(*) from users;
 count 
-------
   100
(1 row)

=> select count(*) from user_display_name;
 count 
-------
   526
(1 row)

I guess 5x qualifies as much larger, so I went ahead and deleted and updated the user_display_name table. Lo and behold, now the Viewer page pops up in less than a second Thanks for helping me out here!

I'll come back with my suggestions and bugreports later today or tomorrow.

[CrimsonScythe]

Sorry about the delay. Here are my notes about the installation document.

1. In the installation, where we get to the createdb and createuser parts, I had some problems. The document says:

Code: Select all

postgres $] /usr/local/bin/createdb USERNAME
postgres $] /usr/local/bin/createuser USERNAME
postgres $] su username

After executing that, the whole database will be owned by the postgres user. This gave me problems later on. However, if I created my user first, su-ed to that user and then created the database, I ran into problems when running the tsearch2.sql part. So I guess the best thing would be to include later in the document the commands for changing the owner of the whole Adium database?

2. When adding the postgresql.jar to the classpath, you might want to include instructions on where to download the postgresql.jar. I thought it was included with PostgreSQL, but that wasn't the case. Maybe you should add this:

Code: Select all

        mkdir -p /usr/local/pgsql/share/java
        cd /usr/local/pgsql/share/java
Download the appropriate driver from http://jdbc.postgresql.org/download.html and put it in the /usr/local/pgsql/share/java directory.

I also have a few bugs in the war file that I installed.

1. Searching in the Search page doesn't seem to do what I expect. (Personal preference alert!) For instance, I was trying to search for a link to Compusa that I sent to a friend of mine. Searching for "compusa" gave me no hits, and neither did "*compusa*" or "%compusa%". Aren't there any wildcards?

2. In the Chats page, clicking on a user gives me a table of the months. Several of the months are missing (i.e. they are not linked) and clicking on the months that are linked really doesn't do anything.

Also, some users are sticking out of the Users "frame, and the main white "frame" ends directly underneath the months. (Let me know if you want screenshots.)

In the top of the Months frame there is a message saying

Code: Select all

Operation requires a scrollable ResultSet, but this ResultSet is FORWARD_ONLY.

3. The Statistics page doesn't work. I get this error message:

Code: Select all

ERROR: column "user_statistics.sender_id" must appear in the GROUP BY clause or be used in an aggregate function 

But, all in all, I love this plugin

[Octernion]

In an odd twist of fate (maybe not that odd, but its still amusing), I decided to randomly install the SQLLogger plugin today as well, and went through all the issues you describe above (also, you should note that the version of the .jar you need is "8.0-310 JDBC 3"). Also, I wasn't really tripped up by the USERNAME thingy because I've used pgsql marginally before, but you should be explicit about saying that USERNAME should not (usually?) be "postgres"...
The "Operation requires a scrollable ResultSet, but this ResultSet is FORWARD_ONLY." error appears when you try to edit a users "info", as well. The statistics dying was a sad point for me, because I was looking forward to loooking at interesting statistics :-).
Also, thanks a ton for the tip on the user names- I did the two commands before I even opened my browser, and I had around 80 user names, and around 1300 display names.... I can imagine how well that would've gone!
Also, has anyone noticed that it loads *really* slowly in Firefox? (very speedy-like in Safari) Or is that just me...

[dbomb]

It seems to go slow for me too. I have a 1.42ghz Mini with 256mb of ram.
But if I wait for it to load, then close the window and go back to it again, it takes a sec or so. Is this just safari caching the page? Once the page does load, the other pages (such as users) load fine.

[jmelloy]

1. In the installation, where we get to the createdb and createuser parts, I had some problems. The document says:

Code: Select all

postgres $] /usr/local/bin/createdb USERNAME
postgres $] /usr/local/bin/createuser USERNAME
postgres $] su username

This needs to be your username, not postgres.

2. When adding the postgresql.jar to the classpath, you might want to include instructions on where to download the postgresql.jar. I thought it was included with PostgreSQL, but that wasn't the case. Maybe you should add this:

Code: Select all

        mkdir -p /usr/local/pgsql/share/java
        cd /usr/local/pgsql/share/java
Download the appropriate driver from http://jdbc.postgresql.org/download.html and put it in the /usr/local/pgsql/share/java directory.

The jar is included with 7.4; it's possible it is not with 8.0.

1. Searching in the Search page doesn't seem to do what I expect. (Personal preference alert!) For instance, I was trying to search for a link to Compusa that I sent to a friend of mine. Searching for "compusa" gave me no hits, and neither did "*compusa*" or "%compusa%". Aren't there any wildcards?

URLs do not index as well as everything else, but wildcards will work with a quote. ("%compusa%") Searching for URLs is probably best suited with the "URL" link.

In the top of the Months frame there is a message saying

Code: Select all

Operation requires a scrollable ResultSet, but this ResultSet is FORWARD_ONLY.

3. The Statistics page doesn't work. I get this error message:

Code: Select all

ERROR: column "user_statistics.sender_id" must appear in the GROUP BY clause or be used in an aggregate function 

These are both bugs; I'd try downloading the WAR file again and seeing if it's fixed.

I've gotta fix the import/display name bug; I don't know what the problem is on that one. (Not that it helps people installing it now.)

PostgreSQL does some major caching -- it will take longer the first time it loads than the second. There's just no way around that.

[CrimsonScythe]

This needs to be your username, not postgres.

Yes, I know. That will lead to the tsearch2.sql imports failing. I got a bunch of permission denied errors.

Regarding the jar file, I must say I found it strange that they hadn't included it in postgres, but you might put up a notice for those who are brave/dumb enough to try the 8.0 version.

URLs do not index as well as everything else, but wildcards will work with a quote. ("%compusa%") Searching for URLs is probably best suited with the "URL" link.

Well, I've tried all of the following:

Code: Select all

compusa
%compusa%
*compusa*
"%compusa%"
"*compusa*"

and none of those gave any hits on the URLs. The three first ones gave the exact same results, and the two last ones gave me no results. The Recent Links link is indeed very nice, but there are a couple of features that I'd love to see there:
1. A search box.
2. Links to jump to the context of the presented links. (Like you have in the regular search. Excellent feature, BTW!)

I'll download and try the new WAR file. Thanks for giving us this plugin. It's shaping up to be truly awesome!!!

[CrimsonScythe]

I installed the new WAR file, and the two bugs were still present. Could it be some breakage in the new postgres version?

[jmelloy]

Yeah, on second thought, they're a case of 8.0 being more picky than 7.4; I'll look into it.

(And maybe even install 8.0 myself!)

[jmelloy]

From looking at the way tsearch indexes, you should be able to snatch it by typing in "www.compusa.com".

I'll add a search to the Recent Links page.

[CrimsonScythe]

Yes, searching for "www.compusa.com" worked, but of course didn't find the "compusa.com" links. I must say that I find it really strange that postgres doesn't support wildcards. Or have you done some optimization that turns wildcards off?