Feeds:
Posts
Comments

Archive for the ‘mysql’ Category

status questions in mysql

mysqld processes 61k questions on one cpu per second. Is mysqld that powerful?

mysql> delimiter $$
mysql> create procedure simple_loop(in var1 int)
-> begin
->  declare counter int default 0;
->  while (counter < var1) do
->   set counter=counter+1;
->  end while;
-> end$$
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql>
mysql> show status like '%question%';
+---------------+---------+
| Variable_name | Value   |
+---------------+---------+
| Questions     | 4200256 |
+---------------+---------+
1 row in set (0.00 sec)
mysql> call  simple_loop(1000000);
Query OK, 0 rows affected (3.27 sec)
mysql> show status like '%question%';
+---------------+---------+
| Variable_name | Value   |
+---------------+---------+
| Questions     | 6200260 |
+---------------+---------+
1 row in set (0.00 sec)
mysql> select (6200260-4200256)/3.27;
+------------------------+
| (6200260-4200256)/3.27 |
+------------------------+
|            611622.0183 |
+------------------------+
1 row in set (0.00 sec)

The database I used is of version 5.0.36. Per the following link, for mysql of verion <5.0.72,  Questions includes every statement (beside sql statement) in a procedure, and if that statement loops 1M times, the statistics of Questions will also increased by 1M.

http://dev.mysql.com/doc/refman/5.0/en/server-status-variables.html#statvar_Questions

Read Full Post »

If use mysql memory engine combined with default memory allocator, we might found that after delete/drop a table, the memory allocated wasn’t released back to OS. Is it memory leak? No.

mysqld uses C function malloc/free to allocate/de-allocate memory, depending on the underling memory allocator it used, syscall sbrk or mmap is used to allocate memory. And if use mmap, munmap is possible to be used to release the memory to OS (the memory allocator has the choice of whether return the free memory to OS or not). But if use sbrk, it’s hard to release memory as the used memory is scattered around and the free space could be in the middle of the memory used (just like if we bump up high water mark in oracle table, we can’t cut down the high water mark easily).

Memory allocator hoard uses mmap, and it will return the free memory to OS if the size of memory reaches a threshold. The following test show that after delete a table, the allocated memory is released back to OS. So if using hoard, and there are tons of delete/insert, some overhead will occur (repeatedly allocate and de-allocate) in comparison with other memory allocators which don’t release back memory to OS. If this happen we could see big sys cpu used.

And I saw a case of hardware upgrade failure for mysql database with memory engine only. The only software change was switching the memory allocator from libc malloc to hoard for mysqld. After the mysqld was up for sometime, a delete job kicked in to delete old data to free space for new data to be inserted. Later the server was hanging there as sys cpu used jumped up to more than 80%. After switch the memory allocator back, everything was back to normal.



TEST--server1$> ps -ef|grep mysqld
oracle 20128 20089   0 12:04:44 pts/2       0:00 /oracle/TEST/home/products/5036x64/bin/mysqld --basedir=/oracle/TEST/ho
oracle 20130 20054   0 12:04:51 pts/2       0:00 grep mysqld
oracle 20089     1   0 12:04:44 pts/2       0:00 /bin/sh /oracle/TEST/home/products/5036x64/bin/mysqld_safe --core-file
Total memory used when mysql is up.
TEST--server1$> pmap -x 20128 | grep anon | awk 'BEGIN {total=0}{total=total+$2}END {print total}'
39768
mysql> delimiter $$
mysql> CREATE PROCEDURE t_proc1(IN var1 INT)
-> BEGIN
->  DECLARE counter INT DEFAULT 0;
->  WHILE (counter < var1) DO
->   insert into t values (rand()*100000,rand()*100000,'a');
->   SET counter=counter+1;
->  END WHILE;
-> END$$
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> delimiter ;
insert 200k rows
mysql>  call t_proc1(200000);
Query OK, 1 row affected (3.04 sec)
mysql>  show table status like 't'\G;
*************************** 1. row ***************************
Name: t
Engine: MEMORY
Version: 10
Row_format: Fixed
Rows: 200000
Avg_row_length: 3011
Data_length: 608108064
Max_data_length: 12562051583
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: NULL
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
ERROR:
No query specified

checking the memory used using pmap


TEST--server1$>  pmap -x 20128 | grep anon | awk 'BEGIN {total=0}{total=total+$2}END {print total}'
635580
mysql> delete from t;   (I tested only delete 100000 rows, memory also reduced.)
Query OK, 200000 rows affected (0.29 sec)
mysql>  show table status like 't'\G;
*************************** 1. row ***************************
Name: t
Engine: MEMORY
Version: 10
Row_format: Fixed
Rows: 0
Avg_row_length: 3011
Data_length: 0                => size is 0 now
Max_data_length: 12562051583
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: NULL
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
ERROR:
No query specified

checking the memory used again using pmap


pmap -x 20128 | grep anon | awk 'BEGIN {total=0}{total=total+$2}END {print total}'
39836 ==>used memory reduced now.

Read Full Post »