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)?

[southshorepizza]

Yes. The customization is mainly read only queries and they are reading indexed columns and tables. However 2 computers read every 30 seconds to get updated information and are writing as needed between those but on a peak hour of 30 orders that writing is only between 40 and 50 writes in that hour. So no where near as many writes as there are reads.

[mc12345678]

Remember though, the fact that columns that are indexed and being read is not the same thing as the query being structured to take advantage of the indexing. The sequence of the queried fields can make or break the use of the indexing, as well as omitting one of the indexed columns when compared to the indices.

[southshorepizza]

Yes they are querried so the db narrows down the results as fast as possible and pulls as many from one query as possible.
Here is an example of the 30 second query

$sql = "SELECT orders_id FROM ".TABLE_ORDERS." WHERE date_purchased < NOW() AND orders_status IN (" . implode(',',$statuses) . ") ORDER BY orders_id ASC";

Limit by date purchased first because that eliminates the majority of the table. Then limit by status.

Here is another one and yes I plan on changing the * to more specifics in the near future.

$sql = 'select * from ' . TABLE_ORDERS . ' o left join ' . TABLE_HTA . ' h ON o.orders_id = h.order_id WHERE o.date_purchased < NOW() AND o.shipping_module_code LIKE "%Flat%" AND h.status = "MADE" ORDER BY o.orders_id ASC';

This one again limits by date. Then limits by shipping module which cuts out 60% of the orders. Then the made is the lowest percentage of finds.

[mc12345678]

Yes they are querried so the db narrows down the results as fast as possible and pulls as many from one query as possible.
Here is an example of the 30 second query

$sql = "SELECT orders_id FROM ".TABLE_ORDERS." WHERE date_purchased < NOW() AND orders_status IN (" . implode(',',$statuses) . ") ORDER BY orders_id ASC";

Limit by date purchased first because that eliminates the majority of the table. Then limit by status.

I'm not where I can review the indices for that table, but the use of the date comparison as provided doesn't help limit the quantity of returned rows at all... It simply returns all orders that have been made. There is no elimination of anything performed by looking for all orders purchased before now. (Even if the < was incorrect, there is/would be something missing to bound the data as there are no orders in the future...)