Mysql / datafeed speed problem

[Tobman]

Tweet

Hello,

I have a question relating to mysql and datafeeds.

I currently have one database for merchant feeds, and I download merchants feeds and read them into a one table for each merchant. One for Comet, one for BnQ etc.

My host recently advised me that it would be better to spread the tables between different databases and that I could be putting too many tables in one database.

I guess my question could be simplified to: are there any speed issues to be aware of with regards to the number of tables, and the number of rows in each table within a database?

It would be MUCH simpler for me to keep adding merchant feeds to the same database. I have a feeling my problems come from poorly optimized queries, rather than too many tables.

Any help is more than appreciated. Thanks guys.

Tom

[Cheapdvds]

how many products are there in total?

[Barry]

The one major advantage of using multiple databases is that its easier to backup and restore databases rather than tables for the majority of people.

You may get small increases in efficiency if you use multiple databases as opposed to tables but probably not alot unless you have a serious amount of data.

It maybe your host in a sort of way asking you to split up the script to save on their server load - so instead of importing a shed load of datafeeds in a loop you fire them off at regular intervals.

Ta

Baz

[D-Mac]

It maybe your host in a sort of way asking you to split up the script to save on their server load - so instead of importing a shed load of datafeeds in a loop you fire them off at regular intervals.

Yep, it is interesting that the suggestion came from the host

[Barry]

Tell you host to have less server applications on their machine

No that probably wouldn't work..

My advice would be that if you are importing feeds - to space them out. - dont hammer the server.

If you are running complex queries like RND or LIKE on the database on the user side then this also maybe slowing down the server. Or maybe you have an infinite loop in there somewhere?

Ta

Baz

[Tobman]

how many products are there in total?

Thanks for the help on this guys,

Cheapdvds, here's the tables, with number of products:

1,723 - 1.8 MiB
3,519 - 1.5 MiB
6,258 - 4.1 MiB
3,381 - 2.2 MiB
431 - 200.1 KiB
6,064 - 3.9 MiB
1 - 2.1 KiB
4,801 - 2.1 MiB
1,117 - 1.3 MiB
67 - 74.7 KiB
4,162 - 1.9 MiB
3,549 - 1.7 MiB
3,893 - 2.4 MiB
285,338 - 212.0 MiB
21,668 - 17.6 MiB
4,302 - 2.8 MiB
55,126 - 49.5 MiB
830 - 632.1 KiB
52 - 32.6 KiB
21,691 - 17.9 MiB
1,854 - 939.5 KiB
492 - 437.4 KiB
241 - 167.2 KiB
5,829 - 3.4 MiB
2,562 - 1.5 MiB
777 - 574.0 KiB
1,255 - 1.0 MiB
261 - 110.8 KiB
4,826 - 6.4 MiB
2,429 - 2.9 MiB
3,343 - 4.5 MiB
2,021 - 2.2 MiB
74 - 11.5 KiB
1,009 - 632.8 KiB
292 - 686.3 KiB
767 - 528.0 KiB
6,758 - 4.2 MiB
13,926 - 8.0 MiB
10,339 - 13.9 MiB
11,067 - 6.1 MiB
5,830 - 4.3 MiB
2,314 - 1.0 MiB
1,331 - 681.6 KiB
40,931 - 30.2 MiB
4,219 - 5.1 MiB
0 - 1.0 KiB
510 - 349.2 KiB
2,329 - 1.4 MiB
1,927 - 1.5 MiB
22,158 - 15.6 MiB
38,081 - 26.1 MiB
9,395 - 9.1 MiB
17,542 - 12.0 MiB
38,055 - 26.3 MiB
3,480 - 1.7 MiB
18,835 - 7.7 MiB
22,961 - 9.9 MiB
6,927 - 7.5 MiB
2,476 - 1.2 MiB
20,072 - 12.9 MiB
16,526 - 9.3 MiB
47,770 - 51.2 MiB
4,225 - 2.4 MiB
13,300 - 15.2 MiB
20,821 - 12.3 MiB
17 - 22.3 KiB
194,512 - 115.1 MiB
1,404 - 2.3 MiB
4,419 - 4.9 MiB
165 - 88.7 KiB
10,786 - 7.8 MiB
520,019 - 261.4 MiB
94 - 63.2 KiB
711 - 782.0 KiB
924 - 1.5 MiB
0 - 1.0 KiB
1,456 - 737.1 KiB
16,257 - 9.6 MiB
522,457 - 264.5 MiB
1,165 - 667.9 KiB
7,989 - 7.5 MiB
3,442 - 1.4 MiB
18,714 - 13.4 MiB
18,830 - 13.1 MiB
9,141 - 3.9 MiB
16,317 - 8.8 MiB


There's 86 tables. Thanks

[Cheapdvds]

wow , quite a few items , i assume the site is a general shopping site? and not a specific area line PS3 games ?

also are the feeds all imported in one go or in batches

[Tobman]

wow , quite a few items , i assume the site is a general shopping site? and not a specific area line PS3 games ?

