Help! Save This Website!

[Drew Aitch]

Hi Chiang Mai

I've just had a hosting company threatening to suspend the account on one of my projects because, they say, the database is running too many queries to retrieve data, and this is taking undue resources on their server.

So, just as a stab in the dark, does anyone here know of anyone in Chiang Mai (maybe you?), who understands the workings of MySQL databases and queries? It looks as though the project needs a little database optimization in order to keep the host happy.

PS. I prefer not to liaise remotely with anyone whenever possible, so please no recommendations for a bloke you might know down in Pattaya, Phuket, or Bangers etc! Thanks

Thanks in advance

Aitch

[BillR]

are you using wordpress? if so install wordpress super cache and that will solve your problem. The other option is to upgrade your hosting to vps or dedicated, a good idea anyway as shared hosting anyone can bring the server down.

[Mestizo]

I seriously doubt you will find anyone in Chiang Mai proficient with mysql. Of course there are people capable of getting it to "work" with a default install, but finding someone who understands tuning/ optimization/ scaling of mysql is extremely rare. If you do find someone, please let me know. We have a full time employment position with a nice farang salary and a work permit waiting for that person.

With that said, I recommend that you run mysqltuner against it. Nice little perl script that will analyze your current configuration and stats and recommend tweaks based on that. Feel free to post your results here and I can go through them with you.

Also, in your /etc/my.cnf file enable slow query logging for like 1 second and dump them to a file. Something like this:

log-slow-queries=/var/log/mysql-slow-query.log

long_query_time=1

That will log the queries from your application that are taking more than 1 second to complete. You can then review those to identify the offending culprits. You can either review these manually or run the file through mysqldumpslow.

Hope this helps. Should be enough to get you started in the right direction.

-Mestizo

[khunphil]

Hi,

It's a complex thing. Optimizing a database need time to review code , checking sql queries, and database design (or redesign), maybe hardware check also. I am an IT programmer working with PHP and MYSQL database for the last 10 years, and living (and working) in Chiangmai - Lamphun.

First thing, as your database is hosted, it could be good to implement it locally, on a server. This is not a heavy investment, but at least you can active some logs to check which querries takes time... too long time. They normally ask the CPU to work hard, and can generate heavy html pages to send to final user.

The main problem you have is that the website is already in production, so you will have to test any change before uploading new code to your server, or it can be catastrophic.

I am not a professional in tuning MYSQL database, but I write some heavy applications (CRM, ERP), and when the database begin to be slow, or customers complaining, I have to look at what can be done. Sometimes, indexing some fields can make you save a lot of CPU cycle, and divide the timing by 1,000. I had to deal with it so I can speak by experience ;-). Small queries can be fast, limiting the number of fields when you make a query can help, and some PHP code to handle some job that we dont wanna give to Mysql can help also. If you are using some open-source code, programmer can have a look at it, but modifying this code will make you loose time when upgrading the software. If you are working with private code that someone wrote for you, but does not maintain any more, it can be more easy to modify it for your own needs.

Try to know more from your provider what kind of resources are lacking here. CPU ? Memory ? Bandwidth ? Try to get as much logs and information you can...

This can be a really interesting work, and I would like to have more free time to help you but I try to relax on week-end ;-) ... anyway I will follow this thread and help you if I can.

Philippe

[khunphil]

Oops I forget the most easy thing ... it is maybe time to upgrade your website contract with your hosting provider, or maybe to find another one ;-). If your website is the "property for rent" website, I wrote some like things, it is not really consuming in term of resources ... except if you have 10,000 customers per hours ...

Keep us informed

Philippe

[Drew Aitch]

Ayup Folks and thanks for your feedback

This is actually a 6 year old project, and it's also a free-for-all that just about pays for itself with sponsorship. It also pretty much runs on auto-pilot and is not one on my money sites as such, but more of a pet project, being the first baby I ever designed.

If I were to upgrade the hosting to VPS or Dedicated, the free-for-all would probably end up actually costing me, and I'd sooner close any site down rather than run it at a loss, no matter how much I enjoyed the project.

