compile pinba on linux-mint14 64bit with nginx, mysql 5.5.31 with sphinx

[Hans Werner]

hi @all,

i try to compile pinba on linux-mint14 64bit with nginx, mysql 5.5.31 with sphinx 2.0.8.
i followed the introduction from https://github.com/tony2001/pinba_engine/wiki/Installation

here my install steps:

apt-get update && apt-get install -y libprotobuf-dev libprotobuf-lite7 libprotobuf7 protobuf-compiler libjudydebian1 libevent-dev libevent-2.0-5 libevent-core-2.0-5 libevent-extra-2.0-5 libevent-openssl-2.0-5 libevent-pthreads-2.0-5 libevent-1.4-2 libevent-dev

i compiled the mysql with:

./configure --with-sphinx-storage-engine --with-extra-charsets=all --with-big-tables --with-unix-socket-path=/var/run/mysqld/mysqld.sock --with-mysqld-user=mysql --with-libwrap --with-ssl --without-docs --basedir=/usr --datadir=/var/lib/mysql --tmpdir=/tmp --plugin_dir=/usr/lib/mysql/plugin --with-embedded-server --with-embedded-privilege-control --with-debug --verbose

# Google Protocol Buffers
cd /usr/local/src
wget http://protobuf.googlecode.com/files/protobuf-2.5.0.tar.gz
tar -xvzf protobuf-2.5.0.tar.gz && rm protobuf-2.5.0.tar.gz && cd protobuf-2.5.0
./configure \
--with-extra-charsets=all \
--with-big-tables
make
make install -j4

# General purpose dynamic array - Judy
wget http://sourceforge.net/projects/judy/files/latest/download
cp download Judy-1.0.5.tar.gz && tar -xvzf Judy-1.0.5.tar.gz && rm Judy-1.0.5.tar.gz && cd judy-1.0.5
./configure \
--with-extra-charsets=all \
--with-big-tables
make
make install -j4

# Install Pinba
wget http://pinba.org/files/pinba_engine-1.0.0.tar.gz
tar -xvzf pinba_engine-1.0.0.tar.gz && rm pinba_engine-1.0.0.tar.gz && cd pinba_engine-1.0.0
./configure \
--with-mysql=/usr/local/src/mysql-5.5.31 \
--with-judy=/usr/local \
--with-protobuf=/usr/local \
--with-event=/usr \
--libdir=/usr/lib/mysql/plugin
make install

here my mysql-engines:
-------------------
Server version: 5.5.31-log Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show engines;
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                    | Transactions | XA   | Savepoints |
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                      | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES          | YES  | YES        |
| CSV                | YES     | CSV storage engine                                         | NO           | NO   | NO         |
| PINBA              | YES     | Pinba engine                                               | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                      | NO           | NO   | NO         |
| SPHINX             | YES     | Sphinx storage engine 2.0.8-release                        | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables  | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                         | NO           | NO   | NO         |
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
8 rows in set (0.04 sec)

-------------------
mysql> mysql -D pinba < default_tables.sql
    -> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysql -D pinba < default_tables.sql' at line 1

so what can i do?
how is the problem or error on my installing-steps?

thanks and cheers, hans

[Antony Dovgal]

On 2013-06-20 20:46, Hans Werner wrote:
> -------------------
> mysql> mysql -D pinba < default_tables.sql
> -> ;
> ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysql -D pinba < default_tables.sql' at line 1
>
> so what can i do?

Execute that command in your shell, not in MySQL shell.
You're already running mysql client and trying to run it again.

--
Wbr,
Antony Dovgal
---
http://pinba.org - realtime profiling for PHP

[Hans Werner]

Hi Antony, many thanks for ansering.

So i try to import the mysql-dump file on the shell but i get the following error:
ERROR 1005 (HY000) at line 3: Can't create table 'pinba.request' (errno: 140)

after that i take a look into sql-dump-file...
if i delete the "ENGINE=PINBA" the create-statement works fine but with engine i got the error.

so i think i have an problem with the config from mysql?
here a snapshot from my my.cnf:
------
[mysqld]
#
# * Basic Settings
#
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
plugin_dir      = /usr/lib/mysql/plugin

