Database loaded?

Brandon Ooi brandon at hotornot.com
Fri Sep 16 15:20:57 PDT 2005


yeah you're right. that's odd, there should have been only 1. perhaps 
it's more loaded than i thought.

I guess i'm worried that under high load the database will quickly 
become the bottleneck. I dont' expect our database machines to do much 
more than 2000 queries per second. is there a way to reduce the load? 
perhaps configure less jobs on some of the trackers?

Brandon

Brad Fitzpatrick wrote:

>I see multiple files being created there.  Sure you were only doing 1 per
>second?
>
>In any case, the traffic doesn't surprise me that much.
>
>- Brad
>
>
>On Wed, 14 Sep 2005, Brandon Ooi wrote:
>
>  
>
>>Hi Brad,
>>
>>Here's a typical second in time for the database.
>>
>>050914 14:38:38       5 Query       SELECT CONNECTION_ID()
>>                      5 Query       SELECT fid, lastupdate FROM
>>unreachable_fids
>>                      5 Query       SELECT CONNECTION_ID()
>>                      5 Query       SELECT d.dmid, c.classid,
>>c.mindevcount FROM domain d LEFT JOIN class c ON d.dmid=c.dmid
>>                      5 Query       SELECT fid FROM file WHERE dmid='1'
>>AND classid='1' AND devcount = '1' AND length IS NOT NULL LIMIT 1000
>>                      9 Query       SELECT CONNECTION_ID()
>>                      9 Query       SELECT fid, lastupdate FROM
>>unreachable_fids
>>                      5 Query       SELECT fid FROM file WHERE dmid='1'
>>AND classid='0' AND devcount = '1' AND length IS NOT NULL LIMIT 1000
>>                      5 Query       SELECT fid FROM file WHERE dmid='1'
>>AND classid='2' AND devcount = '1' AND length IS NOT NULL LIMIT 1000
>>                      9 Query       SELECT /*!40000 SQL_CACHE */ hostid,
>>status, hostname, hostip, http_port, http_get_port, remoteroot, altip,
>>altmask FROM host
>>                      9 Query       SELECT CONNECTION_ID()
>>                      9 Query       SELECT d.dmid, c.classid,
>>c.mindevcount FROM domain d LEFT JOIN class c ON d.dmid=c.dmid
>>                      9 Query       SELECT fid FROM file WHERE dmid='1'
>>AND classid='1' AND devcount = '1' AND length IS NOT NULL LIMIT 1000
>>                      9 Query       SELECT fid FROM file WHERE dmid='1'
>>AND classid='0' AND devcount = '1' AND length IS NOT NULL LIMIT 1000
>>                      9 Query       SELECT fid FROM file WHERE dmid='1'
>>AND classid='2' AND devcount = '1' AND length IS NOT NULL LIMIT 1000
>>                      7 Query       SELECT CONNECTION_ID()
>>                      7 Query       SELECT fid, lastupdate FROM
>>unreachable_fids
>>                      7 Query       SELECT CONNECTION_ID()
>>                      7 Query       SELECT d.dmid, c.classid,
>>c.mindevcount FROM domain d LEFT JOIN class c ON d.dmid=c.dmid
>>                      7 Query       SELECT fid FROM file WHERE dmid='1'
>>AND classid='1' AND devcount = '1' AND length IS NOT NULL LIMIT 1000
>>                      7 Query       SELECT fid FROM file WHERE dmid='1'
>>AND classid='0' AND devcount = '1' AND length IS NOT NULL LIMIT 1000
>>                      7 Query       SELECT fid FROM file WHERE dmid='1'
>>AND classid='2' AND devcount = '1' AND length IS NOT NULL LIMIT 1000
>>                      6 Query       SELECT CONNECTION_ID()
>>                      6 Query       SELECT classid FROM class WHERE
>>dmid='1' AND classname='thumb'
>>                      6 Query       INSERT INTO tempfile SET  fid=NULL,
>>dmid='1', dkey='bd619f4bd0f2eb33d20e6c00b89c0b2c0_thumb.jpg',
>>classid='2', createtime=UNIX_TIMESTA
>>MP(), devids='1'
>>                     11 Query       SELECT CONNECTION_ID()
>>                     11 Query       SELECT classid, dmid, dkey FROM
>>tempfile WHERE fid='168431'
>>                     11 Query       SELECT fid, dmid, dkey, length,
>>classid, devcount FROM file WHERE dmid='1' AND
>>dkey='bd619f4bd0f2eb33d20e6c00b89c0b2c0_thumb.jpg'
>>                     11 Query       INSERT IGNORE INTO file_on SET fid =
>>'168431', devid = '1'
>>                     11 Query       REPLACE INTO file SET
>>fid='168431', dmid='1',
>>dkey='bd619f4bd0f2eb33d20e6c00b89c0b2c0_thumb.jpg', length='1900',
>>classid='2', devc
>>ount=0
>>                     11 Query       DELETE FROM tempfile WHERE fid='168431'
>>                     11 Query       SELECT GET_LOCK('mgfs:fid:168431', 10)
>>                     11 Query       SELECT COUNT(*) FROM file_on WHERE
>>fid='168431'
>>                     11 Query       UPDATE file SET devcount='1' WHERE
>>fid='168431'
>>                     11 Query       SELECT RELEASE_LOCK('mgfs:fid:168431')
>>                     12 Query       SELECT CONNECTION_ID()
>>                     12 Query       SELECT classid FROM class WHERE
>>dmid='1' AND classname='full'
>>                     12 Query       INSERT INTO tempfile SET  fid=NULL,
>>dmid='1', dkey='bd619f4bd0f2eb33d20e6c00b89c0b2c0_full.jpg',
>>classid='1', createtime=UNIX_TIMESTAM
>>P(), devids='4'
>>                     13 Query       SELECT CONNECTION_ID()
>>                     13 Query       SELECT classid, dmid, dkey FROM
>>tempfile WHERE fid='168432'
>>                     13 Query       SELECT fid, dmid, dkey, length,
>>classid, devcount FROM file WHERE dmid='1' AND
>>dkey='bd619f4bd0f2eb33d20e6c00b89c0b2c0_full.jpg'
>>                     13 Query       INSERT IGNORE INTO file_on SET fid =
>>'168432', devid = '4'
>>                     13 Query       REPLACE INTO file SET
>>fid='168432', dmid='1',
>>dkey='bd619f4bd0f2eb33d20e6c00b89c0b2c0_full.jpg', length='34513',
>>classid='1', devc
>>ount=0
>>                     13 Query       DELETE FROM tempfile WHERE fid='168432'
>>                     13 Query       SELECT GET_LOCK('mgfs:fid:168432', 10)
>>                     13 Query       SELECT COUNT(*) FROM file_on WHERE
>>fid='168432'
>>                     13 Query       UPDATE file SET devcount='1' WHERE
>>fid='168432'
>>                     13 Query       SELECT RELEASE_LOCK('mgfs:fid:168432')
>>050914 14:38:39      14 Query       SELECT CONNECTION_ID()
>>                     14 Query       SELECT classid FROM class WHERE
>>dmid='1' AND classname='thumb'
>>                     14 Query       INSERT INTO tempfile SET  fid=NULL,
>>dmid='1', dkey='197721e75db8a3304439bf8fccd0c8510_thumb.jpg',
>>classid='2', createtime=UNIX_TIMESTA
>>MP(), devids='1'
>>                     15 Query       SELECT CONNECTION_ID()
>>                     15 Query       SELECT classid, dmid, dkey FROM
>>tempfile WHERE fid='168433'
>>                     15 Query       SELECT fid, dmid, dkey, length,
>>classid, devcount FROM file WHERE dmid='1' AND
>>dkey='197721e75db8a3304439bf8fccd0c8510_thumb.jpg'
>>                     15 Query       INSERT IGNORE INTO file_on SET fid =
>>'168433', devid = '1'
>>                     15 Query       REPLACE INTO file SET
>>fid='168433', dmid='1',
>>dkey='197721e75db8a3304439bf8fccd0c8510_thumb.jpg', length='1898',
>>classid='2', devc
>>ount=0
>>                     15 Query       DELETE FROM tempfile WHERE fid='168433'
>>                     15 Query       SELECT GET_LOCK('mgfs:fid:168433', 10)
>>                     15 Query       SELECT COUNT(*) FROM file_on WHERE
>>fid='168433'
>>                     15 Query       UPDATE file SET devcount='1' WHERE
>>fid='168433'
>>                     15 Query       SELECT RELEASE_LOCK('mgfs:fid:168433')
>>                     16 Query       SELECT CONNECTION_ID()
>>                     16 Query       SELECT classid FROM class WHERE
>>dmid='1' AND classname='full'
>>                     16 Query       INSERT INTO tempfile SET  fid=NULL,
>>dmid='1', dkey='197721e75db8a3304439bf8fccd0c8510_full.jpg',
>>classid='1', createtime=UNIX_TIMESTAM
>>P(), devids='4'
>>                     17 Query       SELECT CONNECTION_ID()
>>                     17 Query       SELECT classid, dmid, dkey FROM
>>tempfile WHERE fid='168434'
>>                     17 Query       SELECT fid, dmid, dkey, length,
>>classid, devcount FROM file WHERE dmid='1' AND
>>dkey='197721e75db8a3304439bf8fccd0c8510_full.jpg'
>>                     17 Query       INSERT IGNORE INTO file_on SET fid =
>>'168434', devid = '4'
>>                     17 Query       REPLACE INTO file SET
>>fid='168434', dmid='1',
>>dkey='197721e75db8a3304439bf8fccd0c8510_full.jpg', length='25229',
>>classid='1', devc
>>ount=0
>>                     17 Query       DELETE FROM tempfile WHERE fid='168434'
>>                     17 Query       SELECT GET_LOCK('mgfs:fid:168434', 10)
>>                     17 Query       SELECT COUNT(*) FROM file_on WHERE
>>fid='168434'
>>                     17 Query       UPDATE file SET devcount='1' WHERE
>>fid='168434'
>>                     17 Query       SELECT RELEASE_LOCK('mgfs:fid:168434')
>>                     10 Query       SELECT CONNECTION_ID()
>>                     10 Query       SELECT fid, lastupdate FROM
>>unreachable_fids
>>                     10 Query       SELECT /*!40000 SQL_CACHE */ hostid,
>>status, hostname, hostip, http_port, http_get_port, remoteroot, altip,
>>altmask FROM host
>>                     10 Query       SELECT CONNECTION_ID()
>>                     10 Query       SELECT d.dmid, c.classid,
>>c.mindevcount FROM domain d LEFT JOIN class c ON d.dmid=c.dmid
>>                     10 Query       SELECT fid FROM file WHERE dmid='1'
>>AND classid='1' AND devcount = '1' AND length IS NOT NULL LIMIT 1000
>>                     10 Query       SELECT
>>GET_LOCK('mgfs:fid:168434:replicate', 1)
>>                     10 Query       SELECT devid FROM file_on WHERE
>>fid='168434'
>>                     10 Query       INSERT IGNORE INTO file_on SET
>>fid='168434', devid='1'
>>                     10 Query       SELECT COUNT(*) FROM file_on WHERE
>>fid='168434'
>>                     10 Query       UPDATE file SET devcount='2' WHERE
>>fid='168434'
>>                     10 Query       SELECT
>>RELEASE_LOCK('mgfs:fid:168434:replicate')
>>                      2 Query       SELECT CONNECTION_ID()
>>                      2 Query       SELECT fid, lastupdate FROM
>>unreachable_fids
>>                      8 Query       SELECT CONNECTION_ID()
>>                      8 Query       SELECT fid, lastupdate FROM
>>unreachable_fids
>>                      2 Query       SELECT /*!40000 SQL_CACHE */ hostid,
>>status, hostname, hostip, http_port, http_get_port, remoteroot, altip,
>>altmask FROM host
>>                      2 Query       SELECT CONNECTION_ID()
>>                      2 Query       SELECT d.dmid, c.classid,
>>c.mindevcount FROM domain d LEFT JOIN class c ON d.dmid=c.dmid
>>                      2 Query       SELECT fid FROM file WHERE dmid='1'
>>AND classid='1' AND devcount = '1' AND length IS NOT NULL LIMIT 1000
>>                      8 Query       SELECT CONNECTION_ID()
>>                      8 Query       SELECT d.dmid, c.classid,
>>c.mindevcount FROM domain d LEFT JOIN class c ON d.dmid=c.dmid
>>                      2 Query       SELECT
>>GET_LOCK('mgfs:fid:168432:replicate', 1)
>>                      2 Query       SELECT devid FROM file_on WHERE
>>fid='168432'
>>                      8 Query       SELECT fid FROM file WHERE dmid='1'
>>AND classid='1' AND devcount = '1' AND length IS NOT NULL LIMIT 1000
>>                     10 Query       SELECT
>>GET_LOCK('mgfs:fid:168432:replicate', 1)
>>                      8 Query       SELECT
>>GET_LOCK('mgfs:fid:168432:replicate', 1)
>>                      2 Query       INSERT IGNORE INTO file_on SET
>>fid='168432', devid='1'
>>                      2 Query       SELECT COUNT(*) FROM file_on WHERE
>>fid='168432'
>>                      2 Query       UPDATE file SET devcount='2' WHERE
>>fid='168432'
>>                      2 Query       SELECT
>>RELEASE_LOCK('mgfs:fid:168432:replicate')
>>                     10 Query       SELECT devid FROM file_on WHERE
>>fid='168432'
>>                     10 Query       SELECT
>>RELEASE_LOCK('mgfs:fid:168432:replicate')
>>                      8 Query       SELECT /*!40000 SQL_CACHE */ devid,
>>hostid, mb_total, mb_used, mb_asof, status FROM device WHERE status IN
>>('alive', 'down' )
>>                     10 Query       SELECT fid FROM file WHERE dmid='1'
>>AND classid='0' AND devcount = '1' AND length IS NOT NULL LIMIT 1000
>>                      8 Query       SELECT devid FROM file_on WHERE
>>fid='168432'
>>                     10 Query       SELECT fid FROM file WHERE dmid='1'
>>AND classid='2' AND devcount = '1' AND length IS NOT NULL LIMIT 1000
>>                      8 Query       SELECT
>>RELEASE_LOCK('mgfs:fid:168432:replicate')
>>                      8 Query       SELECT fid FROM file WHERE dmid='1'
>>AND classid='0' AND devcount = '1' AND length IS NOT NULL LIMIT 1000
>>                      8 Query       SELECT fid FROM file WHERE dmid='1'
>>AND classid='2' AND devcount = '1' AND length IS NOT NULL LIMIT 1000
>>                     10 Query       SELECT
>>GET_LOCK('mgfs:fid:168433:replicate', 1)
>>                     10 Query       SELECT devid FROM file_on WHERE
>>fid='168433'
>>                      8 Query       SELECT
>>GET_LOCK('mgfs:fid:168431:replicate', 1)
>>                      8 Query       SELECT devid FROM file_on WHERE
>>fid='168431'
>>                     10 Query       INSERT IGNORE INTO file_on SET
>>fid='168433', devid='2'
>>                     10 Query       SELECT COUNT(*) FROM file_on WHERE
>>fid='168433'
>>                     10 Query       UPDATE file SET devcount='2' WHERE
>>fid='168433'
>>                     10 Query       SELECT
>>RELEASE_LOCK('mgfs:fid:168433:replicate')
>>                      8 Query       INSERT IGNORE INTO file_on SET
>>fid='168431', devid='4'
>>                      8 Query       SELECT COUNT(*) FROM file_on WHERE
>>fid='168431'
>>                      8 Query       UPDATE file SET devcount='2' WHERE
>>fid='168431'
>>                      2 Query       SELECT fid FROM file WHERE dmid='1'
>>AND classid='0' AND devcount = '1' AND length IS NOT NULL LIMIT 1000
>>                      8 Query       SELECT
>>RELEASE_LOCK('mgfs:fid:168431:replicate')
>>                      2 Query       SELECT fid FROM file WHERE dmid='1'
>>AND classid='2' AND devcount = '1' AND length IS NOT NULL LIMIT 1000
>>                     20 Connect     mogfs at localhost on mogfs
>>                     20 Query       set autocommit=1
>>                     20 Query       SELECT dmid, namespace FROM domain
>>                     20 Query       SELECT classid FROM class WHERE
>>dmid='1' AND classname='thumb'
>>                     20 Query       SELECT /*!40000 SQL_CACHE */ devid,
>>hostid, mb_total, mb_used, mb_asof, status FROM device WHERE status IN
>>('alive', 'down' )
>>                     20 Query       SELECT /*!40000 SQL_CACHE */ hostid,
>>status, hostname, hostip, http_port, http_get_port, remoteroot, altip,
>>altmask FROM host
>>                     20 Query       INSERT INTO tempfile SET  fid=NULL,
>>dmid='1', dkey='92ee7e4ac1572a5d3d164eb1fbe11fad0_thumb.jpg',
>>classid='2', createtime=UNIX_TIMESTA
>>MP(), devids='2'
>>                     21 Connect     mogfs at localhost on mogfs
>>                     21 Query       set autocommit=1
>>                     21 Query       SELECT dmid, namespace FROM domain
>>                     21 Query       SELECT /*!40000 SQL_CACHE */ devid,
>>hostid, mb_total, mb_used, mb_asof, status FROM device WHERE status IN
>>('alive', 'down' )
>>                     21 Query       SELECT /*!40000 SQL_CACHE */ hostid,
>>status, hostname, hostip, http_port, http_get_port, remoteroot, altip,
>>altmask FROM host
>>                     21 Query       SELECT classid, dmid, dkey FROM
>>tempfile WHERE fid='168435'
>>                     21 Query       SELECT fid, dmid, dkey, length,
>>classid, devcount FROM file WHERE dmid='1' AND
>>dkey='92ee7e4ac1572a5d3d164eb1fbe11fad0_thumb.jpg'
>>                     21 Query       INSERT IGNORE INTO file_on SET fid =
>>'168435', devid = '2'
>>                     21 Query       REPLACE INTO file SET
>>fid='168435', dmid='1',
>>dkey='92ee7e4ac1572a5d3d164eb1fbe11fad0_thumb.jpg', length='1785',
>>classid='2', devc
>>ount=0
>>                     21 Query       DELETE FROM tempfile WHERE fid='168435'
>>                     21 Query       SELECT GET_LOCK('mgfs:fid:168435', 10)
>>                     21 Query       SELECT COUNT(*) FROM file_on WHERE
>>fid='168435'
>>                     21 Query       UPDATE file SET devcount='1' WHERE
>>fid='168435'
>>                     21 Query       SELECT RELEASE_LOCK('mgfs:fid:168435')
>>                      8 Query       SELECT
>>GET_LOCK('mgfs:fid:168433:replicate', 1)
>>                      8 Query       SELECT devid FROM file_on WHERE
>>fid='168433'
>>                      8 Query       SELECT
>>RELEASE_LOCK('mgfs:fid:168433:replicate')
>>                      3 Query       SELECT CONNECTION_ID()
>>                      6 Query       SELECT CONNECTION_ID()
>>                      6 Query       SELECT classid FROM class WHERE
>>dmid='1' AND classname='full'
>>                      6 Query       INSERT INTO tempfile SET  fid=NULL,
>>dmid='1', dkey='92ee7e4ac1572a5d3d164eb1fbe11fad0_full.jpg',
>>classid='1', createtime=UNIX_TIMESTAM
>>P(), devids='2'
>>                     11 Query       SELECT CONNECTION_ID()
>>                     11 Query       SELECT classid, dmid, dkey FROM
>>tempfile WHERE fid='168436'
>>                     11 Query       SELECT fid, dmid, dkey, length,
>>classid, devcount FROM file WHERE dmid='1' AND
>>dkey='92ee7e4ac1572a5d3d164eb1fbe11fad0_full.jpg'
>>                     11 Query       INSERT IGNORE INTO file_on SET fid =
>>'168436', devid = '2'
>>                     11 Query       REPLACE INTO file SET
>>fid='168436', dmid='1',
>>dkey='92ee7e4ac1572a5d3d164eb1fbe11fad0_full.jpg', length='24336',
>>classid='1', devc
>>ount=0
>>                     11 Query       DELETE FROM tempfile WHERE fid='168436'
>>                     11 Query       SELECT GET_LOCK('mgfs:fid:168436', 10)
>>... (there's about 50 more lines of stuff like this for this second in
>>time).
>>
>>
>>It looks like a lot of bookkeeping so I'm including my mogilefsd.conf
>>file here.
>>
>>db_dsn = DBI:mysql:mogfs:host=127.0.0.1
>>db_user = mogfs
>>db_pass = ********
>>conf_port = 7001
>>listener_jobs = 10
>>delete_jobs = 1
>>replicate_jobs = 5
>>#mog_root = /mnt/mogilefs
>>reaper_jobs = 1
>>debug = 3
>>
>>
>>
>>
>>Brad Fitzpatrick wrote:
>>
>>    
>>
>>>What are the queries?
>>>
>>>
>>>On Wed, 14 Sep 2005, Brandon Ooi wrote:
>>>
>>>
>>>
>>>      
>>>
>>>>Hi,
>>>>
>>>>We have mogile running to a small percentage of our users. I'm running a
>>>>single database, 2 trackers and 3 storage nodes. Even when mogile is
>>>>saving only 1 file/second and serving nothing, database seems to be
>>>>doing a lot of work. Here is a mytop view.
>>>>
>>>>Queries Per Second [hit q to exit this mode]
>>>>143
>>>>843
>>>>812
>>>>172
>>>>72
>>>>153
>>>>120
>>>>78
>>>>191
>>>>95
>>>>105
>>>>181
>>>>727
>>>>228
>>>>198
>>>>97
>>>>210
>>>>131
>>>>
>>>>Is this normal behavior? The machine itself has 1GB of main memory.
>>>>Mogile is storing approximately 200,000 files. Load on the db machine
>>>>seem to hover around 0.34.
>>>>
>>>>Any input is appreciated,
>>>>
>>>>Brandon
>>>>
>>>>
>>>>
>>>>
>>>>        
>>>>
>>>
>>>      
>>>
>>    
>>
>
>  
>


More information about the mogilefs mailing list