also are the feeds all imported in one go or in batches

hehe yeah, I've built up quite a few over time. All my sites draw from these central feeds. This is something I've set up this year, rather than having say 10 comet feeds all the same on the server.

I do two manual downloads of all the feeds each day. By that I mean I click the update feeds button on my local PC and it downloads the feeds to the server, unzips them and updates the Mysql. This process takes about 10 mins.

It's dead easy to keep the feeds in one database like this but I'm happy to spread them out if it will help performance. Thanks again for the advice.

[Andrew Starr]

what I've found can be quicker is having one table for all the merchants, but split the different fields up into seperate tables.

depending on how you are using the the data, you could for instance have one table for all the affiliate links, one for the images, one for the product descriptions, or one table for the product names + images + affiliate links etc..

[Cheapdvds]

im no expert , but what i would and have done in the past is have a separate database for each category , also if you have a database with a lot of feeds then spread the updates , update some monday , next lot tuesday

I would also automate the update and if there were still issues you could add a wait statment for every 1000 products or something

[a4uDarian]

Splitting up the data into separate databases will most likely not help your situation especially as you only have 86 tables. Big companies do this (it's called sharding) so that they can load balance between multiple copies of MySQL running on multiple servers. They'll eliminate older data which isn't retrieved as often and stick it on separate db servers to free up the ones accessing newer data.

Seeing as you likely don't have an entire server farm or even just 2 servers to split the load, splitting the database is not going to help your situation. In fact it will probably make it more of a hassle as you'll have to be connecting to the separate db's which will require unique credentials and splitting your app to work with multiple db's (hassle).

Your best bet is to look at the data you have and see how you can optimise it. If you're having trouble with slow select queries, enable the mysql slow query log and it will tell you which queries need optimising. If you're having trouble with slow insert queries, then you probably need to look at optimising mysql's settings itself.

The key for slow select queries are indexes, indexes, indexes. If your queries to retrieve data are doing joins then you'll want to be looking at optimising the joins by providing indexes on the fields in question.

How To Index For Joins With MySQL << This guide is invaluable for that. Put indexes on all your join fields and you'll notice select times drop from 10s+ to 0.1s in an instant. a4u wouldn't be here without them.

As for optimising mysql's settings itself, there's a handy little app called mysql-tuner that will tell you what settings to use based on your available RAM, uptime, slow query log etc. Then you just whack the settings in MySQL's my.conf and fingers crossed you've just saved yourself a server upgrade.

Basically, your host are ****ting you. They probably can't be bothered or don't know how to optimise MySQL properly, unfortunately that does mean you'll need to learn yourself Good luck!

[Cheapdvds]

Splitting up the data into separate databases will most likely not help your situation especially as you only have 86 tables.

Im assuming that you are on shared hosting (as i would have thought it wouldnt be a prob on a dedicated server) , and i mentioned was it a general shopping site because you could have a separate database for each category , so u would select the category you wanted to search in , so instead of searching 3 million products you only search 100,000 products which is a lot quicker .

[a4uDarian]

Trust me, it's always a problem - we struggle with optimising MySQL almost every day Whether you're on shared hosting or not, MySQL is a beast that needs to be tamed in a proper fashion.

I think what you are suggesting (cheapdvds), while good in theory, will lead to massive complications down the line as the data is not normalised in any way. What if you wanted to search for "blue shoes from curry's", would you look in every table in every database? How would you know which category database to look in to get the information you're after?

I can see why the original poster has one table per merchant though, it is an absolute mission getting all the feeds to look "alike" and fit in the same product table. He is losing the ability to search across all merchants in favour of searching individual merchants a lot faster and with a lot less hassle (due to mapping fields etc.). It's not a route I'd take in the long run but if say each page/site only promotes one merchant then it could work fairly well.

[Cheapdvds]

Trust me, it's always a problem - we struggle with optimising MySQL almost every day Whether you're on shared hosting or not, MySQL is a beast that needs to be tamed in a proper fashion.

I think what you are suggesting (cheapdvds), while good in theory, will lead to massive complications down the line as the data is not normalised in any way. What if you wanted to search for "blue shoes from curry's", would you look in every table in every database? How would you know which category database to look in to get the information you're after?

I can see why the original poster has one table per merchant though, it is an absolute mission getting all the feeds to look "alike" and fit in the same product table. He is losing the ability to search across all merchants in favour of searching individual merchants a lot faster and with a lot less hassle (due to mapping fields etc.). It's not a route I'd take in the long run but if say each page/site only promotes one merchant then it could work fairly well.

you would be a bit of a twat though if you were looking for blue shoes at currys wouldnt u?

you have a database for fashion , one for gadgets etc search box and drop down to select the category/database to search , i know loads of people that do this and i have also done it in the past and it works well.

[Cheapdvds]

also if it is shared hosting , then once you start putting feeds with 1 million plus in total and searching that database (and updating) most shared hosts would start raising a few questions which usually means they would like you to find an alternative solution/hosting