# pinda config
pinba_port            = 3300
pinba_address            = 127.0.0.1
pinba_stats_gathering_period    = 10000 #(microseconds)
pinba_stats_history        = 300 #(seconds)
pinba_temp_pool_size        = 10000
pinba_request_pool_size        = 1000000 #<max expected requests per second> * <stats_history> = <request pool size>
pinba_tag_report_timeout    = -1 #Default value is -1, i.e. keep the data updated forever.

skip-external-locking

bind-address            = 127.0.0.1
key_buffer              = 16M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8
------

can you see my misstake? thanks for helping and sorry for the bad english...
cheers hans

[Antony Dovgal]

On 2013-06-21 15:07, Hans Werner wrote:
> Hi Antony, many thanks for ansering.
>
> So i try to import the mysql-dump file on the shell but i get the following error:
> ERROR 1005 (HY000) at line 3: Can't create table 'pinba.request' (errno: 140)

Did you build the MySQL server yourself using the same sources?
Looks like typical problem when building Pinba with MySQL sources that were configured or built with different flags.

> after that i take a look into sql-dump-file...
> if i delete the "ENGINE=PINBA" the create-statement works fine but with engine i got the error.

No, by removing that you turn the tables into MyISAM or InnoDB tables, which is just wrong.

[Hans Werner]

Did you build the MySQL server yourself using the same sources?
Looks like typical problem when building Pinba with MySQL sources that were configured or built with different flags.

yes i must use the source from mysql because i need the sphinx-search engine also...
at the moment i use mysql-5.5.31-source from mysql.com

what you meen with different flags? the options on the configure-options?
i try to compile the protobuf and judy with same of my configure-options from my mysql-compile but than i get an error like --with-big-tables are unknowed option etc..?
as I said I'm not sure if I have understood correctly by the same sources. maybe you can give me an example? 

 

> after that i take a look into sql-dump-file...
> if i delete the "ENGINE=PINBA" the create-statement works fine but with engine i got the error.

No, by removing that you turn the tables into MyISAM or InnoDB tables, which is just wrong.

of course this is clear, just wanted to say that the mysql but actually operates the pinda-mysql-plugin for whatever reason is not running

[Antony Dovgal]

On 2013-06-21 15:52, Hans Werner wrote:
>
> Did you build the MySQL server yourself using the same sources?
> Looks like typical problem when building Pinba with MySQL sources that were configured or built with different flags.
>
> yes i must use the source from mysql because i need the sphinx-search engine also...
> at the moment i use mysql-5.5.31-source from mysql.com
>
> what you meen with different flags? the options on the configure-options?

Configure options and compile-time flags, yes.

> i try to compile the protobuf and judy with same of my configure-options from my mysql-compile but than i get an error like --with-big-tables are unknowed option etc..?

No, I mean MySQL sources.
You need to use the same MySQL sources, preferably after you've built the MySQL server, just to ensure the configure options are the same.

> as I said I'm not sure if I have understood correctly by the same sources. maybe you can give me an example?

1) build MySQL server
2) configure Pinba with --with-mysql=/path/to/previously/used/MySQL/sources/
3) make && you're done.

> of course this is clear, just wanted to say that the mysql but actually operates the pinda-mysql-plugin for whatever reason is not running

It is running, that's why you're seeing that error 140.
It just can't determine the table's type and produces this error.

[Hans Werner]

No, I mean MySQL sources.
You need to use the same MySQL sources, preferably after you've built the MySQL server, just to ensure the configure options are the same.

> as I said I'm not sure if I have understood correctly by the same sources. maybe you can give me an example?

1) build MySQL server
2) configure Pinba with --with-mysql=/path/to/previously/used/MySQL/sources/
3) make && you're done.

 1) buid the mysql-server (location /usr/local/src/mysql-5.5.31)
./configure --with-sphinx-storage-engine --with-extra-charsets=all --enable-thread-safe-client --enable-local-infile --with-pstack --with-fast-mutexes --with-big-tables --with-unix-socket-path=/var/run/mysqld/mysqld.sock --with-mysqld-user=mysql --with-libwrap --with-ssl --without-docs --basedir=/usr --datadir=/var/lib/mysql --tmpdir=/tmp --plugin_dir=/usr/lib/mysql/plugin --with-embedded-server --with-embedded-privilege-control --with-debug --verbose

