FileWave Alliance

The Official Community Forum
  • Page:
  • 1

TOPIC: Recommended settings for mySQL?

Recommended settings for mySQL? 25 Mar 2010 11:07 #185

  • P-M Lejon
  • P-M Lejon's Avatar Topic Author
  • Offline
  • 5th level - Black Belt
  • 5th level - Black Belt
  • Posts: 92
  • Karma: 3
  • Thank you received: 15
Hi,


I'm using a my.cnf file to redirect the path to my database folder (it's on a separate raid disk).


While setting this up I tried the different example files for the cnf files and couldn't actually see any performance improvements between the my-small.cnf and the my-huge.cnf.


Is there any recommended settings for the different parameters in the cnf file?


I also wonder if the Inventory DB uses InnoDB, since there's separate settings for that.




thanks


/P-M


Xserve 2x2 Ghz Xeon 4 GB RAM shared with FileWave server
P-M Lejon
System Administrator
BonnierNews
Sweden

 We do not have any 25 Mar 2010 21:33 #749

  • FileWave
  • FileWave's Avatar
  • Offline
  • User is blocked
  • User is blocked
  • Posts: 218
  • Thank you received: 0
We do not have any recommended settings for the my.conf file outside of those that are necessary to get the Inventory DB up and running in the first place.


The performance optimizations to be found through configuring MySQL will offer little benefit to Inventory as it is my understanding that a lot of the optimizations in MySQL are really geared towards DBs that are under heavy load with many queries a second such as on a busy website.


Inventory does use both InnoDB tables also MyISAM tables.

Speeding up Inventory searches 26 Mar 2010 09:36 #750

  • P-M Lejon
  • P-M Lejon's Avatar Topic Author
  • Offline
  • 5th level - Black Belt
  • 5th level - Black Belt
  • Posts: 92
  • Karma: 3
  • Thank you received: 15
Ok,


So I've tried to narrow down my "slow responding" Inventory a bit more. The InnoDB settings from the cnf files made a little difference (2 sec on "All Computers") but I still have some way to go.


Some more info and timings:

Database has 495 computers.
Database SQL dump file is 787 MB
Time to load "All Computers" is now 13 sec.
Time to load a search for "Show only flagged items" takes approx. 7 sec




According to fseventer, the bottleneck isn't mySQL. It*s the php scripts. Here's what happens when I do the "All Computers, showing 500 entries" search:


