I’m working as project manager (and developer) in a project already built to serve as a Central Customer Service from a large company. In the last days we’re experiencing several ocurrences of deadlocks in our queries and a lot of cpu use from the apache process, making us change a lot of code, especially switching from Active Record to pure SQL (using Yii’s query builder). The application is module based (there are other 2 modules with “small use”) and there are other 4 Yii applications installed in the same server(small to medium use).
The server is Virtual Machine with Windows Server 2008 with 4GB RAM and a 2 core CPU. The database is in another server using SQL Server Enterprise 2008 R2. We’re using sqlsrv (v 3.0 from Microsoft) as the PHP driver.
Five days ago i started to overwrite our code with a lot of performance optimization, especially APC and Yii’s cache built in functionality.
The big problem is that we need a lot of Ajax events, including javascript’s interval to update grids, and we already know that the users use a lot of windows opened at the same time, especially because they work in a “multi-task enviroment” handling a lot of screens and registries, everything in real time. To make it worst the DBA is taking a lot of time just to bring us the profiling results from the queries made from our server to the Database Server.
Anyway, do anyone have experienced some kind of problem like these before, especially using SQL Server with Yii in large scale applications?
Our client’s IT suggested creating a virtual host for each application. The thing is that i don’t understand how this would fix the problem since most of the problem is directly on the CCS application.
More info from our installation:
Apache 2.2.22
PHP 5.4.16
sqlsrv driver (3.0)
Last built Yii
I’m attaching some prints with information about the server.
The main table on our module has 5270 rows. But we have to store the history of changes in that table, so there’s at list 2 rows (status history) for every row found on the main table.
There’s an attachment table too, and other related tables that are linked to this main table.
The query with the most occurrences is:
SELECT t.id_chamado AS id_chamado,
t.ds_chamado AS ds_chamado,
t.dh_chamado AS dh_chamado,
t.id_cliente_contato AS id_cliente_contato,
t.id_atendente AS id_atendente,
t.cd_sequencial AS cd_sequencial,
idClienteContato.id_cliente AS id_cliente,
idClienteContato.nm_cliente_contato AS nm_cliente_contato,
idClienteContato.ds_email AS ds_email,
idClienteContato.dt_aniversario AS dt_aniversario,
idClienteContato.ds_cliente_contato AS ds_cliente_contato,
idCliente.nu_cnpj AS nu_cnpj,
CASE WHEN idClienteContato.id_cliente IS NULL
THEN idClienteContato.ds_cliente_contato + '*'
ELSE idCliente.nm_cliente END AS nm_cliente,
idCliente.cd_key_account AS cd_key_account,
idCliente.id_segmento AS id_segmento,
sarChamadoStatusAtual.id_chamado_status AS id_chamado_status,
sarChamadoStatusAtual.dh_chamado_status AS dh_chamado_status,
sarChamadoStatusAtual.id_email AS id_email,
sarChamadoStatusAtual.cd_tipo AS cd_tipo,
sarChamadoStatusAtual.cd_status AS cd_status,
idEmail.ds_subject AS ds_subject,
idUsuario.nm_usuario AS nm_usuario,
(SELECT COUNT(*)
FROM sac_chamado_demanda ChamadoDemanda
JOIN sar_demanda_area DemandaArea ON (DemandaArea.id_demanda_area = ChamadoDemanda.id_demanda_area)
JOIN sar_chamado_demanda_status StatusAtual ON (StatusAtual.id_chamado_demanda = ChamadoDemanda.id_chamado_demanda AND StatusAtual.cd_ativo = 1)
WHERE ChamadoDemanda.id_chamado = t.id_chamado
AND DemandaArea.id_area <> 1
AND StatusAtual.cd_status NOT IN ('F', 'C')) AS qt_2nivelativo,
idEmail.ds_subject AS ds_subject,
sarChamadoStatusAtual.dh_chamado_status AS dh_chamado_status
FROM sac_chamado t
INNER JOIN sar_chamado_status sarChamadoStatusAtual ON (sarChamadoStatusAtual.id_chamado=t.id_chamado AND sarChamadoStatusAtual.cd_ativo = 1)
LEFT OUTER JOIN sac_cliente_contato idClienteContato ON (t.id_cliente_contato=idClienteContato.id_cliente_contato)
LEFT OUTER JOIN fuc_cliente idCliente ON (idClienteContato.id_cliente=idCliente.id_cliente)
LEFT OUTER JOIN sac_email idEmail ON (sarChamadoStatusAtual.id_email=idEmail.id_email)
LEFT OUTER JOIN sac_atendente idAtendente ON (t.id_atendente=idAtendente.id_atendente)
LEFT OUTER JOIN tkc_usuario idUsuario ON (idAtendente.id_usuario=idUsuario.id_usuario)
This query is executed in a grid that’s updated every 10 seconds.
