Doubt with stored procedures
Hi everybody, I have a doubt with stored procedures functionality. Is possible that a stored procedure works with all databases form the server? I have created a stored procedure on dataBaseA and also works with dataBaseB. Is that correct? Independently of the user privileges defined. Thanks everybody. Best regards, Antonio.
Re: MySQL on Mac OS 10.8
On Tue, Apr 16, 2013 at 11:31 AM, Reindl Harald h.rei...@thelounge.net wrote: Am 16.04.2013 18:34, schrieb Larry Martell: I just set up mysql on Mac OS 10.8. Each time after the machine is rebooted the server fails to start with: 2013-04-13 14:09:54 1 [ERROR] /usr/local/mysql/bin/mysqld: Can't create/write to file '/var/run/mysqld/mysqld.pid' (Errcode: 2 - No such file or directory) Because the /var/run/mysqld dir does not exist. I have to create it manually then the server starts. But I have to do this after each reboot, which is a pain. Anyone know why the /var/run/mysqld dir gets deleted, or how to get around this? you need to consult your operating system's documentation on linux with systemd it would be the following config [root@srv-rhsoft:~]$ cat /etc/tmpfiles.d/mysql.conf d /var/run/mysqld 0755 mysql mysql - Doesn't seem to be anything like that running on MacOS. I googled and didn't find anything. I posted to a mac forum, but haven't received any responses yet. I'll have to try SO. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: MySQL on Mac OS 10.8
Am 17.04.2013 15:32, schrieb Larry Martell: On Tue, Apr 16, 2013 at 11:31 AM, Reindl Harald h.rei...@thelounge.net wrote: Am 16.04.2013 18:34, schrieb Larry Martell: I just set up mysql on Mac OS 10.8. Each time after the machine is rebooted the server fails to start with: 2013-04-13 14:09:54 1 [ERROR] /usr/local/mysql/bin/mysqld: Can't create/write to file '/var/run/mysqld/mysqld.pid' (Errcode: 2 - No such file or directory) Because the /var/run/mysqld dir does not exist. I have to create it manually then the server starts. But I have to do this after each reboot, which is a pain. Anyone know why the /var/run/mysqld dir gets deleted, or how to get around this? you need to consult your operating system's documentation on linux with systemd it would be the following config [root@srv-rhsoft:~]$ cat /etc/tmpfiles.d/mysql.conf d /var/run/mysqld 0755 mysql mysql - Doesn't seem to be anything like that running on MacOS. I googled and didn't find anything. I posted to a mac forum, but haven't received any responses yet. I'll have to try SO you need to modify the StartupItem to create the directory before starting mysqld, as said: OS manual http://dev.mysql.com/doc/mysql-macosx-excerpt/5.5/en/macosx-installation-startupitem.html signature.asc Description: OpenPGP digital signature
RE: Mesaure query speed and InnoDB pool
Run your query twice; take the second time. One more thing -- avoid the Query cache. That could lead to really bogus timings. Yes, but I need cache to be my database size to prevent other pages from pushing out pages for my query, right? Well, yes and no. If the cache is big enough, there won't be any thrashing. If the working set is smaller than the cache, then there won't be any thrashing. That is, if you don't access all the data/index blocks, there could be room for everything that is needed (the working set). I often see, say, 100GB on disk and only 5GB of cache, yet the system is humming along fine -- the working set is 5GB and/or the accesses to other blocks is infrequent enough so that it is not a big issue. I speak about query optimization in general General tips, many relating to optimization: http://mysql.rjweb.org/doc.php/ricksrots Quick lesson in compound indexes (something that novices don't understand -- and a significant optimization principle): http://mysql.rjweb.org/doc.php/index1 I will increase it now. I hope you are referring to increasing RAM. But I will need to disable swapping also to prevent my OS from swapping out InnoDB pages. NO! Don't disable swapping; avoid swapping. Disabling could cause a crash or other nasties. Avoiding means decreasing the tunables so that mysqld does not need to be swapped. This gives the main tunables: http://mysql.rjweb.org/doc.php/memory Decrease each as much as practical for your situation. (For example, change max_connections from 200 down to 5 -- assuming you don't need more than 5 simultaneous connections.) innodb_buffer_pool_size is probably the biggest memory consumer, so it is the easiest way to shrink mysqld's footprint. -Original Message- From: Ilya Kazakevich [mailto:ilya.kazakev...@jetbrains.com] Sent: Wednesday, April 17, 2013 8:05 AM To: Rick James Cc: 'MySQL' Subject: RE: Mesaure query speed and InnoDB pool Hello Rick, Run your query twice; take the second time. For most queries the first run brings everything into cache, then the second gives you a repeatable, though cached, timing. Yes, but I need cache to be my database size to prevent other pages from pushing out pages for my query, right? Or I need to do at the dedicated server.. Please provide EXPLAIN SELECT, SHOW CREATE TABLE, and we will critique your indexes and query plan. I speak about query optimization in general) Handler* is another way to get consistent values. These numbers are unaffected by caching. What variable exactly should I take? Why can't I use Innodb_pages_read? That is number of page reads regardless its source (pool or disk), is not it? 1GB buffer_pool? You have only 2GB of available RAM? Normally, if you are running only InnoDB, the buffer_pool should be set to about 70% of available RAM. I will increase it now. But I will need to disable swapping also to prevent my OS from swapping out InnoDB pages. Ilya. -Original Message- From: Ananda Kumar [mailto:anan...@gmail.com] Sent: Tuesday, April 16, 2013 2:06 AM To: Ilya Kazakevich Cc: MySQL Subject: Re: Mesaure query speed and InnoDB pool Does your query use proper indexes. Does your query scan less number blocks/rows can you share the explain plan of the sql On Tue, Apr 16, 2013 at 2:23 PM, Ilya Kazakevich ilya.kazakev...@jetbrains.com wrote: Hello, I have 12Gb DB and 1Gb InnoDB pool. My query takes 50 seconds when it reads data from disk and about 2 seconds when data already exists in pool. And it may take 10 seconds when _some_ pages are on disk and some are in pool. So, what is the best way to test query performance? I have several ideas: * Count 'Innodb_rows_read' or 'Innodb_pages_read' instead of actual time * Set pool as small as possible to reduce its effect on query speed * Set pool larger than my db and run query to load all data into pool and measure speed then How do you measure your queries' speed? Ilya Kazakevich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: MySQL on Mac OS 10.8
On Wed, Apr 17, 2013 at 8:32 AM, Reindl Harald h.rei...@thelounge.net wrote: Am 17.04.2013 15:32, schrieb Larry Martell: On Tue, Apr 16, 2013 at 11:31 AM, Reindl Harald h.rei...@thelounge.net wrote: Am 16.04.2013 18:34, schrieb Larry Martell: I just set up mysql on Mac OS 10.8. Each time after the machine is rebooted the server fails to start with: 2013-04-13 14:09:54 1 [ERROR] /usr/local/mysql/bin/mysqld: Can't create/write to file '/var/run/mysqld/mysqld.pid' (Errcode: 2 - No such file or directory) Because the /var/run/mysqld dir does not exist. I have to create it manually then the server starts. But I have to do this after each reboot, which is a pain. Anyone know why the /var/run/mysqld dir gets deleted, or how to get around this? you need to consult your operating system's documentation on linux with systemd it would be the following config [root@srv-rhsoft:~]$ cat /etc/tmpfiles.d/mysql.conf d /var/run/mysqld 0755 mysql mysql - Doesn't seem to be anything like that running on MacOS. I googled and didn't find anything. I posted to a mac forum, but haven't received any responses yet. I'll have to try SO you need to modify the StartupItem to create the directory before starting mysqld, as said: OS manual http://dev.mysql.com/doc/mysql-macosx-excerpt/5.5/en/macosx-installation-startupitem.html Yes, I have this, but it doesn't work if /var/run/mysqld is not there. I still have to manually create that dir after each reboot. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Mesaure query speed and InnoDB pool
Hello Rick, Run your query twice; take the second time. For most queries the first run brings everything into cache, then the second gives you a repeatable, though cached, timing. Yes, but I need cache to be my database size to prevent other pages from pushing out pages for my query, right? Or I need to do at the dedicated server.. Please provide EXPLAIN SELECT, SHOW CREATE TABLE, and we will critique your indexes and query plan. I speak about query optimization in general) Handler* is another way to get consistent values. These numbers are unaffected by caching. What variable exactly should I take? Why can't I use Innodb_pages_read? That is number of page reads regardless its source (pool or disk), is not it? 1GB buffer_pool? You have only 2GB of available RAM? Normally, if you are running only InnoDB, the buffer_pool should be set to about 70% of available RAM. I will increase it now. But I will need to disable swapping also to prevent my OS from swapping out InnoDB pages. Ilya. -Original Message- From: Ananda Kumar [mailto:anan...@gmail.com] Sent: Tuesday, April 16, 2013 2:06 AM To: Ilya Kazakevich Cc: MySQL Subject: Re: Mesaure query speed and InnoDB pool Does your query use proper indexes. Does your query scan less number blocks/rows can you share the explain plan of the sql On Tue, Apr 16, 2013 at 2:23 PM, Ilya Kazakevich ilya.kazakev...@jetbrains.com wrote: Hello, I have 12Gb DB and 1Gb InnoDB pool. My query takes 50 seconds when it reads data from disk and about 2 seconds when data already exists in pool. And it may take 10 seconds when _some_ pages are on disk and some are in pool. So, what is the best way to test query performance? I have several ideas: * Count 'Innodb_rows_read' or 'Innodb_pages_read' instead of actual time * Set pool as small as possible to reduce its effect on query speed * Set pool larger than my db and run query to load all data into pool and measure speed then How do you measure your queries' speed? Ilya Kazakevich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: MySQL on Mac OS 10.8
Not a solution but a workaround for [ERROR] /usr/local/mysql/bin/mysqld: Can't create/write to file '/var/run/mysqld/mysqld.pid' explicitly set pid_file= to some existing file path in /usr/local/mysql/bin/mysqld_safe (Line:26) e.g. you could possibly set it to: pid_file=/usr/local/mysql/data/larry.local.pid - Kapil Karekar On 17-Apr-2013, at 8:59 PM, Larry Martell larry.mart...@gmail.com wrote: On Wed, Apr 17, 2013 at 8:32 AM, Reindl Harald h.rei...@thelounge.net wrote: Am 17.04.2013 15:32, schrieb Larry Martell: On Tue, Apr 16, 2013 at 11:31 AM, Reindl Harald h.rei...@thelounge.net wrote: Am 16.04.2013 18:34, schrieb Larry Martell: I just set up mysql on Mac OS 10.8. Each time after the machine is rebooted the server fails to start with: 2013-04-13 14:09:54 1 [ERROR] /usr/local/mysql/bin/mysqld: Can't create/write to file '/var/run/mysqld/mysqld.pid' (Errcode: 2 - No such file or directory) Because the /var/run/mysqld dir does not exist. I have to create it manually then the server starts. But I have to do this after each reboot, which is a pain. Anyone know why the /var/run/mysqld dir gets deleted, or how to get around this? you need to consult your operating system's documentation on linux with systemd it would be the following config [root@srv-rhsoft:~]$ cat /etc/tmpfiles.d/mysql.conf d /var/run/mysqld 0755 mysql mysql - Doesn't seem to be anything like that running on MacOS. I googled and didn't find anything. I posted to a mac forum, but haven't received any responses yet. I'll have to try SO you need to modify the StartupItem to create the directory before starting mysqld, as said: OS manual http://dev.mysql.com/doc/mysql-macosx-excerpt/5.5/en/macosx-installation-startupitem.html Yes, I have this, but it doesn't work if /var/run/mysqld is not there. I still have to manually create that dir after each reboot. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Doubt with stored procedures
- Original Message - From: Antonio Fernández Pérez antoniofernan...@fabergames.com Subject: Doubt with stored procedures I have a doubt with stored procedures functionality. Is possible that a stored procedure works with all databases form the server? I have created a stored procedure on dataBaseA and also works with dataBaseB. Is that correct? Independently of the user privileges defined. Yes, that's all part of the mysql magic, any stored procedure ever defined will work on any server ever to exist. Well, either that, or you might want to provide us with a little more detail, including but not limited to: * Is this MySQL or NDB Cluster? * Software version? * What is the relation between the servers (master, slave, master/master, ...) ? * how exactly did you define the SP, using what user etc. ? * how are you calling the SP, using what user, ... ? * ... That being said, using a set of default assumptions, I can tell you that both GRANT and CREATE PROCEDURE are replicated, so both all procedures and all users should logically exist on both sides of a replication setup. -- Linux Kriek Wanderung April 19-21, 2013 http://www.tuxera.be/lkw -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: MySQL on Mac OS 10.8
Am 17.04.2013 17:29, schrieb Larry Martell:
On Wed, Apr 17, 2013 at 8:32 AM, Reindl Harald h.rei...@thelounge.net wrote:
[root@srv-rhsoft:~]$ cat /etc/tmpfiles.d/mysql.conf
d /var/run/mysqld 0755 mysql mysql -
Doesn't seem to be anything like that running on MacOS. I googled and
didn't find anything. I posted to a mac forum, but haven't received
any responses yet. I'll have to try SO
you need to modify the StartupItem to create the directory
before starting mysqld, as said: OS manual
http://dev.mysql.com/doc/mysql-macosx-excerpt/5.5/en/macosx-installation-startupitem.html
Yes, I have this, but it doesn't work if /var/run/mysqld is not there.
I still have to manually create that dir after each reboot
RTFM i said
https://developer.apple.com/library/mac/#documentation/MacOSX/Conceptual/BPSystemStartup/Chapters/StartupItems.html
what do you think is this below?
oh you have more than one command in StopService()
so write one command more in StartService()
and mkdir/chmod the missing directory BEFORE the daemon command
no, i do not use MacOSX because for me it is nto a serious OS
but i typed in google macosx startupitem create folder before start service
StartService() {
# Insert your start command below. For example:
mydaemon -e -i -e -i -o
# End example.
}
# The stop subroutine
StopService() {
# Insert your stop command(s) below. For example:
killall -TERM mydaemon
sleep 10
killall -9 mydaemon
# End example.
}
signature.asc
Description: OpenPGP digital signature
Re: Doubt with stored procedures
2013/04/17 14:16 +0200, Antonio Fernández Pérez I have a doubt with stored procedures functionality. Is possible that a stored procedure works with all databases form the server? I have created a stored procedure on dataBaseA and also works with dataBaseB. Is that correct? Independently of the user privileges defined. It is the default assumption that a procedure within a database is meant for use within that database, but one can call a procedure from any of the set of databases by qualifying the name--and the MySQL command show procedure status shows all procedures. The only question is the procedure s use of variables: if they refer only to the arguments, it is of no importance whence it is called. This is documented: file:///C:/Program%20Files/MySQL/MySQL%20Server%205.5/HELP/sql-syntax.html#useUSE statements within stored routines are not permitted. When a routine is invoked, an implicit USE db_name is performed (and undone when the routine terminates). The causes the routine to have the given default database while it executes. References to objects in databases other than the routine default database should be qualified with the appropriate database name. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Doubt with stored procedures
Hi everybody, Firstly thanks for your reply. I'm using MySQL only in one server (I have only one), without replication and without cluster configuration. I have defined the stored procedure as a normal user, not as a root. And the call is make as a normal user also. Occurs with MySQL 5.1.49 on Debian 6. This normal user has CREATE PROCEDURE privilege and EXECUTE privilege. Thanks. Best regards, Antonio.
