Версия для печати темы
Нажмите сюда для просмотра этой темы в оригинальном формате
Форум программистов > Perl: Общие вопросы > Большое кол-во запросов к mysql


Автор: n4n0bit 20.6.2008, 13:24
Доброе время суток.

Пишу небольшую примочку к squid, что та вроде биллинга. Написал я редиректор для squid-a, работает он справно, но как принес из дома на работу, там примерно 50-60 компов, то mysql начал жрать 60-70% CPU, как бы можно было оптимизировать? Или никак? а пользоваться уже существующими ( SquidGuard - работает с Berkeley DB, и тд, их валом ).

Или мне поменять БД? Дела в том что запросов через редиректор проходит много, ну скажем человек запросил какой нить ресурс где куча картинок <img src> и эти ссылки все будут проходить через редиректор, а людей то много и mysqld много жрет проца. Нужели в этой ситуации будет выигрывать Berkeley DB ?

Ниже приведена часть редиректора

knife.pl :

Код

#!/usr/bin/perl

BEGIN
{
    push @INC, '/SquidTools';
};

our $VERSION = 0.1; # 11.05.2008

use strict;
use warnings;

use lib '../';

use DBI;

use SquidTools::Setting;
use SquidTools::Utils::Uri;
use SquidTools::Utils::Decode; # <<< TEST >>>
use SquidTools::Redirector::Verifier;

main:
{
    $| = 1;

    # получить конфигурационные данные
    my $cfg = SquidTools::Setting->new('/SquidTools/etc/base.cfg');
    # форматы файлов
    my %Formats;
    # ресурсы пользователей
    my %Resource;
    # категории ресурсов
    my %Categories = $cfg->categories(1);
    
    # заполнить хеши надлежащими данными
    for my $usr ( @{$cfg->{'alluser'}} )
    {
        for( $cfg->resource($usr) )
        {
            $Resource{$usr}{$_} = 0;
        }
        
        for( $cfg->formats($usr) )
        {
            $Formats{$usr}{$_} = 0;
        }
    }
    
    # подключиться к mySQL
    my $dbh = DBI->connect( "DBI:mysql:database=" . $cfg->DB_NAME . ";host=" . $cfg->DB_HOST, $cfg->DB_USER, $cfg->DB_PASS ) or die DBI->errstr; # <<< DEBUG >>>

    ### четние из потока SQUID
    while(<>)
    {
        my $obj = SquidTools::Redirector::Verifier->new( $_, \$dbh, $cfg->ADDR_REDIRECT );
        print $obj->check( \%Formats, \%Categories, \%Resource ) . "\n";
                
        # TODO: фильтр для поисковых машин: yandex, google 
        # TODO: учет статисти в реалтайме
    }
    
    # отключиться от БД
    $dbh->disconnect;
}



SquidTools::Redirector::Verifier :

Код

package SquidTools::Redirector::Verifier;

our $VERSION = 0.1; # 11.05.2008

use strict;
use warnings;

