We are now able to offer no cure = no pay database landscape optimization.

This model has been proven at a large Dutch energy company, which now saves over Euro 12.5mln/year with our landscape optimization.

We do this optimizations on a no cure = no pay basis.

Two possible structures:
We make an inventory of your database landscape and determine the possible savings. If the possible savings are significant, we make a deal for a percentage of the savings, so you do not need money out of your IT budget! This can save you up to 95% of your database landscape.

Or
When the possible savings are not expressable in monetary value, we provide you with our best service, advising you on code optimizations and a database maintenance plan.
(This can save up to 75% of database resources).

This service is provided for:
- Oracle (version 8 and higher);
- Microsoft SQL Server (version 6 and higher);
- Mysql (version 5 and higher).

Discuss the possibilities with us. Use the contact form in the right hand menu to contact us.
  |  permalink

---

Issue:

This issues is related to Magento 1.6.2, but might also show up in other versions.

The re-indexing of magento product attributes is getting slower with a growing number of products.
The problem can be traced back to a poorly written query:
SELECT `pid`.`entity_id`, `pid`.`attribute_id`, `pid`.`store_id`, IFNULL(pis.value, pid.value) AS `value`
FROM (SELECT `s`.`store_id`, `s`.`website_id`, `d`.`entity_id`, `d`.`attribute_id`, `d`.`value` FROM `core_store` AS `s`
LEFT JOIN `catalog_product_entity_int` AS `d` ON 1 = 1 AND d.store_id = 0 WHERE (s.store_id != 0)
) AS `pid`
LEFT JOIN `catalog_product_entity_int` AS `pis` ON pis.entity_id = pid.entity_id AND pis.attribute_id = pid.attribute_id AND pis.store_id = pid.store_id
INNER JOIN `cataloginventory_stock_status` AS `ciss` ON ciss.product_id = pid.entity_id AND ciss.website_id = pid.website_id
WHERE (pid.attribute_id IN(?)) AND (IFNULL(pis.value, pid.value) IS NOT NULL) AND (pid.entity_id IN(?)) AND (ciss.stock_status = 1);


This query contains a subquery which is first evaluated by Mysql before applying the where conditions.

Solution:

In general the re-indexing process of Magento is slow. There are some fixes available which circumvent the problem by queueing index request.
There are sometimes more direct solutions, like running better queries.
In this case fixing the query helps:
Rewriting the query to the following shape:
SELECT `d`.`entity_id`, `d`.`attribute_id`, `s`.`store_id`, IFNULL(pis.value, d.value) AS `value`
FROM `core_store` AS `s`
LEFT JOIN `catalog_product_entity_int` AS `d` ON d.store_id = 0
LEFT JOIN `catalog_product_entity_int` AS `pis` ON pis.entity_id = d.entity_id AND pis.attribute_id = d.attribute_id AND pis.store_id = s.store_id
INNER JOIN `cataloginventory_stock_status` AS `ciss` ON ciss.product_id = d.entity_id AND ciss.website_id = s.website_id
WHERE (d.attribute_id IN(?)) AND (IFNULL(pis.value, d.value) IS NOT NULL) AND (d.entity_id IN(?)) AND (ciss.stock_status = 1) AND s.store_id!=0;

helps the database to be able to apply the where statement much earlier, consuming way less system resources (In our system with >20000 products: From 20s to 0.02s per execution, reducing row evaluation from ~10mln down to 17).

The code to be fixed: Magento core file:
app/code/core/Mage/Catalog/Model/Resource/Product/Indexer/Eav/Source.php