2) configure Pinba (location /usr/local/src/pinba_engine-master)

./configure \
--with-mysql=/usr/local/src/mysql-5.5.31 \
--with-judy=/usr/local \
--with-protobuf=/usr/local \
--with-event=/usr \
--libdir=/usr/lib/mysql/plugin

----- log from configure ----
checking for MySQL source code... /usr/local/src/mysql-5.5.31
checking for MySQL version >= 5.1... ok
checking Google Protocol Buffers install prefix... /usr/local
checking for Google Protobuf compiler... /usr/local/bin/protoc
checking libevent install prefix... /usr
checking if libevent requires -lrt... no
checking for event_base_new in -levent... yes
checking Judy install prefix... /usr/local
configure: Regenerating protocol code
configure: creating ./config.status
-----
make && make install...

but same problem:
mysql -u root -p -D pinba < /usr/local/src/pinba_engine-master/default_tables.sql;

ERROR 1005 (HY000) at line 3: Can't create table 'pinba.request' (errno: 140)

if i try to configure pinba with same mysql-options:
./configure \
--with-extra-charsets=all

than i get "configure: WARNING: unrecognized options: --with-extra-charsets" warnings

I'm really sorry but I still do not see the problem. Now what I can do?

[Antony Dovgal]

On 2013-06-21 17:18, Hans Werner wrote:
>
> No, I mean MySQL sources.
> You need to use the same MySQL sources, preferably after you've built the MySQL server, just to ensure the configure options are the same.
>
> > as I said I'm not sure if I have understood correctly by the same sources. maybe you can give me an example?
>
> 1) build MySQL server
> 2) configure Pinba with --with-mysql=/path/to/previously/used/MySQL/sources/
> 3) make && you're done.
>
>
> 1) buid the mysql-server (location /usr/local/src/mysql-5.5.31)
> ./configure --with-sphinx-storage-engine --with-extra-charsets=all --enable-thread-safe-client --enable-local-infile --with-pstack --with-fast-mutexes --with-big-tables --with-unix-socket-path=/var/run/mysqld/mysqld.sock --with-mysqld-user=mysql --with-libwrap --with-ssl --without-docs --basedir=/usr --datadir=/var/lib/mysql --tmpdir=/tmp --plugin_dir=/usr/lib/mysql/plugin --with-embedded-server --with-embedded-privilege-control --with-debug --verbose

There's no ./configure there, they've switched to cmake several years ago, so where did you get it?


Nevermind, I see the fast-mutexes flag, ok.
I've discussed it here in details: https://github.com/tony2001/pinba_engine/issues/4#issuecomment-19377010

I've explained the issue to MySQL and MariaDB developers already and they seem to be agreeing that this should be fixed on their side.

[Hans Werner]

>   1) buid the mysql-server (location /usr/local/src/mysql-5.5.31)
> ./configure --with-sphinx-storage-engine --with-extra-charsets=all --enable-thread-safe-client --enable-local-infile --with-pstack --with-fast-mutexes --with-big-tables --with-unix-socket-path=/var/run/mysqld/mysqld.sock --with-mysqld-user=mysql --with-libwrap --with-ssl --without-docs --basedir=/usr --datadir=/var/lib/mysql --tmpdir=/tmp --plugin_dir=/usr/lib/mysql/plugin --with-embedded-server --with-embedded-privilege-control --with-debug --verbose

There's no ./configure there, they've switched to cmake several years ago, so where did you get it?

that's the way i install mysql many years and it works fine so why change? don't touch a running system ;)
 

Nevermind, I see the fast-mutexes flag, ok.
I've discussed it here in details: https://github.com/tony2001/pinba_engine/issues/4#issuecomment-19377010

I've explained the issue to MySQL and MariaDB developers already and they seem to be agreeing that this should be fixed on their side.

CXXFLAGS="-DMY_PTHREAD_FASTMUTEX" ./configure \

--with-mysql=/usr/local/src/mysql-5.5.31 \
--with-judy=/usr/local \
--with-protobuf=/usr/local \
--with-event=/usr \
--libdir=/usr/lib/mysql/plugin

WORKS! THANKS!
the "default_tables.sql" successfully imported to mysql :)