Hi there.
I just wan't to know. do you know if there are options to speed up the mySQL DB?
Possibly by adjusting some parameters?
Because my system takes about 2s/Hand using Bulk Import...whats not really acceptable .
As mentioned before: OS: Ubuntu/1.6Ghz Athlon/1GB/5400rpm/mySQL.
But 2 second per Hand...seems still a bit slow, isn't it?!

This might help. It seemed to help with my HEM SQL DB.

http://forumserver.twoplustwo.com/45...mputer-362865/

This goes beyond unacceptable into the amazingly horrible area.

I googled around looking for a simple tuning howto similar to what fozzy just linked for postgres. I didn't find anything that I liked and thought would help. I am not competent to advise on that sort of thing.

I just imported 120k hands on a pc of similar spec (Fedora) and was getting about 5 hands/sec at the end of the import. I considered this to be awfully slow. I also noticed that >80% of the cpu time was in mysql (I really expected the python part of the import to be slow--I guess I will someday be convinced that python isn't all that slow.) I was asleep during most of this import, so no other jobs were slowing down the import.

A few questions: How many hands in the db? Any other jobs running on the pc? Is this bulk or auto import? What sites? What games? Are you using the latest from the git repo?

We (the devs) are aware that this is a problem and are discussing ways to speed this up. I think we have some good ideas, but none of them can be implemented immediately. I have one idea that might give some relief in the short term, but I want to ask the other devs about any impact it might have on other things.

People keep telling me how much better postgres is, so I might turn the import loose on postgres when I go to be tonight to get a comparison.

Tuning mysql

Yeah, I did that and looked at many of those links. I didn't find any that I was willing to tell someone, "here, go do this!"--especially someone whose mysql skills are even less than mine.

If someone has some specific tips that make a difference, I'll put them in the wiki. If there are some things we can set from fpdb, I'll put them in the code today.

I forgot this is mostly mysql and not postgresql users.

I can't help other than to say that there must be something up with My SQL's settings, as I have a virtually identical system (Ubuntu), except that it's an 800 MHz Sempron with a 1/2 Gb of memory, and a hand takes only a fraction of a second to import. The HUD wouldn't work otherwise. I didn't change My SQL's settings from their default values. But your problem must be with My SQL (and there are forums for that), not Fpdb itself.

I've been wondering about the SQ Lite option, which seems to have been on your minds at one point, and it's standard with Python; it's mentioned here and there in the source code. Are there any reasons why this couldn't be added as a database option? Would it be faster (it is supposed to be very fast)? I would test this if it were provided; I wouldn't mind getting rid of My SQL.

The issue appears specifically with bulk import with indexes dropped or not. When importing 1 hand at a time via import this isn't an issue, when pounding the db with multiple updates we see this issue.
I did pull out some of the sql insert and table create code so we could support additional databases - specifically sqlite. We were interested in this so we could simplify the install procedure for windows users.

I'm no sql guru by any means and I get stuck attempting to translate the CREATE table statements - specifically foreign key restraints were an issue.

The other issue from memory was the format for sql args to be provided to the execute command - think they needed to passed in differently.

Thats my memory of this. I've been buried pretty deep in new import code for a while now.

Can anyone shed any light on what was done here?

sh-3.2# uname -a
Darwin blablabla.local 9.6.0 Darwin Kernel Version 9.6.0: Mon Nov 24 17:39:01 PST 2008; root:xnu-1228.9.59~1/RELEASE_PPC Power Macintosh

sh-3.2# port -v installed | grep -i gtk
gtk-doc @1.11_0 (active)
gtk2 @2.14.7_1+x11 (active)
py25-gtk @2.12.1_0+darwin_9 (active)

sh-3.2# cat fpdb-error-log.txt
Traceback (most recent call last):
File "./fpdb.py", line 33, in <module>
import pygtk
ImportError: No module named pygtk

http://fpdb.wiki.****************/Install+in+MacOSX - is the closest anyone has come to making this work on OSX so far.

You probably need to fire this up from within an xterm, or from a quick goolge:

setenv PYTHONPATH "/opt/local/lib/python2.4:/opt/local/lib/python2.4/site-packages"

But make sure the paths are correct in the above.

Thanks! In case anyone runs in to this (on os x), after checking for the correct paths, run:

export PYTHONPATH="/opt/local/lib/python2.5:/opt/local/lib/python2.5/site-packages"

The paths in my example will be correct if you followed the wiki, and use /opt/local for your macports base.

grunch

Does this have a HUD yet? I see in the OP that it didn't start out w/ one.

Ty.

It does but I can't figure out how to launch it...

Yes - http://fpdb.wiki.****************/Features

That page is undergoing some surgery at the moment.

I have some extremely slowass old EIDE hard disks, and I get about 9 to 10 hands per second, 3.33Ghz. From the anecdotes I'm seeing here, it seems to be that mysql is spending a huge amount of time CPU bound, which just doesn't seem to make any sense to me, especially considering that I ran a pretty sizeable database getting hits from 30-40 concurrent users regularly on a 33 MHz 486 a few years back. But, I wasn't using anything more complex than "select x from y where z", because I don't know any better.

I have made some small gains on the python side of things, which might translate into larger gains on slower CPUs, but it seems like mysql is using a lot of cpu..

Auto-import and HUD is the way to start the HUD, it will read any changed file in your HH directory and attempt to find a window to attach the hud windows to.

Noone has managed to get this working on a Mac yet - none of the devs has a Mac, and noone has been able to tell us how to locate open table windows using an xorg command, or a Finder command.

If you are able to help with that we should be able to get HUD support for the Mac working.

Before I install, is there a list of stats offered anywhere?

and can they all be used on the hud?

http://fpdb.wiki.****************/Abbreviations

Hrm, I have exactly 0 experience with X windows, but is there any "surface" type info you guys need?

Not quite sure what you mean by surface.

On linux/windows we discover the tablename from the hh file.
- In Windows we use an api to list all open windows, and search their titles for the table name. once we the window handle we attach our windows to that
- In Linux (XWindows/xorg) we do the same thing but with a different command - xwininfo
- On Mac, xwininfo doesn't return the PokerStars windows because they aren't native X windows.

We need a command on the Mac, Finder or X based that will allow us to find all the open windows and grab the window title.

I'm looking in to this, and I suspect the answer is an AppleScript.

edit: meh maybe not. i'll search more tomorrow.

I would like to get sqlite support working--it would make an easier install for casual users.

SQLite doesn't have foreign key support. That would be a small problem. The other problem is parameter substitution in the SQL drivers. We chose the ? style of parameter passing because it was supported for both the mysql and postgres driver--sqlite does not support that style. There was no parameter passing style supported by all three DBs. (This illustrates a huge design problem in the python DBAPI, IMO.) I have since found some code in the Python Cookbook that abstracts the parameter passing to a higher level. (recipe 7.16 in the 2nd ed.) This would be a worthy improvement, but is not a small/quick job, and might introduce other problems (it will be slower, but probably not significantly).

I am not sure that using sqlite will automatically give a speed gain. But it is worth a look.

In mysql we are using the inno tables. These are the "industrial strength" tables that allow rollback, etc. The myIsam tables are faster, but less reliable. Anybody that would be OK with using SQLite should be OK with using myIsam. Making inno/myIsam a selectable parameter in the config file would not be a big problem.

I spent most of yesterday messing with import speed, mostly bulk import. Here's what I found out:

First I did this testing on a pc of similar speed to those of Eleatic Stranger and FileNotFound. Specifically, 1.6 GHz athlon (32 bit, one core), 512 MB, Fedora 9 fully patched. I had not done any tuning when I started this. For this testing I ran the database on my local pc.

Second, for a single file, import speed can vary by more than a factor of 2 based on the game played, the amount of action, etc. So YMMV.

I have about 120k hands in my mysql db and was getting import speeds of about 5/sec. With the test files I selected, I was also getting 5/sec into an empty db.

Importing those same test files into an empty postgres DB got me 12 hands/sec. Playing around with importing a bit moved that 12/sec up to 15/sec. I have made the "playing around" options in the config file, but I have not pushed those changes (see below).

I noticed that the index dropping/recreating is broken on postgres. I will try to fix this right away.

I tried to play with the postgres tuning in the link from fozzy a few posts above. Needless to say, I completely hosed my postgres server and that is when I went to bed. So I cannot test index dropping/recreating on postgres or any of the other postgres stuff until I fix that server.

A note on those tuning instructions. I think those are written for dedicated postgres SQL servers. Unless you have a lot of ram (like the OP of that advice), you will probably get into trouble with those parameters.

So my interim report on this is that postgres might be significantly faster, but don't try to drop the indexes on the bulk import.

I have a ton of hands at various stakes for Mixed 8-Game and Triple Draw cash games on PokerStars. If you are looking to add support for the mixed game and/or Triple Draw, please let me know, and I will send you a ton of hands.
I like where your tracker is going, and find it very exciting it might soon work realtime in HORSE and other mixed games!

I just sent you a PM with my email address.

Also, the current development version in my git tree imports HORSE games OK on both Stars and FTP. I have not tried the HUD with HORSE, but I think it will get confused (just like me). I will get busy on HUD support for HORSE pretty soon. One annoying thing is that hands from mixed games are not flagged in the db--so you can't tell a holdem hand in HORSE from one in a regular holdem game.

My git repo is here.