The fixed function:
protected function _prepareSelectIndex($entityIds = null, $attributeId = null)
{
$adapter = $this->_getWriteAdapter();
$idxTable = $this->getIdxTable();
// prepare select attributes
if (is_null($attributeId)) {
$attrIds = $this->_getIndexableAttributes(false);
} else {
$attrIds = array($attributeId);
}

if (!$attrIds) {
return $this;
}

/**@var $select Varien_Db_Select*/
$select = $adapter->select()
->from(
array('s' => $this->getTable('core/store')),
array('store_id', 'website_id')
)
->joinLeft(
array('d' => $this->getValueTable('catalog/product', 'int')),
'd.store_id=0',
array()
)
->joinLeft(
array('pis' => $this->getValueTable('catalog/product', 'int')),
'pis.entity_id = d.entity_id AND pis.attribute_id = d.attribute_id AND pis.store_id = d.store_id',
array()
)
->columns(
array(
'd.entity_id',
'd.attribute_id',
's.store_id',
'value' => $adapter->getIfNullSql('pis.value', 'd.value')
)
)
->where('d.attribute_id IN(?)', $attrIds)
->where('s.store_id!=?', 0);

$select->where(Mage::getResourceHelper('catalog')->getIsNullNotNullCondition('pis.value', 'd.value'));

if (!is_null($entityIds)) {
$select->where('d.entity_id IN(?)', $entityIds);
}

/**
* Add additional external limitation
*/
Mage::dispatchEvent('prepare_catalog_product_index_select', array(
'select' => $select,
'entity_field' => new Zend_Db_Expr('d.entity_id'),
'website_field' => new Zend_Db_Expr('s.website_id'),
'store_field' => new Zend_Db_Expr('s.store_id')
));

$query = $select->insertFromSelect($idxTable);
$adapter->query($query);

return $this;
}


Add the following indexes:

CREATE INDEX idx_catprdentint_nn_1 ON catalog_product_entity_int(entity_id, attribute_id, store_id, entity_type_id);

CREATE INDEX idx_cat_proc_ent_nn_5 ON catalog_product_entity_int(entity_id, attribute_id, store_id, value);



Test results show no visible wait time anymore on this index generation when using update on save.



  |  permalink

---

Issue:

The Magento re-indexing of Catalog URL Rewrites is slow. This is due to the very ineffective way the system is working with many deletes.

Solution:

There are 2 indexes which can speed up the process considerably. In the case as used by me, it reduced the to be processed records from ~50000 to ~100 for 1 DELETE query, and for the second delete query it removed the CPU intensive index merge with a more effective range scan. Time spend by the indexing process on this index is reduced with an estimated 95%.

The indexes to be created:

CREATE INDEX idx_core_url_rewrite_nn_3 ON core_url_rewrite(store_id,category_id);

CREATE INDEX idx_core_url_rewrite_nn_4 ON core_url_rewrite(store_id,product_id,category_id);


  |  permalink

---

Steps:
1) Install SuSE or other linux on a harddisk using another PC.
2) Replace the harddrive with your just installed harddrive.

Start and it works.
  |  permalink

---

The following query can become faster by adding an index:

SELECT MAX(RSR_KEY)
FROM RSR, DBINC
WHERE DBINC.DB_KEY = :B2 AND RSR.DBINC_KEY = DBINC.DBINC_KEY AND RSR.RSR_STAMP < :B1;


The indices:

create index idx_rsr_nn_1 on rsr(dbinc_key,rsr_stamp, rsr_key);
create index idx_dbinc_nn_1 on dbinc(db_key, dbinc_key);

  |  permalink

---

Issue:

Crosscheck is about 1 second (or more) per backup piece.

Solution:

It is possible that there are some full table scans in the query

