maria-discuss team mailing list archive

[Paul McCullagh <paul.mccullagh@xxxxxxxxxxxxx>]

Hi Alexandre,

As Arjen says, PBXT is unconventional, and sometimes requires tuning,  
as I explained in my last conference talk: http://www.primebase.org/download/pbxt-uc-2010.pdf 
.

While tuning may be necessary, it may be worth it for the results we  
can achieve.

But I don't expect you to read that now, instead I will help you  
through the first steps.

I think there are 2 problems:

(1) Your index cache may be too low: pbxt_index_cache_size.

Set this to about half of what you set the innodb buffer pool. (e.g.  
if innodb_buffer_pool_size=500M, then set  pbxt_index_cache_size=250M).

You can also set pbxt_record_cache_size=250M (the other half of  
memory), and I recommend pbxt_log_cache_size=128M (a fixed value, not  
dependent on the amount of memory on your machine).

(2) The PBXT estimate of the average row size of telpbxt is probably  
too high. The result is that the table is growing too big with 10m rows.

In order to correct this problem you need to set AVG_ROW_LENGTH for  
the table. For example:

CREATE TABLE telpbxt (
 cpf bigint(20) DEFAULT NULL,
 ...
) ENGINE=PBXT DEFAULT AVG_ROW_LENGTH=250 CHARSET=latin1;

Basically you should make an estimate of the average byte length of  
your rows, and set AVG_ROW_LENGTH for the table accordingly.

In addition, if 90% of your rows have a maximum row length which is  
only 10-20% greater than the average row length, then I recommend  
setting the AVG_ROW_LENGTH to this value.

PBXT can help you out with this. Do the following:

(1) Load the telpbxt table with a few 1000 rows (or use telpbxt2,  
since telpbxt already has too many rows in it).
(2) In mysql client enter: CHECK TABLE telpbxt2; (note: do not do this  
on a table with millions of rows. It will take very long to complete).
(3) Check the MySQL error log, you should see something like the  
output below.

CHECK TABLE: ./test/telpbxt
Record buffer size      = 1024
Fixed length rec. len.  = 1020
Handle data record size = 638
Min/max header size     = 14/26
Min/avg/max record size = 19/624/1019
Avg row len set for tab = not specified
Rows fixed length       = NO
Maximum fixed size      = 16384
...

Paste the output in an e-mail, and I will show you what to use for the  
AVG_ROW_LENGTH.

Best regards,

Paul

On May 4, 2010, at 8:55 PM, Alexandre Almeida wrote:

> Hi Guys,
>
> 	I am setting an enviroment with MariaDB + PBXT, but, I am pretty  
> much disappointed.
>
> 	Do you know if this time (see below) makes sense?
>
> 	Five hours to create a PBXT table with 10mio records... It's too  
> much!!! See my steps:
>
>
> create table telpbxt like telinno;
> Query OK, 0 rows affected (0,50 sec)
>
> fis-> alter table telpbxt engine = pbxts;
> Query OK, 0 rows affected, 1 warning (0,07 sec)
> Records: 0  Duplicates: 0  Warnings: 0
>
> fis-> alter table telpbxt engine = pbxt;
> Query OK, 0 rows affected (0,26 sec)
> Records: 0  Duplicates: 0  Warnings: 0
>
> fis-> show create table telpbxt;
> +--------- 
> +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
> | Table   | Create  
> Table 
>                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
> +--------- 
> +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
> | telpbxt | CREATE TABLE `telpbxt` (
>  `cpf` bigint(20) DEFAULT NULL,
>  `nome` varchar(100) DEFAULT NULL,
>  `log` varchar(100) DEFAULT NULL,
>  `endereco` varchar(100) DEFAULT NULL,
>  `comp1` varchar(100) DEFAULT NULL,
>  `comp2` varchar(100) DEFAULT NULL,
>  `bairro` varchar(100) DEFAULT NULL,
>  `cidade` varchar(100) DEFAULT NULL,
>  `cep` varchar(100) DEFAULT NULL,
>  `ddd` varchar(100) DEFAULT NULL,
>  `telefone` varchar(100) DEFAULT NULL,
>  KEY `idx2` (`nome`(10))
> ) ENGINE=PBXT DEFAULT CHARSET=latin1 |
> +--------- 
> +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
> 1 row in set (0,00 sec)
>
> fis-> insert into telpbxt select * from tel;
> Query OK, 10262048 rows affected (5 hours 2 min 22,14 sec)
> Records: 10262048  Duplicates: 0  Warnings: 0
> <alexandre(jpg).jpg>
> _______________________________________________
> Mailing list: https://launchpad.net/~maria-discuss
> Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp



--
Paul McCullagh
PrimeBase Technologies
www.primebase.org
www.blobstreaming.org
pbxt.blogspot.com