Database user permisions

[southshorepizza]

I'm having to add some users as we are exceeding 25 connections per user on hostgator's system. So if I create a seperate user for the admin and the storefront it should cut down on some of these max connection errors. However I'm not sure what permissions I would want to grant a new user. I can't find it in the wiki or forum either. I'm sure it's here somewhere. Any suggestions?

"Hello,

Thank you for contacting HostGator.

MySQL has been up for 23 days. We do see that you are hitting the 25 mysql connections. Which will show a database connection issue."

[RodG]

I'm having to add some users as we are exceeding 25 connections per user on hostgator's system. So if I create a seperate user for the admin and the storefront it should cut down on some of these max connection errors.

I'm not aware of anyone that has ever actually done this before.
Although its 'an interesting' idea, I have to wonder how many connections you will actually 'save' by having a different user for the admin.

However I'm not sure what permissions I would want to grant a new user. I can't find it in the wiki or forum either. I'm sure it's here somewhere. Any suggestions?

The 'storefront' user will need DATA permissions of Select, Insert, Update & Delete.
Thar 'admin' user will need all of these, plus 'File' (perhaps), as well as Create, Alter, Index, Drop & Create Temporary Tables for the STRUCTURE. These will be the *minimum* (however, by default all of the STRUCTURE and DATA permissions are enabled.

Anyway, although 25 connection per user doesn't seem like much, most connections only exist for a fraction of a second, so in practice it can actually be quite difficult to reach unless you have a very active store, and that being the case perhaps you should consider a different host or a different plan. I fear that even if what you want to do works it will only be a temporary fix.

Cheers
RodG

[southshorepizza]

Thank you. I agree it is bizarre and probably a temporary fix. There is heavy customization which creates quite a few computers running queries at the same time. How we get 25 at once I'm not sure. But none the less this is what their tech support has determined the reason I keep seeing error messages and the dreaded nddbc.html screen. I'll give it a try. Ultimately I would prefer a local server integrated with the website. Unfortunately I have to learn this as I go so it's a slow process. The fix they suggest increases my cost by $150/month. Not something I'm eager to do just yet.

[RodG]

The fix they suggest increases my cost by $150/month. Not something I'm eager to do just yet.

Find a better host. The 'cheap' shared hosts make most of their money by 'up selling' to (and over charging) the most successful (busiest) sites on their typically overloaded and oversold shared servers.

They know that most people find (or think) that migrating to a new host is a major undertaking so the upsell is usually quite successful. They'll even migrate the site at no cost, and who can refuse an offer like that?

Cheers
RodG

[southshorepizza]

I have 3 computers side by side taking orders. I have noticed that with all 3 being used simultaneously and 1 in another room plus possible home users the computers seem to work in sequence. So the third user I see click a button often has to wait for the first two computers to update. I'm already switching from shared hosting to a private dedicated server. I really can't believe that the table locking is taking as long as it is but the hosting company says too many queries are coming in at a time to lock entire tables. I find that hard to believe as we are only processing about 30 orders in a peak hour. These locks should be happening really fast shouldn't they?

Would there be a benefit to moving some tables from myisam to innodb? If so would triggers be necessary or possible to execute commits?

[mc12345678]

Mentioned that the site is highly customized. Did that extra customization include additional database queries, and have those queries been structured to take advantage of the indexing (faster querying)?