SELECT NVL(MIN(SCN),POWER(2,64)-1)
FROM (
SELECT MIN(BRL.LOW_SCN) SCN
FROM BRL
WHERE BRL.DBINC_KEY = :B1 UNION
SELECT MIN(AL.LOW_SCN)
FROM AL
WHERE AL.DBINC_KEY = :B1 UNION
SELECT MIN(XAL.LOW_SCN)
FROM XAL
WHERE XAL.DBINC_KEY = :B1 UNION
SELECT MIN(BDF.CKP_SCN)
FROM BDF
WHERE BDF.DBINC_KEY = :B1 UNION
SELECT MIN(CDF.CKP_SCN)
FROM CDF
WHERE CDF.DBINC_KEY = :B1 UNION
SELECT MIN(XDF.CKP_SCN)
FROM XDF
WHERE XDF.DBINC_KEY = :B1 UNION
SELECT MIN(BCF.CKP_SCN)
FROM BCF
WHERE BCF.DBINC_KEY = :B1 UNION
SELECT MIN(CCF.CKP_SCN)
FROM CCF
WHERE CCF.DBINC_KEY = :B1 UNION
SELECT MIN(XCF.CKP_SCN)
FROM XCF
WHERE XCF.DBINC_KEY = :B1 );


This can be slightly improved by adding several indices:

create index idx_bcf_nn_1 on bcf(dbinc_key,ckp_scn);
create index idx_bdf_nn_1 on bdf(dbinc_key,ckp_scn);
create index idx_brl_nn_1 on brl(dbinc_key,low_scn);


If the indices are not helping, try altering db_file_multiblock_read_count to 0 (oracle 10/11 only). This can speed up the full table scans.
  |  permalink

---

Issue:

There is a large back log in processing the XML uploaded by the agents. The AWR shows database related issues.

Solution:

There are several indexes which prevent some of the full table scans which speed up the processing speed considerably:
CREATE INDEX sysman.mgmt_current_metrics_idx1 ON sysman.mgmt_current_metrics (key_value,metrics_guid,target_guid);

and

CREATE INDEX sysman.mgmt_current_metrics_idx2 ON sysman.mgmt_current_metrics(target_guid, metric_guid, collection_timestamp);