Just for information, this website would still be with its original host if they had not sold out overnight. The company they sold out too was a complete disaster and lost data, buggered up certain functions, and after 2 years of one support ticket after another, I decided not to renew with them.

So now we're with Hosting #3, and they migrated the site over smoothly earlier this month without any downtime. A good start I thought. That was until they sent me an email a couple of days ago complaining that the database queries are draining server resources, and although I understand where they're coming from, there have been not updates for at least 18 months, and there were never any such complaints from the previous hosts, so it seems as though these guys are a little stricter.

Anyhow, those of you who have responded thus far seem to be technically adept. So I've copied some techie snippets below, which I'm sure will make more sense to you than me. I might also try and find a bright spark at one the universities around here who’s fresh up-to-date on such things (but I won't hold my breath!).

It might also be worth noting that nothing is broken on this site and all functions are operating without error or delay from a user perspective. Okay, here's some technical data, and if someone can help translate it a bit more into laymen's terms for me, it would be great and also help should I need to explain to some developer somewhere what needs to be done specifically.

Thanks in advance guys, any feedback is much appreciated

Some of the queries related to your account take a good deal longer than 1 second to complete, and seem to be the source of some latency we are seeing in server response.

Here are examples of such queries:

# User@Host: mrroom5_finder[mrroom5_finder] @ localhost [] # Query_time: 5 Lock_time: 0 Rows_sent: 0 Rows_examined: 2820292 use mrroom5_finder; SET timestamp=1295469838; SELECT view_id FROM views WHERE location = 'http://www.mrroomfinder.com/detail_jp.php?id=841' AND adformat= '234x60_as' AND channel= 'null' AND referrer= 'http://room.m-piece.co.th/?station=15' AND date=now() limit 1;

The queries you're running aren't all that bad in terms of performance. However, the tables you're running them against are quite massive. The views table in your finder database has nearly 3 million rows. On top of this, you're querying against non-indexed data (location, adformat, channel, referrer, and date. You're doing this repeatedly to retrieve 1 record.

In each instance of such a query, you're specifying that the date be today's date. This would lead me to believe that, perhaps, some of the older data in this table is no longer relevant? If this is the case, I would recommend purging some of the old records in the database to speed up some of your queries. If the database only has to search 3,000 rows as opposed to 3 million, your data retrieval will be much faster.

Additionally, I'd look into indexing the columns that you'll be querying against frequently. This will use a bit more storage space, but it will ultimately result in faster queries.

Aitch

[thaitar]

I am not an expert, but I know more than the average user. PM me.

[thaitar]

Also, VPS is not that expensive. You can get a decent VPS at Burst.net for ~ $6/mo. 512 RAM, which should be enough for a small MySQL/PHP project.

[khunphil]

Some of the queries related to your account take a good deal longer than 1 second to complete, and seem to be the source of some latency we are seeing in server response.

Here are examples of such queries:

# User@Host: mrroom5_finder[mrroom5_finder] @ localhost [] # Query_time: 5 Lock_time: 0 Rows_sent: 0 Rows_examined: 2820292 use mrroom5_finder; SET timestamp=1295469838; SELECT view_id FROM views WHERE location = 'http://www.mrroomfinder.com/detail_jp.php?id=841' AND adformat= '234x60_as' AND channel= 'null' AND referrer= 'http://room.m-piece.co.th/?station=15' AND date=now() limit 1;

The queries you're running aren't all that bad in terms of performance. However, the tables you're running them against are quite massive. The views table in your finder database has nearly 3 million rows. On top of this, you're querying against non-indexed data (location, adformat, channel, referrer, and date. You're doing this repeatedly to retrieve 1 record.

In each instance of such a query, you're specifying that the date be today's date. This would lead me to believe that, perhaps, some of the older data in this table is no longer relevant? If this is the case, I would recommend purging some of the old records in the database to speed up some of your queries. If the database only has to search 3,000 rows as opposed to 3 million, your data retrieval will be much faster.

Additionally, I'd look into indexing the columns that you'll be querying against frequently. This will use a bit more storage space, but it will ultimately result in faster queries.

Actually, this guy told us a lot. It is for me true that first, indexing those fields, you will save time, as MySQL does not need to compare field by field what you need, and those fields (location or referrer for example) are quite long. That does not mean that you must index every field, because if you update to many, Mysql has to rewrite index each time, and this can take time. But sure, looking at the whole database and code would help.

For searching on multiple fields, normally you must linked table by ID. For example:

HOUSE_ONE (ID 12) in CHIANGMAI(City ID 15)

HOUSE_TWO (ID 18) in LAMPHUN (CITY ID 55)

HOUSE_THREE (ID 33) in LAMPHUN (CITY ID 55)

If you search houses in Lamphun, much better to ask MYSQL to find all house where the CITY ID is 55, than where the city = "LAMPHUN". Here you need an index on CITY... Hope this helps. MYSQL has a tool (EXPLAIN) which explain your query. Not really easy to get what it means, but this give you an idea of how many lines you are linking together. Good indexing can change this from 5 millions lines ... to only 1 !

Purging can be an option, but if you have linked tables, this must be done in a clever way, to avoid those partially retrieved data. But you can save the old table as (full_table_xxx) then move lines where year is less than 2005 for example to another table (DELETED_LINES_TABLE_XXX). Then you have the listing of what you delete, if one day this may be necessary.

Archiving part of your database (current tables and older tables) could be also an alternative. When customers want to access older data, this query will direct on other tables. Here you must modify your code, to access different tables, or better different database when this is needed. This can be a tricky job by the way.

Hope this can help ... God I miss this kind of work ;-)

Philippe

[nikster]

I am staying away from databases as much as I can - slight aversion agains 70ies tech - but khun phil wouldn't just indexing those columns solve the problems?

If indexing means the index needs to be updated on every insert - maybe there aren't all that many inserts? Are there thousands of inserts per minute? I hope not... and if not, it should be OK no?

Changing the DB schema and / or queries is certainly a lot more risky - you could do timing tests locally to make sure the new indexed columns don't screw anything up...

I certainly know that in any normal programming language or environment updating an index of 3M entries wouldn't take much time at all... I am sure MySQL is heavily optimized at that too.

Edited January 22, 2011 by nikster

[khunphil]

I am staying away from databases as much as I can - slight aversion agains 70ies tech - but khun phil wouldn't just indexing those columns solve the problems?

If indexing means the index needs to be updated on every insert - maybe there aren't all that many inserts? Are there thousands of inserts per minute? I hope not... and if not, it should be OK no?

Changing the DB schema and / or queries is certainly a lot more risky - you could do timing tests locally to make sure the new indexed columns don't screw anything up...

I certainly know that in any normal programming language or environment updating an index of 3M entries wouldn't take much time at all... I am sure MySQL is heavily optimized at that too.

Hi

Actually no, it would not take time. Forget about re-indexing, I used a wrong word. Inserting an index is fast, very fast. 3M record in a table is BIG if you scan it all each time, or small if you access only the data you need. Even with many inserts, when a line contains 10 fields, 1 more field for the index does not count that much. The problem can be true when a line with 10 fields has an index on each, but this is often useless, and proves that your database design is not correct. Imagine a GPS who tells you to turn right then left then right to visit your friend house... if the GPS is right, you dont care too much if the city has many roads or not, because you know exactly where you go... then imagine going in each soi then comparing the address number to the one on your paper...forget it ;-)

Looking at the code, at the queries, you can determine first if you can use ID for each line (this was the case in my example) and then, place a index on that column, then in your code, use ID for your joins queries for example. Then you win ! If not, at least, place an index on fields used in where, join, min, max ...

Changing the DB schema is risky because you have to modify the code. Database design is made with input data, size and frequency of those inputs, and design of output data. As we are not going to redesign the database now, adding indexes on some fields would have no harm, and surely increase performance.

Philippe

Edited January 22, 2011 by khunphil

[whimsy]

I used to do relational database tuning in a corporate environment. I agree with everything your web host said, and also KunPhil's replies. Indexing columns will most likely help a lot. Wihout indexes on the referenced columns then that 3 million row will likely be scanned for matches row by row. That will be a lot of CPU and database queries. Just an index on 'location' may be enough to optimise this query. You should probably have an index on view_id too if you don't already.

As nikster said, in a typical site, the number of updates to indexes are many times less than the number of reads, so the cost of updating indexes when you change an entry may be well worth it. Updating an index should only take milliseconds.

The DBA at the web host has been very helpful in giving you that information. See which other queries either take a significantly long time or are executed the most frequently. Both of these types of queries would be worth tuning. The DBA might have that information already by the sound of it.

[khunphil]

The DBA at the web host has been very helpful in giving you that information. See which other queries either take a significantly long time or are executed the most frequently. Both of these types of queries would be worth tuning. The DBA might have that information already by the sound of it.

Oh yes .. most of them don't take time to explain or give clues, and just shut down the website ;-) ... he is a good one !

Philippe

[whimsy]

re Phillipes last post., Using id's to join tables can be an efficient way to go, I agree. If you do this you must ensure that id's are never changed and deleted ones are not re-used. Unless you have code to deal with this properly you can corrupt your data.

Of course if you have id's in your table you probably have this RI code in place already to prevent id's changing.

Edited January 22, 2011 by whimsy

[khunphil]

re Phillipes last post., Using id's to join tables can be an efficient way to go, I agree. If you do this you must ensure that id's are never changed and deleted ones are not re-used. Unless you have code to deal with this properly you can corrupt your data.

Make them primary keys with auto-increment option ... this protects a lot ...except deleting a record of course... and yes, don't play with ID. Good remark ! Use them to find records, then update/delete/read this record !

Philippe

PS : we are going technical ... and will maybe be moved in computer forum ;-)

