|
Re: performance with mysql / file-caching / hash reference on demand
by tobyink (Canon) on May 02, 2021 at 11:55 UTC
|
In addition to what afoken said, try adding indices to your table.
CREATE INDEX CT_Media_Relations_ix_SupplierID
ON CT_Media_Relations (SupplierID);
CREATE INDEX CT_Media_Relations_ix_SupplierPID
ON CT_Media_Relations (SupplierPID);
-- etc
Indices will somewhat slow down write queries (INSERT, etc) but can have a huge improvement on read queries (SELECT, etc).
Any primary keys will already be indexed, but any other columns containing numbers, dates, and short strings which you frequently filter, sort, or group by should probably be indexed.
| [reply]
[d/l] |
|
|
This would be a very good point but all columns that are in any query are either primary or indxed.
| [reply] |
|
|
| [reply] |
Re: performance with mysql / file-caching / hash reference on demand
by choroba (Cardinal) on May 02, 2021 at 08:26 UTC
|
At work, we do hundreds of database calls every second. Some of the resources have performance problems, but they usually involve scanning tables that have millions of rows. Your description gives me a vague picture, but I can't guess what the problem might be. Are you using CGI with a fresh Perl start on every request, or do you preload the libraries (mod_perl, plack, mojo)? Are you using an ORM, or do you write your SQL queries by hand?
BTW, sometimes, the cheapest solution is to upgrade the hardware.
map{substr$_->[0],$_->[1]||0,1}[\*||{},3],[[]],[ref qr-1,-,-1],[{}],[sub{}^*ARGV,3]
| [reply]
[d/l] |
|
|
Thank you very much for your reply.
The webshop is built on the base of a Links management system which would work with mod_perl.
Some of my code for customer related operations might cause hiccup with mod_perl, so I fear using mod_perl a little.
For displaying detailed pages I work with speedycgi since a couple of years which did improve performance significant.
As far as I have seen one problem is what your question implies.
I loose performance because there are connections built to mysql more often than probably neccessary.
This seems to be more my problem with Perl than a general problem though PHP which I try to avoid seems to be quicker there out of the box.
My webserver is a couple of years old but still is performant and works with SSD:
Architecture: x86_64
CPU op-mode(s): 32-bit, 64-bit
Byte Order: Little Endian
CPU(s): 24
On-line CPU(s) list: 0-23
Vendor ID: GenuineIntel
CPU family: 6
Model: 45
Model name: Intel(R) Xeon(R) CPU E5-2640 0 @ 2.50GHz
Stepping: 7
CPU MHz: 2499.711
BogoMIPS: 5000.02
Virtualization: VT-x
total used free shared buffers cached
Mem: 96455 65478 30976 0 4752 53994
-/+ buffers/cache: 6730 89724
Swap: 949 0 949
At the moment a detailed page needs about 1.1 seconds to display but I want to add significant more information and I would like to get faster - at least not slower.
One code example I am using to get media (images, datasheets) from the DB:
sub get_udx_media_relations {
my $SupplierID = shift;
my $SupplierPID = shift;
my $return;
my $query = qq|SELECT
ProviderID,
SourceID,
Folder,
Filename,
Extension,
Purpose,
Rank,
Priority,
Width,
Height
FROM CT_Media_Relations
WHERE SupplierID = '$SupplierID'
AND SupplierPID = '$SupplierPID'
AND isDeleted != 'Yes'
GROUP BY Filename, Extension
ORDER BY Priority DESC, Rank ASC|;
my $sth = $DB->table('CT_Media_Relations')->prepare ($query)
|| die $GX::SQL::error;
$sth->execute ();
my ($prev_priority);
while ($_ = $sth->fetchrow_hashref) {
if ($prev_priority->{$_->{Purpose}} > $_->{Priority}) {
next unless $_->{Purpose} eq 'PDB';
}
#get rid of mds when manufacturer is present (4 vs 5)
if ($_->{Purpose} eq 'AAB'
or $_->{Purpose} eq 'EBD'
or $_->{Purpose} eq 'PBS'
or $_->{Purpose} eq 'PRI') {
if ($prev_priority->{IMG} > $_->{Priority}) {
next;
}
else {
$prev_priority->{IMG} = $_->{Priority};
}
}
$prev_priority->{$_->{Purpose}} = $_->{Priority};
if ($_->{Purpose} eq 'PRI' && !$return->{Primary}->{path}) {
$return->{Primary} = $_;
$return->{Primary}->{path} = '/dx/' . $_->{ProviderID}
. "/" . $_->{SourceID};
$return->{Primary}->{filename} = $_->{Filename}
. '.' . $_->{Extension};
$return->{Primary}->{filename} = $_->{Folder} . '/'
. $return->{Primary}->{filename} if $_->{Folder};
}
my $media = $_;
$media->{path} = '/dx/' . $_->{ProviderID}
. "/" . $_->{SourceID};
$media->{filename} = $_->{Filename}
. '.' . $_->{Extension};
$media->{filename} = $_->{Folder}
. '/' . $media->{filename} if $_->{Folder};
push(@{$return->{$_->{Purpose}}}, $media);
}
push(@{$return->{IMG}}, @{$return->{EBD}})
if defined(@{$return->{EBD}});
if ( not defined(@{$return->{IMG}})
and defined (@{$return->{PBS}}) ) {
push(@{$return->{IMG}}, @{$return->{PBS}});
}
push(@{$return->{IMG}}, @{$return->{AAB}})
if defined(@{$return->{AAB}});
if (defined @{$return->{IMG}} &&
(!$return->{Primary} ||
$return->{Primary}->{Priority} <
$return->{IMG}[0]->{Priority})) {
$return->{Primary} = shift @{$return->{IMG}};
}
push(@{$return->{IMG}}, @{$return->{PAB}})
if defined(@{$return->{PAB}});
push(@{$return->{IMG}}, @{$return->{DET}})
if defined(@{$return->{DET}});
return $return;
}
My thought was that it could make sense to put these results in a static whatever state (database, file) on a daily basis to avoid doing the same routine over and over agein.
Cheers
derion | [reply]
[d/l]
[select] |
|
|
sub get_udx_media_relations {
my $SupplierID = shift;
my $SupplierPID = shift;
my $return;
my $query = qq|SELECT
ProviderID,
SourceID,
Folder,
Filename,
Extension,
Purpose,
Rank,
Priority,
Width,
Height
FROM CT_Media_Relations
WHERE SupplierID = '$SupplierID'
AND SupplierPID = '$SupplierPID'
AND isDeleted != 'Yes'
GROUP BY Filename, Extension
ORDER BY Priority DESC, Rank ASC|;
No placeholders here. That prevents DBI, DBD and the database itself from caching the query, so it has to be parsed again from zero each and every time. Even if you only change the IDs. Using placeholders and prepare_cached() instead of prepare() could improve speed, at least when used in a persistent environment (i.e. mod_perl, FastCGI, basically everything but plain old CGI). And, even worse, you might become a victim of SQL injection. See https://bobby-tables.com/.
while ($_ = $sth->fetchrow_hashref) {
if ($prev_priority->{$_->{Purpose}} > $_->{Priority}) {
next unless $_->{Purpose} eq 'PDB';
}
#get rid of mds when manufacturer is present (4 vs 5)
if ($_->{Purpose} eq 'AAB'
or $_->{Purpose} eq 'EBD'
or $_->{Purpose} eq 'PBS'
or $_->{Purpose} eq 'PRI') {
if ($prev_priority->{IMG} > $_->{Priority}) {
next;
}
else {
$prev_priority->{IMG} = $_->{Priority};
}
}
$prev_priority->{$_->{Purpose}} = $_->{Priority};
if ($_->{Purpose} eq 'PRI' && !$return->{Primary}->{path}) {
$return->{Primary} = $_;
$return->{Primary}->{path} = '/dx/' . $_->{ProviderID}
. "/" . $_->{SourceID};
$return->{Primary}->{filename} = $_->{Filename}
. '.' . $_->{Extension};
$return->{Primary}->{filename} = $_->{Folder} . '/'
. $return->{Primary}->{filename} if $_->{Folder};
}
my $media = $_;
$media->{path} = '/dx/' . $_->{ProviderID}
. "/" . $_->{SourceID};
$media->{filename} = $_->{Filename}
. '.' . $_->{Extension};
$media->{filename} = $_->{Folder}
. '/' . $media->{filename} if $_->{Folder};
push(@{$return->{$_->{Purpose}}}, $media);
}
I did not even try to fully understand what happens here. But you are fetching data from the database just to discard it, based on values of the "Purpose" and "Priority" columns. That won't speed up things. Try not to fetch data that you don't need. Make the database filter out everything you don't need, i.e. put your criteria into the WHERE condition. That way, the database won't have to find data you don't need, fetch it, serialize it, and push it through the connection to your application where the DBD has to deserialize it, and all of that just to discard that unwanted data.
If the remaining code looks similar, that's where you can really improve performance. Use placeholders everywhere to allow at least the database to cache queries, and filter in the database, not in perl.
Alexander
--
Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)
| [reply]
[d/l]
[select] |
|
|
|
|
|
Re: performance with mysql / file-caching / hash reference on demand
by karlgoethebier (Abbot) on May 03, 2021 at 06:43 UTC
|
As you have implemented now all the cool ideas already given you might consider Stored Procedures. I‘m pretty sure that this will give another performance boost. It might be also worth to take a look at all the funny params in your my.cnf. Regards, Karl
«The Crux of the Biscuit is the Apostrophe»
| [reply]
[d/l] |
|
|
This is new to me and after some reading looks like a simple and good way to make things faster.
Thanks for that!
| [reply] |
|
|
| [reply] |
Re: performance with mysql / file-caching / hash reference on demand
by derion (Sexton) on May 02, 2021 at 18:50 UTC
|
Thanks to all for the comments on my post. I got some new ideas and tasks.
One part of my initial question is still not clear for me.
If caching information to get better performance is a good way why is doing this with Storable and DB a bad or at least not so good way?
I could do my routines on a daily basis, freeze the hashref, store it in a table and retrieve the hashrefs of one item by ID with only one request.
---------------------------
| my_cache_table |
---------------------------
| ID (primary) |
---------------------------
| hashref with prices |
---------------------------
| mod_date of prices |
---------------------------
| hashref with images |
---------------------------
| mod_date of images |
---------------------------
| hashref with other stuff|
---------------------------
| mod_date of other stuff |
---------------------------
I don´t want to appear stubborn but I like to comprehend things. Caching one or another way seems to be common practice the above mentioned way not at all.
Thanks for some more enlightment
| [reply]
[d/l] |
|
|
| [reply] |
Re: performance with mysql / file-caching / hash reference on demand
by Anonymous Monk on May 02, 2021 at 14:30 UTC
|
"Product category pages," which are frequently called-for but rarely change, can also be managed using things like the classic Cache::Memcached. Once you've assembled the necessary (JSON?) information once, you put it the results the cache. Then, before assembling and storing it again, you query the cache first to see if it is already there. You can, if you like, even store complete HTML fragments, ready for immediate display. The "memcached" daemon automatically handles recycling of infrequently-referenced data. Just about every web-site that I am aware of uses this facility routinely for this purpose, and it can have a dramatic positive impact on performance. | [reply] |
|
|
That sounds interesting. Seems a similar approach but storing in memory.
My Database having about 4GB should lead to a manageable size of Data for this method.
Besides the difference that the information is stored in an even faster way than in a JSON or hashref in a DB the approach seems kind of similar from my point of view. I still do not get the real disadvantage of storing a cached element in a table.
Nevertheless Cache::Memcached looks very nice, thank you for that.
| [reply] |
|
|
| [reply] |