Probably there are more (-:
  |  permalink

---

Issue:

Remove under unix gives an argument list too long.

Solution:

A possibility is to write a script:
TOBEREMOVED=start of file name to be removed
function larger_rm ()
{ while read line1; do
if [ $line1==$TOBEREMOVED* ]
then
if [ $line1 != $TOBEREMOVED ]
then
echo "removing "$line1
rm $line1
fi
fi
done
}
ls | larger_rm


This script removes all files except the file name itself (handy with historical files).
Warning: It is slow....
  |  permalink

---

Issue:

OpenSuse 11.4 Xen VM fails with ATA errors after upgrading to the desktop kernel version included in the distribution.

Solution:

Re-start the installation and install the default kernel instead of the Xen desktop kernels.


  |  permalink

---

Issue:

After a restart of the XEN kernel on OpenSuSE 11.4, DHCP of the bridged interface works, but a pint to the route returns with destination host unreachable.

Solution:

The eth interface used to communicate with the outside world is dropped out of the bridged configuration for unknown reasons. Use brctl add br0 eth0 to add the interface (and of course replace br0 and eth0 with your own values).
  |  permalink

---

Issue:

Oracle Enterprise Manager has a large backlog of to be processed XML files.

Solution:

The problem can be that the stored procedure EMD_LOADER.UPDATE_CURRENT_METRICS is slow due to an not efficient index on the table SYSMAN.MGMT_CURRENT_METRICS. The index SYSMAN.MGMT_CURRENT_METRICS_PK is in the incorrect order to be efficient. Add the following index:
CREATE INDEX "SYSMAN"."MGMT_CURRENT_METRICS_IDX1_EFFICIENT" ON "SYSMAN"."MGMT_CURRENT_METRICS" ("KEY_VALUE", "METRIC_GUID", "TARGET_GUID");


  |  permalink

---

Issue:

Your installed kvm virtual machine has run out of disk space and the volume created with qemu-img can not be formatted by Microsoft Windows (XP SP3).

Solution:

Create an image:
qemu-img create -f raw edrive.img 64G
Use fdisk (under linux) to create a disk header in the image:
fdisk /home/kvm/edrive.img
Press w
Add the image to your config:
qemu-kvm -cdrom /home/kvm/somecd.img -hda windowsxp_install.img -hdb edrive.img -m 1024 -net nic -net user &
Now the disk can be partitioned and formatted in Microsoft Windows (XP SP3)


  |  permalink

---

Issue:

SQL Server Management Studio is slow while starting.

Solution:

The management studio can not find a certain URL. This can be fixed by adding the following line to the hosts file:
127.0.0.1 crl.microsoft.com


  |  permalink  |  related link

---

Issue:

Once in a while, depending on the installation size, the apache httpd access_log grows to 2GB on the Oracle Enterprise Manager Grid Control installation.

Solution:

There are two possible solutions:
A. Correct the log rotate setup;
B. Stop the logging.
This solution is the solution to stop the logging (since it is pretty much useless anyway).
Edit the sysman/config/httpd_em.conf file and hash out the following lines:

ErrorLog /oracle/product/oms10g/Apache/Apache/logs/error_log
TransferLog "/oracle/product/oms10g/Apache/Apache/logs/access_log"


to

# ErrorLog /oracle/product/oms10g/Apache/Apache/logs/error_log
# TransferLog "/oracle/product/oms10g/Apache/Apache/logs/access_log"


Delete the old log files from Apache/Apache/logs and restart grid control. The logging should be stopped.

  |  permalink

---

Issue:

The backup of the full database fails with error number -1073548784.

Solution:

Most likely the database requires a log file backup before a full backup is possible.
To see if the log file backup is required, select the following:
select log_reuse_wait_desc from sys.databases where name='your database name';

If this displays 'LOG_BACKUP', then first execute a transaction log backup. After the transaction log backup, the full backup should be possible again.
  |  permalink  |  related link

---

Issue:

The disk containing the tempdb from SQL Server 2005 is near full.

Solution:

The tempdb can be moved by executing the following SQL and restarting the SQL Server instance:

USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = '{location}tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = '{location}templog.ldf');
GO

  |  permalink

---

Issue:

You are trying to implement database mirroring with SQL Server 2005 instructions from http://weblogs.sqlteam.com/tarad/archive/2007/02/13/60091.aspx and fail at:

CREATE ENDPOINT [Mirroring]
AS TCP (LISTENER_PORT = 5022)
FOR DATA_MIRRORING (ROLE = PARTNER, ENCRYPTION = ENABLED);


Solution:

The keyword ENCRYPTION=ENABLED should be ENCRYPTION=SUPPORTED
  |  permalink  |  related link

---

Issue:

Registration of tradebox fails due to badly written browser and java capability check.

Solution:

No claims due to any losses or mishaps when using this solution can be claimed. The solution is from the ABN Amro helpdesk:
Register using windows. At the end save the jnlp file. Copy the file to any system you want to start tradebox.

P.S. This is scary: The file contains the username/password combination in encrypted format. No user is requested anymore. Do not give to friends, keep in a save place. If lost: Block your accounts!
  |  permalink

---

Issue:

A Microsoft SQL Server standby database needs to be brought online.

Solution:

Execute the command:
restore database [databasename] with recovery;
on the database server where the standby database is running.
  |  permalink

---

Issue:

The user rights assignment in SSIS is difficult to configure in such a way that a user does not require (local) administrator rights to be able to use SSIS.

Solution:

The following steps are required:
1. Add the user to the Distributed COM Users group using Administrative tools->Computer management->System tools->Local users and groups->groups->Distributed COM Users;
2. Open Control Panel, double-click Administrative Tools, and then double-click Component Services to start the Component Services MMC snap-in;
3. Expand the Component Services node in the left pane of the console. Expand the Computers node, expand My Computer, and then click the DCOM Config node;
4. Select the DCOM Config node, and then select MsDtsServer in the list of applications that can be configured;
5. Right-click on MsDtsServer and select Properties;
6. In the MsDtsServer Properties dialog box, select the Security tab.
7. Under Launch and Activation Permissions, select Customize, then click Edit to open the Launch Permission dialog box;
8. In the Launch Permission dialog box, add or delete users, and assign the appropriate permissions to the appropriate users and groups. The available permissions are Local Launch, Remote Launch, Local Activation, and Remote Activation. The Launch rights grant or deny permission to start and stop the service; the Activation rights grant or deny permission to connect to the service. Here select Local Activation only;
9. Click OK to close the dialog box;
10. Under Access Permissions, select Customize, and then click Edit;
11. In the Access Permission dialog box, add or delete users, and assign the appropriate permissions to the appropriate users and groups. Here select active Local & remote access;
12. Click OK to close the dialog box;
13. Close the MMC snap-in;
14. Restart the Integration Services service.

  |  permalink  |  related link

---

Next

Links
Home
Contact Me
Stats
Hypersonic database used in OpenOffice and JBoss.
linux.org news and linux updates
Slashdot news for nerds, stuff that matters
The linux kernel site
Java.sun.com

---

Login

---

---

Categories
Faq
General applicable solutions
Java
Linux
  Slackware
  SuSE
SQL
  MySQL
  Oracle
  Microsoft SQL Server
Magento
News
php
  Joomla
Little tree
  Sequoiadendron giganteum
    Grow from seed

---

Search:

 

---

Most Recent Comments
Bjorn Bergli
Monday, March 12, 2007, 01:16 PM
Ethernet mini 250GB Diskimage
Monday, March 12, 2007, 12:29 PM

Bjorn Bergli
Monday, January 29, 2007, 02:48 PM
I originally bought my two ethernet...

Mikael Holber
Tuesday, January 9, 2007, 11:48 AM
I followed Jims notes a while ago. I...

Admar
Thursday, December 7, 2006, 12:11 PM
Nice article. Unfortunately, I...

Jim Studt
Thursday, June 1, 2006, 07:26 AM
I went through the exercise and added...

info
Wednesday, January 18, 2006, 03:07 PM
The bezel does slide back into place...

Cody
Wednesday, January 18, 2006, 03:01 PM
If I push the bezel above the keyboard...

---

Most Recent Entries
No cure = no pay database optimization
Reindexing of magento product attributes is slow
Speeding up magento indexing
Installing linux on Mac Mini A1176
More RMAN catalog speedups
RMAN Crosscheck performance issues
Oracle Enterprise Manager 10g is slow in processing agent data
Argument list too long
OpenSuse 11.4 Xen VM fails with ATA errors
XEN bridged network config SuSE 11.4 not routing correctly after restart
Oracle Enterprise manager is slow in uploading metrics data
Formatting a file for use in KVM/qemu
Microsoft SQL Server Management Studio is slow while starting
HTTP access_log crashes Oracle Enterprise Manager Grid Control
Backup fails
SQL Server temp db fills disk
Database mirroring ENCRYPTION=ENABLED
ABN Amro Tradebox using linux
Bring standby database online
Configuring a user for SSIS
Fast recover of a dataguard
RMAN backup does not respond for long period
Oracle Universal installer not starting
SQL Server does not show all options in the maintenance cleanup plan
Archiver hung error in log while Oracle is not in archive log mode
Oracle chooses full table scan over index while cost is equal
Dataguard fails with divide by zero exception
make fails with stubs32.h not found on 64bit suse 10.3
Layout for view is not found
Starting JRockit Mission Control
Howto create a custom button
Call to JTable object results in blank screen
Adding a view results in error 500
Bea gives error that weblogicWLStore is owned by other thread
Bea Weblogic 9.2 does not start after upgrade
Weblogic jar installer in console mode reports to little disk space
Joomla failes component installation with SQL error
Bea weblogic installation are jar files instead of binary
Bea Weblogic JMS fails to show in JNDI tree in cluster setup
Internet explorer does not show a div

---

Database advice, programming advice, performance optimizations - Page Generated in 0.0421 seconds