sub new
{
    my ( $class, $string, $dbh, $addr_r ) = @_;
    my $self = { addr_r => $addr_r };
    
    # разбить squid-запрос
    @{$self}{'url','ip','user'} = split( /\s+/, $string );
    
    # проверить баланс пользователя
    my $sth = $$dbh->prepare( "SELECT s.login, quota-sum(size)/(1024*1024) as ballance  FROM squidlog s, userpref u WHERE s.login=u.login GROUP BY s.login" );
    $sth->execute;
    
    $self->{'UserOFF'} = 0;
    
    while( my $r = $sth->fetchrow_hashref )
    {
        if( $r->{'login'} eq $self->{'user'} && $r->{'ballance'} =~ /^-/ )
        {
            $self->{'UserOFF'} = 1;
        }
    }
    
    $sth->finish;
    
    # разбить УРЛ
    @{$self}{'proto','addr','path','query'} = SquidTools::Utils::Uri::split( $self->{'url'} );
    # убрать префикс www
    if( $self->{'addr'} =~ /^www\./ ) { $self->{'addr'} =~ s/^www\.//; }

    # проверить наличия УРЛ-а в БД и получить его идантификатор принадлежности к группе ресурсов
    $self->{'url_id'} = $$dbh->selectrow_array( "SELECT groupid FROM uribase WHERE uri=" . $$dbh->quote( "http://".$self->{'addr'} ) );

    return bless $self, $class;
}

sub check
{
    my ( $self, $Formats, $Categories, $Resource ) = @_;
    my $rsig;

    # если баланс пользователя отрицательный, то редирект
    if( $self->{'UserOFF'} == 1 )
    {
        return "302:$self->{'addr_r'}";
    }

    # определить ограничения для данного идантификатора УРЛ-а
    if( defined $self->{'url_id'} )
    {
        # подтвердить запретность данного УРЛ-а для запрашиваемого пользователя
        for( keys %{$$Resource{ $self->{'user'} }} )
        {
            if( $$Categories{ $self->{'url_id'} } eq $_ && exists $$Categories{ $self->{'url_id'} } )
            {
                $rsig = $_;
                last;
            }
        }
        # определить действия исходя из подтвержденности запрета на данный УРЛ
        if( $rsig )
        {
            return "302:" . $self->{'addr_r'} . "?u=" . $self->{'user'} . "&r=$rsig&url=" . $self->{'url'};
        }
        else
        {
            return $self->{'url'};
        }
    }
    # сделать проверку на закачивание файлов
    else
    {
        # получить размер файла
        #my $rf_size = SquidTools::Utils::WebFile::getSizeOnlyFile( $self->{'url'} );
    
        if( $self->{'path'} =~ /\.(\w{2,4})$/ && exists $$Formats{ $self->{'user'}}{$1} )
        {
            return "302:" . $self->{'addr_r'} . "?u=" . $self->{'user'} . "&f=$1&url=" . $self->{'url'};
        }
        else
        {
            return $self->{'url'};
        }
    }
    
}

Автор: ginnie 20.6.2008, 14:28
Уважаемый n4n0bit, посмотрите время выполнения запросов в MySQL (http://search.cpan.org/~timb/DBI-1.50/lib/DBI/Profile.pm). По результатам можно будет сделать выводы.

Автор: n4n0bit 20.6.2008, 15:28
Если я делаю /SquidTools/bin/knife.pl а потом передаю аргументы http://xakep.ru 127.0.0.1 tester GET то сбрасывается дамп в файл такого вида:

Код

DBI::ProfileDumper 2.009894
Path = [ !Statement ]
Program = /SquidTools/bin/knife.pl

+ 1 
= 1 1.78813934326172e-05 1.78813934326172e-05 1.78813934326172e-05 1.78813934326172e-05 1213964287.60126 1213964287.60126
+ 1 SELECT groupid FROM uribase WHERE uri='http://xakep.ru'
= 2 0.000581979751586914 0.000570058822631836 1.19209289550781e-05 0.000570058822631836 1213964287.60128 1213964287.6019
+ 1 SELECT s.login, quota-sum(size)/(1024*1024) as ballance  FROM squidlog s, userpref u WHERE s.login=u.login GROUP BY s.login
= 22 0.0808217525482178 0.000102996826171875 3.814697265625e-06 0.0802509784698486 1213964287.52016 1213964287.60195


Но если этот файл юзает squid, то в файл ничего не сбрасывается.

Автор: ginnie 20.6.2008, 15:45
Можно при помощи 
Код

DBI->trace(0, $filename);

явно указать файл, в который будет записываться статистика. Данные в файл запишутся после перезапуска squid. Смотреть лучше на рабочем сервере (чтобы были реальные цифры).

Автор: n4n0bit 20.6.2008, 15:59
Файл Virifier.pm изменил след образом:

Код

package SquidTools::Redirector::Verifier;

our $VERSION = 0.1; # 20.06.2008

use strict;
use warnings;

# конструктор объекта
sub new
{
    my $class = shift;
    my $self = { };
    
    return bless $self, $class;
}

# проверка УРЛ
sub checkThisUrl
{
    my ( $self, $stream, $dbh, $addr_redirect, $Formats, $Categories, $Resource ) = @_;
    my $rsig;
    
    ## разбить squid на составляющие: URL, IP и LOGIN
    @{$self}{'s.url','s.ip','s.login'} = split( /\s+/, $$stream );
    
    # получить сумму в байтах с посищаемых страниц
    $self->{'s.login.traffic'} =  $$dbh->selectall_arrayref( "SELECT sum(size) AS ballance FROM squidlog s WHERE login= " . $$dbh->quote( $self->{'s.login'} ) . " GROUP BY login;" )->[0][0];

    # если сумма трафика не определена, значит токого login а БД нет и пользователь будет перенаправлен
    return "302:$$addr_redirect?l=$$self{'s.login'}&s=error" unless defined $self->{'s.login.traffic'};

    # получить квоту для данного пользователя
    $self->{'s.login.quota'} = $$dbh->selectall_arrayref( "SELECT quota FROM userpref WHERE login=" . $$dbh->quote( $self->{'s.login'} ) )->[0][0];

    # расчитать баланс и если он отрицательный - перенаправить пользователя
    unless( $self->{'s.login.quota'} - ( $self->{'s.login.traffic'}/1048576 ) )
    {
        return "302:$$addr_redirect?l=$$self{'s.login'}&q=$$self{'quota'}&t=$$self{'s.login.traffic'}";
    }
    
    ## разбить УРЛ на составляющие и записать в хэш
    @{$self}{'u.proto','u.addr','u.path','u.query'} = SquidTools::Utils::Uri::split( $self->{'s.url'} );
    
    # убрать префикс www
    if( $self->{'u.addr'} =~ /^www\./ ) { $self->{'u.addr'} =~ s/^www\.//; }

    ## проверить наличия УРЛ-а в БД и получить его идантификатор принадлежности к группе ресурсов
    if( $self->{'db.url_id'} = $$dbh->selectrow_array( "SELECT groupid FROM uribase WHERE uri=" . $$dbh->quote( "http://".$self->{'u.addr'} ) ) )
    {
        # подтвердить запретность данного УРЛ-а для запрашиваемого пользователя
        for( keys %{$$Resource{ $self->{'s.login'} }} )
        {
            if( $$Categories{ $self->{'db.url_id'} } eq $_ && exists $$Categories{ $self->{'db.url_id'} } )
            {
                $rsig = $_;
                last;
            }
        }
        
        # определить действия исходя из подтвержденности запрета на данный УРЛ
        if( $rsig )
        {
            return "302:$$addr_redirect?u=$$self{'s.login'}&r=$rsig&url=$$self{'s.url'}";
        }
        else
        {
            ## проверить не является данный урл ссылкой на файл
            if( $self->{'u.path'} =~ /\.(\w{2,4})$/ && exists $$Formats{ $self->{'s.login'}}{$1} )
            {
                return "302:$$addr_redirect?u=$$self{'s.login'}&f=$1&url=$$self{'s.url'}";
            }
            else
            {
                return $self->{'s.url'};
            }
        }
    }
    else
    {
        return $self->{'s.url'};
    }
}




Результат домпа DBI::DataDumper:

Код

DBI Profile Data (DBI::ProfileDumper 2.009894)

  Program       : /SquidTools/bin/knife.pl
  Path          : [ !Statement ]
  Total Records : 4 (showing 4, sorted by total)
  Total Count   : 3150
  Total Runtime : 5.450655 seconds  

#####[ 1 ]###########################################################
  Count         : 900
  Total Time    : 4.572807 seconds
  Longest Time  : 0.099952 seconds
  Shortest Time : 0.000006 seconds
  Average Time  : 0.005081 seconds
  Key 1         :

SELECT sum(size) AS ballance FROM squidlog s WHERE login='root' GROUP BY login;


#####[ 2 ]###########################################################
  Count         : 900
  Total Time    : 0.518883 seconds
  Longest Time  : 0.059022 seconds
  Shortest Time : 0.000004 seconds
  Average Time  : 0.000577 seconds
  Key 1         : SELECT quota FROM userpref WHERE login='root'

#####[ 3 ]###########################################################
  Count         : 900
  Total Time    : 0.356132 seconds
  Longest Time  : 0.039826 seconds
  Shortest Time : 0.000003 seconds
  Average Time  : 0.000396 seconds
  Key 1         : SELECT groupid FROM uribase WHERE uri='http://127.0.0.1'

#####[ 4 ]###########################################################
  Count         : 450
  Total Time    : 0.002833 seconds
  Longest Time  : 0.000018 seconds
  Shortest Time : 0.000005 seconds
  Average Time  : 0.000006 seconds
  Key 1         :


Первоночально top показывал что mysqld жрет 70% CPU и машина заметно тормозило, сейчас при тестировании ниже приведенным сценарием CPU грузится до 7%, разница заметна, хочу выразить благодарность ginnie


Код

#!/usr/bin/perl
use strict;
use warnings;

use HTTP::Request;
use LWP::UserAgent;
use Time::HiRes 'sleep';

$| = 1;

my @url = ('http://127.0.0.1/sex') x 30;


for( my $i = 1; $i <= 15; $i++ )
{
    make_new_child( \@url );
}

sub make_new_child
{
    my $uri = shift;
    my $pid = fork();
    
    die "fork() failed" unless defined( $pid );
    
    if( $pid )
    {
        print "<$$> [$pid]\n";
        return $pid;
    }
    else
    {
        $0 = 'sex';
        
        foreach my $u ( @{$uri} )
        {
            get( $u );
        }
        
        exit(0);
    }
    
}

sub get
{
    my $uri = shift;
    
    sleep( 0.0001 );
    my $ua = new LWP::UserAgent;
    $ua->proxy( ['http','ftp'] => 'http://127.0.0.1:3128/' );
    my $req = HTTP::Request->new( GET => $uri );
    $req->proxy_authorization_basic( 'yser', 'pertst' );
    my $res = $ua->request( $req );
    print $res->content;
}



ps: При использовании модуля DBI::ProfileDumper squid сбрасывал файл дампа в свою директорию где лежит его кэш /usr/local/squid/cache ( см. топик выше )

Автор: gcc 20.6.2008, 20:57
ачем логи килать в мускул? лучше в файл а из него потом брать, а apache именно так сделано...

Код

max_connections=600                                                             
max_user_connections=400                                                        
interactive_timeout=20                                                          
wait_timeout=30                                                                 
connect_timeout=6  


попробуйте оптимизировать, например если у вас памяти 2Gb и более, то попробуйте поставить больше памяти для ключей и т.д...

можите постаивить несоклько серверов, сделать один кластер...

Автор: n4n0bit 20.6.2008, 22:40
gcc золотые слова =) мне даже не дают еще один комп для поднятия домена =)))

Чесн сказать немнога вас не понял, ну у меня и берутся логи из фала а потом в mysql, т.е. access.log squid-a остается в оригинале.

Памити вроде 2Gb, хотя не уверен, но то что я делал сегодня это на моем домашнем ПК с 512 ОЗУ, думаю на работе будет работать справнее.

Автор: gcc 21.6.2008, 02:18
а почему вы записываете в DB Berkley?

ну на apache при 1000 пользователей, открыты всегда лог файлы в них записываються всегда логи, и ничего не грузит, грузит но не сильно... если все это записвать в мускул, то говорят, что будет грузить  smile 

может mysql грузится потомучто в нее выполняют insert, а не select?

Автор: n4n0bit 23.6.2008, 14:51
gcc Mysql грузился не при insert а при select, когда пользователи  делаю кучу запросов, тогда было гружение, а при добавлении данных в БД insert-ом проц грузит тока перл скрипт который добавляет, но это решается путем использования модуля Time::HiRes ф-ции sleep при построчном чтении.

ps: всем спс

Powered by Invision Power Board (http://www.invisionboard.com)
© Invision Power Services (http://www.invisionpower.com)