Greyhole and MySQL

robnix
Posts: 12
Joined: Wed Nov 04, 2009 3:39 pm

Greyhole and MySQL

Postby robnix » Fri May 07, 2010 8:17 am

Are there any plans to use MySQL with Greyhole on Amahi in the future? Most of the problems I've run into are due to the r/w locks.

User avatar
gboudreau
Posts: 606
Joined: Sat Jan 23, 2010 1:15 pm
Location: Montréal, Canada
Contact:

Re: Greyhole and MySQL

Postby gboudreau » Sun Jun 27, 2010 6:22 am

I'm personally using MySQL instead of sqlite.
Greyhole was developed using a MySQL backend.
Only the Amahi version of Greyhole uses SQLite.

To use MySQL yourself:

Make sure Greyhole is idle: Make sure that this log file says "Sleeping...":

Code: Select all

tail -f /var/log/greyhole.log
Create the MySQL database & user:

Code: Select all

hda-create-db-and-user greyhole mysql -ugreyhole -pgreyhole greyhole < /usr/share/greyhole/schema-mysql.sql
Edit /var/hda/platform/html/config/greyhole.yml:

Code: Select all

db_engine: mysql # options for mysql db engine db_name: greyhole db_user: greyhole db_pass: greyhole db_host: localhost
Then re-generate greyhole.conf from greyhole.yml: just edit a share location in the Amahi Dashboard, but just hit save without changing the location.

Stop Greyhole: Go in your Amahi Dashboard, in Setup > Settings > Servers, and uncheck the "Watchdog" checkbox for Greyhole, then stop it using the red icon. (You might have to enable advanced settings to see that page.)

Update the MySQL database to prevent Greyhole from re-parsing what it parsed in SQLite:

Code: Select all

fd=`sqlite3 /var/cache/greyhole.sqlite "select value from settings where name = 'last_read_log_smbd_line'"` mysql -uroot -phda -e "update settings set value = '$fd' where name = 'last_read_log_smbd_line'" greyhole
Restart Greyhole from the Amahi Dashboard, and re-enable the watchdog for the service.

PS Sorry about the very late response... I didn't use a Google Alerts for greyhole posts in this forum before today!
- Guillaume Boudreau

User avatar
cpg
Administrator
Posts: 2610
Joined: Wed Dec 03, 2008 7:40 am
Contact:

Re: Greyhole and MySQL

Postby cpg » Sun Jun 27, 2010 10:18 am

I take responsibility for this decision. The feeling was that adding mysql to the dependencies of Greyhole was a bit too much, that it would be more reliably without one extra moving part.

I have lived to regret that decision.

Maybe we can make a small script to set these at the drop of a command?

Also, a note of caution: I am not sure if new platform releases will preserve these settings.
My HDA: Intel(R) Core(TM) i5-3570K CPU @ 3.40GHz on MSI board, 8GB RAM, 1TBx2+3TBx1

User avatar
gboudreau
Posts: 606
Joined: Sat Jan 23, 2010 1:15 pm
Location: Montréal, Canada
Contact:

Re: Greyhole and MySQL

Postby gboudreau » Sun Jun 27, 2010 10:35 am

From hda-platform.spec:
%config(noreplace) /var/hda/platform/html/config/*.yml

i.e. greyhole.yml will not be overwritten by hda-platform updates, even if the file changed in the RPM.
- Guillaume Boudreau

robnix
Posts: 12
Joined: Wed Nov 04, 2009 3:39 pm

Re: Greyhole and MySQL

Postby robnix » Thu Jul 15, 2010 9:37 am

I'm personally using MySQL instead of sqlite.
Greyhole was developed using a MySQL backend.
Only the Amahi version of Greyhole uses SQLite.

To use MySQL yourself:

Create a greyhole MySQL database:

Code: Select all

hda-create-db-and-user greyhole mysql -ugreyhole -pgreyhole greyhole < /usr/share/greyhole/schema-mysql.sql
Then edit /var/hda/platform/html/config/greyhole.yml:

Code: Select all

db_engine: mysql # options for mysql db engine database: greyhole username: greyhole password: greyhole host: localhost
Finally, restart Greyhole:

Code: Select all

service greyhole restart
PS Sorry about the very late response... I didn't use a Google Alerts for greyhole posts in this forum before today!
Thank you very much. This makes a big difference in my ability to Amahi.

whetu
Posts: 30
Joined: Tue Jul 13, 2010 3:32 am

Re: Greyhole and MySQL

Postby whetu » Mon Jul 19, 2010 2:44 am

related question from left field - any thoughts of supporting postgres?

User avatar
gboudreau
Posts: 606
Joined: Sat Jan 23, 2010 1:15 pm
Location: Montréal, Canada
Contact:

Re: Greyhole and MySQL

Postby gboudreau » Mon Jul 19, 2010 3:24 am

related question from left field - any thoughts of supporting postgres?
Never thought anyone would ever need or want such a thing. Why?
- Guillaume Boudreau

whetu
Posts: 30
Joined: Tue Jul 13, 2010 3:32 am

Re: Greyhole and MySQL

Postby whetu » Tue Jul 20, 2010 4:27 pm

related question from left field - any thoughts of supporting postgres?
Never thought anyone would ever need or want such a thing. Why?
Partly curiosity, and partly because it's just been my professional experience that MySQL is a pile of turd :)

In my last three sysadmin jobs I've had to really nurse inherited MySQL systems along, hand tuning its configuration to glean any reasonable performance (mysqltuner.pl is a godsend) and fixing queries that didn't work across versions etc. People think that they just install MySQL and then life will be all rainbows and kittens, but the reality is that it's so shoddy that it needs constant maintenance, and may the IT Gods have mercy on you should you decide to scale it.

On the other hand, every single instance of PostGres I've come across just works (tm) and scales brilliantly, compared to MySQL it's set-and-forget, and the same can be said about the Oracle and MS-SQL systems I've maintained (yeah, you read it: I've had a better time with MS-SQL than MySQL - how depressing is that? :() I don't want to go too far into it as it'd be a thread hijack, but yeah - just be aware that there's a large number of IT Pros out there who, based on experience, have a very dim view of MySQL. Just look at any MySQL/Postgres discussion on slashdot, for example, you'll see them there in the comments.

So I just figured that if people are going to skip sqlite they may as well go for something that doesn't suck. Especially when it comes to anything related to the well-being of their data. Sure, MySQL might be perfectly fine at this level of scale, but I know for sure which RDBMS I'd prefer to trust :)

(Unfortunately I'm only a sysadmin, not a dev nor a full blown DBA - if I could help you out, I'd gladly do it.)

Who is online

Users browsing this forum: No registered users and 2 guests