Item Images hosted on an external CDN

Comments

20 comments

  • Avatar
    Chris Muench

    So you are asking if you can enter a URL instead of uploading it?

    0
    Comment actions Permalink
  • Avatar
    John Clark

    Essentially - yes, but without the application storing the image in it's database.

    0
    Comment actions Permalink
  • Avatar
    Peyton

    Definitely like this idea. I will eventually implement this into my installation.

    0
    Comment actions Permalink
  • Avatar
    Michael Kelly-Gleeson

    This would be a good idea I think, to store the image data outside the database.

    with many images the database becomes very big.

    0
    Comment actions Permalink
  • Avatar
    Marc

    How much effect does a large amount of image bloat have on database performance?  Our database is about 2.2G in size. The image table is about 1.8G of it. We are seeing quite a bit of stalling lately. I do like everything in 1 backup-able database that manages the images itself but would look into external image hosting if it would speed up performance considerably. 

    Is it possible to compress the images stored in the database? Would that boost performance? We have over 20k products and store a product image for each item.  They are primarily jpg and a few pngs. 

    0
    Comment actions Permalink
  • Avatar
    Chris Muench

    That shouldn’t effect performance too much as it is all in one table. Where is slowness happening?

    0
    Comment actions Permalink
  • Avatar
    Chris Muench

    If you go to store config and check “speed up search queries” can help searches performance

    0
    Comment actions Permalink
  • Avatar
    Peyton

    If I had the time, I would implement and post the code. It could be done in less than 10-15 lines I believe. I have already hardened security on my setup to prevent the signatures and other app files from being scraped via image id lookup. A simple check to see if the app file's URI is set and instead of grabbing file_data, simply return that link would be incredibly easy and very beneficial for performance and maintainability of the system. Just my .02.

    0
    Comment actions Permalink
  • Avatar
    Marc

    The slowness happens at random points. Sometimes when changing tabs in new item creation, sometimes at POS when looking up a customer or item. Sometimes it will stall for up to 15-20 seconds or more.  We only have 2 POS terminals and a back office management machine. So very few consecutive connections.

    We run the software on a dedicated T30 with Intel Xeon E3-1225 Quad-Core 3.30 GHz and 32G of ram that does nothing else but run the POS system and do backups. It's running a clean install of Server 2016 Standard but I am using the auto-installed version of PHPPOS for ease of updating. I have noticed while being in the same room as the server, when it stalls I can look over and see the HDD light activity light on solid. As soon as it stops the system becomes responsive again.  I have the system running on an SSD and backing up to a separate drive. The SSD drive is the one that came in the server (I assumed it would have a decent drive in it but don't know for sure) and I have a feeling it may be more a "read" spec drive.  I have a Samsung 883 DCT inbound since I know it is a good drive for heavy read and write environments.

    I would really like to keep the database and all images contained together to ease backup and updating routines and avoid link breaking. For years we used a third party web hosted application (Crystal Commerce) for our POS and they were constantly breaking our image links and I would have to spend days manually rebuilding all the database entries and reloading all the images.  Nightmare juice.

    0
    Comment actions Permalink
  • Avatar
    Marc

    PS. turning on "Speed up Search Queries" Seemed to help a bit but it messed with a lot of our day to day operation and slowed us down even more. We couldn't do partial searches and such and could not sort admin tables.

    0
    Comment actions Permalink
  • Avatar
    Chris Muench

    You might consider changing innodb_buffer_pool_size=5G in my.cnf (c:/phppos/MySQL/) by default the buffer pool is small. If you have that much ram I would make it large.

    0
    Comment actions Permalink
  • Avatar
    Marc

    I don't see that file in that folder.  I see a my.ini  but does not have that line in it. Just max_allowed_packet. If I can find that file, What would be a good starting point and can I set it too large? 

    0
    Comment actions Permalink
  • Avatar
    Chris Muench

    My.ini is right file; add line below [mysqld]

    0
    Comment actions Permalink
  • Avatar
    Marc

    Hmmm as soon as I add that line in and try to restart SQL server in won't start. Just kicks out exit code 1. Delete the line back out and SQL starts up fine. 

    0
    Comment actions Permalink
  • Avatar
    Chris Muench

    Also send .err file in MySQL/Data folder

    0
    Comment actions Permalink
  • Avatar
    Marc

    2019-09-15 21:28:53 6704 [Note] Plugin 'FEDERATED' is disabled.
    2019-09-15 21:28:53 6704 [ERROR] innobase_buffer_pool_size can't be over 4GB on 32-bit systems
    2019-09-15 21:28:53 6704 [ERROR] Plugin 'InnoDB' init function returned error.
    2019-09-15 21:28:53 6704 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
    2019-09-15 21:28:53 6704 [ERROR] Unknown/unsupported storage engine: INNODB
    2019-09-15 21:28:53 6704 [ERROR] Aborting

     

    Confused? This is on my seperate dev machine running win 10 64bit?  I am not at shop currently to test on live machine.

     

    0
    Comment actions Permalink
  • Avatar
    Marc

    Just tried setting it to 1G and it starts. 1-3G lets it start 4 or more and it errors.

     

    0
    Comment actions Permalink
  • Avatar
    Chris Muench

    Ah ok, could be windows limit. 3g should be bigger than your database so that will help

    0
    Comment actions Permalink
  • Avatar
    Marc

    When it starts it tells me:

    2019-09-15 21:35:20 12108 [Note] Plugin 'FEDERATED' is disabled.
    2019-09-15 21:35:20 12108 [Note] InnoDB: Using atomics to ref count buffer pool pages
    2019-09-15 21:35:20 12108 [Note] InnoDB: The InnoDB memory heap is disabled
    2019-09-15 21:35:20 12108 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
    2019-09-15 21:35:20 12108 [Note] InnoDB: Memory barrier is not used
    2019-09-15 21:35:20 12108 [Note] InnoDB: Compressed tables use zlib 1.2.3
    2019-09-15 21:35:20 12108 [Note] InnoDB: Not using CPU crc32 instructions
    2019-09-15 21:35:20 12108 [Note] InnoDB: Initializing buffer pool, size = 3.0G
    2019-09-15 21:35:20 12108 [Note] InnoDB: Completed initialization of buffer pool
    2019-09-15 21:35:20 12108 [Note] InnoDB: Highest supported file format is Barracuda.
    2019-09-15 21:35:20 12108 [Note] InnoDB: 128 rollback segment(s) are active.
    2019-09-15 21:35:20 12108 [Note] InnoDB: Waiting for purge to start
    2019-09-15 21:35:20 12108 [Note] InnoDB: 5.6.23 started; log sequence number 2234657215
    2019-09-15 21:35:20 12108 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
    2019-09-15 21:35:20 12108 [Note] - '127.0.0.1' resolves to '127.0.0.1';
    2019-09-15 21:35:20 12108 [Note] Server socket created on IP: '127.0.0.1'.
    2019-09-15 21:35:20 12108 [Note] Event Scheduler: Loaded 0 events
    2019-09-15 21:35:20 12108 [Note] C:\PHPPOS\mysql\bin\mysqld.exe: ready for connections.
    Version: '5.6.23' socket: '' port: 3306 MySQL Community Server (GPL)

    0
    Comment actions Permalink
  • Avatar
    Marc

    Ok I'll try it when I get back to the shop. Thanks!

     

    0
    Comment actions Permalink

Please sign in to leave a comment.