Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MySQL server config for table that has longblob field

Posted on 2009-03-30
8
Medium Priority
?
809 Views
Last Modified: 2012-05-06
I have a server that right now is pretty much just used for MySQL.  The DB has various tables but one of the main tables is one used for storing binary files in a longblob field.  The files can be anywhere from a few hunder KB in size to a few MB.  Another field is "program" and that is a always used in the query to get binary files just for the "program."  For the most common program the fields can be > 45 for a specific version and total 700+ records for that program.  A field "version" is also used to narrow down the results.  The "query" will just end up getting one record if any at all.

This is hosted on a Windows 2003 SBS server if that matters.

The problem is the "download" of the blob seems to be taking some time.  If the server is restarted, the number of records with the same program are lowered, or with "programs" that have fewer records and smaller binaries the download is at a normal speed.  What are configurations in the INI that would make the most difference in this type of DB and table?  What are status or server variables (in MySQL Admin) that will be especially useful in narrowing down problems that would cause this?  There are other uses for the server (e.g. web server) and DB (e.g. other tables with records that aren't blob fields) so let me know if those could be negatively impacted or factor into any of this.

I am familiar with MySQL but just not an expert in its set up, especially in a situation like this.  Let me know if you need any other info.  Thanks!

bol
0
Comment
Question by:b0lsc0tt
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 2

Accepted Solution

by:
rydersaint earned 2000 total points
ID: 24026259
hello b0lsc0tt

here is some ideas

longblob is a very slow data type
if you could get an native to ascii converter script or code on your files then it convert them on the fly to and from you DB from text to binary (like similar to what most mail server do).
Then you would be able to use the text datatype instead

If its only a max of a few MB then you could already switch it too mediumblob (this is faster too)

however if you are really stuck with longblob I sugest put in a really good raid5 or raid10
with lot of memory. ZFS is also a very good performance enhancer

Hope this helps
Ryder
0
 
LVL 54

Author Comment

by:b0lsc0tt
ID: 24053306
Ryder,
Thanks for the response.  I have made that change to that field (longblob to mediumblob).  Thanks for the input and recommendation on it.
Any other ideas or suggestions?  Let me know it some other details are needed.
bol
0
 
LVL 2

Expert Comment

by:rydersaint
ID: 24103475
ok so mediumblob then

I would recommend having two tables

one like

TABLE1
keyID (INT) --primary key | filelink (INT) | file (mediumblob) --your medium blob

and then another table in memory

CREATE TABLE TABLE2 (blah blah blah) ENGINE = MEMORY;

TABLE2
keyID (INT) --primary key | filelink (INT)| fileName (VARCHAR) -- name of your file | and so on

then select with a join like

SELECT * FROM TABLE1
LEFT JOIN TABLE2 ON TABLE1.keyID=TABLE2.keyID
WHERE fileName = 'the requested filename';

(* Note - you will need another table for table2 to copy from as all data leaves the memory table when restarting the server. Basically one for storage and one for selecting from)
0
What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

 
LVL 54

Author Comment

by:b0lsc0tt
ID: 24103600
Thanks for the response and additional suggestion.  I am interested in trying to use it but not real clear how the second table will help.  Is the "engine=memory" part the key to an improvement?  How or why does it help?
What is the purpose of the filelink field in each table?  Is that for some relationship or what?  The ID seems to be what you use for the JOIN.
Thanks for clarifying and the extra details.
bol
0
 
LVL 2

Assisted Solution

by:rydersaint
rydersaint earned 2000 total points
ID: 24103677
I am sorry b0lsc0tt not enough coffee today

SELECT * FROM TABLE1
LEFT JOIN TABLE2 ON TABLE1.filelink=TABLE2.filelink
WHERE fileName = 'the requested filename';

is correct is the correct query

yes the memory engine loads into system memory so all queries are automaticly indexed and very fast
I would recommend this way for any blob queries
 
0
 
LVL 54

Author Comment

by:b0lsc0tt
ID: 24133925
rydersaint,
Thanks!  I have held off responding or closing this because I was hoping to test it.  That won't be able to happen right away though so I won't leave you hanging.  I do appreciate your help and info.
bol
0
 
LVL 54

Author Closing Comment

by:b0lsc0tt
ID: 31564579
Thanks!
0
 
LVL 2

Expert Comment

by:rydersaint
ID: 24136212
thank you for the points b0lsc0tt
let me know if you need any other info
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question