… it is a lot larger problems with MySQL 5.5 with addition of user variables, stored procedures, prepared statements etc which can be a memory hog. My intent with this post is dual. I would encourage MySQL… have created and forgot to remove (or still use for processing). Of course you will see these tables going away if…
Post: UDF -vs- MySQL Stored Function
September 6, 2010 By 6 Comments
… to sphinx – MySQL did not have a function to do the thing so I thought I’ll write MySQL Stored Function and we… process 100k rows: mysql> select ltrim_junk_mysql(author), ltrim_junk_mysql(title) from paintings limit 100000; 100000 rows in set (2.97 sec) mysql… as it shows how much overhead you have by using mysql stored routines interface. So anyway, I asked my colleague Sasha to…
Post: MySQL Stored Procedures problems and use practices
June 12, 2007 By 21 Comments
… “original” MySQL style – to process data via cursors, handle error via Handlers etc. If you spent last 10 years writing Stored Procedures for… log will contain full stored procedure calls. On the other hand Stored Procedures Indeed can help to Improve MySQL Performance. For DBT2 benchmarks… to do more work to send it back than Stored Procedure to process it. Stored Procedures also have number of performance gotchas which…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 12 Comments
… transactions. After each transaction it must wait for FlexCDC to process changes from that transaction. In practice, this means that it…, Flexviews includes a MySQL stored procedure API, called the SQL_API which is used to define the view. These stored procedures maintain the… of tables used by the above view, as stored in the data dictionary: mysql> select * from flexviews.mview_table where mview_id…
Post: Statement based replication with Stored Functions, Triggers and Events
December 16, 2011 By 3 Comments
…the SQL process will stop with an error. Example: mysql> CREATE FUNCTION this_year() RETURNS INT DETERMINISTIC RETURN YEAR(CURDATE()); mysql> insert …t VALUES(this_year())’ STORED PROCEDURES The behaviour of stored procedures and functions are completely different. If our stored procedure write data …
Post: Distributed Set Processing with Shard-Query
May 14, 2011 By 27 Comments
… by passing around rows instead of sets? From a distributed processing standpoint, rows are the worst case for performance. Optimal mathematical… all the nodes. A single temporary table is used to store the results from all the nodes. Since we are projecting… SQL, but right now only MySQL storage nodes are supported. Amdahl’s law applies to the distributed processing. The results from the…
Post: Modeling MySQL Capacity by Measuring Resource Consumptions
March 8, 2011 By 1 Comment
… need other resources such as network IO or memory to store temporary table, but let us ignore them for a moment… ? You can take a look at procfs for MySQL process: root@ubuntu:/var/log/mysql# cat /proc/19018/stat 19018 (mysqld) S 1… and #15 here is kernel and user CPU usage of MySQL process in 1/100 of the second. (This is pretty idle…
Post: How Percona diagnoses MySQL server stalls
March 3, 2011 By 6 Comments
…use a logical approach and good tools. The process is straightforward: Determine what conditions are observably … you need to look through it. It is stored in /root/collected/ by default. Use the “sift… many things that can cause a stall in MySQL, and they usually begin microscopically and get …
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
… can’t decide yet, then don’t store everything in the database. Instead, store the source data in some fashion that you… processing in MySQL, you’re going to end up heavily I/O bound. Listen to any of the talks at past MySQL… the processing outside the database lets you parallelize trivially. If you need to analyze the non-aggregated data, you can store it…
Post: PHP Large result sets and summary tables.
July 6, 2007 By 8 Comments
… ? Because by default mysql_query uses mysql_store_result C library call and buffers all result set in the process memory. Not good if… long enough MySQL Server may think client is dead and close connection. This well may happen if you need long processing for… much more efficient. Another alternative is of course to use MySQL Stored Procedures which can be fit to do this simple job…