[Drew Aitch]

Hi Guys, and ha, ha, lol

You've kind of lost me here, but i can see you're all enjoying yourself I would love to be 'code savy', because i have so many ideas for database driven projects, but coders do not come cheap. I do design all my websites personally, and add scripts along with a few bells and whistles, but when it comes to database queries and all that goes with them, i just don't have the time to study unfortunately

What's so annoying about this particular project is that it has had 4 different developers work on it over the years, and all 3 after the first one said the same, which is along the lines of (after they know i don't understand MySQL):

"Ohhhh,tut, tut, tut! I don't know who worked on your site before me, but the code is in a right ole mess. I'll have to charge you $$$ to clean it up before i can even start on your updates!"

They've all said stuff similar to this and charged their fees, yet it seems there is always someone new complaining about the code, only this time it's the new host and not some hired helper One can't help but wonder whether any of them actually do anything with the database. After all, how would i know what's been done, or not done, on work that's latent to a db layman like me!

I'm enjoying reading the posts though, and will continue to try and understand what has been written here so that i'm not so blind if some shark tries to pull the wool over my virtual eyes with yet another, "Ohhh, tut, tut, tut..!"

I agree this thread is probably best placed in the computer forum, but the only thing i would like to do is keep it local if possible, as per my opening post. Otherwise i can see a zillion 'PVT ME' posts coming from expats nationwide.

Aitch

[sbk]

Moving to internet forum per OP request

[Crushdepth]

However, the tables you're running them against are quite massive. The views table in your finder database has nearly 3 million rows. On top of this, you're querying against non-indexed data (location, adformat, channel, referrer, and date. You're doing this repeatedly to retrieve 1 record.

In each instance of such a query, you're specifying that the date be today's date. This would lead me to believe that, perhaps, some of the older data in this table is no longer relevant?

This looks good advice to me. Do you really need to keep your most ancient records? Time burns quickly on the internet, are they still useful?

There is a limit to how hard you can squeeze budget webhosting, and the database server is usually the first thing to die.