26 mar 2010 09.49.41 ?[61769]: /Volumes/filewaveData/FileWaveInventory/WebServer/prefs/user_1.xml, Stat Changed, 0
26 mar 2010 09.49.41 ?[61769]: /Volumes/filewaveData/FileWaveInventory/WebServer/prefs/user_1.xml, Content Modified, 0
26 mar 2010 09.49.41 mysqld[73696]: /Volumes/filewaveData/FileWaveInventory/databases/asset_trustee/sys_view.frm~, File Created, 0
26 mar 2010 09.49.41 mysqld[73696]: /Volumes/filewaveData/FileWaveInventory/databases/asset_trustee/sys_view.frm~, Content Modified, 0
26 mar 2010 09.49.41 mysqld[73696]: /Volumes/filewaveData/FileWaveInventory/databases/asset_trustee/sys_view.frm~, Renamed, 0
26 mar 2010 09.49.41 mysqld[73696]: /Volumes/filewaveData/FileWaveInventory/databases/asset_trustee/sys_view.frm, Renamed, 0
26 mar 2010 09.49.41 mysqld[73696]: /Volumes/filewaveData/FileWaveInventory/databases/asset_trustee/sys_view.frm~, File Created, 0
26 mar 2010 09.49.41 mysqld[73696]: /Volumes/filewaveData/FileWaveInventory/databases/asset_trustee/sys_view.frm~, Content Modified, 0
26 mar 2010 09.49.41 mysqld[73696]: /Volumes/filewaveData/FileWaveInventory/databases/asset_trustee/sys_view.frm~, Renamed, 0
26 mar 2010 09.49.41 mysqld[73696]: /Volumes/filewaveData/FileWaveInventory/databases/asset_trustee/sys_view.frm, Renamed, 0
26 mar 2010 09.49.41 ?[61769]: /Volumes/filewaveData/FileWaveInventory/WebServer/templates_c/wrtkoYZBc, File Created, 0
26 mar 2010 09.49.41 ?[61769]: /Volumes/filewaveData/FileWaveInventory/WebServer/templates_c/wrtkoYZBc, Stat Changed, 0
26 mar 2010 09.49.41 ?[61769]: /Volumes/filewaveData/FileWaveInventory/WebServer/templates_c/wrtkoYZBc, Content Modified, 0
26 mar 2010 09.49.41 ?[61769]: /Volumes/filewaveData/FileWaveInventory/WebServer/templates_c/wrtkoYZBc, Renamed, 0
26 mar 2010 09.49.41 ?[61769]: /Volumes/filewaveData/FileWaveInventory/WebServer/templates_c/%%AA^AA9^AA9D9FDA%%sys.tpl.php, Renamed, 0
26 mar 2010 09.49.41 ?[61769]: /Volumes/filewaveData/FileWaveInventory/WebServer/templates_c/%%AA^AA9^AA9D9FDA%%sys.tpl.php, Chown, 0
26 mar 2010 09.49.41 ?[61769]: /Volumes/filewaveData/FileWaveInventory/WebServer/templates_c/wrtrQAPuK, File Created, 0
26 mar 2010 09.49.41 ?[61769]: /Volumes/filewaveData/FileWaveInventory/WebServer/templates_c/wrtrQAPuK, Stat Changed, 0
26 mar 2010 09.49.41 ?[61769]: /Volumes/filewaveData/FileWaveInventory/WebServer/templates_c/wrtrQAPuK, Content Modified, 0
26 mar 2010 09.49.41 ?[61769]: /Volumes/filewaveData/FileWaveInventory/WebServer/templates_c/wrtrQAPuK, Renamed, 0
26 mar 2010 09.49.41 ?[61769]: /Volumes/filewaveData/FileWaveInventory/WebServer/templates_c/%%C7^C70^C708BAE1%%search_stats.tpl.php, Renamed, 0
26 mar 2010 09.49.41 ?[61769]: /Volumes/filewaveData/FileWaveInventory/WebServer/templates_c/%%C7^C70^C708BAE1%%search_stats.tpl.php, Chown, 0
26 mar 2010 09.49.41 ?[61769]: /Volumes/filewaveData/FileWaveInventory/WebServer/templates_c/wrtdHzc4Q, File Created, 0
26 mar 2010 09.49.41 ?[61769]: /Volumes/filewaveData/FileWaveInventory/WebServer/templates_c/wrtdHzc4Q, Stat Changed, 0
26 mar 2010 09.49.41 ?[61769]: /Volumes/filewaveData/FileWaveInventory/WebServer/templates_c/wrtdHzc4Q, Content Modified, 0
26 mar 2010 09.49.41 ?[61769]: /Volumes/filewaveData/FileWaveInventory/WebServer/templates_c/wrtdHzc4Q, Renamed, 0
26 mar 2010 09.49.41 ?[61769]: /Volumes/filewaveData/FileWaveInventory/WebServer/templates_c/%%8D^8D6^8D625396%%search_pagination.tpl.php, Renamed, 0
26 mar 2010 09.49.41 ?[61769]: /Volumes/filewaveData/FileWaveInventory/WebServer/templates_c/%%8D^8D6^8D625396%%search_pagination.tpl.php, Chown, 0
26 mar 2010 09.49.42 System[0]: /Volumes/filewaveData/FileWaveInventory/WebServer/prefs, Folder Changed, 115384962
26 mar 2010 09.49.42 System[0]: /Volumes/filewaveData/FileWaveInventory/databases/asset_trustee, Folder Changed, 115384984
26 mar 2010 09.49.42 System[0]: /Volumes/filewaveData/FileWaveInventory/WebServer/templates_c, Folder Changed, 115385035
26 mar 2010 09.49.51 ?[61769]: /Volumes/filewaveData/FileWaveInventory/WebServer/templates_c/wrtQO4okp, File Created, 0
26 mar 2010 09.49.51 ?[61769]: /Volumes/filewaveData/FileWaveInventory/WebServer/templates_c/wrtQO4okp, Stat Changed, 0
26 mar 2010 09.49.51 ?[61769]: /Volumes/filewaveData/FileWaveInventory/WebServer/templates_c/wrtQO4okp, Content Modified, 0
26 mar 2010 09.49.51 ?[61769]: /Volumes/filewaveData/FileWaveInventory/WebServer/templates_c/wrtQO4okp, Renamed, 0
26 mar 2010 09.49.51 ?[61769]: /Volumes/filewaveData/FileWaveInventory/WebServer/templates_c/%%8D^8D6^8D625396%%search_pagination.tpl.php, Renamed, 0
26 mar 2010 09.49.51 ?[61769]: /Volumes/filewaveData/FileWaveInventory/WebServer/templates_c/%%8D^8D6^8D625396%%search_pagination.tpl.php, Chown, 0
26 mar 2010 09.49.51 ?[61769]: /Volumes/filewaveData/FileWaveInventory/WebServer/templates_c/wrtCyZTNB, File Created, 0
26 mar 2010 09.49.51 ?[61769]: /Volumes/filewaveData/FileWaveInventory/WebServer/templates_c/wrtCyZTNB, Stat Changed, 0
26 mar 2010 09.49.51 ?[61769]: /Volumes/filewaveData/FileWaveInventory/WebServer/templates_c/wrtCyZTNB, Content Modified, 0
26 mar 2010 09.49.51 ?[61769]: /Volumes/filewaveData/FileWaveInventory/WebServer/templates_c/wrtCyZTNB, Renamed, 0
26 mar 2010 09.49.51 ?[61769]: /Volumes/filewaveData/FileWaveInventory/WebServer/templates_c/%%8B^8BF^8BF16D52%%nav.tpl.php, Renamed, 0
26 mar 2010 09.49.51 ?[61769]: /Volumes/filewaveData/FileWaveInventory/WebServer/templates_c/%%8B^8BF^8BF16D52%%nav.tpl.php, Chown, 0
26 mar 2010 09.49.51 ?[61769]: /Volumes/filewaveData/FileWaveInventory/WebServer/templates_c/wrtbZ0Td0, File Created, 0
26 mar 2010 09.49.51 ?[61769]: /Volumes/filewaveData/FileWaveInventory/WebServer/templates_c/wrtbZ0Td0, Stat Changed, 0
26 mar 2010 09.49.51 ?[61769]: /Volumes/filewaveData/FileWaveInventory/WebServer/templates_c/wrtbZ0Td0, Content Modified, 0
26 mar 2010 09.49.51 ?[61769]: /Volumes/filewaveData/FileWaveInventory/WebServer/templates_c/wrtbZ0Td0, Renamed, 0
26 mar 2010 09.49.51 ?[61769]: /Volumes/filewaveData/FileWaveInventory/WebServer/templates_c/%%4C^4C6^4C622369%%sys_nav_saved.tpl.php, Renamed, 0
26 mar 2010 09.49.51 ?[61769]: /Volumes/filewaveData/FileWaveInventory/WebServer/templates_c/%%4C^4C6^4C622369%%sys_nav_saved.tpl.php, Chown, 0
26 mar 2010 09.49.52 System[0]: /Volumes/filewaveData/FileWaveInventory/WebServer/templates_c, Folder Changed, 115385086
26 mar 2010 09.49.54 ?[61769]: /Volumes/filewaveData/FileWaveInventory/WebServer/templates_c/wrtvxng6B, File Created, 0
26 mar 2010 09.49.54 ?[61769]: /Volumes/filewaveData/FileWaveInventory/WebServer/templates_c/wrtvxng6B, Stat Changed, 0
26 mar 2010 09.49.54 ?[61769]: /Volumes/filewaveData/FileWaveInventory/WebServer/templates_c/wrtvxng6B, Content Modified, 0
26 mar 2010 09.49.54 ?[61769]: /Volumes/filewaveData/FileWaveInventory/WebServer/templates_c/wrtvxng6B, Renamed, 0
26 mar 2010 09.49.54 ?[61769]: /Volumes/filewaveData/FileWaveInventory/WebServer/templates_c/%%7A^7A9^7A905613%%toolbar.tpl.php, Renamed, 0
26 mar 2010 09.49.54 ?[61769]: /Volumes/filewaveData/FileWaveInventory/WebServer/templates_c/%%7A^7A9^7A905613%%toolbar.tpl.php, Chown, 0


Changing to only 10 Computers at a time reduces the search to 4 sec but that's not a good option, since there isn't a simple way to just get the info for 1 computer without creating a search...


Any suggestions?




/P-M
P-M Lejon
System Administrator
BonnierNews
Sweden

Searching for a Single System 01 Apr 2010 16:40 #756

  • ben
  • ben's Avatar
  • Offline
  • User is blocked
  • User is blocked
  • Posts: 16
  • Thank you received: 0
Hi PM-


The best workaround in the current implementation is create a New Computer Search and use the Apply button to get the results. There is not a need to save the search. Here are some steps.


- Click New Computer Search


- Select one item from System Info and click to have it moved to the list on the right


- Click Next


- Enter in the search criteria


- Hit Apply and modify your search as needed until you get the Computer you are looking for


Additionally, you can save this search to avoid the first steps and then edit it any time you want to look for a certain system and rather than saving each time, you can just hit the Apply button to get the results.


 Hi Ben,your solution is 07 Apr 2010 20:38 #759

  • P-M Lejon
  • P-M Lejon's Avatar Topic Author
  • Offline
  • 5th level - Black Belt
  • 5th level - Black Belt
  • Posts: 92
  • Karma: 3
  • Thank you received: 15
Hi Ben,

your solution is what I meant when I said "creating a search".

My co-workers are used to FileMaker with its buttons and pre-made search fields . Moving to Inventory is a complete new ball park for them.
An example of a simple search could in their case be "Who's the last logged in (and when) on computer with ID=1234?". It would be nice to have some sort of filtering in each search result

Let me know when you are sketching on new features for the next big release and I'll give you a bunch of good and annoying requests
P-M Lejon
System Administrator
BonnierNews
Sweden
  • Page:
  • 1